Help with performance of query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with performance of query

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-14 : 02:07:30

I am using SQL Express 2005 and have the following query that is taking 39 seconds to return with the input case below. The EventDetails table is about 1 million rows, EventExtraData about 2.1 Million rows and the SiteDetails table 15 rows. Any ideas on how to speed it up?

This is the input and in this case only intends to return 1 row (taking 39 sec). I have found the time it takes to return seems to be proportional to the time range in @StartDateRange and @EndDateRange. If I make this range only one day it returns in under a second.

USE [VC]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetAckedEventsEx]
@SiteID = 5,
@Top = 1,
@StartDateRange = N'03/14/2010',
@EndDateRange = N'04/14/2010'

SELECT 'Return Value' = @return_value

Query:

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[GetAckedEventsEx] Script Date: 04/14/2010 15:17:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <25 August 2009,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetAckedEventsEx]

@SiteID int,
@Top int,
@StartDateRange DateTime,
@EndDateRange DateTime

As

SET NOCOUNT ON

SELECT
E.EventID,
[CameraNumber],
[CameraName],
[EventType],
[Description],
[Priority],
[State],
[Cause],
[SourceID],
[SourceType],
[ACKFlag],
[FullyACKed],
[EventStartTime],
[LocalStartTime],
[EventEndTime],
[LocalEndTime],
[SiteID],
[StateType],
[StateData],
[SessionID],
[Indicator],
[ZoneNumber],
[EventCode],
[AlarmProfile],
[ZoneText],
[DetectorText],
[RID],
[MetaDataType],
[PictureID],
[CameraMask],
[RawData],
[EventExtraDataID],
EventExtraData.EventID As EventExtraDataEventID,
[Type],
[Format],
[FileName],
[StartTime],
[EndTime]
FROM (SELECT Top (@Top) *
FROM EventDetails
WHERE SiteID = @SiteID AND
EventDetails.LocalStartTime BETWEEN @StartDateRange AND @EndDateRange AND
EventDetails.FullyAcked = 1
ORDER BY EventDetails.LocalStartTime DESC, EventDetails.EventID DESC
) As E
LEFT JOIN
EventExtraData ON E.EventID = EventExtraData.EventID

SET NOCOUNT OFF

Return

Indexes

EventDetails:
Clustered index on EventID.
Nonclustered index on Ackflag, Fullyacked, Localstarttime, Localendtime, Siteid, Eventstarttime, Eventendtime.
EventExtraData:
Clustered index on EventID, EventExtraDataID
SiteDetails:
Clustered index on SiteID
Nonclustered index on SiteID, SiteName, TransmitterType, DeviceID

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-14 : 02:56:40

Hi,

SELECT Top (@Top) *
FROM EventDetails
WHERE SiteID = @SiteID AND
EventDetails.LocalStartTime BETWEEN @StartDateRange AND @EndDateRange AND
EventDetails.FullyAcked = 1
ORDER BY EventDetails.LocalStartTime DESC, EventDetails.EventID DESC

In the above query you have IRDER BY clause, this makes to decrease query performance.
Instead of using directly query, you can create one temporary table like below
Insert Into @TemporaryTable
SELECT Top (@Top) *
FROM EventDetails
WHERE SiteID = @SiteID AND
EventDetails.LocalStartTime BETWEEN @StartDateRange AND @EndDateRange AND
EventDetails.FullyAcked = 1
ORDER BY EventDetails.LocalStartTime DESC, EventDetails.EventID DESC

Then form the query as below

SELECT
E.EventID,
[CameraNumber],
[CameraName],
[EventType],
[Description],
[Priority],
[State],
[Cause],
[SourceID],
[SourceType],
[ACKFlag],
[FullyACKed],
[EventStartTime],
[LocalStartTime],
[EventEndTime],
[LocalEndTime],
[SiteID],
[StateType],
[StateData],
[SessionID],
[Indicator],
[ZoneNumber],
[EventCode],
[AlarmProfile],
[ZoneText],
[DetectorText],
[RID],
[MetaDataType],
[PictureID],
[CameraMask],
[RawData],
[EventExtraDataID],
EventExtraData.EventID As EventExtraDataEventID,
[Type],
[Format],
[FileName],
[StartTime],
[EndTime]
FROM @TemporaryTable As E
LEFT JOIN
EventExtraData ON E.EventID = EventExtraData.EventID

