Query Help

Marty Jones $organization over 15 years ago

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

Parents

Ryan Creps (Past Staff Member) $organization over 15 years ago

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
where
      l.primary_ind = 'Y'
      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 Jones
Sent: Thursday, November 11, 2010 11:46 AM
To: Ryan Creps
Subject: [Tessitura Technical Forum] Query Help

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

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!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Reply

Ryan Creps (Past Staff Member) $organization over 15 years ago

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
where
      l.primary_ind = 'Y'
      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 Jones
Sent: Thursday, November 11, 2010 11:46 AM
To: Ryan Creps
Subject: [Tessitura Technical Forum] Query Help

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

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!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Children

No Data