Select Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select Query Performance

Author

Topic

cardgunner

326 Posts

Posted - 2014-06-20 : 10:17:28

First of all my terminology may not be right for the different elements of SQL query.

I have a select query that does not perform well (takes are 7 minutes to run).

It performs OK (1 minute to run) when I take out the lines in the where clause that get their info from the declare statements at the beginning of the query.

The two main tables in the query has 1227551 records(sli245) and 873570 records (sli255)

What can be done to optimize the performance leaving the declare criteria in it?

I am using the dropcleanbuffers just for testing purposes. They will not remain in the final product.

I have found no change when using UNION ALL

Taking out the max queries in the where clauses also goes nothing or not enough to matter.

I run both sides of the union individually and they both run under 1 minute.

It really appears to be something with the declare at the beginning.

This query is used for reporting purposes thru SSRS where the users will specify the criteria. Most of the time it is run on a month's worth of data.


use companydb
go
checkpoint;
go
dbcc dropcleanbuffers;
go

declare @brnch varchar(6)
declare @date1 datetime
declare @date2 datetime
declare @ext_emno varchar(4)
declare @ordtyp varchar(2)
declare @int_emno varchar(4)
declare @srvtyp varchar(2)

set @brnch=''
set @date1='2012-06-01 00:00:00.000'
set @date2='2012-07-01 00:00:00.000'
set @ext_emno=''
set @ordtyp=''
set @int_emno=''
set @srvtyp=''

select	
	gp.ord_type as 'ord_type',
	gp.inv_type as 'inv_type',
	gp.inv_numb as 'inv_numb',
	gp.ord_numb as 'ord_numb',
	gp.ord_segm as 'ord_segm',
	gp.lin_numb as 'line_num',
	gp.ord_dept as 'ord_dept',
	gp.itm_mnfr as 'itm_mnfr',
	gp.itm_srce as 'itm_srce',
	gp.inv_cust as 'inv_cust',
	gp.itm_numb as 'itm_numb',
	gp.itm_qnty as 'itm_qnty',
	gp.itm_invd as 'itm_invd',
	case 
		when gp.itm_sale is NULL then 0
		else gp.itm_sale
	end as 'itm_sale',
	gp.itm_cost as 'itm_cost',
	gp.grs_prof as 'grs_prof',
	gp.grp_perc as 'grp_perc',
	case
		when gp.int_reps is NULL then ''
		else gp.int_reps
	end as 'int_reps',
	case
		when gp.ext_reps is NULL then ''
		else gp.ext_reps 
	end as 'ext_reps',
	gp.inv_date as 'inv_date',
	case
		when gp.int_repn is NULL then ''
		else gp.int_repn
	end as 'int_repn',
	ccom001.t_nama ext_repn
