November 3, 2008 at 2:13 pm
The following query uses a temporary table.
Northwind.dbo.Categories = 8 rows,
Northwind.dbo.Products = 77 rows
SELECT GETDATE() as Time
GO
CREATE TABLE #Temp_Example (
[CategoryID] INT NOT NULL,
[Category_Count] INT NOT NULL
)
INSERT INTO #Temp_Example (CategoryID, Category_Count)
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CATEGORYNAME
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, #Temp_Example.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID
ORDER BY C.CategoryName
DROP TABLE #Temp_Example
GO
SELECT GETDATE() as Time
--Duration = 80-110
==================
The following query uses a derived table.
Northwind.dbo.Categories = 8 rows,
Northwind.dbo.Products = 77 rows
SELECT GETDATE() as Time
GO
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN (
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CategoryName
)CT ON C.CategoryID = CT.CategoryID
ORDER BY C.CategoryName
GO
SELECT GETDATE() as Time
--Duration = 50-80
***
By using derived tables instead of temporary tables, you reduce disk I/O thereby improving the application's performance
Question: Any suggestions ?
Regards
Kevin
November 3, 2008 at 8:21 pm
1. I see no difference in the time when measured properly...
--SELECT GETDATE() as Time
--GO
SET STATISTICS TIME ON
CREATE TABLE #Temp_Example (
[CategoryID] INT NOT NULL,
[Category_Count] INT NOT NULL
)
INSERT INTO #Temp_Example (CategoryID, Category_Count)
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CATEGORYNAME
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, #Temp_Example.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID
ORDER BY C.CategoryName
DROP TABLE #Temp_Example
SET STATISTICS TIME OFF
GO
--SELECT GETDATE() as Time
PRINT REPLICATE('=',100)
-- --Duration = 80-110
--
-- ==================
-- The following query uses a derived table.
-- Northwind.dbo.Categories = 8 rows,
-- Northwind.dbo.Products = 77 rows
-- SELECT GETDATE() as Time
-- GO
SET STATISTICS TIME ON
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN (
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CategoryName
)CT ON C.CategoryID = CT.CategoryID
ORDER BY C.CategoryName
SET STATISTICS TIME OFF
-- GO
-- SELECT GETDATE() as Time
--Duration = 50-80
2. There will probably be a little difference in time because you've included an INSERT in the first set. 2nd set only has one operation.
3. The following statement you posted....
By using derived tables instead of temporary tables, you reduce disk I/O thereby improving the application's performance
... is true ONLY if you intend to use the calculation saved in the Temp Table once.
4. For complex joins with aggragates, it is sometimes MUCH cheaper to use your first method even if the results in the Temp table will only be used once. It just didn't turn out that way for your simple example. Divide and Conquer is a powerful and effective method to greatly enhance the performance of queries.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2008 at 9:30 pm
As I said the first time you asked this question:
What is the preferred option, temporary table or derived table.
The general thinking is to prefer derived tables because they leave the optimizer with more freedom to optimize. However, experience also shows that the more complex the query, the less likely the optimizer is going to make the best choices. Consequently, for significantly complex queries, converting one or more derived tables to temporary tables serves both as a way to force certain decisions on the optimizer and as a way to simplify the query, thus allowing the optimizer to make better decisions with what remains. The catch here is that if you convert the wrong derived table to a temporary table, then you end up forcing a bad choice on the optimizer, thus making the overall performance worse instead of better.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply