• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

SQL Stored procedure ruinning very slow

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
shah36
Asked:
shah36
  • 2
1 Solution
 
Eugene ZCommented:
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
 
Shaun KlineLead Software EngineerCommented:
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
 
shah36Author Commented:
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
 
shah36Author Commented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now