SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select Query Performance
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/23/2014 :  13:33:22  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

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



Thanks. What if that field is already indexed in another non-cluster index. I shouldn't, or it wouldn't help to, index it again will it?

CardGunner
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 06/23/2014 :  13:43:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by cardgunner

quote:
Originally posted by gbritton

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



Thanks. What if that field is already indexed in another non-cluster index. I shouldn't, or it wouldn't help to, index it again will it?

CardGunner



Is it the first column in the index?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/23/2014 :  13:50:52  Show Profile  Reply with Quote
no

CardGunner
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 06/23/2014 :  13:52:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
Then the existing index is unlikely to be used unless it matches what your query needs. Ordering of the columns in the index matters.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/23/2014 :  14:14:47  Show Profile  Reply with Quote
So take table tcisli255100. It is in the query joined or it's in a where clauses with these fields t_srvo, t_item, t_acln, t_lino, t_vers_c, t_ratd and t_invt.

these are the current indexes

name               index                Column
tcisli255100	Itcisli255100_1a	t_acln
tcisli255100	Itcisli255100_1a	t_invt
tcisli255100	Itcisli255100_1a	t_lino
tcisli255100	Itcisli255100_1a	t_slcp
tcisli255100	Itcisli255100_1a	t_srvo
tcisli255100	Itcisli255100_1a	t_vers_c
tcisli255100	Itcisli255100_2a	t_acln
tcisli255100	Itcisli255100_2a	t_idoc
tcisli255100	Itcisli255100_2a	t_invt
tcisli255100	Itcisli255100_2a	t_ityp
tcisli255100	Itcisli255100_2a	t_lino
tcisli255100	Itcisli255100_2a	t_sfcp
tcisli255100	Itcisli255100_2a	t_slcp
tcisli255100	Itcisli255100_2a	t_srvo
tcisli255100	Itcisli255100_2a	t_vers_c
tcisli255100	Itcisli255100_3a	t_acln
tcisli255100	Itcisli255100_3a	t_invt
tcisli255100	Itcisli255100_3a	t_lino
tcisli255100	Itcisli255100_3a	t_slcp
tcisli255100	Itcisli255100_3a	t_srvo
tcisli255100	Itcisli255100_3a	t_stat
tcisli255100	Itcisli255100_3a	t_vers_c
tcisli255100	Itcisli255100_4a	t_acln
tcisli255100	Itcisli255100_4a	t_ccfg
tcisli255100	Itcisli255100_4a	t_codt
tcisli255100	Itcisli255100_4a	t_invt
tcisli255100	Itcisli255100_4a	t_lino
tcisli255100	Itcisli255100_4a	t_ojmd
tcisli255100	Itcisli255100_4a	t_oseq
tcisli255100	Itcisli255100_4a	t_slcp
tcisli255100	Itcisli255100_4a	t_srvo
tcisli255100	Itcisli255100_4a	t_stat
tcisli255100	Itcisli255100_4a	t_vers_c
tcisli255100	Itcisli255100_5a	t_acln
tcisli255100	Itcisli255100_5a	t_codt
tcisli255100	Itcisli255100_5a	t_invt
tcisli255100	Itcisli255100_5a	t_lino
tcisli255100	Itcisli255100_5a	t_slcp
tcisli255100	Itcisli255100_5a	t_srvo
tcisli255100	Itcisli255100_5a	t_stat
tcisli255100	Itcisli255100_5a	t_vers_c
tcisli255100	Itcisli255100_6a	t_acln
tcisli255100	Itcisli255100_6a	t_insq_c
tcisli255100	Itcisli255100_6a	t_invt
tcisli255100	Itcisli255100_6a	t_lino
tcisli255100	Itcisli255100_6a	t_orin_c
tcisli255100	Itcisli255100_6a	t_slcp
tcisli255100	Itcisli255100_6a	t_srvo
tcisli255100	Itcisli255100_6a	t_vers_c
tcisli255100	Itcisli255100_7a	t_acln
tcisli255100	Itcisli255100_7a	t_bilf_c
tcisli255100	Itcisli255100_7a	t_bilt_c
tcisli255100	Itcisli255100_7a	t_rcno_c
tcisli255100	Itcisli255100_7a	t_rrcd_c
tcisli255100	Itcisli255100_7a	t_srvo


