For example in the following query
SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen', 'Smith')
Assuming LastName is indexed, is the single query more efficient than doing three separate queries on LastName?
Doing a single query is faster than running several queries to retrieve the same amount of data, because you save on the number of network round-trips. In my experience, the number of roundtrips is usually the biggest killer of performance in multi-query scenarios.
Comparing to a single query with three conditions, like this,
SELECT * FROM Persons WHERE LastName='Hansen' OR LastName='Pettersen' OR LastName='Smith'
is different: most optimizers figure out that it is the same as the
IN query, so your performance stays the same.
If you will create index in column it will affect your performance.. You can use non-clustered indices on any foreign key columns.
This will help you..
try to implement several queries like this some of with index and some of without index.. You will know difference..
In this specific case, the answer is probably yes: As Dems said:
1 network round trip instead of 3, 1 parse overhead instead of three, 1 travel of the index instead of 3
But that's not true for the general case. Examples:
INclause. Oracle limits you to 1000. So even if it would be faster to do in one query, you can't always.
INis a sub query it can be faster to run that sub query, process the results in your app and build a second query with a value list instead (usually because of index and/or type casting issues).