Recursive is query is slow

Question

Recursive is query is slow

vkundar

Hall of Fame

Points: 3431
More actions
November 11, 2008 at 8:44 am

#195694

Hi,
I have a recursive query which is very slow. AS this is a dataware housing ENV, We will not create any indexes or constraints. So the query is scanning the Table. I dont have any much knowledge in recursive query.
How I will improve the perf ?
Please find the query attached and do let me know your inputs.
Cheers,
Got an idea..share it !!
DBA_Vishal

Viewing 12 posts - 1 through 11 (of 11 total)

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

I understand why you might not build constraints in a data warehouse (sort of), but it makes no sense at all to choose not to build indexes. I mean, you want to query the data. You want to do reads against the data. I'd think indexes would be more important than they ever are on an OLTP system. I know our datamarts and our data warehouse have indexes.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

In a data warehouse environment indexes are even more important. They may get in the way of the nightly loads, but thats when you drop, load, and rebuild.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

As the others have said, indexes are important, especially in a datawarehouse.

That being said, you need to be careful with recursion as it is basically hidden RBAR. It may be that a recursive CTE is not the best performing option for this query. Of course proper indexing will definitely speed up this query.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 4

Our indexes in the Base Warehouse tend to be different than what is on Production, but they are very important - both for when we load data, and for user queries.

We also have some constraints, but these are mostly so we cannot do things like load a code which isn't in a reference table, or an item for which we have no item master record.

Might be time to use profiler, and test some indexing. And watch out for that RBAR. :w00t:

Greg E

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 5

You may also want to try creating some views.

This would seem to have some hope from the start.

Kind of a Parent Key table, if I'm reading this right.

Greg E

SET @STRATEGY_TYPE_KEY = 2

SELECT @PARENT_KEY =

(

SELECT STRATEGY_KEY FROM stage.DIM_STRATEGY

WHERE STRATEGY_ID = 'GT-406733'

AND EFCT_END_DT IS NULL

AND STRATEGY_TYPE_KEY = @STRATEGY_TYPE_KEY)

vkundar Hall of Fame Points: 3431 More actions · Answer 6

Yes you are right.

We have seen lots of sort warning in the profiler with Sub eventype = 2. As per the MS link it says we need to look into the query and thus we are planning to change the code itself. The ideas of views is good and definetly I will look into it.

Thanks every one for your valuable time.

MS link :http://msdn.microsoft.com/en-us/library/ms178041.aspx.

Cheers,

Got an idea..share it !!

DBA_Vishal

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

vkundar (11/11/2008)
Hi,
I have a recursive query which is very slow. AS this is a dataware housing ENV, We will not create any indexes or constraints. So the query is scanning the Table. I dont have any much knowledge in recursive query.
How I will improve the perf ?
Please find the query attached and do let me know your inputs.

Recursion? You might as well use a cursor especially for hierarchies like you have. Even with the proper indexes, performance will be nill. 😉

Since it's a data warehouse where much data should actually be preprocessed/precalculated and the fact the hierarchies typically don't change minute by minute, consider turning your "Adjacency Model" for the hierarchical data into a "Nested Set Model". Joe Celko has written several articles and a book on it. Here's a link to one of the articles... some of the code needs a tweek to work properly, but it's an outstanding concept for expanding hierarchies both downward and upward in the tree.

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

http://www.developersdex.com/gurus/articles/112.asp?Page=1

Celko isn't the only one that's written about it...

http://www.codeproject.com/KB/database/nestedsets.aspx

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

Ghanshyam Borasaniya SSC-Addicted Points: 407 More actions · Answer 8

Hi there,

I have good experience with oracle (start with .. connect by clause ) and SQL server hierarchical queries. Indexes must be there where parent key and child key columns which are building parent child relation.

From your attached query text, PARENT_STRATEGY_KEY and STRATEGY_KEY column looks candidate from index. You can do small POC by keeping index and without index and realize the performance.

Best regards

Ghanshyam Borasaniya

Sr. DBA.

vkundar Hall of Fame Points: 3431 More actions · Answer 9

Jeff thanks for the links.

gborasaniya you are right as this in data warehousing environment mainly in ETL we have constraints on using or creating indexes. we need to deal with raw data.

Cheers,

Got an idea..share it !!

DBA_Vishal

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

vkundar (11/18/2008)
Jeff thanks for the links.
gborasaniya you are right as this in data warehousing environment mainly in ETL we have constraints on using or creating indexes. we need to deal with raw data.

Just curious... why have they hogtied you in such a fashion? Just because something is ETL, doesn't mean that indexes shouldn't be used... especially when it comes to a data warehouse.

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

jgrubb SSCrazy Points: 2542 More actions · Answer 11

Jeff Moden (11/18/2008)
vkundar (11/18/2008)
Jeff thanks for the links.
gborasaniya you are right as this in data warehousing environment mainly in ETL we have constraints on using or creating indexes. we need to deal with raw data.
Just curious... why have they hogtied you in such a fashion? Just because something is ETL, doesn't mean that indexes shouldn't be used... especially when it comes to a data warehouse.

Agreed. Only time I'd think it makes sense to avoid indexes is if you read all the data once, and then discard. As soon as you query with criteria, gotta get you some indexes. Elsewise, you are stuck with getting better disk I/O and caching as your only options for speeding it up.