We have a sql server database (on azure) with one large table c1billion rows. Each row consists of a key char32 which is an md5 hash and a value int. All queries select multiple values from the table using a key in clause. Sometimes we have 1000+ items in the in clause and the query can be very slow. What are the top things we can do to improve query performance in terms of table structure (partitioning?) and query structure (maybe chunking queries)?

  • 2
    What does the query plan look like? What does "very slow" mean (minutes? hours?)? What sort of performance are you hoping to achieve? A single-table query using a single IN list predicate with 1000 values should just do 1000 index seeks using the primary key index which should be pretty efficient (though that's relative-- if you're counting milliseconds, efficiency is relative). – Justin Cave Mar 6 '15 at 7:07

I assume you mean a SQL Server instances hosted in an Azure VM (as opposed to a SQL Azure instance).

Other than that, approach this as any other performance investigation. Measure. Read How to analyse SQL Server performance. The solution will depend a lot on what your measurements find.

Not the answer you're looking for? Browse other questions tagged or ask your own question.