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
Try this…
FROM t_order o With(nolock)
JOIN T_Sub_Lineitem sl With(nolock) on o.order_no = sl.order_no
JOIN t_lineitem l With(nolock) on sl.li_seq_no = l.li_seq_no and l.primary_ind = 'Y'
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
AND sl.Sli_Status not in (4,13) --Switch this to "IN" (1,3,3,4,etc)
and sl.zone_no > 0
There’s an index on t_sub_lineitem.order_no, and it’s always faster to use a where clause that’s Inclusive instead of exclusive.
-Ryan
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty JonesSent: Thursday, November 11, 2010 11:46 AMTo: Ryan CrepsSubject: [Tessitura Technical Forum] Query Help
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
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.
Ryan,
Looks like I lost the index seek on T_Sub_Lineitem. What it is showing, is a little over 2 million rows being returned from T_Sub_Lineitem when the actual rows are a little less than 240,000.
Marty Jones
Director of Information Services
Omaha Performing Arts1200 Douglas Street
Omaha, Nebraska 68102
P 402.661.8469
Marty.Jones@omahaperformingarts.org
www.omahaperformingarts.org
For tickets, call Ticket Omaha at 402.345.0606
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan CrepsSent: Thursday, November 11, 2010 11:17 AMTo: Martin A. JonesSubject: RE: [Tessitura Technical Forum] Query Help
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...
What’s the difference between having the constraint in the join instead of (or in addition to) the where clause? I was under the impression that standard joins were nothing more than “syntactic sugar” to make it easier for us to understand the query and that the query optimizer knows how to deal with it correctly in either case.
Thanks.
-steve carlock
Information Technology Manager
The Granada
(805) 899-3000 x 111 (phone)
(805) 899-3081 (fax)
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan CrepsSent: Thursday, November 11, 2010 9:16 AMTo: Steve CarlockSubject: RE: [Tessitura Technical Forum] Query Help
You’re correct. Nothing really….actually forgot that Primary_ind was in the where clause there already. My change was more about making the join to T_SUB_LINEITEM through T_ORDER, not through T_LINEITEM, but still checking for primary_ind on T_LINEITEM.
The query engine still deconstructs the query into a very verbose format for processing anyway. Using primary_ind = ‘y’ in the join or in the where clause should make no difference.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Steve CarlockSent: Thursday, November 11, 2010 1:41 PMTo: Ryan CrepsSubject: RE: [Tessitura Technical Forum] Query Help
Unknown said: What’s the difference between having the constraint in the join instead of (or in addition to) the where clause? I was under the impression that standard joins were nothing more than “syntactic sugar” to make it easier for us to understand the query and that the query optimizer knows how to deal with it correctly in either case.
I've been told that "the JOIN logic is processed before the WHERE clause. So, putting logic in the JOIN clause can eliminate unwanted records sooner than if you put them in the WHERE clause." But, in real life, as Ryan said, the difference is probably not noticeable.
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'
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.