CTE performance

Question

CTE performance

Viewing 15 posts - 16 through 30 (of 64 total)

You must be logged in to reply to this topic. Login to reply

kazim.raza SSCrazy Points: 2390 More actions · Answer 1

Um.. I am trying to get those but my staging server is offline right now; I'll make an attempt again. B/w you want those 161 K rows with proper data? I'll stuff some random entries there as that's propriety stuff, you know! 🙂

Oh yeah; I have been looking at your tally table and incorporating it in my code. I am considering to join it with #Hie and then get it.. say what?

actually I can do that with CTE eliminating #HIE too. let me give it a shot.

Thanks,

Kazim Raza

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

kazim.raza (5/9/2010)
Um.. I am trying to get those but my staging server is offline right now; I'll make an attempt again. B/w you want those 161 K rows with proper data? I'll stuff some random entries there as that's propriety stuff, you know! 🙂
Oh yeah; I have been looking at your tally table and incorporating it in my code. I am considering to join it with #Hie and then get it.. say what?
actually I can do that with CTE eliminating #HIE too. let me give it a shot.
Thanks,
Kazim Raza

Now you're thinking! I'll keep working on it even though you are, as well, because the other thing you'll need for this is a nice Cross Tab.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Heh... there's a fly in the ointment, Kazim. I thought that OrgEntityID was supposed to be unique according to the configuration of your CTE. The data you gave me has dupes in OrgEntityID. How do you want to handle that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kazim.raza SSCrazy Points: 2390 More actions · Answer 4

OrgEntityID has to be unique by all means, can you please tell me the ID that's occurring more than once?

Thanks,

Kazim Raza

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

kazim.raza (5/9/2010)
OrgEntityID has to be unique by all means, can you please tell me the ID that's occurring more than once?
Thanks,
Kazim Raza

There are 2554 in the text file you attached. I used the following code to find them.

SELECT OrgEntityID FROM #Org GROUP BY OrgEntityID HAVING COUNT(*) > 1 ORDER BY OrgEntityID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kazim.raza SSCrazy Points: 2390 More actions · Answer 6

Jeff, Can you please fix this one? I have a feeling if we fix this one we'll be through at least as a step 1.

if OBJECT_ID(N'tempdb..#a', N'U') is not null drop table #a

create table #a( a nvarchar(1000))

Insert Into #a

select '.A.A 02.A 02D.A 251487.Jeff Moden 1.'

Union all

select '.A.A 02.A 02D.A 251487.Jeff Moden 1.'

union all

select '.A.A 02.A 02D.A 251487.Jeff Moden 1.'

SELECT [1] as Column1, [2] as Column2, [3] as Column3, [4] as Column4, [5] as Column5

FROM (

SELECT

SUBSTRING(#a.a,N+1,CHARINDEX('.',#a.a,N+1)-N-1) as splitt

,row_number() over(order by N) as level

--,LEN(#a.a)

--,SUBSTRING(#a.a,N,1)

--,N

FROM #a, (

select top 1000 row_number() over (order by id) N

from syscolumns

) Tally

WHERE N <LEN(#a.a)

AND

SUBSTRING(#a.a,N,1) = '.'

) AS Splitt

PIVOT (

max(splitt)

FOR level IN ([1],[2],[3],[4],[5])

) as pvt

~ Kazim Raza

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Sure... I'll take a look at it. What do you mean by "fix" it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kazim.raza SSCrazy Points: 2390 More actions · Answer 8

these EntityIDs have different Types with them, say Jeff Mordan is Moderator of this site but at the same time he's an author as well.

Did you have a look at the #a snippet? Do you feel fixing that bit would do the trick?

~ Kazim Raza

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

kazim.raza (5/9/2010)
these EntityIDs have different Types with them, say Jeff Mordan is Moderator of this site but at the same time he's an author as well.
Did you have a look at the #a snippet? Do you feel fixing that bit would do the trick?
~ Kazim Raza

I see lot's of things that could be wrong but I don't know 100% what you're trying to do. I see 3 identical rows of test data, the code returns only 1, and it doesn't appear to be splitting at the period properly. Soooooo, lemme ask... what is the expected output of the #a code???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kazim.raza SSCrazy Points: 2390 More actions · Answer 10

Sorry I pasted the same row and didn't change the data.

create table #a( a nvarchar(1000))

Insert Into #a

select '.A.A 02.A 02D.A 251487.Jeff Moden 1.'

Union all

select '.A.A 02.A 02D.A 251488.Jeff Moden 2.'

union all

select '.A.A 02.A 02D.A 251489.Jeff Moden 3.'

I want to plug it in the tally table bit such that it should give me this result

Column1Column2Column3Column4Column5

A A 02A 02DA 251487Jeff Moden 1

A A 02A 02DA 251488Jeff Moden 2

A A 02A 02DA 251489Jeff Moden 3

~ Kazim Raza

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Correction... it's splitting at the period properly but because of the exact duplication of the 3 rows, it's not returning the correct levels. What do you want to do about the exact duplication of the 3 rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Sorry... our posts overlapped... I'll take a look.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Almost there... just another 10 or so minutes...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kazim.raza SSCrazy Points: 2390 More actions · Answer 14

Please take your time I am here...b/w, I am going to apply this thin on #HIE or the CTE , just doing a dry run on #a with 3 records.

~ Kazim Raza

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 15

kazim.raza (5/9/2010)
Please take your time I am here...b/w, I am going to apply this thin on #HIE or the CTE , just doing a dry run on #a with 3 records.
~ Kazim Raza

Apply this to #a in your example and, IIRC, you'll apply this technique on the #HIE table you created with the CTE... or.... you can add the CTE to the top of this particular food chain...

Here's the code for the #a demo... It provides the output you asked for which I also believe is correct to feed your "architect" with. 😀

WITH

ctePreNumber AS

( --=== Add a row number to each "a" so we can repivot after the split

SELECT ROW_NUMBER() OVER (ORDER BY a.a) AS RowNum,

a.a AS StringToSplit

FROM #a AS a

)

,

cteSplit AS

( --=== Obviously, this does the split

SELECT pn.RowNum,

SUBSTRING(pn.StringToSplit,N+1,CHARINDEX('.',pn.StringToSplit,N+1)-N-1) AS SplitData,

ROW_NUMBER() OVER (PARTITION BY pn.StringToSplit ORDER BY N) AS Level

FROM ctePreNumber AS pn

CROSS JOIN

( --=== This makes for an inline Tally table

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N

FROM Master.sys.All_Columns

) t

WHERE t.N < LEN(pn.StringToSplit)

AND SUBSTRING(pn.StringToSplit, t.N, 1) = '.'

) --=== This is a CrossTab to replace the Pivot. CrossTabs are faster than Pivots

SELECT MAX(CASE WHEN Level = 1 THEN SplitData ELSE NULL END) AS Column1,

MAX(CASE WHEN Level = 2 THEN SplitData ELSE NULL END) AS Column2,

MAX(CASE WHEN Level = 3 THEN SplitData ELSE NULL END) AS Column3,

MAX(CASE WHEN Level = 4 THEN SplitData ELSE NULL END) AS Column4,

MAX(CASE WHEN Level = 5 THEN SplitData ELSE NULL END) AS Column5

FROM cteSplit

GROUP BY RowNum

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)