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 |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2014-01-08 : 16:13:11
|
| Hi,I have a quick question and I know there can be many different answers.If I'm loading datasets into a physical table, sometimes I use a temporary table within tempdb and sometimes, depending on the size of the dataset, I create a physical table within the database I'm working with to store the dataset.I'm sure I read somewhere that if the datasets are large, it suggested creating physical tables on the db your reading the tables in your query and this is partly due to bloating the tempdb and contention issues.Does anyone agree with this? Is it always safe to use tempdb?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-08 : 16:55:39
|
| I don't see a need to use tempdb if you are using a regular table. It would depend upon my hardware configuration and specs though. If tempdb were on special hardware such as SSD disks and my database was on regular disks, then I might consider using tempdb. There shouldn't be contention issues if you are using a regular table in tempdb as nothing else should be contending with it. I just put my regular table in the user database though and use tempdb for # temp tables. My most critical system has SSDs for the mdf/ndf files of the user database and tempdb is on the storage array with regular disks.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2014-01-08 : 17:13:44
|
quote:
Originally posted by tkizer
I don't see a need to use tempdb if you are using a regular table. It would depend upon my hardware configuration and specs though. If tempdb were on special hardware such as SSD disks and my database was on regular disks, then I might consider using tempdb. There shouldn't be contention issues if you are using a regular table in tempdb as nothing else should be contending with it.
Thanks for responding Tara. I'm not sure I explained it well. I'd be using a 'temporary' physical table in the user database to store results and this would be used for subsequent queries. Afterwards, this table would be dropped from the user db. I'd do this sometimes rather than storing results in the tempdb for subsequent queries.Just to see the thoughts of the community doing it this way rather than using the tempdb for large datasets for subsequent queries.Cheers |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-08 : 17:24:51
|
| My answer is still the same. It would depend on the environment, specifically the hardware and config.If tempdb is on the same disks as the user database, then it really doesn't matter where you create the object.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2014-01-09 : 10:02:26
|
| I'm not sure what would be faster in terms of selecting or inserting into a temp table vs. a physical table but I'd agree with Tara.You can still create indexes on temp tables. And temp tables has the advantage that you know no one else can use it. Also, if this is in a stored procedure and you are developing in dev first, you don't have to worry about moving any table changes to Live. |
|
|
|
|
|
|
|
|
Using the tempdb for large datasets