Query Runs for Long Inspite of Indexes on table

Question

Query Runs for Long Inspite of Indexes on table

sdennis

SSC-Addicted

Points: 431
More actions
November 11, 2013 at 1:43 pm

#300129

Hi
I have a table which has more than 6 million records .
Below is the table
CREATE TABLE [Off].[EngineResponseResult](
[EngineResponseResultId] [int] IDENTITY(2505000,1) NOT NULL,
[EngineRequestId] [uniqueidentifier] NOT NULL,
[OffRequestId] [uniqueidentifier] NOT NULL,
[S3Key] [varchar](300) NOT NULL,
[S3versionId] [varchar](50) NOT NULL,
[InsertDateTime] [datetime] NOT NULL,
[RespondedDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_EngineResponseResult] PRIMARY KEY CLUSTERED
(
[PricingEngineResponseResultInstanceId] ASC
))
It has a Default getdate constraint on Insertdatetime
When I try to run below query .It takes 15 mins to return 298851 rows
select * FROM [Off].[EngineResponseResult] WITH (NOLOCK)
WHERE [RespondedDateTime] > DATEADD(Day,-5,GETDATE())
Can you suggest me what can be done to decrease the execution time..(There are no deadlocks)

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Start by taking out the nolock (unless incorrect results are acceptable) and replacing the * with the specific columns you need. Then post the other indexes the table has (because the pk does not support that query) as well as the execution plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sdennis SSC-Addicted Points: 431 More actions · Answer 2

I actually Used all the columns names in the SELECT and also there is only Clustered Index on the table and I treied removing NOLOCK.I Checked the Execution Plan and it goes for an clustered Index scan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Well of course it'll go for a clustered index scan if there's only a clustered index on the table. There's no other possible method of getting the rows required.

Do you really need every single column from the table?

Drop the nolock, not for performance reasons, for data correctness. Nolock allows incorrect results under concurrent access.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sdennis SSC-Addicted Points: 431 More actions · Answer 4

Yes,I need all the columns to be Inserted into another Table Actually as a refresh.

The clusteredIndex column is EngineResponseResultID

There is an Non clustered Index on Respondeddatetime and another non clusteredIndex on offrequestID

When I run this query

SELECT EngineResponseResultID,[EngineRequestId]

,[OffRequestId]

S3Key,

S3versionId,

InsertDateTime

,RespondedDateTime,

,InsertDateTimeUTC FROM SmartOffers.PricingEngineRequest

WHERE [iNSERTDateTimeutc] > DATEADD(Day,-5,GETDATE())

It takes 13 mins to return the results

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

sdennis (11/11/2013)
Yes,I need all the columns to be Inserted into another Table Actually as a refresh.
The clusteredIndex column is EngineResponseResultID
There is an Non clustered Index on Respondeddatetime and another non clusteredIndex on offrequestID
When I run this query
SELECT EngineResponseResultID,[EngineRequestId]
,[OffRequestId]
S3Key,
S3versionId,
InsertDateTime
,RespondedDateTime,
,InsertDateTimeUTC FROM SmartOffers.PricingEngineRequest
WHERE [iNSERTDateTimeutc] > DATEADD(Day,-5,GETDATE())
It takes 13 mins to return the results

According to what you've posted, there is no index on the InsertDateTimeUTC column. That means that SQL Server must to a table scan to find the rows according to the criteria in your WHERE clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

d_uvarajan SSC Veteran Points: 214 More actions · Answer 6

Try to create a non-clustered index on the column which is used in the "Where" condition and later try to execute the query.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Evaluate how often that table is accessed by the ID column and how often it's accessed by the date column. If it's evaluated by the date far more often, consider making the PK a nonclustered index and putting the clustered index onto the date column.

With the amount of the table you're talking about, if you put a nonclustered index it would be ignored unless it's covering, and covering means duplicating the table essentially.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

GilaMonster (11/12/2013)
Evaluate how often that table is accessed by the ID column and how often it's accessed by the date column. If it's evaluated by the date far more often, consider making the PK a nonclustered index and putting the clustered index onto the date column.
With the amount of the table you're talking about, if you put a nonclustered index it would be ignored unless it's covering, and covering means duplicating the table essentially.

+100 As my old DBA would say, "True Dat!"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)