Query tuning

Question

Query tuning

vkundar

Hall of Fame

Points: 3431
More actions
November 3, 2008 at 9:31 am

#193222

Hi,
I have a cursor which fetch around 1000 rows from the table.
This step is fast there is no isue. But which inserting the row into the table takes lots of time. So i was thinking how to tune this.
Query is attached with time statistics data.
Waiting for your replies.....
Cheers,
Got an idea..share it !!
DBA_Vishal

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

The insert statement takes ten seconds and this is being run inside the cursor 1000 times. I think it's pretty clear that it is the cursor that is your problem. Even if you got the statement to run in 100ms you're still going to run it 1000 times - that's 100 seconds. And if tomorrow you're suddenly doing it over 10,000 rows then it will take even longer.

But, if you're going to persist with using the cursor and if you want to tune the insert statement then try and tune the corresponding select statement (below)... get the execution plan and look at where you're doing index scans and where it might be appropriate to place indexes.

SELECT 10126824 as STRATEGY_KEY, DEAL_DETAIL_ID as DEAL_ID, '10/03/2008' as COB_DT from STAGE.DEAL_DETAIL WHERE DEAL_HEADER_ID IS NOT NULL AND [STRATEGY_KEY]>0 AND COB_DT = '10/03/2008' AND STRATEGY_NM IN ('GS&T-GT-406733','PSAT-GT-407230','Americas Products-GT-407234','Distillates-GT-408232','2007 Roll-GT-408495','GTL Diesel Sep 2007 - GLP-GT-387447')

Karl
source control for SQL Server

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Please provide the DDL's including any keys and indexes for STAGE.DEAL_DETAIL and STAGE.REF_DEAL_STRATEGY.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

vkundar (11/3/2008)
Hi,
I have a cursor which fetch around 1000 rows from the table.
This step is fast there is no isue.

Heh.... Bzzzzt! Wrong answer! Deduct 10 points! 😛 As Karl suggested, using the cursor IS the issue... 😉

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

Minaz Amin SSChampion Points: 11052 More actions · Answer 4

I agree with you Cursor takes time. I had the same experience earlier.

Is there a way to reduce the time taken for the Insert statement in the cursor ? I am just blank on this. It will of real use.

"More Green More Oxygen !! Plant a tree today"

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

shalu (11/4/2008)
Is there a way to reduce the time taken for the Insert statement in the cursor ? I am just blank on this.

So is everyone else... that's why you shouldn't use a cursor to begin with. 😉

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

vkundar Hall of Fame Points: 3431 More actions · Answer 6

Thanks SQLZ & Jeff for replying. I have added indexes as suggested by SQLZ and it has improved the performance.

Would like to have a pointers about indexing and other best tips for query tuning in Dataware housing.

Cheers,

Got an idea..share it !!

DBA_Vishal

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Heh... the best tip has already been given... did you get rid of the cursor?;)

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