4

I have a big query to get multiple rows by id's like

SELECT * 
FROM TABLE 
WHERE Id in (1001..10000)

This query runs very slow and it ends up with timeout exception. Temp fix for it is querying with limit, break this query into 10 parts per 1000 id's.

I heard that using temp tables may help in this case but also looks like ms sql server automatically doing it underneath.

What is the best way to handle problems like this?

Sergiy Kozachenko
  • 1,399
  • 11
  • 31
  • 1
    This might help you. http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach – Felix Pamittan Jul 06 '15 at 09:57
  • Well it's not all that much work to rewrite it using a temporary table, and joining with said temporary table. You can add a primary key on Id on the temporary table for faster lookup. I'm guessing it'll run a lot faster that way in your case, having thousands of Id's. – TT. Jul 06 '15 at 11:14
  • @TT. Thanks for you answer, can you give some example of script that it will looks like. And also I am wondering, maybe ms sql server already doing it underneath, so this will be redundant or even not so effective then ms sql verion of it. Probably we need to delete the temp table after. – Sergiy Kozachenko Jul 06 '15 at 12:55
  • Do you have an index on the ID column? – Joseph Idziorek Jul 06 '15 at 12:59
  • @JosephIdziorek, yes – Sergiy Kozachenko Jul 06 '15 at 12:59

1 Answers1

2

You could write the query as follows using a temporary table:

CREATE TABLE #ids(Id INT NOT NULL PRIMARY KEY);
INSERT INTO #ids(Id) VALUES (1001),(1002),/*add your individual Ids here*/,(10000);

SELECT
  t.*
FROM
  [Table] AS t
  INNER JOIN #ids AS ids ON
    ids.Id=t.Id;

DROP TABLE #ids;

My guess is that it will probably run faster than your original query. Lookup can be done directly using an index (if it exists on the [Table].Id column).

Your original query translates to 

SELECT * 
FROM [TABLE]
WHERE Id=1000 OR Id=1001 OR /*...*/ OR Id=10000;

This would require evalutation of the expression Id=1000 OR Id=1001 OR /*...*/ OR Id=10000 for every row in [Table] which probably takes longer than with a temporary table. The example with a temporary table takes each Id in #ids and looks for a corresponding Id in [Table] using an index.

This all assumes that there are gaps in the Ids between 1000 and 10000. Otherwise it would be easier to write 

SELECT * 
FROM [TABLE]
WHERE Id BETWEEN 1001 AND 10000;

This would also require an index on [Table].Id to speed it up.

TT.
  • 15,774
  • 6
  • 47
  • 88