from (	
	select
		'service' as 'ord_type',
		sli255.t_ityp as 'inv_type',
		sli255.t_idoc as 'inv_numb',
		sli255.t_srvo as 'ord_numb',
		sli255.t_acln as 'ord_segm',
		sli255.t_lino as 'lin_numb',
		sli250.t_cofc as 'ord_dept',
		ibd001.t_cmnf as 'itm_mnfr',
		ibd001.t_cpcl as 'itm_srce',
		RTRIM(sli250.t_itbp) + ' - ' + com100.t_nama as 'inv_cust',
		case 
			when sli255.t_invt = '10' then ltrim(rtrim(sli255.t_item)) 
            when sli255.t_invt = '24' then ltrim(rtrim(sli255.t_desc))
        end as 'itm_numb',
		sli255.t_dqua as 'itm_qnty',
		case
			when (sli255.t_amti-sli255.t_ldai) = 0 then soc220.t_inam
			else(sli255.t_amti-sli255.t_ldai)
		end as 'itm_invd',
		soc220.t_asin as 'itm_sale',
		sli255.t_acin_1 as 'itm_cost',
		case
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) != 0) then (soc220.t_inam - sli255.t_acin_1)
			else ((sli255.t_amti-sli255.t_ldai) - sli255.t_acin_1)
		end as 'grs_prof',
		case
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) = 0) then 0
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) != 0) then ((soc220.t_inam) - (sli255.t_acin_1))/(soc220.t_inam) 
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) != 0) then ((sli255.t_amti-sli255.t_ldai) - (sli255.t_acin_1))/(sli255.t_amti-sli255.t_ldai) 
			else 0
		end as 'grp_perc',
		sli255.t_ratd as 'inv_date',
		soc220.t_crep as 'int_reps',
		sli250.t_crep_c as 'ext_reps',
		com001.t_nama as 'int_repn'
	from tcisli255100 as sli255
	join tcisli250100 as sli250 on sli255.t_srvo = sli250.t_srvo
	join ( 
		select	
			t_srvo,
			case
				when t_srvo LIKE 'T%' then 'TRUCK'
				when t_srvo LIKE 'E%' then 'EQUIP'
			end as 'srv_area'
		from tcisli250100)
	as sli250_2 on sli250.t_srvo = sli250_2.t_srvo
	left join ttcibd001100 as ibd001 on sli255.t_item = ibd001.t_item
	join ttccom100100 as com100 on sli250.t_itbp = com100.t_bpid
	left join ttssoc220100 as soc220 on sli255.t_srvo = soc220.t_orno and sli255.t_acln = soc220.t_acln and sli255.t_lino = soc220.t_lino
	left join ttccom001100 as com001 on soc220.t_crep = com001.t_emno
	where 
		(sli255.t_invt = '10') and 
		(@srvtyp = '' or sli250_2.srv_area = @srvtyp) and
		sli255.t_vers_c = (select max(a.t_vers_c) from tcisli255100 as a where ((a.t_srvo = sli255.t_srvo) and (a.t_acln = sli255.t_acln) and (a.t_lino = sli255.t_lino))) and
		(@int_emno = '' or com001.t_emno = @int_emno) and 
		(sli255.t_ratd between @date1 and @date2) and 
		(@brnch='' or substring(sli250.t_cofc,3,1)=@brnch)

	UNION
	select
		'sales' as 'ord_type',
		sli245.t_ityp as 'inv_type',
		sli245.t_idoc as 'inv_numb',
		sli245.t_slso as 'ord_numb',
		'' as 'ord_segm',
		sli245.t_pono as 'lin_numb',
		sli245.t_cofc  as 'ord_dept',
		ibd001.t_cmnf as 'itm_mnfr',
		ibd001.t_cpcl as 'itm_srce',
		rTRIM(sli240.t_itbp) + ' - ' + com100.t_nama as 'inv_cust',
		ltrim(rtrim(sli245.t_item)) as 'itm_numb',
		sli245.t_dqua as 'itm_qnty',
		(sli245.t_amti-sli245.t_ldai) as 'itm_invd',
		(sli245.t_amti-sli245.t_ldai) as 'itm_sale',
		sli245.t_copr_1 as 'itm_cost',
		((sli245.t_amti-sli245.t_ldai) - sli245.t_copr_1) as 'grs_prof',
		case
			when cast((sli245.t_amti-sli245.t_ldai) as decimal(10,2)) = 0 then 0
			else(((sli245.t_amti-sli245.t_ldai) - sli245.t_copr_1)/(sli245.t_amti-sli245.t_ldai)) 
		end as 'grp_perc',
		sli245.t_ratd as 'inv_date',
		sli240.t_crep_c as 'int_reps',
		sli240.t_crep as 'ext_reps',
		com001.t_nama as 'int_repn'
	from tcisli245100 as sli245
	join tcisli240100 as sli240 on sli245.t_slso = sli240.t_slso
	left join ttcibd001100 as ibd001 on sli245.t_item = ibd001.t_item
	join ttccom100100 as com100 on sli240.t_itbp = com100.t_bpid
	join ttccom001100 as com001 on sli240.t_crep_c = com001.t_emno
	where 
		(sli245.t_vers_c = (select max(a.t_vers_c) from tcisli245100 as a where ((a.t_slso = sli245.t_slso) and (a.t_pono = sli245.t_pono)))) and
		(sli245.t_ityp = ' RP' or sli245.t_ityp = '  P') and
		(sli245.t_chtp=1 ) and
		(@int_emno = '' or com001.t_emno = @int_emno) and
		(sli245.t_ratd between @date1 and @date2) and 
		(@brnch='' or substring(sli245.t_cofc,3,1)=@brnch)

)gp left join ttccom001100 ccom001 on ccom001.t_emno=gp.ext_reps
where
	(@ordtyp = '' or gp.ord_type = @ordtyp) and 
	(@ext_emno='' or gp.ext_reps=@ext_emno) and (gp.int_reps != 1015)
