• Replies 10 replies
  • Subscribers 338 subscribers
  • Views 75 views
  • Users 0 members are here
Related

Query Help

Marty Jones
$organization

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
  • Former Member
    Former Member $organization

    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 Creps
    Sent: Thursday, November 11, 2010 9:16 AM
    To: Steve Carlock
    Subject: RE: [Tessitura Technical Forum] Query Help

     

    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!

  • $organization in reply to Former Member

    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.

Reply
  • $organization in reply to Former Member

    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.

Children
No Data