SQL Timeout Error

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

If I run the following query I get a timeout error with [Shaft] = 'tshepong air' but with any other Shaft eg [Shaft] = 'kopanang air' I get no error even where there is a lot more data.

What could cause this?

Select [COY],Sum([M Shift]) As [M Shift], Sum([N Shift]) As [N Shift], Sum([Sick]) As [Sick],  Sum([AWOP]) As [AWOP], Sum([Leave]) As [Leave], Sum([Comp Leave]) As [Comp Leave], Sum([5 Sat Day]) As [5 Sat Day], Sum([5 Sat Night]) As [5 Sat Night], Sum([6 Sat Day]) As [6 Sat Day], Sum([6 Sat Night]) As [6 Sat Night] , Sum([PPH]) As [PPH], Sum([Shift Worked In Day]) As [Shift Worked In Day], Sum([Shift Worked In Night]) As [Shift Worked In Night], Sum([Lesedi Training]) As [Lesedi Training], Sum([Mine Training]) As [Mine Training], Sum([IOD]) As [IOD] From [vTimesheets] Where [Day] >= '2018-11-16' And [Day] <='2018-11-30' And [Shaft] = 'tshepong air' Group By [COY]

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Senior Software Developer
Commented:
The fact that it's timing out on one search criterion and not others could just be down to the SQL cache. The bigger issue for me is why it's taking so long in the first place. You do have quite a few aggregate functions in this query but the query itself doesn't look very complex and the execution plan shouldn't be too bad either. What I suspect is happening here is that you are missing one or more indexes that you could use to dramatically improve performance. You haven't indicated which SQL product you're using but from the syntax it looks like Microsoft SQL Server. If that's the case then you can run the query in SSMS and display the execution plan. It will help you determine if you can add indexes to improve performance.

One additional tip for you. It might be helpful to those of us trying to answer your question if you formatted your query to be more readable by separating it into more than one line. The same query above is much more readable like this:
SELECT [COY],
       SUM([M Shift]) AS [M Shift],
       SUM([N Shift]) AS [N Shift],
       SUM([Sick]) AS [Sick],
       SUM([AWOP]) AS [AWOP],
       SUM([Leave]) AS [Leave],
       SUM([Comp Leave]) AS [Comp Leave],
       SUM([5 Sat Day]) AS [5 Sat Day],
       SUM([5 Sat Night]) AS [5 Sat Night],
       SUM([6 Sat Day]) AS [6 Sat Day],
       SUM([6 Sat Night]) AS [6 Sat Night],
       SUM([PPH]) AS [PPH],
       SUM([Shift Worked In Day]) AS [Shift Worked In Day],
       SUM([Shift Worked In Night]) AS [Shift Worked In Night],
       SUM([Lesedi Training]) AS [Lesedi Training],
       SUM([Mine Training]) AS [Mine Training],
       SUM([IOD]) AS [IOD]
FROM [vTimesheets]
WHERE [Day] >= '2018-11-16'
      AND [Day] <= '2018-11-30'
      AND [Shaft] = 'tshepong air'
GROUP BY [COY]

Open in new window

pcelba
Commented:
To investigate why some query times out you must know four things:
1) what indexes are created on the table
2) what is rows distribution for your criteria and what is the total number of rows in the table
3) when statistics were updated the last time
4) if vTimesheets is a view then the view definition must also be disclosed

Knowing above facts you may then look at execution plans.

I would guess this index could help a lot:
CREATE INDEX ixSomeName ON YourTableName ([COY])
  INCLUDE ([Day], [Shaft])

or
CREATE INDEX ixSomeName ON YourTableName ([COY], [Day], [Shaft])

If you have such index already but execution plan shows it is not used then you have obsolete statistics and you should update them by e.g.
EXEC sp_updatestats
Murray BrownASP.net/VSTO Developer

Author

Commented:
Thanks very much for the help. Sorry abut the late reply

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial