Maximum values possible in a WHERE IN query
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, ...........)
开发者_JS百科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.
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 thenJOIN
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
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
精彩评论