Performance question
-
Does anybody know if there is a performance difference between these two example queries:
SELECT * FROM tblFox WHERE stringFieldValue IN ('Ring-ding-ding-ding-dingeringeding!', 'Wa-pa-pa-pa-pa-pa-pow!')
SELECT * FROM tblFox WHERE stringFieldValue='Ring-ding-ding-ding-dingeringeding!' OR stringFieldValue= 'Wa-pa-pa-pa-pa-pa-pow!'
???? I would Google it if I knew what to enter to get a relevant answer... :sigh:
Anything that is unrelated to elephants is irrelephant
Anonymous
-----
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-----
I'd just like a chance to prove that money can't make me happy.
Me, all the time -
Does anybody know if there is a performance difference between these two example queries:
SELECT * FROM tblFox WHERE stringFieldValue IN ('Ring-ding-ding-ding-dingeringeding!', 'Wa-pa-pa-pa-pa-pa-pow!')
SELECT * FROM tblFox WHERE stringFieldValue='Ring-ding-ding-ding-dingeringeding!' OR stringFieldValue= 'Wa-pa-pa-pa-pa-pa-pow!'
???? I would Google it if I knew what to enter to get a relevant answer... :sigh:
Anything that is unrelated to elephants is irrelephant
Anonymous
-----
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-----
I'd just like a chance to prove that money can't make me happy.
Me, all the timeI did not know exactly that there is performance difference. But query using IN will reduce your typing and looks good. If there are few more data to search in OR condition, You type field name again and again??
-
Does anybody know if there is a performance difference between these two example queries:
SELECT * FROM tblFox WHERE stringFieldValue IN ('Ring-ding-ding-ding-dingeringeding!', 'Wa-pa-pa-pa-pa-pa-pow!')
SELECT * FROM tblFox WHERE stringFieldValue='Ring-ding-ding-ding-dingeringeding!' OR stringFieldValue= 'Wa-pa-pa-pa-pa-pa-pow!'
???? I would Google it if I knew what to enter to get a relevant answer... :sigh:
Anything that is unrelated to elephants is irrelephant
Anonymous
-----
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-----
I'd just like a chance to prove that money can't make me happy.
Me, all the timeDepends on what database you're using and what indexes you have in place, but normally the query optimizer would realize that they are the same thing.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller
-
Does anybody know if there is a performance difference between these two example queries:
SELECT * FROM tblFox WHERE stringFieldValue IN ('Ring-ding-ding-ding-dingeringeding!', 'Wa-pa-pa-pa-pa-pa-pow!')
SELECT * FROM tblFox WHERE stringFieldValue='Ring-ding-ding-ding-dingeringeding!' OR stringFieldValue= 'Wa-pa-pa-pa-pa-pa-pow!'
???? I would Google it if I knew what to enter to get a relevant answer... :sigh:
Anything that is unrelated to elephants is irrelephant
Anonymous
-----
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-----
I'd just like a chance to prove that money can't make me happy.
Me, all the timeJohnny J. wrote:
Does anybody know if there is a performance difference between these two example queries:
Not so much difference I think. But I prefer
INinstead ofORin this case. UsingINyou could write cleaner query. What would you do if there're 10+ORconditions in your query?Johnny J. wrote:
I would Google it if I knew what to enter to get a relevant answer... :sigh:
I tried http://www.google.com/search?q=SQL+IN+vs+OR[^] & got this one SQL Speed Test: IN vs OR[^]
thatraja
Code converters | Education Needed No thanks, I am all stocked up. - Luc Pattyn When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
-
Does anybody know if there is a performance difference between these two example queries:
SELECT * FROM tblFox WHERE stringFieldValue IN ('Ring-ding-ding-ding-dingeringeding!', 'Wa-pa-pa-pa-pa-pa-pow!')
SELECT * FROM tblFox WHERE stringFieldValue='Ring-ding-ding-ding-dingeringeding!' OR stringFieldValue= 'Wa-pa-pa-pa-pa-pa-pow!'
???? I would Google it if I knew what to enter to get a relevant answer... :sigh:
Anything that is unrelated to elephants is irrelephant
Anonymous
-----
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-----
I'd just like a chance to prove that money can't make me happy.
Me, all the timeIf you are using SQL Server, you could use "Show Execution Plan" and see if there is a difference. If you are using Oracle, I believe there is a 1000 element limit to the "IN" clause. At a previous company I worked for we got bit by this. (This was a while ago, maybe Oracle 9, it may not be an issue now)
-
If you are using SQL Server, you could use "Show Execution Plan" and see if there is a difference. If you are using Oracle, I believe there is a 1000 element limit to the "IN" clause. At a previous company I worked for we got bit by this. (This was a while ago, maybe Oracle 9, it may not be an issue now)
Oracle still has this limit (in older versions it was 256 elements) The purpose with the limit is that parsing the query is quite resource demanding and if you're having more than 1000 elements in an in statement they're claiming that you're doing something wrong anyway. Use an array instead.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller