Why would using a Temp table be faster than a nested query?
- by Mongus Pong
We are trying to optimise some of our queries.
One query is doing the following:
SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (<complex subquery>) Date,
INTO [#Gadget]
FROM task t
SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID) as Client
FROM [#Gadget]
order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC
DROP TABLE [#Gadget]
(I have removed the complex subquery, cos I dont think its relevant other than to explain why this query has been done as a two stage process.)
Now I would have thought it would be far more efficient to merge this down into a single query using subqueries as :
SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID)
FROM
(
SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (<complex subquery>) Date,
FROM task t
) as sub
order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC
This would give the optimiser better information to work out what was going on and avoid any temporary tables. It should be faster.
But it turns out it is a lot slower. 8 seconds vs under 5 seconds.
I cant work out why this would be the case as all my knowledge of databases imply that subqueries would always be faster than using temporary tables.
Can anyone explain what could be going on!?!?
