Query Help

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.

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!

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!

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!

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.

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!

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!

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!