order by 
	gp.int_reps, 
	gp.inv_date

CardGunner

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-20 : 11:40:55

The corelated subquery


(select max(a.t_vers_c) from tcisli245100 as a where ((a.t_slso = sli245.t_slso) and (a.t_pono = sli245.t_pono))))

in the where clause is causing an O(n^2) query. Basically, for every row in the main query, SQL is executing the subquery. Try to convert it to a join and subquery instead.

cardgunner

326 Posts

Posted - 2014-06-20 : 12:02:10

I did take that out and ran it without and there was no change in performance. I found that odd. Seeing I tried 100 different things I'll try it again however as of now that is not a factor.

CardGunner

cardgunner

326 Posts

Posted - 2014-06-20 : 13:42:17

I stand corrected. By eliminating them it did increase the performance. It runs twice as fast. However it still takes over 3 minutes to run. Comparatively I take the same max statement out and write in the dates where needed and eliminate all the @ criteria and it runs in 40 seconds.

CardGunner

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-20 : 14:30:43

Of course it runs faster that way! You're doing sql's work for it

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-20 : 14:48:31

Here's how I tackle it:

Run this in your query window (it's per session). You can either keep running it while testing things or just comment it out as it's now set to ON for that session.
SET STATISTICS IO ON

Show us the output that you'll now have due to this setting.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cardgunner

326 Posts

Posted - 2014-06-20 : 14:58:48

Okay so I got query 'G' and Query 'B'

G runs OK at 1:16

B is painfully slow at 6:59

@gbritton "Of course it runs faster that way! You're doing sql's work for it"
Does that mean that it can't get any faster then 7 minutes?

Again this query fuels a report and the users will select criteria and that criteria will drive the report so it will have to have a declare statment.

Query G

checkpoint;
go
dbcc dropcleanbuffers;
go

select	
	gp.ord_type as 'ord_type',
	gp.inv_type as 'inv_type',
	gp.inv_numb as 'inv_numb',
	gp.ord_numb as 'ord_numb',
	gp.ord_segm as 'ord_segm',
	gp.lin_numb as 'line_num',
	gp.ord_dept as 'ord_dept',
	gp.itm_mnfr as 'itm_mnfr',
	gp.itm_srce as 'itm_srce',
	gp.inv_cust as 'inv_cust',
	gp.itm_numb as 'itm_numb',
	gp.itm_qnty as 'itm_qnty',
	gp.itm_invd as 'itm_invd',
	case 
		when gp.itm_sale is NULL then 0
		else gp.itm_sale
	end as 'itm_sale',
	gp.itm_cost as 'itm_cost',
	gp.grs_prof as 'grs_prof',
	gp.grp_perc as 'grp_perc',
	case
		when gp.int_reps is NULL then ''
		else gp.int_reps
	end as 'int_reps',
	case
		when gp.ext_reps is NULL then ''
		else gp.ext_reps 
	end as 'ext_reps',
	gp.inv_date as 'inv_date',
	case
		when gp.int_repn is NULL then ''
		else gp.int_repn
	end as 'int_repn',
	ccom001.t_nama ext_repn
from (	
	select
		'service' as 'ord_type',
		sli255.t_ityp as 'inv_type',
		sli255.t_idoc as 'inv_numb',
		sli255.t_srvo as 'ord_numb',
		sli255.t_acln as 'ord_segm',
		sli255.t_lino as 'lin_numb',
		sli250.t_cofc as 'ord_dept',
		ibd001.t_cmnf as 'itm_mnfr',
		ibd001.t_cpcl as 'itm_srce',
		RTRIM(sli250.t_itbp) + ' - ' + com100.t_nama as 'inv_cust',
		case 
			when sli255.t_invt = '10' then ltrim(rtrim(sli255.t_item)) 
            when sli255.t_invt = '24' then ltrim(rtrim(sli255.t_desc))
        end as 'itm_numb',
		sli255.t_dqua as 'itm_qnty',
		case
			when (sli255.t_amti-sli255.t_ldai) = 0 then soc220.t_inam
			else(sli255.t_amti-sli255.t_ldai)
		end as 'itm_invd',
		soc220.t_asin as 'itm_sale',
		sli255.t_acin_1 as 'itm_cost',
		case
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) != 0) then (soc220.t_inam - sli255.t_acin_1)
			else ((sli255.t_amti-sli255.t_ldai) - sli255.t_acin_1)
		end as 'grs_prof',
		case
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) = 0) then 0
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) != 0) then ((soc220.t_inam) - (sli255.t_acin_1))/(soc220.t_inam) 
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) != 0) then ((sli255.t_amti-sli255.t_ldai) - (sli255.t_acin_1))/(sli255.t_amti-sli255.t_ldai) 
			else 0
		end as 'grp_perc',
		sli255.t_ratd as 'inv_date',
		soc220.t_crep as 'int_reps',
		sli250.t_crep_c as 'ext_reps',
		com001.t_nama as 'int_repn'
	from tcisli255100 as sli255
	join tcisli250100 as sli250 on sli255.t_srvo = sli250.t_srvo
	join ( 
		select	
			t_srvo,
			case
				when t_srvo LIKE 'T%' then 'TRUCK'
				when t_srvo LIKE 'E%' then 'EQUIP'
			end as 'srv_area'
		from tcisli250100)
	as sli250_2 on sli250.t_srvo = sli250_2.t_srvo
	left join ttcibd001100 as ibd001 on sli255.t_item = ibd001.t_item
	join ttccom100100 as com100 on sli250.t_itbp = com100.t_bpid
	left join ttssoc220100 as soc220 on sli255.t_srvo = soc220.t_orno and sli255.t_acln = soc220.t_acln and sli255.t_lino = soc220.t_lino
	left join ttccom001100 as com001 on soc220.t_crep = com001.t_emno
	join(select max(t_vers_c) as t_vers_c, t_srvo, t_acln, t_lino
		from tcisli255100
		group by t_srvo, t_acln, t_lino
		)a on a.t_srvo=sli255.t_srvo and a.t_acln=sli255.t_acln and a.t_lino=sli255.t_lino and a.t_vers_c=sli255.t_vers_c
	where 
		(sli255.t_invt = '10')  and
		(sli255.t_ratd between '2012-06-01 00:00:00.000' and '2012-07-01 00:00:00.000')
		
	UNION

	select
		'sales' as 'ord_type',
		sli245.t_ityp as 'inv_type',
		sli245.t_idoc as 'inv_numb',
		sli245.t_slso as 'ord_numb',
		'' as 'ord_segm',
		sli245.t_pono as 'lin_numb',
		sli245.t_cofc  as 'ord_dept',
		ibd001.t_cmnf as 'itm_mnfr',
		ibd001.t_cpcl as 'itm_srce',
		rTRIM(sli240.t_itbp) + ' - ' + com100.t_nama as 'inv_cust',
		ltrim(rtrim(sli245.t_item)) as 'itm_numb',
		sli245.t_dqua as 'itm_qnty',
		(sli245.t_amti-sli245.t_ldai) as 'itm_invd',
		(sli245.t_amti-sli245.t_ldai) as 'itm_sale',
		sli245.t_copr_1 as 'itm_cost',
		((sli245.t_amti-sli245.t_ldai) - sli245.t_copr_1) as 'grs_prof',
		case
			when cast((sli245.t_amti-sli245.t_ldai) as decimal(10,2)) = 0 then 0
			else(((sli245.t_amti-sli245.t_ldai) - sli245.t_copr_1)/(sli245.t_amti-sli245.t_ldai)) 
		end as 'grp_perc',
		sli245.t_ratd as 'inv_date',
		sli240.t_crep_c as 'int_reps',
		sli240.t_crep as 'ext_reps',
		com001.t_nama as 'int_repn'
	from tcisli245100 as sli245
	join tcisli240100 as sli240 on sli245.t_slso = sli240.t_slso
	left join ttcibd001100 as ibd001 on sli245.t_item = ibd001.t_item
	join ttccom100100 as com100 on sli240.t_itbp = com100.t_bpid
	join ttccom001100 as com001 on sli240.t_crep_c = com001.t_emno
	join(select max(t_vers_c) as t_vers, t_slso, t_pono
		from tcisli245100
		group by t_slso, t_pono
		)a on a.t_slso=sli245.t_slso and a.t_pono=sli245.t_pono and a.t_vers=sli245.t_vers_c
	where 
		(sli245.t_ityp = ' RP' or sli245.t_ityp = '  P') and
		(sli245.t_chtp=1 ) and
		(sli245.t_ratd between '2012-06-01 00:00:00.000' and '2012-07-01 00:00:00.000')  
		
)gp left join ttccom001100 ccom001 on ccom001.t_emno=gp.ext_reps

