Query Tune

Question

Post reply

Query Tune

Mike Levan

SSChampion

Points: 14201
More actions
February 12, 2009 at 2:01 pm

#130966

select distinct a.RevID
into #Rev
from vwBud as a inner join dbo.datesJi as b
on a.RevID=b.RevID
where (JoinDt between '12/4//2001' and '8/31/2008')
and
(JoinDt between effectivedate and expirationdate)
and
(
(left(research,3) between '800' and '994')
or
(research like '9955%')
or
(research between '99580' and '99585')
)
select *
into #Rev_Bud
from Budget
where RevID in (select RevID from #Rev)
and
(JoinDt between '1/1/2004' and '8/31/2008')
Is there a way better to tune this query to run fast. This pulls nearly 2 million records

Viewing 15 posts - 1 through 15 (of 28 total)

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

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

determine where the query is slowing down.. Review the execution plan.

this site is GOLD! http://www.simple-talk.com/sql/performance/execution-plan-basics/

SWAGs

1.. place a clustered index on RevID in the temporary table.

2 - make 'where RevID in (yada) a Inner Join (sql probably already does this)

3 - ensure your where statement columns are indexed.

Suggestions:

- meaning full alibi's... I always use alibis but A & B crapcodea

- do not Select INTO ____ , define the table and do insert (temp table creation is then a single step instead of part of transaction & more)

daralick

GSquared SSC Guru Points: 260824 More actions · Answer 2

There probably are ways to tune it. You'll need to start by providing definitions for what it's pulling from. Looks like at least one view and maybe some tables. You'll need to provide the definitions for those, and for any tables the views are pulling from. It would also be good to have the definitions of any indexes on those.

Also best if you provide a few rows of sample data that can be inserted into those tables, once they've been created from the definitions.

Is also helpful if you post the execution plan.

Use the "Script" function when you right-click a table or view, so that you can post the scripts that will creat them. That's what I mean by definitions.

With that, we can certainly help you tune the query.

If you can't post all that, then at the very least, save the actual execution plan and zip that up and post it, so we can view that. With just that, we might be able to give a few pointers.

- 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

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 3

You're pulling all of the data between '12/4//2001' and '8/31/2008' and then limiting it in your select statement to '1/1/2004' and '8/31/2008' - Do you need to populate the temp table with all of this data or could you just limit it in the temp table ? Just a small point but I always find it makes queries much easier to follow if when you use an alias on the table you actually use it in your where clause 🙂

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

Daryl (2/12/2009)
- meaning full alibi's... I always use alibis but A & B crapcodea

The term is "alias"... 😉

- do not Select INTO ____ , define the table and do insert (temp table creation is then a single step instead of part of transaction & more)

Ummm... you should probably read the following before you perpetuate that myth any further... and never say something like that unless you have both the reason why and the proof either in viable code or a Microsoft article. 😉

http://support.microsoft.com/kb/153441/EN-US/

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

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 5

But Jeff

NOTE: This problem does not apply to SQL Server 7.0 and later.

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 6

It's hard to not to say something stupid without the execution plan (and all too easy then). The one thing that bothers me about the code so far is this, it looks like you're just doing two inner joins. Rather than performing the initial insert from the select statement and then the second insert, why not simply JOIN the second select statement with the first?

Also, using DISTINCT is almost always a sign that something is wrong with the structure, the data, or the code. DISTINCT causes aggregation which, by it's nature, is slower than straight select statements.

Please post the actual execution plans.

"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

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 7

I guess I need an alibi for my alias...

I did NOT know that the locking behavior on temp stopped at 6.5...

thx...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Hi Mike

Can you put all the table aliases into the first query please?

select distinct a.RevID

into #Rev

from vwBud as a inner join dbo.datesJi as b

on a.RevID=b.RevID

where (JoinDt between '12/4/2001' and '8/31/2008')

and

(JoinDt between effectivedate and expirationdate)

and

(

(left(research,3) between '800' and '994')

or

(research like '9955%')

or

(research between '99580' and '99585')

)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mike Levan SSChampion Points: 14201 More actions · Answer 9

Chris

I dont see any diffrence in your query when compared to mine. Its just the same, then how would that improve performance?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Mike Levan (2/13/2009)
Chris
I dont see any diffrence in your query when compared to mine. Its just the same, then how would that improve performance?

There's no difference at all, Mike, just fishing for information.

First thing I'd do in your shoes is examine the first query in some detail. Since you're using IN () in the second query, you don't need DISTINCT in the first. If you're using DISTINCT to reduce massive duplication in the first query, then try a standard join in the second, or as Grant is suggesting, squeeze it in as a derived table.

Are the dupes in the first query a result of the join to the second table(datesJi)? Can you derive the second table (pre-aggregate into a derived table) to yield distinct RevID? Without knowing which columns belong to which tables, it's difficult.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mike Levan SSChampion Points: 14201 More actions · Answer 11

I cant change the requirement in the query and also we do have duplicates in that view, this is how we process. I am only looking for any way to change the query and get the same result set.

thanks

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

Not much we can do without the DDL for the original tables/views, sample data for the tables, and expected results to test against.

Mike Levan SSChampion Points: 14201 More actions · Answer 13

is there a way i can get distinct value without using DISTINCT, i think this keyword makes query slow.

or let me put this way, how many ways i can run the same query, i just want to comapre run times in all the ways.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Experiment, Mike. Since you're using IN () in the second query, you don't need DISTINCT in the first unless it's to dramatically reduce the rowcount because you're feeding into a temp table.

Introduce the first query into the second as a derived table, see how it goes. My guess - and it's an uninformed one without table structures and everything else that's been asked for - is that it will half the execution time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden