Looking for a little help on a query that I am writing. Sort of a normal sales query but when I run it, it is taking longer that I would think it would. I am including the From and where clause of the query and wondering if I should be doing something different.
Any suggestions would be helpful.
Thanks,
User can select multiple seasons which the #perfs table is populated with those perf_no.
FROM
t_order o With(nolock) JOIN
t_lineitem l With(nolock) on l.order_no = o.order_no JOIN
T_Sub_Lineitem sl With(nolock) on l.li_seq_no = sl.li_seq_no JOIN
#perfs tp ON sl.perf_no = tp.Perf_no JOIN
TR_MOS m ON m.id = o.MOS JOIN
#Mos_Category mm ON mm.id = m.category JOIN
t_sli_detail sd With(nolock) on sd.sli_no = sl.sli_no Join
TR_PRICE_TYPE pt With(nolock) on pt.id = sl.price_type Join
t_pmap pm With(nolock) on pm.pmap_no = sd.pmap_no
where
l.primary_ind = 'Y'
AND sl.Sli_Status not in (4,13)
and sl.zone_no <> 0
How about something like:
[etc etc]from t_order ojoin t_lineitem l on o.order_no = l.order_no and l.primary_ind = 'Y'join t_sub_lineitem li on l.order_no = li.order_no and l.perf_no = li.perf_nowhere li.sli_status in (4,13) and li.zone_no != 0
Thanks Chris,
When I tried this, the query took a little longer. In looking at the execution plan for T_SubLineitem, it was showing an Index scan where using l
l
.li_seq_no = sl.li_seq_no
Causes an Index seek which is a little better.
Unknown said: Thanks Chris, When I tried this, the query took a little longer. In looking at the execution plan for T_SubLineitem, it was showing an Index scan where using l .li_seq_no = sl.li_seq_no Causes an Index seek which is a little better. Thanks,
Interesting. I thought something like the join I sent might be worth a try; nice to know what you found out about it...