Reduce the store Procedure execution time

Question

Reduce the store Procedure execution time

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

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

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

We will need the DDL (CREATE TABLE statement(s), including INDEX definitions) for any source table(s), some sample data for the source table(s) (a series of INSERT INTO statements) (not a lot, just enough to represent the problem domain and NOT REAL DATA), expected results based on the sample data.

Not too sure what can be done, though, because a quick look at the code shows a lot of <>, not null, and like % + some string value + % in the WHERE clauses that may cause table/clustered index scans.

SS999 SSCommitted Points: 1663 More actions · Answer 2

One of the source tables have Clusterd index and other table has 1 Unique,Non-clustered and 12 Non-Unique, Clustered indexes. I tried using the estimated execution plan which suggested me for some missing indexes and i have created them and also i have added indexes on the temp tables. I could able to being it down to only 2 mins.

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

SS999 (3/23/2012)
One of the source tables have Clusterd index and other table has 1 Unique,Non-clustered and 12 Non-Unique, Clustered indexes. I tried using the estimated execution plan which suggested me for some missing indexes and i have created them and also i have added indexes on the temp tables. I could able to being it down to only 2 mins.

We will need the DDL (CREATE TABLE statement(s), including INDEX definitions) for any source table(s), some sample data for the source table(s) (a series of INSERT INTO statements) (not a lot, just enough to represent the problem domain and NOT REAL DATA), expected results based on the sample data.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

And the current execution plan please, The actual plan

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

SS999 SSCommitted Points: 1663 More actions · Answer 5

when i m trying to execute with actual execution plan it is taking more than 8 mins and getting hung :(. I have no clue what is going on.

SS999 SSCommitted Points: 1663 More actions · Answer 6

i have just shortened the startdate and enddate for just 10days with the actual execution plan. when i execute without selecting it it took be about 58secs to complete. When i m executing with selecting the actual plan, it still executing and so far it been 7 and a half mins.

Any clue??

eforta.it SSC-Addicted Points: 454 More actions · Answer 7

Full Version number of your release?

Might need a CU or SP?

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 8

SS999 (3/23/2012)
i have just shortened the startdate and enddate for just 10days with the actual execution plan. when i execute without selecting it it took be about 58secs to complete. When i m executing with selecting the actual plan, it still executing and so far it been 7 and a half mins.
Any clue??

Yes... stop trying. The code has a WHILE Loop in it and it's going to try to generate multiple execution plans for each iteration. It'll get to 100 plans and then start printing error messages for each and every "step" in each and every interation of the loop.

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

I may have to do this in several steps. Here's my "first blush" look at it.

The very first thing to do is to remove the ORDER BY from the inserts to the temp tables. They serve absolutely no logical purpose and do nothing but waste precious time and clock cycles.

Second, there's not much we can do to "tune" this code because it's actually the equivalent of a huge cross join cleverly disguised by a loop. The code builds tables "A" and "B" and then for every row in table "B", it searches through all the rows in table "A" just like any many-to-many join would. The code needs to be rewritten to do a join between the two temp tables and then table "C" (the table variable) can be totally eliminated. Actually, it's worse than a mere cross-join because the code also goes back and updates table "B" and we know there're no indexes to help that along so it's going to make for an extra table scan for each iteration there, as well.

In the WHERE clause, you don't need to check to see if something is either NULL or BLANK. If you use SomeColumn > '' only, then it can't possibly be NULL because if it's greater than a BLANK, it can't possibly be NULL.

The only places where you might be able to "tune" this code is in the population of table "A" and "B" temp tables. The correct indexes could help that code quite a bit. If you'd manually select and execute just the SELECT portions of those two pieces of code, you could quickly get a single execution plan for each of those pieces of code where folks could make a recommendation for you.

I'd have to sit down and figure out the precise logic to figure out the actual join between table "A" and "B" (it looks like "simple" "fuzzy" name checking) but that WHILE loop has to go. It's the cause of the accidental worse-than-a-cross-join problem.

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

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

Here's the next piece. Drop the CREATE TABLE code for the two temp tables and use SELECT/INTO instead. You're pushing data to temp tables which is in the simple recovery mode. Since that's true, SELECT/INTO will use minimal logging which will give you a pretty good boost in performance.

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 11

Ok... this just isn't right. The @StartDate variable is already set to 30 days ago. It won't help much with performance but why are we recalculating it in the following WHERE clause???

WHERE dp.received_date > CONVERT(CHAR(10), DATEADD(mm, -1, GETDATE()) - DAY(GETDATE()), 101)-- Always go back one month for policies

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 12

Jeez... someone was crazy about order. The code that populates the variables has another ORDER BY in it. Lose the ORDER BY in the following code.

SELECT TOP 1 @TempVQQuoteID = QuoteID, @TempVQAppFName = ApplicantFirstName, @TempVQAppLName = ApplicantLastName,

@TempVQAppDba = ApplicantDBA, @TempVQBusName = BusinessName, @TempVQAgentID = AgentID, @TempVQStateID = StateID,

@TempVQQuoteStatus = QuoteStatus, @TempVQQuoteNum = QuoteNum

FROM #VQuotesResults WHERE Checked = 0 ORDER BY QuoteID

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 13

Hooooo boooyyy. I'll bet credits to navy beens that the following is actually a view. We need to see the code for that...

FROM v_quotes vq

Also, I'm assuming that the Status table is quite bit smaller than that view. In the following code...

FROM v_quotes vq

JOIN [Status] s ON vq.StatusID = s.StatusID

WHERE vq.received_date BETWEEN @StartDate AND @EndDate

AND vq.StatusID <> 3-- Don't want bound ones

AND vq.IsActive = 1

If you change the check for vq.StatusID <> 3 to s.StatusID <> 3, you should have a better chance of using indexes on the table that underly the view. There could be a significant performance increase there but don't know for sure because I can't see what's going on from here. If there's not already one there, the underlying table(s) responsible for the StatusID column in the view would certainly benefit from an index. So would the Received_Date column and the IsActive column. I recommend a composite index for the task of indexing the 3 if they're all on the same table.

You might also want to dig a little deeper and find out if the view is really necessary. The view could have joins to a bunch of tables that aren't necessary for this query.

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 14

I guess that's about it. Most of the changes I've recommended can be done without getting rid of the WHILE loop. However, that's a large part of the problem as it forms not one but two accidental cross-joins because of the table or index scans that I think may be happening. It REALLY needs to be replaced by a join between the two temp tables. I suppose we could help you do that but now would be a good time to get some developers from your company involved. It shouldn't be your job to rewrite really bad code.

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

SS999 SSCommitted Points: 1663 More actions · Answer 15

Hello Jeff,

Thank u so much for the replies. I would definetely consider ur suggestions and ask my developer to re-write whatever it takes to tune-up this code with ur suggesstions.

Thanks bunch to u and all for the feedback.

Thanks!!!