| Author |
Topic |
|
senthilaa
Starting Member
12 Posts |
 Posted - 2008-01-22 : 08:42:30
|
| Hi, I wanna know is there any advantage of perf gain when using Derived Tables over Temp Tables, advice me which one is better to use. Can I create Indexes and Insert/Update records into Derived Tables.-Senthil |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-22 : 08:45:16
|
It depends for which aspect you are referring.If you want less writes/reads during a query, a derived table is the preferred choice. Otherwise you have to read the data twice.No, you can't create an index over a derived table. The dervied table itself utilize the present indexes on the underlying tables.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
senthilaa
Starting Member
12 Posts |
Posted - 2008-01-22 : 09:03:52
|
| Thanks for the response,My problem is, we have very large tables(millions) of records, to update some of the columns we use/create Temp tables and joins with some small/big tables do some manipulation and finally update the original table. In this case, can we use Derived tables, I want to know whether I can do some manipulation(Insert/Update/Deletion) on these derived tables-Senthil |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-22 : 09:24:19
|
The problem here is not "million records". No big deal.The problem seems to be bad indexing or bad query. Or a combination of both.Show us the query.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
senthilaa
Starting Member
12 Posts |
Posted - 2008-01-22 : 09:44:06
|
| Thanks again, last query, can I do Insert/Update/Deletion with these derived tables-Senthil |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-22 : 09:49:20
|
Delete, yes.Insert and update, no.Read Books Online what derived table is.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-01-22 : 10:39:47
|
| only in 2005 or higher version derived tables are updatable/deletableMadhivananFailing to plan is Planning to fail |
|
|
|
senthilaa
Starting Member
12 Posts |
Posted - 2008-01-22 : 12:35:08
|
| Hi Madhivanan we also use SQL 2K5 only.Derived tables are updatable/deletable. Just curious to know, whether it is insertable, so that, we can use derived tables in place of temp table :)-Senthil |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-22 : 13:32:16
|
No, you can't BOTH update and SELECT from a derived table.quote:
Read Books Online what derived table is.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Advantage of Derived Table over Temp Table