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
  • 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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now