Solved

SQL Stored procedure ruinning very slow

Posted on 2013-12-12
4
310 Views
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
FROM         CSUTeams INNER JOIN
                      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
ORDER BY MonthEnd

kind regards,
0
Comment
Question by:shah36
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
Comment Utility
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
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
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.
0
 

Author Comment

by:shah36
Comment Utility
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,
0
 

Author Closing Comment

by:shah36
Comment Utility
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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now