Maximum values possible in a WHERE IN query

Question

I have a table with over 3000000 entries, and i need to delete 500000 of them with given ID's. My idea is to create a query like:

DELETE FROM TableName WHERE ID IN (id1, id2, ...........)

which I generate with a simple C# code. The question is: is there a limit to how many values I can set in the array of ID's.

And if someone have a better way to achieve this delete more efficiently I'm open to ideas.

Hows does you C# code determine the list of ids? If it can be specified in T-SQL as a combination of where/having clauses and/or sub queries that may be a better approach. — dkackman, Apr 23 '11 at 17:09
possible duplicate of [Maximum size for a SQL Server Query? IN clause? Is there a Better Approach](http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach) — Martin Smith, Apr 23 '11 at 20:41
The C# code is extracting the id's from an data origin source (XML file), all who stand some condition. — Noam Shaish, Apr 24 '11 at 07:46

Robert Koritnik · Answer 1 · 2011-04-23T17:20:35.297

1

If your IDs can't be determined with whatever comparison (as in WHERE ID < 1000000) you could

INSERT them into a temp table with multiple inserts and then
JOIN this temp table to yours

But inserts may become problematic. You should check that. How could you speed this thing up?

make deletes in several bulks
insert IDs into temp table in bulks

edited Apr 23 '11 at 17:20

answered Apr 23 '11 at 17:14

Robert Koritnik

103,639
52
277
404

If you were just deleting all ids less than 1000000 I don't see why you would introduce a temp table at all. – Martin Smith Apr 23 '11 at 17:35
So it shouldn't be an ID list, but rather an *ID range* list, I think. – Andriy M Apr 23 '11 at 18:23
@Martin: I said when IDs **can't** be determined by some simple comparison... Not can. – Robert Koritnik Apr 23 '11 at 18:26
I really dont see how adding a temp table is going to improve the time it take.... creating temp table/joining on existing table/then deleteing..... while all the time i have a list of IDs i want to delete (primary keys) – Noam Shaish Apr 24 '11 at 07:49
@Noam Shaish: Querying DB several times (like 1.000.000 times) will be significantly slower than inserting IDs in a bulk into a temp table and doing a `delete` **once** for all million records. That's how it's going to improve speed. – Robert Koritnik Apr 25 '11 at 22:03

score 1 · Accepted Answer · answered Apr 24 '11 at 07:54

At the end my solution which works not so bad: 1. Sorted the ID's (to save server paging) 2. Created with C# code query's with 500 ID's in them. 3. sent the query's one by one.

I assume that when i worked with query having 1000+ ids the sql server time to process the query was slowing me down (after all any query you run in sql server is being process and optimized).

I Hope this help someone

Maximum values possible in a WHERE IN query

2 Answers2