CodingIsFun
Programmer
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.
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.
