• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql

 
Manuel Paco
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Help please.
I have just two values Yes/No.

What is faster approach?
SELECT * FROM table WHERE smoker = 'No'
or
SELECT * FROM table WHERE smoker LIKE 'N%'
Thank you.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are using an Oracle database, then you can test it yourself very easily (in SQL*Plus). Just time both queries by issuing the command:

before running the queries.
[Or am I missing something?]
Hope this helps you.
Good Luck,
Avi.
 
unni krishnan
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Manuel Paco:
Help please.
I have just two values Yes/No.

What is faster approach?
SELECT * FROM table WHERE smoker = 'No'
or
SELECT * FROM table WHERE smoker LIKE 'N%'
Thank you.
 
unni krishnan
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Manuel Paco:
Help please.
I have just two values Yes/No.

What is faster approach?
SELECT * FROM table WHERE smoker = 'No'
or
SELECT * FROM table WHERE smoker LIKE 'N%'
Thank you.


the secound one is the faster access
thank you
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Unni,
I'd really appreciate it if you would show how you arrived at your conclusion.
Thankyou,
Avi.
 
Michael Zalewski
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In general, the LIKE operator will be an order of magnitude slower than an = operator. Also, a LIKE operator will not be able to take advantage of any indexes which may exist on the operand columns.
However, your sample is so simple, you might not be able to find a difference. For example, if you time the two queries in Oracle, I bet the second one (whichever one is run second) will run fastest. That's because the server will have cached all the results from the first query, then the second query only needs to retrieve from the server cache.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic