SQL Timeout Error

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

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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 22

Russ SuterSenior Software DeveloperCommented: 2018-11-30

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 45

pcelbaCommented: 2018-12-01

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 BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented: 2018-12-12

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

SQL Timeout Error

Who is Participating?