Slow SP 3

[nice95gle]

This innocent looking SP which I inherited from another programmer, which has long left the company, is taking 5-6 mins to execute. Can anyone suggest anything I can change to make it run faster?

Sometimes you need a second set of eyes to see something you might of missed.

BTW: Hold_RPT has 2,450,972 rows

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


ALTER PROCEDURE dbo.Hold_RPT_Rules
@asofdateProcessed datetime

AS

Declare @cash varchar(15)

set @cash = (select top 1 SPR from Data_maintenance..Hold_RPT_Override (nolock) 
		where cusip='-cash-' and asofdate=@asofdateProcessed)


select coalesce(@cash,(select top 1 h2.SPR
		from Hold_RPT H2 (nolock)
		where h2.asofdate=h.asofdate
		and h2.portsyb=h.portsyb
		and h2.ts=h.ts
		and h2.sect in 
			(select distinct lkup as Tvl from Data_maintenance..ia_codes (nolock) 
			where context ='Cashmmf')
		and h2.cusip not like '-cash-'
		order by h2.mktvl desc))

from Hold_RPT H (nolock)
where H.AsofDate=@AsofDateProcessed
and h.cusip='-cash-'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Well Done is better than well said
- Ben Franklin

 

[SQLSister]

well to start with even though you are only selecting the value of a variable you are joining to this big table for the times when the variable might be null, but you are joining to it every time it runs.

So maybe instead of coalesce it would be faster to use and if statement - then at least it would only hit that table if it needed to.

Also subselects are normally much slower than derived tables.

Finally look at your indexing, you are ordering on mktvl, is it indexed?

Is that distinct really needed, usually when acessing a table of codes, it would not be. Distinct slows things down as well.


"NOTHING is more important in a database than integrity." ESquared

 

[nice95gle]

Ok...tell me play with some of your suggestions and get back to you.

Thanks SQLSister.

Well Done is better than well said
- Ben Franklin

 

[genomon]

SET NOCOUNT ON may help a wee bit...

Alcohol and calculus don't mix, so don't drink and derive.

 

[nice95gle]

I'm still working on it, got it down to exactly 4 mins. But thank you both for the taking the time to response.

Well Done is better than well said
- Ben Franklin

 

[gmmastros]

4 minutes is still too long. What does your query look like now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

Is there something about ALTER vs CREATE PROCEDURE; I think I read somewhere that ALTER runs differently (possibly longer) because it doesn't store an execution plan. Shoot, I may be thinking about queries....
Sorry to be so vague, but no time to research right now

Alcohol and calculus don't mix, so don't drink and derive.

 

[ca8msm]

and h2.sect in
            (select distinct lkup as Tvl from Data_maintenance..ia_codes (nolock)
            where context ='Cashmmf')
        and h2.cusip not like '-cash-'

A few questions..

1) Instead of the "in" statement, couldn't you join directly to the table?

2) What is the "not like" doing? Shouldn't there be a % sign if you are performing a "like" comparision?

3) What does the execution plan show? Are there any scans?

4) What indexes do you have on the table?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[genomon]

Here is what I was after, but don't think it gives any love to your problem:

Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either inefficient or invalid, given the new state of the database. SQL Server detects the changes that invalidate an execution plan, and marks the plan as invalid. A new plan must then be recompiled for the next connection that executes the query. The conditions that cause a plan to be invalidated include:

Any structural changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).


New distribution statistics generated either explicitly from a statement such as UPDATE STATISTICS or automatically.


Dropping an index used by the execution plan.


An explicit call to sp_recompile.


Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).


For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.

Alcohol and calculus don't mix, so don't drink and derive.