Nxt_Gate Posted October 27, 2004 Share Posted October 27, 2004 Hi there, i have a simple question , i have this query and need to know which is faster Select * from student where status IN (1,2,3) OR Select * from student where (status = 1 or status = 2 or status = 3) Thanks in advance! Link to comment Share on other sites More sharing options...
0 azcodemonkey Posted October 27, 2004 Share Posted October 27, 2004 Hi there, i have a simple question , i have this query and need to know which is fasterSelect * from student where status IN (1,2,3) OR Select * from student where (status = 1 or status = 2 or status = 3) Thanks in advance! 584815399[/snapback] You could always use Query Analyzer to determine what the time might be. Ctrl-L gives you the execution plan with times for each operation. I'm guessing that the difference will be negligble, if any. You'd better your performance by losing the splat and specifying which columns you want. Link to comment Share on other sites More sharing options...
0 _kane81 Posted October 28, 2004 Share Posted October 28, 2004 I don't think it matters what statement you use. SQL Server automatically optimizes all queries, it doesnt matter what the statement is Link to comment Share on other sites More sharing options...
0 jamend Posted October 28, 2004 Share Posted October 28, 2004 Tried the profiler? Link to comment Share on other sites More sharing options...
0 azcodemonkey Posted October 28, 2004 Share Posted October 28, 2004 SQL Server automatically optimizes all queries 584819089[/snapback] It does? Splat queries are totally slower than specifying the columns you want, and SQL won't help you. The difference between IN and WHERE is probably negligible, but I've yet to see SQL optimize my queries. What do I need to turn on for that to happen? Link to comment Share on other sites More sharing options...
0 _kane81 Posted October 28, 2004 Share Posted October 28, 2004 It does? Splat queries are totally slower than specifying the columns you want, and SQL won't help you. The difference between IN and WHERE is probably negligible, but I've yet to see SQL optimize my queries. What do I need to turn on for that to happen? 584819998[/snapback] What's a splat query? you mean "SELECT *", of course it is faster to select a single column rather than an entire row. using IN and WHERE would be optimized by SQL Server as it parses the query into database level code. queries are optimizes at the database level not at the query level. an example of this is... utilization of indexes is not seen in SQL. It is done automatically when an index exists. the use of memory buffers is also not seen in SQL Link to comment Share on other sites More sharing options...
0 azcodemonkey Posted October 28, 2004 Share Posted October 28, 2004 What's a splat query? you mean "SELECT *", of course it is faster to select a single column rather than an entire row. using IN and WHERE would be optimized by SQL Server as it parses the query into database level code.queries are optimizes at the database level not at the query level. an example of this is... utilization of indexes is not seen in SQL. It is done automatically when an index exists. the use of memory buffers is also not seen in SQL 584820143[/snapback] Ah, ok. You still have to set up indexes manually, but I see what you mean now. Link to comment Share on other sites More sharing options...
0 James Rose Posted October 28, 2004 Share Posted October 28, 2004 As a generalization the IN statement is faster than a collection of OR statements. Especially for larger collections) Link to comment Share on other sites More sharing options...
0 _kane81 Posted October 28, 2004 Share Posted October 28, 2004 As a generalization the IN statement is faster than a collection of OR statements. Especially for larger collections) 584822269[/snapback] at the database level I suspect SQL Server would probably convert the IN statement into the OR statement anyway. as when you break down IN, it would be similar to an OR statement. IN is a construct to make query comparing simpliar to define. (note: I dont actually know any of this stuff as fact, I'm just assuming, but the course I went on about SQL Server did say that the engine optimizes all queries on prepared and run. I guess it is similar to the compiler optimizations on compilation. you use to use "const" to tell the compiler that this variable doesn't change, but now days the compiler automatically makes variables const if its value is never changed. I think we use const/final more to tell other programmer that they cant modify this value at design time. ) Link to comment Share on other sites More sharing options...
Question
Nxt_Gate
Hi there, i have a simple question , i have this query and need to know which is faster
Select * from student where status IN (1,2,3)
OR
Select * from student where
(status = 1 or status = 2 or status = 3)
Thanks in advance!
Link to comment
Share on other sites
8 answers to this question
Recommended Posts