...and another solution using the pre-split-string table

😎

USE tempdb;

GO

DECLARE @t TABLE (Id INT)

INSERT INTO @t

SELECT 101 UNION ALL

SELECT 102 UNION ALL

SELECT 103 UNION ALL

SELECT 108;

DECLARE @t2 TABLE (CategoryId INT, Id INT)

INSERT...

Quick solution for SQL Server 2012 and later, replaces also the split into @t

😎

USE tempdb;

GO

DECLARE @t2 TABLE (CategoryId INT, Id INT)

INSERT INTO @t2

SELECT 2, 50 UNION ALL

SELECT 2, 51 UNION...

Quick thought, remove the FOR XML from the nested query.

😎

First suggestion is to add one step where the files are directly loaded into a staging table as an XML blob, without any shredding or processing. This will give you...

You are absolutely right Gianluca, the source of the problem has to be determined before applying any fixes. What I left out in the previous post was of course using...

That is correct, so be careful!

It...

Quick thought, the cost threshold for parallelism is way too low, would suggest setting it to 50 for a start. Can you provide more information on the setup?

😎

Further on Luis's post, do only one dateadd operation and use convert for the formatting.:cool:

Done this on exactly the same version/edition and the performance is brilliant so no worries there. One thing to check though is the configuration of tempdb, rule of thumb, recommend...

Further on mickyT's post, the cross row referencing window functions are perfect for this kind of queries, here is a quick sample.

😎

USE tempdb;

GO

;WITH SAMPLE_DATA(SD_ID,SD_DATE) AS

( SELECT * FROM (VALUES

...

Thanks for the question:-)

Got the same on 2014, non of the options...

Had few time-outs today, took me half an hour to respond to one post using the 3/4G on the mobile. Thought it was a carrier issue but then on a...

Grant Fritchey (10/15/2014)

---

BOR15K (10/15/2014)

---

Apologies for the complete blunt question.

I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

and all...

peacesells (10/15/2014)

---

I am bulk inserting a whole bunch of xml files into a sql table using OPENROWSET. The problem is that this process is really really slow. Any idea...

BOR15K (10/15/2014)

---

Eirikur Eiriksson (10/15/2014)

---

BOR15K (10/15/2014)

---

Apologies for the complete blunt question.

I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

and...