doing a select IN with a variable: IN (@myList)

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 2000 Forums

Transact-SQL (2000)

doing a select IN with a variable: IN (@myList)

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-10-03 : 10:28:43

Hi,
I have a variable that contains the list of my values like this:

@myList = 'val1',val2', 'val3'

So @myList is a string containing the list of values seperated by commas

I need to use @myList in the query:
select * from MyTable where myColumn IN (@myList)

How do I achieve that pls?

Because the query I wrote above doesn t work.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 11:08:26

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 14:40:07

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 16:21:21

Select * from table1 where ',' + @mylist + ',' like '%,' + col1 + ',%'

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 02:32:58

quote:
Originally posted by Peso

Select * from table1 where ',' + @mylist + ',' like '%,' + col1 + ',%'

E 12°55'05.25"
N 56°04'39.16"

As sommarskog says, dynamic sql in this case is much faster

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-05 : 10:13:14

"As sommarskog says, dynamic sql in this case is much faster"

I'm surprised.

You mean either dynamic SQL for

1) "SELECT ... IN (" & MyList & ")"

or 2) Peso's "LIKE" solution?

I was doing some work with a client yesterday. They had an IN list using dynamic SQL (1); the IN list had about 200 items in it, and the PARSE time for SQL Server was 1.5 SECONDS!!

Replacing that with a join to temp table, or a Split List, cut the parse to a few MILLISECONDS (the runtime in both cases was a few MS)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-05 : 10:25:59

You definitely want to parse this into a temp table or table variable and join to that, that will be the cleanest, clearest, and most efficient.

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 11:08:39

quote:
Originally posted by Kristen

"As sommarskog says, dynamic sql in this case is much faster"

I'm surprised.

You mean either dynamic SQL for

1) "SELECT ... IN (" & MyList & ")"

or 2) Peso's "LIKE" solution?

I was doing some work with a client yesterday. They had an IN list using dynamic SQL (1); the IN list had about 200 items in it, and the PARSE time for SQL Server was 1.5 SECONDS!!

Replacing that with a join to temp table, or a Split List, cut the parse to a few MILLISECONDS (the runtime in both cases was a few MS)

Kristen

Dynamic SQL in this case is faster than the one used by Peso (not considering having temp table or other methods)

Read http://www.sommarskog.se/arrays-in-sql-2000.html#realslow

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-05 : 11:30:31

Well, that;s not my experience. A long "IN" list kills the Parse time ...

The IN list I was using yesterday was for an INT Column which was the PK (clustered index). So there was no problem in quickly finding lots of records via the PK, but it was the Parse that took the time.

I'll knock up a test when I have a moment

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 07:37:41

OK, I ran a little test


CREATE TABLE TEMP_071006
(
	T_ID int IDENTITY(1,1) NOT NULL,
	T_Stuff varchar(2000) NOT NULL,	-- Force "realistic" records / page
	PRIMARY KEY
	(
		T_ID
	)
)
GO
INSERT TEMP_071006(T_Stuff)
SELECT	REPLICATE('a', 1998)
FROM	NumbersTable
WHERE	ID BETWEEN 1 AND 4000

I emptied cache etc. before each test to get a level playing field


SELECT	T_Stuff
FROM	dbo.TEMP_071006
WHERE	T_ID = 2000

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TEMP_071006'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms

Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__20CCCE1C]),
SEEK:([TEMP_071006].[T_ID]=Convert([@1])) ORDERED FORWARD)

So single record uses PK


SELECT	T_Stuff
FROM	dbo.TEMP_071006
WHERE	T_ID IN (1000,2000)

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'TEMP_071006'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms

Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__20CCCE1C]),
SEEK:([TEMP_071006].[T_ID]=1000 OR [TEMP_071006].[T_ID]=2000) ORDERED FORWARD)

So we are getting an OR for the query plan for 2 records; fair enough.


SELECT	T_Stuff AS X1
FROM	dbo.TEMP_071006
		-- 100 IDs
WHERE	T_ID IN (40,80,...,4000)

SQL Server parse and compile time:
CPU time = 31 ms, elapsed time = 37 ms.
Table 'TEMP_071006'. Scan count 100, logical reads 400, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 20 ms, elapsed time = 20 ms.

Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__22B5168E]),
SEEK:([TEMP_071006].[T_ID]=40 OR [TEMP_071006].[T_ID]=80 OR ... [TEMP_071006].[T_ID]=4000) ORDERED FORWARD)

Same parse time (25-ish ms) every time I run it - i.e. I'm the only user on the DB, and even if I do not clear cache.


ELECT	T_Stuff AS X1
FROM	dbo.TEMP_071006
		-- 200 IDs
WHERE	T_ID IN (20,40,60,80,...,4000)

