when to use derived tables and temp tables

after reading some stuffs about derived tables and temp tables..

now i'm all confused when to use either of them..

based from your database programming experiences which of them performs better?

need your expertise.. thanks

-rhamille

use derived tables whenever possible, i.e. if you can write the sql with derived tables, do so

rudy

oh.. sorry what i mean was the difference in performance..

and someone also said that i can use a derived table inside a view..

would it be ok if i'll just make a view out of that derived table

so that i'll just use that view instead of including the sql statement for that derived table..

for example..

CREATE PROCEDURE [dbo].[ToPool]
(
@ordBy [varchar](4),
@sortBy [varchar](25)
)
AS
BEGIN
SELECT y.productCode, SUM(quantity * z.lastPurchasePrice) AS estimateCost, quoteDesc, quoteNo, poolDate, closingDate, emergency
FROM ( -- this is a derived table "y" right?
SELECT dbo.PoolLine.productCode, SUM(dbo.PoolLine.quantity) AS quantity, dbo.QuoteType.quoteDesc, dbo.Pool.quoteNo, dbo.Pool.poolDate, dbo.Pool.closingDate, NULL AS emergency
FROM dbo.Pool INNER JOIN
dbo.PoolLine ON dbo.Pool.poolID = dbo.PoolLine.poolID INNER JOIN
dbo.QuoteType ON dbo.Pool.quoteType = dbo.QuoteType.quoteType
GROUP BY dbo.PoolLine.productCode, dbo.QuoteType.quoteDesc, dbo.Pool.quoteNo, dbo.Pool.poolDate, dbo.Pool.closingDate
UNION ALL
SELECT TOP 100 PERCENT dbo.MRFLine.productCode, SUM(dbo.MRFLine.quantity) AS quantity, NULL AS quoteDesc, NULL AS quoteNo, NULL AS poolDate, NULL AS closingDate,emergency
FROM dbo.MRF INNER JOIN
dbo.MRFLine ON dbo.MRF.mrfID = dbo.MRFLine.mrfID
WHERE (dbo.MRF.statusID = 3) AND (dbo.MRF.projectID IS NULL)
GROUP BY dbo.MRFLine.productCode, emergency
UNION ALL
SELECT TOP 100 PERCENT dbo.MRF.projectCode AS productCode, SUM(dbo.MRF.quantity) AS quantity, NULL AS quoteDesc, NULL AS quoteNo, NULL AS poolDate, NULL AS closingDate,emergency
FROM dbo.MRF
WHERE (dbo.MRF.statusID = 3) AND (dbo.MRF.projectID IS NOT NULL)
GROUP BY dbo.MRF.projectCode, emergency) y INNER JOIN
( -- this is another derived table "z" which i joined to "y"
SELECT TOP 100 PERCENT x.productCode, MIN(ProductBrand.lastPurchasePrice) AS lastPurchasePrice
FROM ( -- derived table "x" i joined inside "z"
SELECT DISTINCT productCode, MAX(lastPurchaseDate) AS lastPurchaseDate
FROM ProductBrand
GROUP BY productCode) x INNER JOIN ProductBrand
ON x.productCode=ProductBrand.productCode AND x.lastPurchaseDate=ProductBrand.lastPurchaseDate
GROUP BY x.productCode) z ON y.productCode=z.productCode
GROUP BY y.productCode, y.quoteDesc, y.quoteNo, y.poolDate, y.closingDate, y.emergency
ORDER BY
CASE
WHEN @sortBy = 'y.productCode' THEN y.productCode
WHEN @sortBy = 'SUM(quantity * z.lastPurchasePrice)' THEN SUM(quantity * z.lastPurchasePrice)
WHEN @sortBy = 'y.quoteDesc' THEN y.quoteDesc
WHEN @sortBy = 'y.quoteNo' THEN y.quoteNo
WHEN @sortBy = 'y.poolDate' THEN y.poolDate
WHEN @sortBy = 'y.closingDate' THEN y.closingDate
END
RETURN
END
GO

-----

can i just make x, y and z as views and make a view out of them? and so that i can have a simple order by without having parameters for it? what will be the difference in performance?

any suggestions/comments? thanks..

rhamille

derived tables and views should perform the same -- you asked about temp tables, and temp tables are different

i did not analyze your derived tables (the whole thing looks mighty complicated) but essentially, yes, you can usually just replace a derived table with a view

oh aight... so like.. in essense it would be like nesting views right? would you recommend it then? well i didn't like the concept of temp tables..

i dunno.. i have to say this is my first big project.. and my paranoia for the speed of my program is really getting into me..

can you give me some tips maybe?

recommend derived tables? yes, if you are 100% that the derived table will be used only once in that query -- otherwise, if it's used in more than one query, obvioulsy a view is better

tips? yeah

rule #1 -- design the tables properly

rule #2 -- forget about performance until you are sure your query is returning the right results

rule #3 -- see rule #1

aight.. gee tnx man..

well databases are not my forte.. i just got stuck here for this project 1 man team so to speak hehehe.. i do both the programming and the database side.. but good for me coz m learning a lot of db stuffs.. i'll keep in mind everything that you have said...

o yeah another thing.. how many subqueries/joins do you think is enough? and how many levels of nested views should i take into consideration?

if you find yourself doing more than two levels of nesting, see rule #1

well.. i guess.. i was over normalizing the design.. hehehe.. can u giv me also tips on database normalization..

coz when i was normalizing it.. all i was thinking was to avoid the redunduncy of row data per table.. that's why i kept splitting my tables..

more than two levels? are joins considered as another nest level? does joining, lets say 3-5 tables, are bad design already?

"avoid the redunduncy of row data per table" sounds right

don't worry about joins, they are efficient as long as you have indexed your join column

there are a number of links to good tutorials on my site:

http://r937.com/links.cfm?links=sql

aight.. tnx..