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)?
closed as unclear what you're asking by Colin 't Hart, ypercubeᵀᴹ, Kin Shah, RLF, RolandoMySQLDBA Mar 6 '15 at 19:06
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
I assume you mean a SQL Server instances hosted in an Azure VM (as opposed to a SQL Azure instance).
BINARY(16)for MD5 hash. Is half the size of
- Must have a clustered index on the hash
- Choose an appropriate hosting plan. Read Premium Storage: High-Performance Storage for Azure Virtual Machine Workloads
- Follow best practices. Read Performance Guidance for SQL Server in Azure Virtual Machines and Performance Best Practices for SQL Server in Azure Virtual Machine
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.