Running total , missing date , NULL in amount, exception handling

Question

Running total , missing date , NULL in amount, exception handling

Viewing 8 posts - 16 through 22 (of 22 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

I saw huge performance improvement after implementing 'quirky method'. Yes, The execution time was reduced from 45 minutes to 30 seconds

I noticed one thing in the execution time.

Before Fine Tuning:

Running the same query from onsite(US) with same parameter, took 5 minutes only.

Running the same query from offshore with same parameter took 45 minutes.

How this much of difference? I am wondering the time difference. is there any problem with network setup?

After Fine Tuning:

Running the same query from onsite(US) with same parameter, took 30 seconds

Running the same query from offshore with same parameter took 27 seconds.

karthik

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 2

I can't be sure because I was actually there to see everything happen, but it probably has to do with the number of reads before and after tuning. Queries that need to "talk" alot will obviously be much slower over a long haul connection than a much shorter or direct connection.

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

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Jeff,

Code has been moved to PROD. Execution time is 26 seconds.

Dynamic Sql Part : 13 Seconds

Quirky Update(# table) : 6 Seconds

Real Table Update (by using the above #table) : 7 seconds

But the end users expects to finish the procedure within 5-10 seconds.

I am looking the dynamic sql part now.

is there any way to reduce the execution time of 'quirky update' ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

karthik M

One Orange Chip

Points: 29139

January 27, 2010 at 10:51 pm

#1109745

Network team is looking Latency issue.

karthik

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

karthikeyan-444867 (1/27/2010)
Jeff,
Code has been moved to PROD. Execution time is 26 seconds.
Dynamic Sql Part : 13 Seconds
Quirky Update(# table) : 6 Seconds
Real Table Update (by using the above #table) : 7 seconds
But the end users expects to finish the procedure within 5-10 seconds.
I am looking the dynamic sql part now.
is there any way to reduce the execution time of 'quirky update' ?

Heh... now wait just a minute... In all the code blocks contained on this thread, I don't see any dynamic SQL directed at the resolution of this problem and the "Quirky" update is the fastest of the 3 things above... I'd concentrate on the dynamic SQL part first, whatever it is.

Also, how many rows are we talking about the "Quirky" update doing?

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

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Also, how many rows are we talking about the "Quirky" update doing?

300000 rows(nearly).

karthik

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

In that case, I guess I'd need to see the code, the tables, and the indexes for all 3 sections. I have a 7 year old single 1.8 GHz box with only a Gig of ram and the quirky update will do a million rows in the same time you say it takes to only do 300,000.

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

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Jeff,

For time being They agreed the current execution time. But i need to look into this for next month release.

Meanwhile i have been asked to fine tune one more procedure. I am looking into it. I will get back to you shortly...Thanks a lot for your help 🙂

karthik