Query seems very slow for what its doing

Question

Post reply

Query seems very slow for what its doing

Viewing 3 posts - 16 through 17 (of 17 total)

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

Paul8112 SSC Enthusiast Points: 154 More actions · Answer 1

Thanks to both for your help. Will experiment and let you know the outcome.

Regards,

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

This part of the code is going to cause problems:

WHERE

p.RegionID = COALESCE(@RegionID, p.RegionID)

AND

p.PriceBracketID = COALESCE(@PriceBracketID, p.PriceBracketID)

If you get index use ata ll on this table, it's going to be an index scan instead of an index seek.

For max performance on a catch-all query, as much of a pain as it is, you'd be better off with ad hoc queries.

"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

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

I'm not sure why people think they need to do everything in a single query. Use the input parameters to get only the data you need from the properties table and jam it into a temp table. Then, you join to that relatively small temp table and things will move on rather smartly.

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