order by 
	gp.int_reps, 
	gp.inv_date

Query B

checkpoint;
go
dbcc dropcleanbuffers;
go

declare @brnch varchar(6)
declare @date1 datetime
declare @date2 datetime
declare @ext_emno varchar(4)
declare @ordtyp varchar(2)
declare @int_emno varchar(4)
declare @srvtyp varchar(2)

set @brnch=''
set @date1='2012-06-01 00:00:00.000'
set @date2='2012-07-01 00:00:00.000'
set @ext_emno=''
set @ordtyp=''
set @int_emno=''
set @srvtyp=''

select	
	gp.ord_type as 'ord_type',
	gp.inv_type as 'inv_type',
	gp.inv_numb as 'inv_numb',
	gp.ord_numb as 'ord_numb',
	gp.ord_segm as 'ord_segm',
	gp.lin_numb as 'line_num',
	gp.ord_dept as 'ord_dept',
	gp.itm_mnfr as 'itm_mnfr',
	gp.itm_srce as 'itm_srce',
	gp.inv_cust as 'inv_cust',
	gp.itm_numb as 'itm_numb',
	gp.itm_qnty as 'itm_qnty',
	gp.itm_invd as 'itm_invd',
	case 
		when gp.itm_sale is NULL then 0
		else gp.itm_sale
	end as 'itm_sale',
	gp.itm_cost as 'itm_cost',
	gp.grs_prof as 'grs_prof',
	gp.grp_perc as 'grp_perc',
	case
		when gp.int_reps is NULL then ''
		else gp.int_reps
	end as 'int_reps',
	case
		when gp.ext_reps is NULL then ''
		else gp.ext_reps 
	end as 'ext_reps',
	gp.inv_date as 'inv_date',
	case
		when gp.int_repn is NULL then ''
		else gp.int_repn
	end as 'int_repn',
	ccom001.t_nama ext_repn
