Help improving sql query

Posted on 2014-10-31
Medium Priority
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 U.email 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 2000 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: http://en.wikipedia.org/wiki/Sargable

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

765 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