MSDE, SELECT WHERE IN (list_of_args) clause, slow response time
Posted on 2003-03-17
I have the following query:
SELECT componentID FROM ComponentTable WHERE componentID IN (list_of_componentID)
componentID is a primary key. Table size 70000 components.
list_of_componentID format 'item1,item2...item1000' e.g (1,2,3,4,5...,1000)
on MSDE such a query for the first time work with poor performance (12 sec for 1000 arguments), I check it on access and give very fast response. As well I tried it with different number of arguments like: Q:10 x A:100 or Q:100 x A:10 ( 'Q' number of queries, 'A' number of arguments), it was much better (2 - 4 sec) but still it is very slow for me.
I checked the execution plan and found that index is used.
My question is how I can improve the performance here ?
thanks in advance,