SQL Server parse and compile time:
CPU time = 109 ms, elapsed time = 253 ms.
Table 'TEMP_071006'. Scan count 200, logical reads 800, physical reads 182, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 20 ms, elapsed time = 342 ms.
Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__22B5168E]),
SEEK:([TEMP_071006].[T_ID]=20 OR [TEMP_071006].[T_ID]=40 OR ....

Parse time going up sharpish as the number of IN keys increases


SELECT	T_Stuff AS X1
FROM	dbo.TEMP_071006
		-- 200 IDs
WHERE	T_ID IN (SELECT T_ID FROM #TEMP)

SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 104 ms.
Table 'TEMP_071006'. Scan count 200, logical reads 1048, physical reads 91, read-ahead reads 91.
Table '#TEMP__00000001C07A'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 218 ms.

|--Nested Loops(Inner Join, OUTER REFERENCES:([#TEMP].[T_ID]) WITH PREFETCH)
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#TEMP__00000001C07A].[PK__#TEMP__293D6D32]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__22B5168E]),
SEEK:([TEMP_071006].[T_ID]=[#TEMP].[T_ID]) ORDERED FORWARD)

IN table of 200 IDs instead still has length elapsed time for the parse (but not much CPU), not quite sure why, but the average is consistent


SELECT	T_Stuff AS X1
FROM	dbo.TEMP_071006 AS T1
		-- 200 IDs
	JOIN #TEMP AS T2
		ON T2.T_ID = T1.T_ID

SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 91 ms.
Table 'TEMP_071006'. Scan count 200, logical reads 1048, physical reads 91, read-ahead reads 91.
Table '#TEMP_00000001C07A'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 288 ms.

|--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[T_ID]) WITH PREFETCH)
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#TEMP_00000001C07A].[PK__#TEMP__293D6D32] AS [T2]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__22B5168E] AS [T1]),
SEEK:([T1].[T_ID]=[T2].[T_ID]) ORDERED FORWARD)

Simple join has same query plan as the IN Table, and same Parse and Run-times

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 08:00:05

Re-run on a fire-breathing dragon running SQL2005. No Drop Cache used (ermm ... there's a good reason for that

)

1:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'TEMP_071006'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__2A164134]),
SEEK:([MyDB].[dbo].[TEMP_071006].[T_ID]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

2:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'TEMP_071006'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__2A164134]),
SEEK:([MyDB].[dbo].[TEMP_071006].[T_ID]=(1000) OR [MyDB].[dbo].[TEMP_071006].[T_ID]=(2000)) ORDERED FORWARD)

3:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 23 ms.
Table 'TEMP_071006'. Scan count 0, logical reads 315, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1368 ms.

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003], [Expr1004]) WITH UNORDERED PREFETCH)
|--Constant Scan(VALUES:(((40)),((80)),...,((4000))))
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__2A164134]),
SEEK:([MyDB].[dbo].[TEMP_071006].[T_ID]=[Expr1003]) ORDERED FORWARD)

NOTE: This is across Internet, so the time to display the records is significant here

4:

SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 27 ms.
Table 'TEMP_071006'. Scan count 0, logical reads 621, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 2760 ms.

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003], [Expr1004]) WITH UNORDERED PREFETCH)
|--Constant Scan(VALUES:(((20)),((40)),((60)),((80)),...,((4000))))
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__2A164134]),
SEEK:([MyDB].[dbo].[TEMP_071006].[T_ID]=[Expr1003]) ORDERED FORWARD)

Compared to the others there is some REAL time in the Parse for this one, but only slightly more for the 200 INs than the previous 100 INs

5:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 12 ms.
Table 'TEMP_071006'. Scan count 0, logical reads 621, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TEMP_000000000610'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2735 ms.

|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[#TEMP].[T_ID], [Expr1006]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#TEMP]))
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__2A164134]),
SEEK:([MyDB].[dbo].[TEMP_071006].[T_ID]=[tempdb].[dbo].[#TEMP].[T_ID]) ORDERED FORWARD)

Again, some some time in the Parse here too, but only Elapsed, not CPU

6:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
Table 'TEMP_071006'. Scan count 0, logical reads 621, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TEMP_000000000610'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2719 ms.

|--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[T_ID], [Expr1004]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#TEMP] AS [T2]))
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[TEMP_071006].[PK__TEMP_071006__2A164134] AS [T1]),
SEEK:([T1].[T_ID]=[tempdb].[dbo].[#TEMP].[T_ID] as [T2].[T_ID]) ORDERED FORWARD)

Same query plan, but NOT identical. Dunno if that means they both share the same cache'd entry in SQL 2000 but not in SQL 2005 [and that's probably splitting hairs anyway]. Again, some some time in the Parse here too, but only Elapsed, not CPU

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources