7
\$\begingroup\$

I have two queries; one runs significantly faster than the other, but the faster one is the one that I would have bet money on to be less efficient. These are both being executed through a front end C# console based application and are stored as procedures in SQL Server 2008 R2.

Basically, I have the first one I wrote, which is depreciating a store VIEW that I didn't think needed to be there any more, and the second one, which was replacing the VIEW in a store procedure that I had. What I don't get, is the second one is about 15 times faster, as in, it executes instantaneously and the other takes about 15 seconds

Is this something I don't know about T-SQL or just a fluke? Anyone able to drop some knowledge on me and possibly help me speed up some similar queries in the future?

The only real difference is that the faster one is making a table and selecting from it, where as the slower one is just making the table! It doesn't make sense that when you just make the table it takes longer than making and querying it... or does it? Info on this would be awesome if nothing else for my own personal knowledge.


Slower, but significantly smaller query (this replaced the view that was being stored):

SELECT DISTINCT [ModelRequests].[RequestID] AS 'Request ID',
          ([usr_].[firstname] + ' ' + [usr_].[lastname]) AS 'Full Name',
          [usr_].[company_name] AS 'Company Name',
          [usr_].[city] AS 'City',
          [usr_].[state] AS 'State',
          ISNULL([Branch].[BranchName], [BranchCanada].[BranchName])  AS 'Branch Name',
          ISNULL([Branch].[BranchID], [BranchCanada].[BranchID]) AS 'Branch ID',
          LEFT((REPLACE (REPLACE([Zip], ' ', ''), '-', '')), 6) AS 'Zip'

          FROM [Products].[dbo].[Requests] [Requests]

          LEFT JOIN [HOST].[dbo].[usr] [usr] ON
          CONVERT(varchar(50), [usr].[user_id]) = [ModelRequests].[username]

          LEFT JOIN [Location].[dbo].[CountryToContinent] [CountryToContinent]
          ON [CountryToContinent].[CountryCode] = [usr].[country]

          LEFT JOIN [Location].[dbo].[Continent] [Continent] 
          ON [Continent].[ContinentCode] = [CountryToContinent].[ContinentCode]

          LEFT JOIN [Location].[dbo].[ZipCode] [ZipCode]
          ON [ZipCode].[ZipCode] = [usr].[zip]

          LEFT JOIN [Location].[dbo].[Branch] [Branch]
          ON [Branch].[BranchID] = [ZipCode].[BranchID]

          LEFT JOIN [Location].[dbo].[ZipCode] [ZipCodeCanada]
          ON LEFT([ZipCodeCanada].[ZipCode], 3) = LEFT([usr].[zip], 3)

          LEFT JOIN [Location].[dbo].[Branch] [BranchCanada]
          ON [BranchCanada].[BranchID] = [ZipCodeCanada].[BranchID]

          WHERE ([ModelRequests].[cached] IN (1) OR
                [ModelRequests].[cached] IS NULL) AND
                [ModelRequests].[RequestDateTime] BETWEEN @DateStart AND @DateEnd

OPTION (OPTIMIZE FOR (@DateStart UNKNOWN , @DateEnd UNKNOWN));

Larger query that runs insanely fast:

DECLARE @DateStart datetime,
        @DateEnd datetime

SELECT [Request ID]
          ,[Full Name]
          ,[Company Name]
          ,[City]
          ,[State]
          ,[Branch Name]
          ,[Branch ID]
          ,[Zip]

    FROM (SELECT DISTINCT [ModelRequests].[RequestID] AS 'Request ID',
          ([usr_].[firstname] + ' ' + [usr_].[lastname]) AS 'Full Name',
          [usr_].[company_name] AS 'Company Name',
          [usr_].[city] AS 'City',
          [usr_].[state] AS 'State',
          ISNULL([Branch].[BranchName], [BranchCanada].[BranchName])  AS 'Branch Name',
          ISNULL([Branch].[BranchID], [BranchCanada].[BranchID]) AS 'Branch ID',
          LEFT((REPLACE (REPLACE([Zip], ' ', ''), '-', '')), 6) AS 'Zip'

          FROM [Products].[dbo].[Requests] [Requests]

          LEFT JOIN [HOST].[dbo].[usr] [usr] ON
          CONVERT(varchar(50), [usr].[user_id]) = [ModelRequests].[username]

          LEFT JOIN [Location].[dbo].[CountryToContinent] [CountryToContinent]
          ON [CountryToContinent].[CountryCode] = [usr].[country]

          LEFT JOIN [Location].[dbo].[Continent] [Continent] 
          ON [Continent].[ContinentCode] = [CountryToContinent].[ContinentCode]

          LEFT JOIN [Location].[dbo].[ZipCode] [ZipCode]
          ON [ZipCode].[ZipCode] = [usr].[zip]

          LEFT JOIN [Location].[dbo].[Branch] [Branch]
          ON [Branch].[BranchID] = [ZipCode].[BranchID]

          LEFT JOIN [Location].[dbo].[ZipCode] [ZipCodeCanada]
          ON LEFT([ZipCodeCanada].[ZipCode], 3) = LEFT([usr].[zip], 3)

          LEFT JOIN [Location].[dbo].[Branch] [BranchCanada]
          ON [BranchCanada].[BranchID] = [ZipCodeCanada].[BranchID]

          WHERE ([ModelRequests].[cached] IN (1) OR
                [ModelRequests].[cached] IS NULL) AND
                [ModelRequests].[RequestDateTime] BETWEEN @DateStart AND @DateEnd) AS [LeadsGeneration_ViewAllModelRequests]

    WHERE [LeadsGeneration_ViewAllModelRequests].[Request Date Time] BETWEEN @DateStart AND @DateEnd

    OPTION (OPTIMIZE FOR (@DateStart UNKNOWN , @DateEnd UNKNOWN));