This will increase some performance.

Solutions are easy. Understanding the problem, now, that's the hard part

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-14 : 03:05:32

Thanks ganeshkumar08 I can try that. I re-indexed and found this made a big difference. Now the query is taking about 8 seconds when the date range is 3 months. Still I wound expect it to return in under a second?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-14 : 03:28:56

Tested ganeshkumar08 suggestion and it made no difference unfortuneately.

Kristen
Test

22859 Posts

Posted - 2010-04-14 : 03:53:29

"Nonclustered index on Ackflag, Fullyacked, Localstarttime, Localendtime, Siteid, Eventstarttime, Eventendtime."

Is this a single composite index, or several different indexes?

If a single index the first column needs to be "selective" (if [Ackflag] is a Yes/No type field then it won't be

)

Next it needs to be chosen for this query, based on the selectiveness of the first key column for this query, and also any stats on the other columns in the index.

So for this I would expect that you need an index on EventDetails with LocalStartTime as the first column, also containing SiteID and FullyAcked (which probably have no benefit to the index seek, so could just be INCLUDE columns)

It may help if LocalStartTime is indexes as DESCENDING (given that you have a Descending ORDER BY on this query - but SQL may be able to sort that out using an ASCENDING order index anywayu - try an Index sorted each way and see if there is a different? (I'd be interested to know

)

I would not do a SELECT * in the inner query but either a) explicitly state the columns required [by the oputer query] or b) just get the ID and then re-JOIN that to the EventDetails table in the outer query.

e.g.


FROM
(
	SELECT Top (@Top) EventID
	FROM EventDetails
	WHERE	SiteID = @SiteID 
		AND EventDetails.LocalStartTime BETWEEN @StartDateRange AND @EndDateRange
		AND EventDetails.FullyAcked = 1
	ORDER BY EventDetails.LocalStartTime DESC, EventDetails.EventID DESC
) As E1
	JOIN EventExtraData AS E
		ON E.EventID = E1.EventID
	LEFT JOIN EventExtraData 
		ON E.EventID = EventExtraData.EventID

Kristen
Test

22859 Posts

Posted - 2010-04-14 : 03:55:30

P.S. It is just possible that an index wit key columns SiteID, LocalStartTime and (include column) FullyAcked would work even better, but SiteID may not be sufficiently selective for the index to be used. If it IS used then that will be faster than my previous suggestion. (The Query Plan will show you which index is actually being used.)

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-14 : 22:34:23

I am new to sql and have been learning sql and creating stored procedures. Basically getting them to work and that was it. I have now found some queries are running too slow and I need to fix them. Kristen's info above has been invaluable as it has pointed to something I know nothing about and as it turns out critical, that is indexing and execution plans. Does anyone know where I can find info on how to read execution plans, what the details mean and how to measure overall if one plan is better than another, what are we trying to acheive in a plan and how to create an ideal set of indexes?

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 02:36:07

"how to measure overall if one plan is better than another"

A good starting point is to look at the Logical I/O and the number of SCANs (i.e. ignoring the Physical I/O which will vary according to what is in the cache at the time).


-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

BEGIN TRANSACTION
 ... put query here ...
ROLLBACK
SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

Comment in either the STATISTICS or SHOWPLAN

STATISTICS will run the query (for real) and give you timings for the various parts of the query. You can optionally include the BEGIN TRANSACTION / ROLLBACK to prevent any inserts/updates from changing the data - so you can run the test multiple times etc. with out the data actually changing (an INSERT will probably create a PK such that you couldn't run it again if you did not use ROLLBACK)

SHOWPLAN will show you the query plan. You want SEEK rather than SCAN (either Table or Index) and you want to see the index you expect being used - SQL will use the Clustered Index (which is normally the same as Primary Key index) when it doesn't find a suitable index, so look out fo where the name of that index appears when you think some other index / means should be being used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources