asked on

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

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

higginspi

I agree with Lowfatspread. I have much better performance with EXISTS than with IN.

vgoot

ASKER

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

higginspi

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.

tony_o1

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

ispaleny

This will give you power to solve your problem:

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

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

ispaleny

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

https://www.experts-exchange.com/questions/20516352/Using-a-List-generated-from-a-column-in-the-where-clause.html

Good luck!

CleanupPing

vgoot:
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
EXPERTS:
Post your closing recommendations! No comment means you don't care.

monosodiumg

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.

monosodiumg
EE Cleanup Volunteer