SQL Stored procedure ruinning very slow

Posted on 2013-12-12
Medium Priority
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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 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