t_ratd and t_item don't appear to be in any of them.

Do I create a new index for just t_ratd and then another for t_item?

Do I need to create any for the other items?

CardGunner

Edited by - cardgunner on 06/23/2014 14:15:30
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 06/23/2014 :  14:18:18  Show Profile  Reply with Quote
If you have indexs on gp.ord_type, gp.ext_reps and gp.int_reps it should help.

Also, this won't speed anything up, but you can write that last where clause more succinctly:


where @ordtyp in ('', gp.ord_type) 
  and @ext_emno in ('', gp.ext_reps) 
  and (gp.int_reps != 1015)
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 06/23/2014 :  14:20:41  Show Profile  Reply with Quote
Note that having each column indexed might speed up your query, but it will certainly kill INSERTs and DELETEs. BTW, what is your clustered index?
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/23/2014 :  14:32:50  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

Note that having each column indexed might speed up your query, but it will certainly kill INSERTs and DELETEs. BTW, what is your clustered index?



I don't do any inserts or deletes.

What I'm struggling with now is how many times should a field be in an index and the ordering of it.

CardGunner
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/23/2014 :  15:27:47  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

Note that having each column indexed might speed up your query, but it will certainly kill INSERTs and DELETEs. BTW, what is your clustered index?



What table are you looking to see the index on?

CardGunner
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 06/23/2014 :  15:52:03  Show Profile  Reply with Quote
What's your ci ON tcisli255100 ? Just Curious to see what columns and types. (Don't want entries to be too long).

BTW, someone does inserts/deletes on that table, since it already has data!
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/23/2014 :  16:10:52  Show Profile  Reply with Quote
I can't get the properties of this to open in a way that I can copy and paste so here it is.

t_slcp Asc Int 4 No No
t_srvo Asc nvarchar(9) 18 No No
t_acln Asc Int 4 No No
t_invt Asc Int 4 No No
t_lino Asc Int 4 No No
t_vers_c Asc Int 4 No No

This is the first index and it's clustered and unique. It is for tcisli255100

CardGunner
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/23/2014 :  16:26:10  Show Profile  Reply with Quote
So I got this query to run in 1:13 seconds with the temp tables created. However eveytime I try to write in put in any parameter in the where for for sli250.t_crep_c it doubles the run time.

I have indexed sli250.t_crep_c, and also indexed sli250.t_srvo



checkpoint;
go
dbcc dropcleanbuffers;
go

SET STATISTICS IO ON

declare @brnch varchar(6)
declare @date1 datetime
declare @date2 datetime
declare @ext_emno nvarchar(6)
declare @ordtyp varchar(8)
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',
	gp.ext_reps 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 #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
	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) 	
/* adding 	(@ext_emno='' or sli250.t_crep_c=@ext_emno) here doubles the run time) */

	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 #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
	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) and 
		(@ext_emno='' or sli240.t_crep_c=@ext_emno) 
)gp left join ttccom001100 ccom001 on ccom001.t_emno=gp.ext_reps and ccom001.t_emno!='1015'
where  @ordtyp in ('', gp.ord_type) 
/* adding 	(@ext_emno='' or gp.ext_reps=@ext_emno) here also doubles the run time) */
		
order by 
	gp.int_reps, 
	gp.inv_date

(18241 row(s) affected)
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 'ttcibd001100'. Scan count 2, logical reads 9680, physical reads 4, read-ahead reads 4835, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli240100'. Scan count 1, logical reads 18120, physical reads 0, read-ahead reads 3608, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#sli245_____________________________________________________________________________________________________________000000000119'. Scan count 1, logical reads 5425, physical reads 0, read-ahead reads 5393, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli245100'. Scan count 1, logical reads 136234, physical reads 3, read-ahead reads 135936, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ttccom100100'. Scan count 2, logical reads 2682, physical reads 0, read-ahead reads 1332, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ttccom001100'. Scan count 6, logical reads 94, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ttssoc220100'. Scan count 0, logical reads 27989, physical reads 1, read-ahead reads 945, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli250100'. Scan count 2, logical reads 3931, physical reads 6, read-ahead reads 3923, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#sli255_____________________________________________________________________________________________________________000000000118'. Scan count 1, logical reads 3472, physical reads 0, read-ahead reads 3448, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tcisli255100'. Scan count 1, logical reads 94723, physical reads 4, read-ahead reads 94718, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


