October 4, 2010 at 3:52 am
Hello friends,
I am facing problem since last 5-6 month in sql server in query executing it takes 10-12 mins to execute even if database is on local pc.
I want to decrease it and want to make it execte withing 30-40 sec..
It is getting data of whole month with subquery of 1-30 date and each joining 3-5 tables for 300-500 persons.
so any ideas....
One thing that i can do some process in background and insert fetched data to the some other table and at the time of executing above query fetch data from that table.
if its possible then how can i do it?Is there any other way to solve this problem?
Thanks in advance..
RAHUL PATEL
October 4, 2010 at 5:31 am
Based on the information we have so far it's almost impossible to help...
There are many possinle issues causing the bad performance:
[SET GUESSING ON]
1) poor database design (why are there 3-5 tables for 300-500 persons?)
2) poor query desgin (is that "subquery" for day 1-30 actually within some sort of a loop?)
3) missing indexes to support the query (if performance drops over time it seems like there are some indexes missing)
4) missing archiving method (if there are data in that table that aren't used frequently, it might help to move those data into an archive table or even archive db)
[SET GUESSING OFF]
Please see the 2nd link in my signature onhow to post performance issues. The more info you provide, the better we can help.
October 4, 2010 at 5:45 am
rahulpatel112 (10/4/2010)
I am facing problem since last 5-6 month in sql server in query executing it takes 10-12 mins to execute even if database is on local pc.I want to decrease it and want to make it execte withing 30-40 sec..
It is getting data of whole month with subquery of 1-30 date and each joining 3-5 tables for 300-500 persons.
so any ideas....
One thing that i can do some process in background and insert fetched data to the some other table and at the time of executing above query fetch data from that table.
if its possible then how can i do it?Is there any other way to solve this problem?
Welcome to the wonderful world of troubleshooting.
Have you taken a look at execution plan and compare it with available indexes?
Have you traced the query so to know where time is spend?
As alreary pointed out by previous poster provided information is generic, you have either to figure it out what the problem is or provide info enough for somebody to help.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 5, 2010 at 2:52 am
Hi there,
A good idea would be to post us the query to are trying to run.
You may want to omit sensitive data (if applicable).
Cheers,
October 5, 2010 at 2:58 am
Please post query, table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply