Inefficient query actually more efficient?

Question

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

---

---

---

Execution Plan for Slow Query

---

---

---

---

Answer 1

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.

Answer 2

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.

Answer 3

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