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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

760 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

17 Experts available now in Live!

Get 1:1 Help Now