Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
 Posted - 2004-03-09 : 01:14:43
|
| I have a query that Joins to about 6 Derived tables.Each of the derived tables return a maximum of < 500 rows.This query runs for hours and hours - (about 6 and a half yesterday).What I tried out this morning was to create temp tables for each of these derived tables and then just join the main query to the temp tables.Each temp table took about 2 minutes to create and then the main query ran for about 4 Minutes.So what took 6 and a half hours yesterday (using derived tables) took 16 minutes this morning (using temp tables).Does this mean that derived tables are more expensive on sql server?.Can anyone shed some light here?Duane. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-03-09 : 01:35:42
|
It depends  Damian |
 |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-03-09 : 09:12:52
|
| In the case you just described the answer is yes. But I would not assume that means in all cases derived tables are too costly. Damian is correct, it depends. The only way to know is to test which is precisely what you did. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-09 : 09:16:08
|
| Thanks Guys.I thought There might be some straight forward way to find out.Theres nothing wrong with extra testing though is there?Duane. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-09 : 09:25:30
|
| Just out of curiosity, can you post both queries? :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-09 : 09:33:39
|
| I'm not sure that I am allowed to post exact code here.I can post the code with different table names and columns but I'm not sure that it will have the desired affect.Still interested?Duane. |
|
|
|
|
|
|
Who can explain this?