SQL: SELECT IN faster and best practice?

Question

if i m using 2 queries:

first: queries all keys/ids required

second: select * from tab1 where tab1.id in (...ids list..,,)

ids list can be several thousands...

is it wise or best practice or recommended to do things like that?

Answer 1

As a general rule, it's perfectly acceptable best practice to use an IN clause in your statements... given that you use a subquery for larger sets of values.

The answer should always be: it depends! It's not clear from your question whether your list of values would be a SELECT, or whether you'd hardcode them. It would definitely be more performant if those several thousand value were in another table (temp table or table variable) and you subqueried them. i.e.

    SELECT * FROM Customer 
    WHERE CustomerID IN (SELECT ID FROM MyOtherLargeTableOfCustomers)

• how well indexed is that column that you're performing that IN clause on?
• what's the datatype? If a numeric datatype, then you shouldn't have any performance problems.
• ensure your STATISTICS (if SQL Server) are updated regularly.
• if the datatype is char-based, or guid, you may run into some performance problems, especially when you're running multiple queries at the same time, and the list is in the thousands as you describe.
• consider JOINing those tables instead; that should always produce a faster query.

SELECT * FROM Customer AS C 
INNER JOIN MyOtherLargeTableOfCustomers AS M
ON C.CustomerID = M.CustomerID

Answer 2

if your IDs are queryable in a joinable table, using an inner join SHOULD give you the fastest query and give you a substantially smaller query to send to the db.