Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure Performance Issue 4

Status
Not open for further replies.

CodingIsFun

Programmer
Apr 9, 2004
134
US
Hi all experts,

I have moved a process over to a new server. The stored procedures are running much slower. After an hour of investigating I found a solution to the problem. I was wondering if anyone can explain.

Windows 2000 Server.
Software MS Server2000 service pack 4.

the stored procedures:

create procedure dbo.summary_extract
@id int
AS

declare @id2 int
set @id2 = @id

select
bi.invoice_id,
bi.tax_type,
sum(isnull(tax_federal,0)) as federal_tax,
sum(isnull(tax_state,0)) as state_tax,
sum(isnull(tax_county,0)) as county_tax,
sum(isnull(tax_local,0)) as local_tax
from dbo.base bitb with (nolock)
left join dbo.items app with (nolock) on bitb.p_id = app.p_id
left join dbo.invoice bi with (nolock) on 0 = 0
and bi.account_id = coalesce(app.origin_a_id,bitb.a_id)
and bi.id = bitb.id
where 0 = 0
and bitb.id = @id2
group by bi.invoice_id,bi.tax_type
GO


create procedure dbo.summary_extract2
@id int
AS

select
bi.invoice_id,
bi.tax_type,
sum(isnull(tax_federal,0)) as federal_tax,
sum(isnull(tax_state,0)) as state_tax,
sum(isnull(tax_county,0)) as county_tax,
sum(isnull(tax_local,0)) as local_tax
from dbo.base bitb with (nolock)
left join dbo.items app with (nolock) on bitb.p_id = app.p_id
left join dbo.invoice bi with (nolock) on 0 = 0
and bi.account_id = coalesce(app.origin_a_id,bitb.a_id)
and bi.id = bitb.id
where 0 = 0
and bitb.id = @id
group by bi.invoice_id,bi.tax_type
GO

They both do the same thing but summary_extract2 takes 20 minutes and summary_extract takes 1 second

Is there any reason for this. Is it a server setting or is it service pack 4 or something else?

thanks in advance.
 
declare @id2 int
set @id2 = @id

WOW! and that took 19 to 20 seconds of the query! How did you ever think of doing that?

The only things that springs to mind are ansi set values at the time of compliation, or maybe collation...

You might try scripting both and see if there are any differences..

Rob
 
The good old parameter sniffer. Brilliant!

Thanks Denis well deserved Star.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top