from (	
	select
		'service' as 'ord_type',
		sli255.t_ityp as 'inv_type',
		sli255.t_idoc as 'inv_numb',
		sli255.t_srvo as 'ord_numb',
		sli255.t_acln as 'ord_segm',
		sli255.t_lino as 'lin_numb',
		sli250.t_cofc as 'ord_dept',
		ibd001.t_cmnf as 'itm_mnfr',
		ibd001.t_cpcl as 'itm_srce',
		RTRIM(sli250.t_itbp) + ' - ' + com100.t_nama as 'inv_cust',
		case 
			when sli255.t_invt = '10' then ltrim(rtrim(sli255.t_item)) 
            when sli255.t_invt = '24' then ltrim(rtrim(sli255.t_desc))
        end as 'itm_numb',
		sli255.t_dqua as 'itm_qnty',
		case
			when (sli255.t_amti-sli255.t_ldai) = 0 then soc220.t_inam
			else(sli255.t_amti-sli255.t_ldai)
		end as 'itm_invd',
		soc220.t_asin as 'itm_sale',
		sli255.t_acin_1 as 'itm_cost',
		case
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) != 0) then (soc220.t_inam - sli255.t_acin_1)
			else ((sli255.t_amti-sli255.t_ldai) - sli255.t_acin_1)
		end as 'grs_prof',
		case
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) = 0) then 0
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) = 0) and (cast(soc220.t_inam as decimal(10,2)) != 0) then ((soc220.t_inam) - (sli255.t_acin_1))/(soc220.t_inam) 
			when (cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) != 0) then ((sli255.t_amti-sli255.t_ldai) - (sli255.t_acin_1))/(sli255.t_amti-sli255.t_ldai) 
			else 0
		end as 'grp_perc',
		sli255.t_ratd as 'inv_date',
		soc220.t_crep as 'int_reps',
		sli250.t_crep_c as 'ext_reps',
		com001.t_nama as 'int_repn'
	from tcisli255100 as sli255
	join tcisli250100 as sli250 on sli255.t_srvo = sli250.t_srvo
	join ( 
		select	
			t_srvo,
			case
				when t_srvo LIKE 'T%' then 'TRUCK'
				when t_srvo LIKE 'E%' then 'EQUIP'
			end as 'srv_area'
		from tcisli250100)
	as sli250_2 on sli250.t_srvo = sli250_2.t_srvo
	left join ttcibd001100 as ibd001 on sli255.t_item = ibd001.t_item
	join ttccom100100 as com100 on sli250.t_itbp = com100.t_bpid
	left join ttssoc220100 as soc220 on sli255.t_srvo = soc220.t_orno and sli255.t_acln = soc220.t_acln and sli255.t_lino = soc220.t_lino
	left join ttccom001100 as com001 on soc220.t_crep = com001.t_emno
	join(select max(t_vers_c) as t_vers_c, t_srvo, t_acln, t_lino
		from tcisli255100
		group by t_srvo, t_acln, t_lino
		)a on a.t_srvo=sli255.t_srvo and a.t_acln=sli255.t_acln and a.t_lino=sli255.t_lino and a.t_vers_c=sli255.t_vers_c
	where 
		(sli255.t_invt = '10') and 
		(@srvtyp = '' or sli250_2.srv_area = @srvtyp) and
		(@int_emno = '' or com001.t_emno = @int_emno) and 
		(sli255.t_ratd between @date1 and @date2) and 
		(@brnch='' or substring(sli250.t_cofc,3,1)=@brnch)

	UNION
	select
		'sales' as 'ord_type',
		sli245.t_ityp as 'inv_type',
		sli245.t_idoc as 'inv_numb',
		sli245.t_slso as 'ord_numb',
		'' as 'ord_segm',
		sli245.t_pono as 'lin_numb',
		sli245.t_cofc  as 'ord_dept',
		ibd001.t_cmnf as 'itm_mnfr',
		ibd001.t_cpcl as 'itm_srce',
		rTRIM(sli240.t_itbp) + ' - ' + com100.t_nama as 'inv_cust',
		ltrim(rtrim(sli245.t_item)) as 'itm_numb',
		sli245.t_dqua as 'itm_qnty',
		(sli245.t_amti-sli245.t_ldai) as 'itm_invd',
		(sli245.t_amti-sli245.t_ldai) as 'itm_sale',
		sli245.t_copr_1 as 'itm_cost',
		((sli245.t_amti-sli245.t_ldai) - sli245.t_copr_1) as 'grs_prof',
		case
			when cast((sli245.t_amti-sli245.t_ldai) as decimal(10,2)) = 0 then 0
			else(((sli245.t_amti-sli245.t_ldai) - sli245.t_copr_1)/(sli245.t_amti-sli245.t_ldai)) 
		end as 'grp_perc',
		sli245.t_ratd as 'inv_date',
		sli240.t_crep_c as 'int_reps',
		sli240.t_crep as 'ext_reps',
		com001.t_nama as 'int_repn'
	from tcisli245100 as sli245
	join tcisli240100 as sli240 on sli245.t_slso = sli240.t_slso
	left join ttcibd001100 as ibd001 on sli245.t_item = ibd001.t_item
	join ttccom100100 as com100 on sli240.t_itbp = com100.t_bpid
	join ttccom001100 as com001 on sli240.t_crep_c = com001.t_emno
	join(select max(t_vers_c) as t_vers, t_slso, t_pono
		from tcisli245100
		group by t_slso, t_pono
		)a on a.t_slso=sli245.t_slso and a.t_pono=sli245.t_pono and a.t_vers=sli245.t_vers_c
	where 
		(sli245.t_ityp = ' RP' or sli245.t_ityp = '  P') and
		(sli245.t_chtp=1 ) and
		(@int_emno = '' or com001.t_emno = @int_emno) and
		(sli245.t_ratd between @date1 and @date2) and 
		(@brnch='' or substring(sli245.t_cofc,3,1)=@brnch)

)gp left join ttccom001100 ccom001 on ccom001.t_emno=gp.ext_reps
where
	(@ordtyp = '' or gp.ord_type = @ordtyp) and 
	(@ext_emno='' or gp.ext_reps=@ext_emno) and (gp.int_reps != 1015)
