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 |
|
varalakshmi
Yak Posting Veteran
98 Posts |
 Posted - 2015-04-27 : 06:04:11
|
| Hi,While writing a stored procedure, we use temporary tables to do some calculations. Every time the procedure is run, these temporary tables should be truncated and loaded again.In this scenario which is the best approach in terms of performance, whether to drop the intermediate temporary tables and create it everytime the procedure is run using SELECT INTO statement or just truncate and load the table using INSERT statement.Thanks in Advance.- Varalakshmi |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-04-27 : 06:53:33
|
| First put a check to drop the temp table if it exists.Second, do an insert into temp table.--------------------Rock n Roll with SQL |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-27 : 07:05:14
|
| You have a temporary table with a fixed name in the database?What happens if two people run the SProc at the same time?Could you use a #TempTable instead? (which will be created just for the duration of the user's process, and be unique to that process) |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-04-27 : 07:19:59
|
| Sorry missed one step after the first one, create a temp table.Assumption is it is a local temp. table.Kristen, even if the temp. table name is same would not two different people(two different connections) have their own copy of this temp table?--------------------Rock n Roll with SQL |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-27 : 07:52:01
|
| The way I read "Every time the procedure is run, these temporary tables should be truncated and loaded again." it seemed that there was already a table in the database and the proposal was either to Truncate and Insert (new rows) or to Drop / Create and Insert rows.I am presuming that in that scenario there is one table name shared by all users.Whereas if the APP was changed to use a #TempTable then that would be unique to each user's session.Perhaps all hell will break loose if two people do the process at the same time, regardless of whether there is a locally-unique table, or not! in which case there may already be sempahores etc. in place to prevent that.But its also quite possible that I have misunderstood what the O/P was after.IF I had a fixed table and the choice was TRUNCATE or DROP / CREATE then I think:If the Index(es) on the table are likely to be useful (i.e. the pages are likely to be reused because the distribution of records is the same / similar to previous data set(s), then I would use truncate.This would be good if data is imported daily where, say, 90% of the data is the same as yesterday. (That said, in that instance I would write a more sophisticated import which deleted old stale rows, inserted new ones, and updated any that had changed (assuming that the UPDATES would be a small proportion of the rows in the table)If the index structure will be useless - e.g. import is by INVOICE No. and no rows imported are the same as before, then the ever-increasing number of Invoice No. probably means that Drop / Create table is better.if the import is large, and there are secondary indexes, it may well be better to Drop indexes, Import data, Re-create indexes. Also, in that instance, it will help if the data is pre-sorted into clustered index order. If the data is imported with BCP then a Hint can be provided that the imported data is pre-sorted, which will speed up the Bulk Load process. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-04-27 : 08:16:01
|
| Thanks for the reply Kristen. I guess OP should now find the answer in your post.--------------------Rock n Roll with SQL |
|
|
|
varalakshmi
Yak Posting Veteran
98 Posts |
Posted - 2015-04-27 : 08:31:32
|
| Thanks Kristen and rocknpop for the suggestions.- Varalakshmi |
|
|
|
|
|
|
|
|
SELECT INTO and INSERT INTO