CardGunner
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 06/23/2014 :  16:49:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
You may need to break the ORs into separate queries.

For instance: (sli245.t_ityp = ' RP' or sli245.t_ityp = ' P').

How does it perform if you run just one of those and then run it again with the other?
Does the total time for both run faster than if run together?

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

Edited by - tkizer on 06/23/2014 16:50:13
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/24/2014 :  07:33:46  Show Profile  Reply with Quote
Finally. So instead of (@ext_emno='' or gp.ext_emno=@ext_emno) I reversed the table (@ext_emno='' or ccom100.t_emno=@ext_emno)and it ran in less then a minute.

Why would that be?


CardGunner
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 06/24/2014 :  07:45:11  Show Profile  Reply with Quote
I noticed this section:


	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


Can't you collapse those two joins on the tcisli250100 table into one?

Also:

quote:

Finally. So instead of (@ext_emno='' or gp.ext_emno=@ext_emno) I reversed the table (@ext_emno='' or ccom100.t_emno=@ext_emno)and it ran in less then a minute.



Look at the IO against the two options. I bet you'll see less against the second one! Could it simply be that table ttccom100100 has fewer rows or a better index? You can check the execution plan to see what the optimizer thinks. Setting you your predicates so that they run against the smaller table is a good practice.
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/24/2014 :  08:40:28  Show Profile  Reply with Quote
I tried adding another instance of table tccom001100 in either side of the union query and redid the parameter and it worked just as well. I'm assuming that it would be better this way?

Also by doing this I could eliminate the this left join
left join ttccom001100 ccom001 on ccom001.t_emno=gp.ext_reps and ccom001.t_emno!='1015'
and add what is required in the union.




checkpoint;
go
dbcc dropcleanbuffers;
go

SET STATISTICS IO ON

declare @brnch varchar(6)
declare @date1 datetime
declare @date2 datetime
declare @ext_emno nvarchar(6)
declare @ordtyp varchar(8)
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='513'
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',
	gp.ext_reps 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 #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
	left join ttccom001100 as ext_ccom001 on ext_ccom001.t_emno=sli250.t_crep_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) and 
		(@ext_emno='' or ext_ccom001.t_emno=@ext_emno)

	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 #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
	left join ttccom001100 as ext_ccom001 on ext_ccom001.t_emno=sli240.t_crep
	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) and 
		(@ext_emno='' or ext_ccom001.t_emno=@ext_emno)

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


CardGunner

Edited by - cardgunner on 06/24/2014 08:49:20
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/24/2014 :  08:48:43  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

I noticed this section:

Can't you collapse those two joins on the tcisli250100 table into one?

Also:

Could it simply be that table ttccom100100 has fewer rows or a better index? You can check the execution plan to see what the optimizer thinks. Setting you your predicates so that they run against the smaller table is a good practice.



Yes on both.

ttccom001100 has far fewer records. I never thought about it but it makes sense. I'll take a look at the others and keep this in mind.

CardGunner
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 06/24/2014 :  10:27:11  Show Profile  Reply with Quote
BTW the CI index you posted is a little big. One thing to keep in mind: The CI entry is used as a row locator in every NCI. In your case that's quite a few bytes replicated in each NCI. There's plenty of good material on designing indexes. Some popular and smaller choices are identity columns (e.g. MyID int identity(1,1)) and datetime columns or a combination like (datetime, identity). Of course you need to look at it all carefully to ensure you have a useful, unique CI that satisfies many queries and minimizes page splits when inserting records without wasting too much space in the NCIs.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/24/2014 :  11:05:03  Show Profile  Reply with Quote
quote:
Originally posted by cardgunner

Finally. So instead of (@ext_emno='' or gp.ext_emno=@ext_emno) I reversed the table (@ext_emno='' or ccom100.t_emno=@ext_emno)and it ran in less then a minute.

Why would that be?


CardGunner

I can't say for sure, but it is possible that changing the query around caused QL to create/use a different execution plan.
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 06/24/2014 :  11:11:29  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

BTW the CI index you posted is a little big.


Unfortunely I'm not the architect of this db. I just have to work with it.

CardGunner
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000