Times Out because long "IN" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Times Out because long "IN"

Author

Topic

sjoppery
Starting Member

1 Post

Posted - 2007-09-24 : 11:09:33

I currently have a SELECT statement searching a view as below. The view joins about 45 tables. The sql is created dynamically because the number of ProductIDs can vary.

SELECT *
FROM dbo.vwSearch
WHERE ProductID IN (10701,10719)
ORDER BY Tag

This works fine but the ammount of productID that are used could be any number. When lots of product IDs are used (such as 7000) then it times out because it takes too long. Can anyone help me please?

Many thanks

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 11:16:08

Might be that the query optimiser is taking ages, because of the long list, so if you get a query plan that can be changed you might fair better.

Only way to achieve that is to JOIN to a table which already has the Product ID list in it.

So you might have to make a table with columns for, say, BatchNumber and ProductID

Then insert your 7000 Product IDs, with the next available batch number

And then query:


SELECT * 
FROM dbo.MyBatchTable AS B
     JOIN dbo.vwSearch AS S
         ON S.ProductID = B.ProductID
WHERE B.BatchNo = 1234
ORDER BY Tag

Generally I would recommend passing a list of values, such as your Product Codes, in a string parameter, which you then use a SPLIT function to separate into rows. However, 7000 entries is going to exceed a varchar(8000) datatype, so I guess you'll have to try a Split function using varchar(MAX), or XML or somesuch.

Some discussion on Split function here :http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 11:16:22

Make sure there is an index on ProductID at least.

What I would do is to restructure the code. I would put my list of ProductIDs (7000 of them) into a #TempTable prior to the select and then use that #TempTable in a join to vwSearch rather than doing the massive IN you are doing.

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 11:24:03

I don't suppose many of the 7,000 ProductIDs are contiguous are they?

If so perhaps you could change the IN statement to accommodate the contiguous ranges with:

(ProductID IN (1,10,100,1000)
OR ProductID BETWEEN 2 AND 7
OR ProductID BETWEEN 12 AND 17
OR ProductID BETWEEN 102 AND 107)

to reduce the size of the WHERE clause somewhat ....

... not sure it will help performance enough, but I reckon that SQL Server is converting the IN to a long list of ORs which is giving the query optimiser a headache!

"I would put my list of ProductIDs (7000 of them) into a #TempTable"

I'm struggling a it with a method of getting that many into a #TEMP table with dynamic SQL "efficiently" ... but I'm open to ideas on that one!

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 11:31:33

Well, you could have a seperate function or sp that you pass a parameter (or parameters) to that contain your list of values for the IN statement. The sp or function puts them into a temp table to be used in the select. I mentioned restructring the code which means some stuff would need to happen before the select statement that has the IN statement.

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 11:46:35

Personally I think once the IN lists get this big there are problems trying to get the list Server-Side

I'm doing some work with a client now that does this type of processing. If I stop to think about it we';ve never had this problem, and its probably because we don't design our applications that way

The client basically does something like:

"Get me a list of all the ClientIDs who are interested in Stuff-X"

strResult = EnormousCommaDelimitedList

"Email all clients where ClientID in " & strResult

Whereas I suppose in effect out applications would do:

"Send Email to all clients in the set of [Clients interested in Stuff-X]"

so the EnormousCommaDelimitedList never got ClientSide in the first place.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources