Large dynamic query optimization questions...

Question

Large dynamic query optimization questions...

Jim Mace

SSC Eights!

Points: 820
More actions
June 22, 2007 at 2:20 pm

#177196

I inherited an application that was coded by an individual who made a lot of dynamic queries, and was really a backyard administrator. Anyway, I have a stored procedure that takes a dynamically generated where clause as a parameter that has hundreds if not thousands of OR operations on a single primary key field. For example, "AND (RecurrentOutcomeParticipants.ProgramID = 2732 OR RecurrentOutcomeParticipants.ProgramID = 1247 OR RecurrentOutcomeParticipants.ProgramID = 1253 OR RecurrentOutcomeParticipants.ProgramID = 1277 OR RecurrentOutcomeParticipants.ProgramID = 1280 OR RecurrentOutcomeParticipants.ProgramID = 1389 OR RecurrentOutcomeParticipants.ProgramID = 1469 OR RecurrentOutcomeParticipants.ProgramID = 1472 OR RecurrentOutcomeParticipants.ProgramID = 1519 OR RecurrentOutcomeParticipants.ProgramID = 1554 OR RecurrentOutcomeParticipants.ProgramID = 1555 OR RecurrentOutcomeParticipants.ProgramID = 1556 OR RecurrentOutcomeParticipants.ProgramID = 1611 OR RecurrentOutcomeParticipants.ProgramID = 1613 OR RecurrentOutcomeParticipants.ProgramID = 1625 OR RecurrentOutcomeParticipants.ProgramID = 1626 OR RecurrentOutcomeParticipants.ProgramID = 1628 OR RecurrentOutcomeParticipants.ProgramID = 1678 OR RecurrentOutcomeParticipants.ProgramID = 1711 OR RecurrentOutcomeParticipants.ProgramID = 1722 OR RecurrentOutcomeParticipants.ProgramID = 1726 OR RecurrentOutcomeParticipants.ProgramID = 1732...)"
I was just wondering if there was a more efficient way to do this kind of query.
Thanks,
Jim

Viewing 5 posts - 1 through 4 (of 4 total)

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

VadimK Mr or Mrs. 500 Points: 551 More actions · Answer 1

I would create a temp table, dynamicaly add ProgramID values in it, index it and then use it in INNER JOIN to eliminate unwanted records.

HTH

Jim Mace SSC Eights! Points: 820 More actions · Answer 2

I think that's pretty much what is happening because he is using this select statement as a table in a inner join.

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

Doesn't help the processing speed at all but does take less size for the dynamic SQL, the WHERE clause should be constructed more like the following...

FROM RecurrentOutcomeParticipants rop

WHERE rop.ProgramID IN

(2732,1247,1253,1277,1280,1389,1469,1472,1519,1554,1555,1556,1611,1613,1625,1626,1628,1678,1711,1722,1726,1732...)"

...BUT only if it's being called from a GUI... if it's being called from another stored proc, the numbers should be stored in a temp table and then joined to as previous suggested... that's not so much for speed as it is to aid in troubleshooting by getting rid of dynamic SQL.

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

Even using a huge IN statement is a bad idea. You have two problems - the first is that SQL 7 (and 2000 to some extent) does a bad job of managing this type of statement. What it can put into memory, it does, and then it spills over into TempDB, but it does not manage to order or index anything. So, very large lists in an IN statement are very inefficient and should be replaced by a table variable or temp table that can be put into a usable order.

The second problem - that I have experienced in a legacy application - is that SQL has a buffer limit for queries. I cannot tell you exactly what it is - it may be based on the amount of memory the server has, but when you reach it, you will know it. On my SQL 7 server, we found that the buffer limit of the SQL server was larger than the buffer limit of the error logging operation and the server would end up in an infinite loop trying to log the errors. It was a mess. Avoid allowing dynamic SQL that will create very large OR or IN statements - populate a temporary table and join to it and you will get much better performance and a more stable environment.