Query Performance

Question

Query Performance

Mike Levan

SSChampion

Points: 14201
More actions
December 5, 2008 at 9:24 am

#196701

performance wise which query is faster.
select distinct a.empid
into #excl
from #sap as a inner join salary as b
on a.empid = b.empid
where (servcdate between '01/01/1995' and '1/10/2008') and
(
place in ('07','70','80','BS') or startd = '2' or empcode between '550' and '559'
or
empcode in ('115','125','135','145','155','235')
)
or
select distinct a.empid
into #excl
from #sap as a inner join salary as b
on a.empid = b.empid
where (a.servcdate between '01/01/1995' and '1/10/2008') and
(
place in ('07','70','80','BS') or a.startd = '2' or a.empcode between '550' and '559'
or
a.empcode in ('115','125','135','145','155','235')
)
the diff between these 2 is that in the second query i have given alias names in the where clause.
thanks

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Using Aliases is the where clause should not improve performance. The query plans should be the same.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Mike Levan SSChampion Points: 14201 More actions · Answer 2

above query pulls 20k records in 24min, i thing its not gud though my indexes are all doing good.

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

Table definitions please, index definitions and the execution plan, saved as a .sqlplan file, zipped and attached to your post.

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Mike Levan (12/5/2008)
above query pulls 20k records in 24min, i thing its not gud though my indexes are all doing good.

All the OR's limit the usefullness of indexes. As Gail has asked, for real help post the DDL.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 5

And comma delimited IN statements are notorious. Like Gail said, execution plans & structures.

"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

GSquared SSC Guru Points: 260824 More actions · Answer 6

Replace all the Or and In statements with Union queries and the whole thing will probably speed up. Depends on your indexes, of course, but it can make a huge difference.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

GSquared (12/5/2008)
Replace all the Or and In statements with Union queries and the whole thing will probably speed up. Depends on your indexes, of course, but it can make a huge difference.

That was true on SQL 2000 because the optimiser had more options for a union than it had for an or. On 2005 it has the same options for both and the radically different plans are far less likely.

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: 1004441 More actions · Answer 8

Mike Levan (12/5/2008)
above query pulls 20k records in 24min, i thing its not gud though my indexes are all doing good.

Why do you think your indexes are "all doing good"? Are they all INDEX SEEKS in the execution plan?

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