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
Hi Marty,
Good day.
To improve the selection, the most important part is putting index on the tables.
but most of tables you listed are system tables, there is not much we can do here.
#perfs and #mos_category, I believe they are small tables. put index on them will not make any difference.
the only thing we can do here is changing the sequence after "where"
l.primary_ind = 'Y' is useless. -----(it will return full table. same as zone_no)
sl.zone_no <> 0 is useless. we should avoid to use "not" and "<>"
only sl.Sli_Status can exclude some records, this should be put at first.
So the statement should be
where sl.Sli_Status in (1,2,3,5,6,7,8,9,10,11,12,14)
and l.primary_ind = 'Y'
and sl.zone_no > 0
Thanks Ben,
I made the changes that you suggested such as sli_status to the top and the zone_no > 0 but seems like I just am not going to be able to make it any quicker for the user. I am starting to think that I should execute my procedure every 15 minutes or so and store the results and have the users request pull from the stored results. Hmm...
Thanks for the help.