order by 
	gp.int_reps, 
	gp.inv_date

CardGunner

cardgunner

326 Posts

Posted - 2014-06-23 : 07:33:47

@tkizer - Tara thanks for the advice. Below are the results. I don't know what it all means but I'm researching it in the mean time.

Query B

Table 'ttcibd001100'. Scan count 1, logical reads 59422, physical reads 4393, read-ahead reads 4817, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ttccom100100'. Scan count 1, logical reads 41353, physical reads 598, read-ahead reads 399, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ttccom001100'. Scan count 2, logical reads 27344, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli245100'. Scan count 2, logical reads 147323, physical reads 4, read-ahead reads 146412, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli240100'. Scan count 1, logical reads 18109, physical reads 0, read-ahead reads 9978, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli255100'. Scan count 472851, logical reads 4135140, physical reads 12422, read-ahead reads 83757, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ttssoc220100'. Scan count 0, logical reads 3915680, physical reads 7366, read-ahead reads 51076, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli250100'. Scan count 2, logical reads 3936, physical reads 6, read-ahead reads 3928, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CardGunner

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-23 : 09:04:58

Couple of questions:

1. Why all the

cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2))

? What is the datatype of those columns?
2. Have you tried using pre-aggregation? That is, instead of using Select Max... as a subquery, build a temp table with the Max values and refer to that in your main query. It would mean one pass through the data to get the max values instead of one-per-row of the main query. If computing max is expensive, this may speed things up for you. You'll have to experiment with it to be sure.

