Sql-server – Optimize this SQL query for performance

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.

There are multiple things to look at to improve query performance.

• Understand your system load
• Volume of data you are expecting to be in the tables mentioned in query
• Volume of data you are going to fetch from tables mentioned in query
• Indexes applied on tables mentioned in query
• Structure of the query

I can suggest you improvements related to only query structure as I am not fully aware of your system. I am assuming that you have proper indexes applied on your tables.

After looking at your query I can suggest you following things,

• You dont need DISTINCT clause here as you are selecting Customer.CustId which I assume to be a primary key of Customer table & its always going to be unique which inter makes related row always unique in result set.
• You are using too many subqueries which I dont think is a good practice. Try to use SQL joins as much as you can. Plus I think you can merge some of these queries. For example, you are selecting EmailAddr from Customer having count greater than 1 & created after yesterday. You can combine both these conditions in one single query as following.

    SELECT 
        [EmailAddr] AS [EmailAddr] 
    FROM 
        [Customer] AS [Customer]
    WHERE 
        [Customer].[EmailAddr] != '' 
        AND [Customer].[Crtd_DateTime] >= DATEADD(day, -1, CAST(GETDATE() AS DATE)
    GROUP BY 
        [Customer].[EmailAddr]
    HAVING COUNT(*) > 1

same thing with phone also

SELECT 
    [Phone] AS [Phone]
FROM 
    [Customer] AS [Customer]
WHERE 
    [Customer].[Phone] != '' 
    AND [Customer].[Crtd_DateTime] >= DATEADD(day, -1, CAST(GETDATE() AS DATE)
GROUP BY 
    [Customer].[Phone]
HAVING COUNT(*) > 1

You can use CTE here. Define 2 CTEs one for email data & one for phone data. Then you can do inner join with Customer table on email & phone to get correct data. (check my query given below to get better picture)

• Try avoiding functions in your where clause. While querying for email & phone data you are checking for yesterdays date also. Try to store this value in some sql variable first & then use that variable value to compare. example,

    DECLARE @dateTimeToCheck DATETIME
    set @dateTimeToCheck = DATEADD(day, -1, CAST(GETDATE() AS DATE)

I would also like to suggest following things, - Try to write simple queries. Divide them into logical unites. This will help you to understand of complex queries easily & it will help you in query optimization also. - Use alias names for columns you are selecting. This will not improve performance but it will definitely improve readability.

Here is my improved version of your query.

DECLARE @dateTimeToCheck DATETIME
SET @dateTimeToCheck = DATEADD(day, -1, CAST(GETDATE() AS DATE)

;WITH cteEmail AS (
    SELECT 
        [EmailAddr] = [Customer].[EmailAddr] 
    FROM 
        [Customer] AS [Customer] WITH(NOLOCK)
    WHERE 
        [Customer].[EmailAddr] != '' 
        AND [Customer].[Crtd_DateTime] >= @dateTimeToCheck
    GROUP BY 
        [Customer].[EmailAddr]
    HAVING COUNT(*) > 1
)

;WITH ctePhone AS (
    SELECT 
        [Phone] = [Customer].[Phone]
    FROM 
        [Customer] AS [Customer]
    WHERE 
        [Customer].[Phone] != '' 
        AND [Customer].[Crtd_DateTime] >= @dateTimeToCheck
    GROUP BY 
        [Customer].[Phone]
    HAVING COUNT(*) > 1
)

SELECT 
    [CustomerId] = [Customer].CustId,
    [PriceClassID] = [Customer].PriceClassID,
    [Name] = [Customer].Name ,
    [First] = [CustomerEDI].[S4Future01],
    [Last] = [CustomerEDI].[S4Future02],
    [Address1] = [Customer].[Addr1],
    [Address2] = [Customer].[Addr2],
    [City] = [Customer].[City],
    [State] = [Customer].[State],
    [Zip] = [Customer].[Zip],
    [Country] = [Customer].[Country],
    [CustomerEmail] = [Customer].[emailAddr],
    [CustomrePhone] = [Customer].[Phone],
    [LastOrder] = [Customer].[lastorder],
    [snotetext] = CONVERT(varchar(8000), [Customer].[snotetext])
FROM 
    [Customer] AS [Customer] 
INNER JOIN 
    [cteEmail] AS [CteEmail] ON [Customer].EmailAddr = [cteEmail].[EmailAddr]
INNER JOIN 
    [ctePhone] AS [CtePhone] on [Customer].Phone = [ctePhone].[Phone]
ORDER BY 
    [Customer].[EMailAddr]
    , [Customer].[Phone] ASC
    , [Customer].[Name]
    , [Customer].[Last]
    , [Customer].[First] ASC;

I hope it helps.