Same code but one is faster and another one is slower

Question

Same code but one is faster and another one is slower

SQLAddict01

SSC Eights!

Points: 885
More actions
August 24, 2020 at 5:29 am

#3782806

Do you know why the query 1 runs for hours as opposed to the query 2 with runs in seconds.? and how do I make query 1 faster?
Query 1:SELECT distinct a,b ,c
FROM [dbo].[d]
WHERE Date > DATEADD(d,-3,GETDATE())
Query 2:
SELECT distinct a,b ,c
FROM [dbo].[d]
WHERE Date > DATEADD(d,-3,GETDATE()) and Date <= DATEADD(d,-2,GETDATE())
UNION
SELECT distinct a,b ,c
FROM [dbo].[d]
WHERE Date > DATEADD(d,-2,GETDATE()) and Date <= DATEADD(d,-1,GETDATE())
UNION
SELECT distinct a,b ,c
FROM [dbo].[d]
WHERE Date > DATEADD(d,-1,GETDATE()) and Date <= DATEADD(d,-0,GETDATE())
UNION
SELECT distinct a,b ,c
FROM [dbo].[d]
WHERE Date >= GETDATE()

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

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

DesNorton SSC-Insane Points: 24276 More actions · Answer 1

In order for anybody to assist with performance issues, you will need to upload the execution plan of the 2 different queries. The actual execution plan is preferred.

Just guessing here, but I would expect that the 2nd query is doing multiple range scans, while the 1st query is doing a full table scan.

How to post data/code on a forum to get the best help.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

Agreed. Please post the execution plan. It's just not possible to say what's going on without it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Bad choice of clustered index.

It must be not on identity column but on [Date].

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Sergiy wrote:

Bad choice of clustered index.
It must be not on identity column but on [Date].

I'll have to say "It Depends". If most queries are based on the ID, then the advice to put the CI on [Date] could throw a major wrench into everything else.

--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)

Sergiy SSC Guru Points: 110206 More actions · Answer 5

queries on ID are typically for a single record, rarely for a small group of ID's mentioned in a list.

nobody is ever interested in records with ID's within a range, like ID > @ID

With a small sample of values optimiser will go for a non-clustered index scan followed by row lookup, like it does in the second version of query posted by the OP. Queries will still be fast, nobody's gonna complain.

and the Date column is the one which gets queried by range all the time - performance of those queries to suffer a lot without a clustered index having Date in 1st position. Exactly as it stated by the OP.

_____________
Code for TallyGenerator

Brahmanand Shukla SSC Eights! Points: 980 More actions · Answer 6

Sergiy wrote:

queries on ID are typically for a single record, rarely for a small group of ID's mentioned in a list.
nobody is ever interested in records with ID's within a range, like ID > @ID
With a small sample of values optimiser will go for a non-clustered index scan followed by row lookup, like it does in the second version of query posted by the OP. Queries will still be fast, nobody's gonna complain.
and the Date column is the one which gets queried by range all the time - performance of those queries to suffer a lot without a clustered index having Date in 1st position. Exactly as it stated by the OP.

Creating CI on DateTime (NOT Date) would definitely help in SELECT but on the other side will impact INSERT/UPDATE/DELETE due to overhead of adding uniquifier. If the Database is READ intensive then this can be a good solution, given CI has been made composite to control the uniqueness. However, making CI composite would increase the size of index key.

If CI is created on Date column and thousands/millions of record having same Date then CI won't make sense.

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

sgmunson SSC Guru Points: 110639 More actions · Answer 7

Well, ... there's still no response from the original poster with an execution plan...

So, while we can all speculate as to what's going on, and ponder on what the value of the CI being on the date column would do, it's all just guesswork that may or may not apply to the actual situation... I'll stick with Grant on needing to see the execution plan, and with Jeff on the "it depends" comment.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)