Query Optimisation - 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

Other SQL Server Topics (2005)

Query Optimisation

Author

Topic

DanielLH
Starting Member

4 Posts

Posted - 2008-08-15 : 04:17:28

Hi,

A few questions regarding some queries....

1. Which of the following, would be the best approach in terms of speed... (The example shows 9 IDs, but the query could contain hundreds of sequential numbers)

a) SELECT BLAH FROM TABLE WHERE BLAHID IN (1,2,3,4,5,6,7,8,9)
b) SELECT BLAH FROM TABLE WHERE BLAH ID >=1 AND BLAHID <=9

Also, I discovered that in MSSQL 2008 you can multiple INSERTs as follows...

INSERT INTO TABLE (A,B,C) VALUES (1,2,3), (4,5,6), (7,8,9)

I tried in 2005 with no luck. Anyone know any different?

Any help much appreciated.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 04:45:19

INSERT INTO TABLE (A,B,C)
SELECT 1,2,3 UNION ALL
SELECT 4,5,6 UNION ALL
SELECT 7,8,9

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 04:45:51

See execution plan if there is any difference between the two queries.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 05:06:00

For IN query, you get this execution plan
|--Index Seek(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]), SEEK:([master].[dbo].[spt_values].[number]=(1) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(2) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(3) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(4) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(5) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(6) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(7) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(8) AND [master].[dbo].[spt_values].[type]=N'P' OR [master].[dbo].[spt_values].[number]=(9) AND [master].[dbo].[spt_values].[type]=N'P') ORDERED FORWARD)

and for the comparison query you get this execution plan
|--Clustered Index Seek(OBJECT:([master].[dbo].[spt_values].[spt_valuesclust]), SEEK:([master].[dbo].[spt_values].[type]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0) AND [master].[dbo].[spt_values].[number] >= CONVERT_IMPLICIT(int,[@2],0) AND [master].[dbo].[spt_values].[number] <= CONVERT_IMPLICIT(int,[@3],0)) ORDERED FORWARD)

But as with so many other things, this depends on present indexes.

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

DanielLH
Starting Member

4 Posts

Posted - 2008-08-15 : 07:41:43

Peso, many thanks for your help.

I've not touched Execution Plans before, and just attempted to run the 2 queries and compare the execution plans. It seems that the values for CPU, IO and operator costs are much higher for a <= => than using IN, suggesting that IN would be fastest method, however from the plans you've posted, I would assume that the <= => method would be faster. Any comments?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 07:57:35

The second query will be faster due the the present clustered index on master..spt_values table.
You will have to investigate your table to see which indexes (if any) are beig used.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources