SQL Stored procedure ruinning very slow

Posted on 2013-12-12
Last Modified: 2014-02-13
Hi Everybody,

We have a timecard database and people fill in their timecards weekly. Our Head of Service wanted a report to see the percentage of each service completing their time card in previous month. I did write a stored procedure and it was taking about 10-15 seconds to execute but all of a sudden it is taking over three minutes and still not displaying any results.

Here is my query it contains somme aggregation can you please tell me the alternative query without secrificing the results?

SELECT     COUNT(CSUUsers.NewESR) * dbo.fnFindNoOfWeeks(CONVERT(datetime, CSUComp.MonthEnd, 103)) AS TotEmp, CAST(MAX(CSUComp.TotComp)
                      AS float) + CAST(MAX(ISNULL(LetOffs.LetOffs, 0)) AS float) AS Totcomp, MAX(CSUComp.TotalHours) AS TotalHours, CSUServiceAreas.ServiceArea,
                      CSUServiceAreas.ServiceID, ROUND(((MAX(CSUComp.TotComp) + MAX(ISNULL(LetOffs.LetOffs, 0))) * 100) / (COUNT(CSUUsers.NewESR)
                      * dbo.fnFindNoOfWeeks(CONVERT(datetime, CSUComp.MonthEnd, 103))), 0) AS pComp, CONVERT(datetime, CSUComp.MonthEnd, 103) AS MonthEnd
FROM         CSUServiceAreas INNER JOIN
                      CSUTeams ON CSUServiceAreas.ServiceID = CSUTeams.ServiceArea INNER JOIN
                      CSUUsers ON CSUTeams.TeamID = CSUUsers.CSUTeam INNER JOIN
                          (SELECT     COUNT(TimecardHeader.Login) AS TotComp, CSUServiceAreas_1.ServiceArea, CSUServiceAreas_1.ServiceID,
                                                   SUM(TimecardHeader.TotalHours) AS TotalHours, TimecardHeader.MonthEnd
                            FROM          CSUServiceAreas AS CSUServiceAreas_1 INNER JOIN
                                                   CSUTeams AS CSUTeams_1 ON CSUServiceAreas_1.ServiceID = CSUTeams_1.ServiceArea INNER JOIN
                                                   CSUUsers AS CSUUsers_1 ON CSUTeams_1.TeamID = CSUUsers_1.CSUTeam INNER JOIN
                                                   CSUEmployeeData ON CSUUsers_1.Login = CSUEmployeeData.Login INNER JOIN
                                                   TimecardHeader ON CSUEmployeeData.Login = TimecardHeader.Login
                            WHERE      (CONVERT(datetime, TimecardHeader.MonthEnd, 103) BETWEEN '2013-04-30' AND DATEADD(s, - 1, DATEADD(month, DATEDIFF(month, 0,
                                                   GETDATE()), 0)))
                            GROUP BY CSUServiceAreas_1.ServiceArea, CSUServiceAreas_1.ServiceID, TimecardHeader.MonthEnd) AS CSUComp ON
                      CSUServiceAreas.ServiceID = CSUComp.ServiceID LEFT OUTER JOIN
                          (SELECT     CAST(COUNT(ISNULL(TimecardLetOffs.LetOffUserLogin, 0)) AS float) AS LetOffs, CSUServiceAreas.ServiceID, TimecardLetOffs_Det.MonthEnd
                      CSUUsers ON CSUTeams.TeamID = CSUUsers.CSUTeam INNER JOIN
                      CSUServiceAreas ON CSUTeams.ServiceArea = CSUServiceAreas.ServiceID INNER JOIN
                      TimecardLetOffs ON CSUUsers.Login = TimecardLetOffs.LetOffUserLogin INNER JOIN
                      TimecardLetOffs_Det ON TimecardLetOffs.ID = TimecardLetOffs_Det.ParentID
WHERE     (TimecardLetOffs_Det.MonthEnd BETWEEN '2013-04-30' AND DATEADD(s, - 1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)))
GROUP BY CSUServiceAreas.ServiceID, TimecardLetOffs_Det.MonthEnd) AS LetOffs ON CSUServiceAreas.ServiceID = LetOffs.ServiceID
GROUP BY CSUServiceAreas.ServiceArea, CSUServiceAreas.ServiceID, CSUComp.MonthEnd

kind regards,
Question by:shah36
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 43

Accepted Solution

Eugene Z earned 500 total points
ID: 39713930
1. do you see any errors in sql error log?
2. maybe you have no data for your join criteria (in this case this query would like to check all tables)
3. you may do not have all indexes for your query
4. how often do you run update stats\reindex \etc for this database
5. make sure #5 is implemented on the regular base
6. try to run   ->   exec sp_recompile 'your proc'    
7. try to set smaller timeframe criteria for your code  (instead of BETWEEN '2013-04-30' -set maybe for 1 week..)

regarding "alternative query without sacrificing the results?":

you need to post these tables structure including tables indexes; some not real data sample and desire result ..
how many records in your tables?

for now please check #1-#5
LVL 26

Expert Comment

by:Shaun Kline
ID: 39713989
Three suggestions come to mind:
1.) The dbo.fnFindNoOfWeeks function is found twice in your main select. Move it into your CSUComp subquery.
2.) Your subqueries include tables that do not appear to provide any value to the main query. An example in the first subquery is CSUServiceAreas. You include this table in the main query to provide the Service Area description, but also include that in the subquery. Instead of including that table and returning the ServiceAreaID, remove it and return CSUTeams_1.ServiceArea. (Second subquery also has the duplicated table.)
3.) Add the WITH (NOLOCK) statement to the tables in the FROM clauses.

If you can, use the DISPLAY EXECUTION PLAN in SSMS to see where your query is taking the biggest performance hit.

If this will be a repeated request, are you implementing it in a reporting tool, such as SSRS or Crystal Reports? If so, consider off loading the summations to those tools instead of performing it within SQL.

One other suggestion that goes beyond the "simple request" of the manager: as this database would be considered a transactional system, if it is heavily used during the day, it may be worth exploring the possibility of copying the data to a reporting database using a nightly process. Generally speaking, transactional databases are not tuned for reporting. A reporting database could be tuned for generating reports, and much of the calculations you are performing could be run off-hours or during low peak hours to prevent locks from occurring due to people running reports and people adding/updating data.

Author Comment

ID: 39721198
Thank you so much for your replies. Actually i joined the organisation only few weeks back and it seems that i don't have sufficient rights to add indexes etc on the tables.  I have requested our IT to grant me access but they are horribly slow.

@EugeneZ Re (try to set smaller timeframe criteria for your code  (instead of BETWEEN '2013-04-30' -set maybe for 1 week..)

it worked fine when i shortened the period.

There are no indexes on the tables apart from the primary keys.

kindest regards,

Author Closing Comment

ID: 39856550
Thanks for your help. after a long wait i got access to the database and the database is designed horribly, the tables are not normalised properly and there were no indexes. For now i have created the indexes and they are running fine but i will need to change the structure of the db sometime.

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question