Note that the i/o for table tcisli255100 indicates to me that pre-aggregation may help indeed.

cardgunner

326 Posts

Posted - 2014-06-23 : 10:20:58

1)t_amti and t_ldai are float. I had issues with this awhile ago and fixed it by casting them as decimal(10,2). What the issues were I can't remember it was years ago.

2)I know taking the max out has sped things up to be twice as fast. But creating a table to hold the max values would not work for live runs of data. The users like the data to be live. Maybe I'm missing something but I'm thinking I would create a table to hold the max values and update it daily or hourly but any case it would not be live data. So if the user corrected an order and then ran the report it wouldn't show correctly untill the table was updated.

I created new indexes on tcisli255100 and tcisli245100 and it sped things up a very little bit, 30 seconds faster.

CardGunner

cardgunner

326 Posts

Posted - 2014-06-23 : 10:47:45

I took out the cast((sli255.t_amti-sli255.t_ldai) as decimal(10,2)) and it actually took 30 seconds longer.

CardGunner

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-23 : 11:09:37

aha, so those values are float. that explains the cast since you are comparing to 0. with float arithmetic, sometimes you don't get 0 as a result but just a very small number. One approach is to say is the number within some tolerance, say 1 cent. So if abs(number) < .01, consider it to be zero.

How long does the Max subquery run on its own?

