Query Perfomance problems

Question

Query Perfomance problems

mpeters-878100

Right there with Babe

Points: 716
More actions
November 11, 2009 at 3:25 pm

#73400

I have the following 2 queries The first one takes 2 minutes to run and the second takes 3 seconds to run. They are effectively the same queries but the 1st uses variables to pass the dates to the where clause
--This query takes 2 minutes to run
declare @StartDateas datetime,
@EndDate asDatetime
set @StartDate = '2009-11-12 08:58:52.867'
set @Enddate = '2009-11-13 08:58:52.867'
select * from Caseevent
where eventDuedate>= @startDate
and EventDuedate<= @EndDate
--This query takes 3 seconds to run
declare @StartDateas datetime,
@EndDate asDatetime
set @StartDate = '2009-11-12 08:58:52.867'
set @Enddate = '2009-11-13 08:58:52.867'
select * from Caseevent
where eventDuedate>= '2009-11-12 08:58:52.867'
and EventDuedate<= '2009-11-13 08:58:52.867'
The Caseevent table is fairly big and has 6860142 records in it, but i dont understand the execution time difference between the 2 queries, it seems like sql server applies the row filters after all the rows are returned when you pass variables
I anyone can help out that would be great thanks
Matt

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Is this actually being run from within a stored procedure?

If so, may I suggest that you read the second article I reference in my signature block below regarding performance issues? Please follow the instructions provided in the article and you will get excellent assistance in return.

Also, you might be experiencing parameter sniffing. While reading the article, could you post the code for the proc?

mpeters-878100 Right there with Babe Points: 716 More actions · Answer 2

Hey Lynn

Thanks for the quick reply. After having a detailed look at the execution plans i managed to hit the nail on the head

I compared the 2 execution plans and found that when the datetime variables where passed, the query uses only the clustered index on the table, where as when i hard coded the dates it used a different non clustered index on the date field

I was able to minimize the run time of the query that used variables by forcing the query to use the non clustered index on the date field and it more than halved the query time.

If you like i will post the 2 execution plans and the DDL of the table

Sergiy SSC Guru Points: 110207 More actions · Answer 3

Index on eventDuedate should be clustered.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I agree with Sergiy, especially if you have numerous queries that make use of the eventDueDate column in the WHERE clause of queries or if this is a frequent path to the data.

Using index hints should be a last resort.

Properly defined indexes are the better way to go.

mpeters-878100 Right there with Babe Points: 716 More actions · Answer 5

This query is used in a report which is run maybe twice a week. The application uses this table a lot more and there are a lot of lookups on the caseid column, this is the column that holds the clustered index.

So putting the clustered index on the EventDuedate column would probably hinder the applications performance. It's also a 3rd party application so i don't really want to fiddle with it too much

Anyway I will be upgrading from 2000 to 2008 soon so i'll be sure to query the sys.dm_db_index_operational_stats dmv relating to the indexes on this table.

Thanks for the help

I've included the create script for this table and the indexes below

/****** Object: Table [dbo].[CASEEVENT] Script Date: 11/12/2009 16:02:14 ******/

CREATE TABLE [dbo].[CASEEVENT](

[CASEID] [int] NOT NULL,

[EVENTNO] [int] NOT NULL,

[CYCLE] [smallint] NOT NULL,

[EVENTDATE] [datetime] NULL,

[EVENTDUEDATE] [datetime] NULL,

[DATEREMIND] [datetime] NULL,

[DATEDUESAVED] [decimal](1, 0) NULL,

[OCCURREDFLAG] [decimal](1, 0) NULL,

[CREATEDBYACTION] [nvarchar](2) NULL,

[CREATEDBYCRITERIA] [int] NULL,

[ENTEREDDEADLINE] [int] NULL,

[PERIODTYPE] [nchar](1) NULL,

[DOCUMENTNO] [smallint] NULL,

[DOCSREQUIRED] [smallint] NULL,

[DOCSRECEIVED] [smallint] NULL,

[USEMESSAGE2FLAG] [decimal](1, 0) NULL,

[GOVERNINGEVENTNO] [int] NULL,

[EVENTTEXT] [nvarchar](254) NULL,

[LONGFLAG] [decimal](1, 0) NULL,

[EVENTLONGTEXT] [ntext] NULL,

[JOURNALNO] [nvarchar](20) NULL,

[IMPORTBATCHNO] [int] NULL,

[EVENTTEXT_TID] [int] NULL,

[EMPLOYEENO] [int] NULL,

[SENDMETHOD] [int] NULL,

[SENTDATE] [datetime] NULL,

[RECEIPTDATE] [datetime] NULL,

[RECEIPTREFERENCE] [nvarchar](50) NULL,

[DISPLAYORDER] [smallint] NULL,

[rowguid31] [uniqueidentifier] ROWGUIDCOL NOT NULL,

[FROMCASEID] [int] NULL,

[DUEDATERESPNAMETYPE] [nvarchar](3) NULL,

[LOGUSERID] [nvarchar](50) NULL,

[LOGIDENTITYID] [int] NULL,

[LOGTRANSACTIONNO] [int] NULL,

[LOGDATETIMESTAMP] [datetime] NULL,

[LOGAPPLICATION] [nvarchar](128) NULL,

[LOGOFFICEID] [int] NULL,

CONSTRAINT [XPKCASEEVENT] PRIMARY KEY CLUSTERED

(

[CASEID] ASC,

[EVENTNO] ASC,

[CYCLE] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

/****** Object: Index [XIE3CASEEVENT] Script Date: 11/12/2009 16:12:26 ******/

CREATE NONCLUSTERED INDEX [XIE3CASEEVENT] ON [dbo].[CASEEVENT]

(

[EVENTDUEDATE] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

mpeters-878100 Right there with Babe Points: 716 More actions · Answer 6