Help improving sql query

Posted on 2014-10-31
Last Modified: 2014-12-02
I need help improving this query

 Select distinct pc.codeID,pc.codeName,pc.AwardAmmount,pc.description  
                        from PromoCode PC
                     inner join lkupPromoCode_location PL  WITH (NOLOCK) on pc.CodeID = PL.codeID
                     left outer join lkuppromoCode_product PP WITH (NOLOCK) on pp.codeID = PC.CodeID
                 left outer join lkupPromocode_package PPP WITH (NOLOCK) on PPP.codeID = PC.CodeID
                     WHERE PC.status = 1  
                     and PC.StartDate <='#DateFormat(now(),"mm/dd/yyyy")#'  
                 and PC.promoType = 0
                 AND ((pc.genEmail  is NOT NULL and pc.genEmail = (select from users U where u.userKey=#session.userid#)) OR   pc.genEmail  is NULL)

                 and DATEDIFF(D,pc.CodeIssueExpiry,GETDATE()) <= 0
                 and PL.LocationID  IN (Select lkupPromoCode_location.locationID from lkupPromoCode_location  where lkupPromoCode_location.codeID=pc.codeID and lkupPromoCode_location.type = 1) 
                     and (ppp.packageID in 
                                 (Select lkupPromoCode_package.packageID 
                                 from lkupPromoCode_package  
                                 where lkupPromoCode_package.codeID=pc.codeID 
                                 and lkupPromoCode_package.packageID in (#ArrayToList(ListToArray(session.promocode.packageKey))#) 
                              and lkupPromoCode_package.type = 1)
                              OR pp.productID in (
                              Select lkuppromoCode_product.productID 
                              from lkuppromoCode_product  
                              where lkuppromoCode_product.codeID=pc.codeID 
                              and lkuppromoCode_product.productID in (#ArrayToList(ListToArray(session.promocode.productKey))#) and lkuppromoCode_product.type = 1 ))

Open in new window

Question by:erikTsomik
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

by:Swatantra Bhargava
ID: 40416910

Can you please provide table structure in sql file.

Expert Comment

by:Swatantra Bhargava
ID: 40416917
Try to use

"and lkupPromoCode_location.type = 1"

instead of

"and PL.LocationID  IN (Select lkupPromoCode_location.locationID from lkupPromoCode_location  where lkupPromoCode_location.codeID=pc.codeID and lkupPromoCode_location.type = 1) "

If this works, try for other queries.
LVL 49

Accepted Solution

PortletPaul earned 500 total points
ID: 40417875


Always look for alternatves to select distinct. Making a result distinct involves extra effort and can slow down a complex query a great deal.

2. Implicit INNER JOINs

You use LEFT OUTER JOIN on 2 tables.

Then you also require each of those tables to comply with an IN() condition inside the where clause. Those IN() conditions will remove any unmatched records (those that the OUTER JOIN specifically allows) so the OUTER JOIN is ineffectual and they become the equivalent of INNER JOINS. So, use INNER JOIN on those 2 tables.

see point below:
    (PC.genEmail IS NOT NULL
      AND PC.genEmail = (
                          FROM users U
                          WHERE U.userKey = #session.userid#
  OR PC.genEmail IS NULL

Open in new window

3. Unnecessary complexity

If some field must equal a given value, it cannot be NULL at the same time so asking if it is also not null is redundant.

4. DATEDIFF(D, PC.CodeIssueExpiry, GETDATE()) <= 0

This works, but it isn't as efficient as it could be. You are forcing every record through a function to compare to a single constant (zero). It is much more efficient to to avoid use of that function on each row.

Equivalents are:
    PC.CodeIssueExpiry >= dateadd(day, datediff(day,0, GETDATE() ), 0)
    PC.CodeIssueExpiry >= cast(GETDATE() as date)

see point below:
AND PL.LocationID IN (
  FROM lkupPromoCode_location
  WHERE lkupPromoCode_location.codeID = PC.codeID
  AND lkupPromoCode_location.type = 1

Open in new window

5. use join conditions

The code snippet above can be achieved without added scanning of that table, like this:

  INNER JOIN lkupPromoCode_location PL WITH (NOLOCK)
       ON PC.CodeID = PL.codeID
          AND PL.type = 1

6. #ArrayToList(ListToArray(session.promocode.packageKey))#

I have no idea what actually happens with this but it looks weird.

What exactly does session.promocode.packageKey store?
Does it really hold more than one value?

Is it necessary to use both ListToArray and ArrayToList?

7. Try This please

    , codeName
    , AwardAmmount
    , description
FROM PromoCode
              SELECT NULL
              FROM PromoCode pc
                INNER JOIN lkupPromoCode_location PL WITH (NOLOCK)
                    ON PC.CodeID = PL.codeID
                          AND PL.type = 1
                INNER JOIN lkuppromoCode_product PP WITH (NOLOCK)
                    ON PP.codeID = PC.CodeID
                          AND PP.type = 1
                          AND PP.productID IN (#ArrayToList(ListToArray(session.promocode.productKey))#)
                INNER JOIN lkupPromocode_package PPP WITH (NOLOCK)
                    ON PPP.codeID = PC.CodeID
                          AND PPP.type = 1
                          AND PPP.packageID IN (#ArrayToList(ListToArray(session.promocode.packageKey))#)
              WHERE PC.status = 1
              AND PC.StartDate <= '#DateFormat(now(),"mm/dd/yyyy")#'
              AND PC.promoType = 0
              AND ( PC.genEmail = (
                                    FROM users U
                                    WHERE U.userKey = #session.userid#
                    OR PC.genEmail IS NULL
              AND PC.CodeIssueExpiry >= dateadd(day, datediff(day,0, GETDATE() ), 0)
              and PC.CodeID = PromoCode.CodeID /* NB THIS CORRELATES THE INNER TO OUTER QUERY */

Open in new window

8. Next Step: Execution Plan

While EE experts can make many suggestions about a queries efficiency the most useful item to assist in optimization is an "Execution Plan". If you need further assistance on this query please provide an executions plan (see you previous question for a link).

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question