Urgent help

Question

Urgent help

Viewing 10 posts - 16 through 24 (of 24 total)

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

Harveysburger SSCommitted Points: 1562 More actions · Answer 1

if you want to venture into analyzing the execution plan of your proc, look for "Index Scan"... that will give you clues on indexes (or WHERE clauses...) that should be changed

samsql Hall of Fame Points: 3044 More actions · Answer 2

samsql

Hall of Fame

Points: 3044

September 15, 2008 at 5:58 am

#871680

Please find the attachment of the execution plan

GSquared SSC Guru Points: 260824 More actions · Answer 3

samsql, I still need you to clarify what you're asking for when you mention cases.

- 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

magarity kerns SSCrazy Points: 2763 More actions · Answer 4

GSquared (9/22/2008)
samsql, I still need you to clarify what you're asking for when you mention cases.

He wants you to optimize the sections that have CASE statements for him too.

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

Sam,

My recommendation is to peel one potato at a time. To know which potato to peel first, you must do some measuring. Put the following at the beginning of your code...

SET STATICSTICS TIME ON

Then, just before each section of Selects identifies by the rather sparse comments, add the following...

PRINT REPLICATE('=',120)

PRINT 'put a section name here'

That way, you can see which section(s) of code are taking the longest and you can concentrate on those first.

At the bottom of the code, add...

SET STATISTICS TIME OFF

Without know what's taking the longest or not, you have no real chance of ever getting this working right.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Jeff Moden (9/22/2008)
My recommendation is to peel one potato at a time. To know which potato to peel first, you must do some measuring.

Gosh Jeff, you make it all sound so glamorous. 😛

[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]

homebrew01 SSC Guru Points: 55546 More actions · Answer 7

Jeff Moden (9/22/2008)
Sam,
My recommendation is to peel one potato at a time. To know which potato to peel first, you must do some measuring. Put the following at the beginning of your code...
SET STATICSTICS TIME ON
Then, just before each section of Selects identifies by the rather sparse comments, add the following...
PRINT REPLICATE('=',120)
PRINT 'put a section name here'
That way, you can see which section(s) of code are taking the longest and you can concentrate on those first.
At the bottom of the code, add...
SET STATISTICS TIME OFF
Without know what's taking the longest or not, you have no real chance of ever getting this working right.

I did a similar thing with a long running job a little while ago. Since it was running in batch and I couldn't easily run it interactively, at each break point, I wrote "Section Name_x" and "Date_time" to a table, then I could look at the table later and see how long each step took. .... It was quite helpful.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

homebrew01 (9/23/2008)
Jeff Moden (9/22/2008)
Sam,
My recommendation is to peel one potato at a time. To know which potato to peel first, you must do some measuring. Put the following at the beginning of your code...
SET STATICSTICS TIME ON
Then, just before each section of Selects identifies by the rather sparse comments, add the following...
PRINT REPLICATE('=',120)
PRINT 'put a section name here'
That way, you can see which section(s) of code are taking the longest and you can concentrate on those first.
At the bottom of the code, add...
SET STATISTICS TIME OFF
Without know what's taking the longest or not, you have no real chance of ever getting this working right.
I did a similar thing with a long running job a little while ago. Since it was running in batch and I couldn't easily run it interactively, at each break point, I wrote "Section Name_x" and "Date_time" to a table, then I could look at the table later and see how long each step took. .... It was quite helpful.

Why not use the profiler with sp:StmtStarting and sp:StmtCompleted in Stored Procedures Events / TSQL SQL:StmtStarting and SQL:StmtComplted for that particular Job to get the details instead of doing Print statements or writing to log table?

-Roy

homebrew01 SSC Guru Points: 55546 More actions · Answer 9

Why not use the profiler with sp:StmtStarting and sp:StmtCompleted in Stored Procedures Events / TSQL SQL:StmtStarting and SQL:StmtComplted for that particular Job to get the details instead of doing Print statements or writing to log table?

I wasn't familiar with that, and since the job ran at night, it seemed like a pretty simple thing to set up.

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 10

Roy Ernest (9/23/2008)
Why not use the profiler with sp:StmtStarting and sp:StmtCompleted in Stored Procedures Events / TSQL SQL:StmtStarting and SQL:StmtComplted for that particular Job to get the details instead of doing Print statements or writing to log table?

Absolutely correct... but have you looked at the code? There's about 10,000 Selects that look nearly identical.

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