当前位置: 动力学知识库 > 问答 > 编程问答 >

Is SQL IN more efficient than doing individual lookups?

问题描述:

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:

  • Some databases don't allow arbitrary numbers of elements in the IN clause. Oracle limits you to 1000. So even if it would be faster to do in one query, you can't always.
  • If the expression after the IN is 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).
分享给朋友:
您可能感兴趣的文章:
随机阅读: