April 11, 2014 at 6:15 am
I have a large database.Earlier one of my queries used to take around 20-25 minutes to execute.But as the data base is increasing by the day ,the query now takes more than 30 minutes to run and gets timed out.
What are the practices I can follow while designing a database so as to avoid such situations for as long as possible?
April 11, 2014 at 7:10 am
1. Look at the query plan for any table scans and try to restate your query to avoid them
2. Add indices to match the columns you join or search on.
3. Consider partitioning your table if it fits your scenario.
April 11, 2014 at 7:50 am
While you're at it, why don't you go ahead and post the query and definitions for the relevant tables here? I'd be willing to bet that you could get a lot of advice on how to optimize the slow-running query.
April 11, 2014 at 11:06 am
Tune the query. Tune the data structure. Tune the indexes. Keep the statistics up to date. That's about all I can based on the query running slow and that's all the information I have. I'd suggest checking out my book on query tuning in the links below. It covers all these topics and more in depth.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply