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
  • 2
LVL 42

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

756 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