Execution Plan For Fast Query

enter image description here


enter image description here


enter image description here


enter image description here

Execution Plan for Slow Query

enter image description here


enter image description here


enter image description here


enter image description here


\$\endgroup\$
  • \$\begingroup\$ there is not ModelRequests table in the queries \$\endgroup\$ – paparazzo Jun 8 '17 at 18:36
7
\$\begingroup\$

You have

OPTION (OPTIMIZE FOR (@DateStart UNKNOWN, @DateEnd UNKNOWN));

in the second query.

Add it to the first one and see what happens, but I am guessing that it might speed that one up as well.


This is not needed.

WHERE [LeadsGeneration_ViewAllModelRequests].[Request Date Time] BETWEEN @DateStart AND @DateEnd 

These values will never be outside of the range between @DateStart and @DateEnd because that same expression is in the nested Select statement, so you don't even need that. It won't slow down your query

Really the outside query isn't doing anything

It is just regurgitating the inside SELECT statement.
so that shouldn't slow it down either.

\$\endgroup\$
  • \$\begingroup\$ Optimize for was in the original query, sorry, missed it on the copy/paste. The query does run properly though, not sure which parenthesis you're referring to? \$\endgroup\$ – Volearix Mar 19 '14 at 17:04
  • \$\begingroup\$ That's where you are incorrect though. The 2nd where increases performance! It makes no sense. Just tested it. With the redundant where clause... 1.018 seconds on average over 10 test runs. Without the redundant where clause... 3.084 seconds! Makes no sense. \$\endgroup\$ – Volearix Mar 19 '14 at 17:43
  • \$\begingroup\$ Comment on Question. something is different that you aren't showing. \$\endgroup\$ – Malachi Mar 19 '14 at 17:45
  • \$\begingroup\$ Nope, all there. Just copy/paste Executed it. Query plan posted. \$\endgroup\$ – Volearix Mar 19 '14 at 17:52
3
\$\begingroup\$

Looks like your SELECT DISTINCT is scanning a much larger result set (resources 81% as opposed to 21%) in your slower query. Also I noticed your slower query is missing DECLARE @DateStart datetime, @DateEnd datetime that may affect how SQL optimizes it, for example if the SQL server was sorting and truncating out all dates outside those dates right off the bat instead of at the end perhaps, since it knows those values already? I'd be curious to add that to the slower statement and see what happens.

\$\endgroup\$
-1
\$\begingroup\$

try this as it gets rid of an OR

SELECT DISTINCT [ModelRequests].[RequestID] AS 'Request ID',
      ([usr_].[firstname] + ' ' + [usr_].[lastname]) AS 'Full Name',
      [usr_].[company_name] AS 'Company Name',
      [usr_].[city] AS 'City',
      [usr_].[state] AS 'State',
      ISNULL([Branch].[BranchName], [BranchCanada].[BranchName])  AS 'Branch Name',
      ISNULL([Branch].[BranchID], [BranchCanada].[BranchID]) AS 'Branch ID',
      LEFT((REPLACE (REPLACE([Zip], ' ', ''), '-', '')), 6) AS 'Zip'

      FROM [Products].[dbo].[Requests] [ModelRequests]

      LEFT JOIN [HOST].[dbo].[usr] [usr] ON
      CONVERT(varchar(50), [usr].[user_id]) = [ModelRequests].[username]

      LEFT JOIN [Location].[dbo].[CountryToContinent] [CountryToContinent]
      ON [CountryToContinent].[CountryCode] = [usr].[country]

      LEFT JOIN [Location].[dbo].[Continent] [Continent] 
      ON [Continent].[ContinentCode] = [CountryToContinent].[ContinentCode]

      LEFT JOIN [Location].[dbo].[ZipCode] [ZipCode]
      ON [ZipCode].[ZipCode] = [usr].[zip]

      LEFT JOIN [Location].[dbo].[Branch] [Branch]
      ON [Branch].[BranchID] = [ZipCode].[BranchID]

      LEFT JOIN [Location].[dbo].[ZipCode] [ZipCodeCanada]
      ON LEFT([ZipCodeCanada].[ZipCode], 3) = LEFT([usr].[zip], 3)

      LEFT JOIN [Location].[dbo].[Branch] [BranchCanada]
      ON [BranchCanada].[BranchID] = [ZipCodeCanada].[BranchID]

      WHERE isnull([ModelRequests].[cached], 1) = 1 
      AND [ModelRequests].[RequestDateTime] BETWEEN @DateStart AND @DateEnd
\$\endgroup\$
  • \$\begingroup\$ Really a DV for this? \$\endgroup\$ – paparazzo Jun 20 '17 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.