CTE performance

Question

CTE performance

Viewing 15 posts - 46 through 60 (of 64 total)

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

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

First, it would probably run a bit faster if you didn't select so many unused columns from #Hie.

Second, since your latest query isn't actually using OrgEntityType, my suggestion would be to simply delete all but one of the dupes from the #ORG table.

--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 2

Those unused columns were just for the purpose of dev check; I am keeping FullyQualifiedName, Rownum and EntityID only.

Duplicates records need to be there as they'll be going in the Data mart. Is there anyway I can have them within this query?

~ Kazim Raza

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

Those unused columns were just for the purpose of dev check; I am only keeping FullyQualifiedName, Rownum and EntityID.

Duplicates records need to be there as they'll be going in the Data mart. Is there anyway I can have them within this query?

~ Kazim Raza

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

kazim.raza (5/10/2010)
Those unused columns were just for the purpose of dev check; I am only keeping FullyQualifiedName, Rownum and EntityID.
Duplicates records need to be there as they'll be going in the Data mart. Is there anyway I can have them within this query?
~ Kazim Raza

How are they going into the datamart? I see nothing in the final select having to do with OrgEntityType and since all the rows in the test data were duplicated for both OrgEntityID and ParentID, it won't make a difference.

--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 5

Jeff:

Each row is unique in the data set. EntityID may occur twice but it will form a unique row with Type. As far as its usage goes, if you look at the FullyQualifiedName, each period represents an EntityType having a certain level.

I am going to add combine OrgEntityID and EntityTypeID( I will include it in select) and then select only these two fields from our latest CTE.

~ Kazim Raza

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

Yo nailed it.. here's what I did

I have joined EntityType with FullyQualifiedName. The script would treat it as another extension to levels. My last select remains intact and I get all rows and no nulls. So far I haven't seen any delays, I await expert's opinion though :).

Thanks

~Kazim Raza

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

kazim.raza (5/11/2010)
Yo nailed it.. here's what I did
I have joined EntityType with FullyQualifiedName. The script would treat it as another extension to levels. My last select remains intact and I get all rows and no nulls. So far I haven't seen any delays, I await expert's opinion though :).
Thanks
~Kazim Raza

I was going to suggest something similar... if you keep track of "positions" instead of people, there's no chance of duplication. Sounds like you sussed it by making the realization that a person's name and "type" are unique and that should work fine.

Heh... but will it make our "friend" happy? 😛

--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 8

Heh... see Kazim? I'm not the only one that want's a piece of your "architect". 😉

--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 9

Um..... I have been pondering over it too.. what design would you propose keeping in view that the Cube will be processed on the data mart with the current design we have?

~ Kazim Raza

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

The one in probably the most famous article ever when it comes to how to do things using "Nested Set" hierarchies... written by none other than Joe Celko himself...

http://intelligent-enterprise.informationweek.com/001020/celko.jhtml;jsessionid=SDL2T2BF3SLWFQE1GHPSKH4ATMY32JVN

I'll also tell you that, because of the nature of your original data being in flux all the time, that I've developed a method for converting the "Adjacency List Model" (ie: Parent/Child model which you use) to Joe's famous "Nested Set" model in a very high speed fashion.

Either that or someone needs to take the time to setup a full blown OLAP hierarchy to build the cubes. It sounds like your "architect" may have been trying to backdoor such a setup.

If you setup to use a "Nested Set", a cube to support the hierarchy may become totally unnecessary. Unfortunately, that would probably also mean that the current design would have to change... a lot.

--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 11

Jeff:

its not calculating N correctly for some of the recurring entityIDs though I have extended the type along with fullyqualifiedname. I am in the mid of a deployment and I am completely lost :(... what could be going wrong? Weird though but if filter the inner query (below) with between enabled; it works well but with the entire data set it simply doesn't work.

SELECT distinct Org.*,

SUBSTRING(Org.FullyQualifiedName,N+1,CHARINDEX('.',Org.FullyQualifiedName,N+1)-N-1) AS SplitOrg, N, CHARINDEX('.',Org.FullyQualifiedName,N+1),

ROW_NUMBER() OVER (PARTITION BY Org.FullyQualifiedName ORDER BY N) AS Level

--,t.N, LEN(Org.FullyQualifiedName)

FROM (select /*top 100*/ /*#Hie.EntityName, '.' + REPLACE(FullyQualifiedName, '..', '.')*/ FullyQualifiedName FullyQualifiedName

--, #Hie.FullyQualifiedLevel, #Hie.Level OrgLevel,

,#Hie.OrgEntityID, #Hie.OrgEntityType, #Hie.RowNum from #Hie /*where RowNum < 1001*/) AS Org

CROSS JOIN Tally t

WHERE t.N < LEN(Org.FullyQualifiedName)

AND SUBSTRING(Org.FullyQualifiedName, t.N, 1) = '.' --and Org.OrgEntityID between 69760 and 69765

order by OrgEntityID, RowNum

~ Kazim Raza

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

kazim.raza

SSCrazy

Points: 2390

May 13, 2010 at 12:15 pm

#1167123

my bad; its calculating the Levels incorrectly.

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

I solved it, partitioning on FullyQualifiedName and OrgEntityID worked.

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

So, everything is "good" so far? I mean other than the original design by our mutual "friend".... 😛

--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 15

Lol... So far so good.. both the design and our 'friend' 😛

I want to discuss that with you... how about if get you the schema of our data mart? Are you willing to brainstorm on it with me?

~ Kazim Raza