Help improving sql query

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

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swatantra BhargavaTechnical Specialist/DeveloperCommented:

Can you please provide table structure in sql file.
Swatantra BhargavaTechnical Specialist/DeveloperCommented:
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.


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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.