• 0

Question about SQL SERVER 2000


Question

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

  • 0
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!

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

  • 0
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

  • 0
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

  • 0
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

  • 0
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

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.