Inefficient query actually more efficient?

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 [CountryTo

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.

StackExchange Code Review Q#44770, answer score: 7