cardgunner

326 Posts

Posted - 2014-06-23 : 11:30:49

max of tcsli245100 is 21 secs with 1122863 rows
max of tcsli255100 is 14 secs with 652547 rows

Is there a wait time for indexes to index? Meaning I left the index on the test server and the run times are improving. How long does it take for the a new index to index?

CardGunner

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-23 : 11:31:17

Have you messed with the WHERE clause?

where
	(@ordtyp = '' or gp.ord_type = @ordtyp) and 
	(@ext_emno='' or gp.ext_reps=@ext_emno) and (gp.int_reps != 1015)

A lot of times OR predicates will cause performance issues (scans). So, I'd suggest removing the OR's and see what affect that has on performance. If it is minimal, then leave them. If significant, then you might want to try a dynamic catch-all query or multiple code paths to avoid the ORs.

Here is a link about catch-all queries:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

cardgunner

326 Posts

Posted - 2014-06-23 : 11:49:59

Funny that you have said this as I have removed (@ext_emno='' or gp.ext_reps=@ext_emno) and (gp.int_reps != 1015) and it runs alot faster on the test server. But not so much better on the live. Only differance I can see is I removed the new index from the live.

I created a new non-clustered index on tcicli255100 on fields srvo, item, acln, lino, vers_c, invt, ratd and on tcisli245100 on fields slso, item, ityp,chtp,ratd,pono,vers_c.

CardGunner

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-23 : 12:00:50

quote:
Originally posted by cardgunner

max of tcsli245100 is 21 secs with 1122863 rows
max of tcsli255100 is 14 secs with 652547 rows

Is there a wait time for indexes to index? Meaning I left the index on the test server and the run times are improving. How long does it take for the a new index to index?

CardGunner

Try pulling it out to a temp table and join on that. The execution plan may be computing the max several times over. pre-aggregation (even as a test) is a way to verify or refute that theory.

cardgunner

326 Posts

Posted - 2014-06-23 : 12:13:41

#gbritton - Okay but I never created a temp table before, so I'm looking it up and figuring out how to do this.

CardGunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-23 : 12:28:16

The STATISTICS IO output does not look good. You are likely missing indexes. I'd target these tables: ttssoc220100 and tcisli255100. But the other reads are very high too. Start with those two though.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cardgunner

326 Posts

Posted - 2014-06-23 : 13:07:00

I have never created (had to create) indexes before. What am I looking for? Do I make sure all the fields in the joins and the fields in the parameters are in a index or do I have to create a new one if they are not in one already.

CardGunner

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-23 : 13:24:20

Go for indexes on the predicates (join ON columns and WHERE columns). Also, beware that function calls in either ON or WHERE can cause the index to be bypassed

cardgunner

326 Posts

Posted - 2014-06-23 : 13:31:10

So I created to temp tables #sli245 and #sli255 and ran those and then ran the query and it took 3:06. Running normal took 6:33. If a reran with out the create tables using the tables I created it ran in 2:36. The joins I did was

join #sli255 as a on a.t_srvo=sli255.t_srvo and a.t_acln=sli255.t_acln and a.t_lino=sli255.t_lino and a.t_vers_c=sli255.t_vers_c
join #sli245 as a on a.t_slso=sli245.t_slso and a.t_pono=sli245.t_pono and a.t_vers_c=sli245.t_vers_c

So it's getting close.

Now if I take out the

where
	(@ordtyp = '' or gp.ord_type = @ordtyp) and 
	(@ext_emno='' or gp.ext_reps=@ext_emno) and (gp.int_reps != 1015)

It runs in 1:36.

So I'm getting close to getting close but I do need those parameters.

Also I haven't indexed the live server (which I just ran this on) so that may also help.

CardGunner

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources