Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Decrease Query execution time. Expand / Collapse
Author
Message
Posted Monday, October 4, 2010 3:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 19, 2013 11:09 PM
Points: 11, Visits: 88
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
Post #997426
Posted Monday, October 4, 2010 5:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 7,147, Visits: 13,213
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.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #997459
Posted Monday, October 4, 2010 5:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #997471
Posted Tuesday, October 5, 2010 2:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:45 AM
Points: 53, Visits: 87
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,
Post #998155
Posted Tuesday, October 5, 2010 2:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #998159
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse