MSDE, SELECT WHERE IN (list_of_args) clause, slow response time

Posted on 2003-03-17
Medium Priority
Last Modified: 2010-05-18
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,
Vitaly Goot
Question by:vgoot
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 100 total points
ID: 8152117
IN is not really thought for 1000 arguments, you should use the following instead:
create a "temporary" table with 1 column, and insert the 1000 values in there. The SQL should join the data table with this temporary table. Last step is obviously to drop the temporary table to cleanup.
LVL 50

Assisted Solution

Lowfatspread earned 100 total points
ID: 8152216
try  the temporary table idea, but using exists may give
better performance...

Select ComponentId
FROM ComponentTable
WHERE Exists (Select componentid from list_of_componentID
    where componenttable.componentid=list_of_componentid.componentid)



Expert Comment

ID: 8152235
I agree with Lowfatspread.  I have much better performance with EXISTS than with IN.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 8153662
ok, I tested this solution with profiler. The overall process took 1000 - 1200 ms (major time did insert). So the next question is how I can improve the insert time, I saw different methods (insert, bulk insert, connection execute or command execute methods), any hints ?

BTW: difference between EXISTS and IN statements was minor.

Vitaly Goot

Expert Comment

ID: 8154555
Your best bet for increasing the performance is as angelll suggests. Put the compenents in the list in a temp table.  Also, see if the putting a primary key or index on the temp table helps.  It will increase the insert time, but decrease the time it takes to select them in your query.


Expert Comment

ID: 8155443
The msde also supports indexed views..... you could try creating an indexed view using a nolock compiler directive for your select statements..... this should speed selects greatly

as far as the insertions goe.... off the cuff i would suggest concantenating the sql statements that do the inserts in to on big string and the using the execute statement. Not doing the inserts one at a time in a loop.
(Quick and dirty i know)

If i'm off base here here could you give me a better idea of the datasource you inserting from, xml, csv, in memory array??

LVL 13

Expert Comment

ID: 8155982
This will give you power to solve your problem:

1. index temp table and join it.
2. use transaction
3. if it cause a deadlock, add nolock table option
"FROM ComponentTable (nolock)"

begin tran
set nocount on
create table #test(x int primary key)
insert #test(x) values (1)
insert #test(x) values (2)
insert #test(x) values (3)
insert #test(x) values (4)
insert #test(x) values (5)
insert #test(x) values (6)
insert #test(x) values (7)
insert #test(x) values (8)
insert #test(x) values (9)
insert #test(x) values (10)
insert #test(x) values (11)
insert #test(x) values (12)
insert #test(x) values (13)
insert #test(x) values (14)
insert #test(x) values (15)
insert #test(x) values (16)
insert #test(x) values (17)
insert #test(x) values (18)
set nocount off
SELECT ComponentTable.componentID
FROM ComponentTable
JOIN #test on ComponentTable.componentID=#test.componentID
drop table #test
commit tran
LVL 13

Expert Comment

ID: 8155996
B, If you want set pass comma-delimited string to SQL,
consider usage of function returning indexed table.


Good luck!

Expert Comment

ID: 9275807
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
LVL 12

Expert Comment

ID: 11183377
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: angelIII http:#8152117, Lowfatspread http:#8152216

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question