January 22, 2003 at 7:52 pm
A:
alter procedure dbo.us_wyj_delivergroup_09
@strdate varchar(16),
@par1 int ,
@par12 varchar(20),
@par2 int,
@par3 int,
@par31 int,
@par311 varchar(20),
@par4 int,
@par41 int,
@par411 varchar(20),
@par42 int,
@par421 varchar(20),
@par431 varchar(20),
@par5 int,
@par51 int,
@par511 varchar(20),
@par512 varchar(20)
as
if object_id('tempdb..##us_depart') is not null
drop table ##us_depart
Declare @lc_date1 char(8),@lc_date2 char(8)
Declare @ld_date1 datetime,@ld_date2 datetime
select @lc_date1 = left(@strdate,8)
select @lc_date2 = right(@strdate,8)
declare @exesql varchar(6000)
set @exesql='
select operatedate,dbo.bf_QuantityExchange(sum(defaultquantity),defaultunitid,3)as defaultquantity, sum( amountincludetax ) as amountincludetax
into ##us_depart
from b_businessaccount with (index(id_businessaccount_operatedate))
where
operatedate >='''+@lc_date1+''' and operatedate<='''+@lc_date2+'''
'
declare @sql varchar(6000)
set @sql=' and storeinoutreceipttypeid>5 '
if @par1=1
set @sql=@sql+' and DATENAME(weekday,operatedate)='''+@par12 +''''
if @par2=1
set @sql=@sql+' and storeinoutreceipttypeid=7 '
if @par3=1
begin
if @par31=1
set @sql=@sql+' and productid in (select productid from productinfo where productcode like '+@par311+')'
else
set @sql=@sql+' and productid in (select productid from productinfo where ismainproduct=1)'
end
if @par4=1
if @par41=1
set @sql=@sql+' and targetid in (select customerid from f_customer where jycustomtype='+@par411+')'
else if @par42=1
set @sql=@sql+' and targetid in (select customerid from f_customer where customercode='+convert(varchar(20),@par421)+')'
else
set @sql=@sql+' and targetid in (select customerid from f_customer where customercode='+convert(varchar(20),@par431)+')'
if @par5=1
begin
if @par51=1
set @sql=@sql+' and targetid in (select customerid from f_customer where delivergroupid in (select delivergroupid from f_delivergroup where departid='+@par511+'))'
else
set @sql=@sql+' and targetid in (select customerid from f_customer where delivergroupid in (select delivergroupid from f_delivergroup where delivergroupcode='+@par512+'))'
end
set @exesql=@exesql+@sql+' group by operatedate,defaultunitid'
exec(@exesql)
exec('select operatedate as operatedate,DATENAME(weekday,operatedate)as operateweek, sum(defaultquantity) as operatequantity, sum(amountincludetax) as operatetax from ##us_depart group by operatedate order by operatedate ')
exec('select sum(defaultquantity)as quantity ,sum(amountincludetax)as tax ,count(Distinct(operatedate)) as num from ##us_depart ' )
drop table ##us_depart
GO
January 22, 2003 at 7:54 pm
B:
ALTER procedure dbo.us_wyj_delivergroup_06
@strdate varchar(16),
@par1 int ,
@par12 varchar(20),
@par2 int,
@par3 int,
@par31 int,
@par311 varchar(20),
@par4 int,
@par41 int,
@par411 varchar(20),
@par42 int,
@par421 varchar(20),
@par431 varchar(20),
@par5 int,
@par51 int,
@par511 varchar(20),
@par512 varchar(20)
as
--if exists (select * from dbo.sysobjects where id = object_id(N'##us_depart')) --and OBJECTPROPERTY(id, N'IsUserTable') = 1)
if object_id('tempdb..##us_depart') is not null
drop table ##us_depart
Declare @lc_date1 char(8),@lc_date2 char(8)
Declare @ld_date1 datetime,@ld_date2 datetime
select @lc_date1 = left(@strdate,8)
select @lc_date2 = right(@strdate,8)
----------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------
declare @exesql varchar(6000)
set @exesql='
select a.operatedate,dbo.bf_QuantityExchange(sum(a.defaultquantity),a.defaultunitid,3)as defaultquantity, sum( a.amountincludetax ) as amountincludetax
into ##us_depart
from b_businessaccount as a with (index(id_businessaccount_operatedate)) ,f_customer as b, d_delivergroup as c ,f_productinfo as d
where
a.operatedate >='''+@lc_date1+''' and a.operatedate<='''+@lc_date2+'''
and b.delivergroupid=c.delivergroupid and a.targetid=b.customerid
and a.productid=d.productid
'
----------------------------------------------------------------------
declare @sql varchar(6000)
set @sql=' and a.storeinoutreceipttypeid>5 '
if @par1=1
set @sql=@sql+' and DATENAME(weekday,a.operatedate)='''+@par12 +''''
if @par2=1
set @sql=@sql+' and a.storeinoutreceipttypeid=7 '
if @par3=1
begin
if @par31=1
set @sql=@sql+' and d.productcode like '+@par311
else
set @sql=@sql+' and d.ismainproduct=1'
end
if @par4=1
if @par41=1
set @sql=@sql+' and b.jycustomtype='+@par411
else if @par42=1
set @sql=@sql+' and b.customercode='+convert(varchar(20),@par421)
else
set @sql=@sql+' and b.customercode='+convert(varchar(20),@par431)
if @par5=1
begin
if @par51=1
set @sql=@sql+' and c.departid='+@par511
else
set @sql=@sql+' and c.delivergroupcode='+@par512
end
set @exesql=@exesql+@sql+' group by a.operatedate ,a.defaultunitid'
exec(@exesql)
exec('select operatedate as operatedate,DATENAME(weekday,operatedate)as operateweek, sum(defaultquantity) as operatequantity, sum(amountincludetax) as operatetax from ##us_depart group by operatedate order by operatedate ')
exec('select sum(defaultquantity)as quantity ,sum(amountincludetax)as tax ,count(Distinct(operatedate)) as num from ##us_depart ' )
---------------------------------------------------------------------------
drop table ##us_depart
GO
January 22, 2003 at 7:58 pm
which is better 'a' or 'b' ?
any good advice to improve the two stored procedures ?
thanks in advance.
January 24, 2003 at 6:47 am
quote:
which is better 'a' or 'b' ?any good advice to improve the two stored procedures ?
Neither a nor b are particularly appealing. There are a number of factors that could be contributing to the poor performance of _either_ of those queries.
1. Do not use index hints unless you are absolutely sure that the query processor is not choosing to use the specific index itself.
2. Almost certainly, this procedure is recompiling its execution plan every time it is run. (To verify this, run a Profiler trace on recompilations...). The reasons for recompilation range from the fact that you are using the EXEC statement on dynamically generated SQL rather than simply executing SQL statements. Read more about this online, and if you absolutely _have_ to use dynamically generated SQL, execute it using the sp_executesql stored procedure and you have a better chance of not recompiling the query plan each time.
3. Do not mix DML with DDL in the same procedure. You are dropping and creating (with a SELECT INTO statement) a global temp table ##us_data) This alone will result in poor performance. First, try to redesign the solution to NOT use the global temp table. If it is absolutely necessary to use one, then create a separate procedure that handles the creation of the table, then call that procedure from within this one. That way, both execution plans can be compiled and saved separately.
4. In the WHERE expression you are building, you are doing multiple, and nested, subselects (all the IN() expressions). Replace these with INNER JOINS and you will reduce the IO reads dramatically from the f_customer and productinfo tables.
That's all for now. Try those things and post some results...
Cheers,
Jay
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply