temp DB advice - Using Temporary tables
NickName:Thomas_ITsavvy Ask DateTime:2015-06-11T12:24:23

temp DB advice - Using Temporary tables

I am working a report, where in the result is combination of multiple #temp tables. Structure is as below

  1. Stored procedure 1 which has a temp table which gives a 0.5 million rows

  2. Stored procedure 2 which has a temp table which give 0.1 million rows

Finally i need to combine the result set of above 2 SP , again use a temp table and make one final result set for report. Now i am worried about the performance, later if data increases, will it effect temp db. We usually stage the data monthly , in a month it Database may contain about 1 million rows. How much is the maximum capacity temp db accommodates. Will it effect with above approach.

Copyright Notice:Content Author:「Thomas_ITsavvy」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/30771549/temp-db-advice-using-temporary-tables

Answers
Anuj Tripathi 2015-06-18T04:22:12

First, it is not about number of rows it is about size of row. So, if you have 7KB per row then for .6 million rows it would be roughly around 4 GB. Now, this is not the end, SQL Server use TempDb for storing internal objects, version objects and user objects which also include intermediate result. You can expect the size to be grown more than 4 GB in your case.\nThere are two possible ways to overcome this:\n\n\nTune your queries, minimize use of Temp table, Table variable, CTE, large objects like VARCHAR(MAX) or cursors.\nIncrease your Tempdb file size. Calculate the max size either based on observation [re-building indexes is a best bet]\n\n\nIn real world scenerio, there is always a chance for improvement in query itself. Check if you can avoid using tempdb by joining table correctly or by using views.",


More about “temp DB advice - Using Temporary tables” related questions

temp DB advice - Using Temporary tables

I am working a report, where in the result is combination of multiple #temp tables. Structure is as below Stored procedure 1 which has a temp table which gives a 0.5 million rows Stored procedure 2

Show Detail

SQL Temp DB , Difference Between Temporary tables & Tables

I would like to know that, Does creating a table in the table section of TempDB differ from creating a Hash table. Creating a table in Temp DB USE [tempdb] GO /****** Object: Table [dbo].[abc...

Show Detail

Temporary tables in YugaByte DB

The idea of temporary tables is present in many databases. Are there any plans to support temporary tables in YugaByte DB SQL clusters?

Show Detail

Use of variable tables, tempdb growth and db session sleeping

While i have got some further insight from this post i am still struggling to get a fuller understanding of my recent use of variable tables and the associated tempdb growth. I have recently been ...

Show Detail

Use of temp tables in SQLite databases

I was browsing through the source code for Mendeley for Android by Martin Eve and saw that Temp tables are created along with the main tables by calling replace on the tables with _id. For example ...

Show Detail

SAS temporary DB2 tables - Creating an index

I have not found a clear answer from my DBA's on this one. I am in a DB2 warehouse...huge tables. I often practice temp tables with an rsubmit, such as... execute (declare global temporary table s...

Show Detail

Store greenplum temporary tables in memory [temp_buffers]

I'm trying to figure out if greenplum could store temporary table data in memory. According to the documentation it is possible, while the memory size is controlled by the [temp_buffers] parameter....

Show Detail

Temporary tables and AS statement usage

If I have a query, say: SELECT * FROM ( SELECT fields FROM tables WHERE condition ) AS TEMP_TABLE are the results of the above query saved in a temporary table called TEMP_...

Show Detail

Create function with temporary tables that return a select query using these temp tables

I need to create a function, which returns results of a SELECT query. This SELECT query is a JOIN of few temporary tables created inside this function. Is there any way to create such function? Her...

Show Detail

Using Temp tables in SSIS

I am using a Temporary table in Stored Procedure in SQL Server. I am trying to use that SP in OLE DB Source Editor. I can see the data output returned in the Query Builder that comes with Build Qu...

Show Detail