Reputation: 1061
We are running an import stored procedure in SQL Server 2008R2-SP1 that loads thousand of rows into several tables at a time. We are having problems with tempDB
and transaction log
size.
Something like this:
CREATE PROCEDURE spReallyHugeImportDataProcedure
@id int
AS
BEGIN
CREATE TABLE #temp(...)
INSERT INTO #temp
SELECT *
FROM AlotOfJoins
INSERT INTO FinalTable
SELECT * FROM AlotOfJoins
DROP #tempTable
INSERT INTO #temp
SELECT *
FROM AlotOfJoins
INSERT INTO FinalTable
SELECT * FROM AlotOfJoins
DROP #tempTable
INSERT INTO #temp
SELECT *
FROM AlotOfJoins
INSERT INTO FinalTable
SELECT * FROM AlotOfJoins
DROP #tempTable
-- And so on....
END
We are trying to split the whole process and run several times for a small set of data.
Like this:
CREATE PROCEDURE spReallyHugeImportDataProcedure
@id int
AS
BEGIN
DECLARE @SomeSortOfCounter int = 100
WHILE(@SomeSortOfCounter <> 0)
BEGIN
-- TRY TO START A NEW TRANSACTION
BEGIN TRAN
CREATE TABLE #temp(...)
INSERT INTO #temp
SELECT *
FROM AlotOfJoins
WHERE SomeFileterWorkinWithTheCounter = @SomeSortOfCounter
INSERT INTO FinalTable
SELECT * FROM AlotOfJoins
DROP #tempTable
INSERT INTO #temp
SELECT *
FROM AlotOfJoins
WHERE SomeFileterWorkinWithTheCounter = @SomeSortOfCounter
INSERT INTO FinalTable
SELECT * FROM AlotOfJoins
DROP #tempTable
INSERT INTO #temp
SELECT *
FROM AlotOfJoins
WHERE SomeFileterWorkinWithTheCounter = @SomeSortOfCounter
INSERT INTO FinalTable
SELECT * FROM AlotOfJoins
DROP #tempTable
-- And so on....
-- TRY TO RELASE TEMP OBJECTS,
-- OR GIVE TO THE SERVER THE OPORTUNITY TO DO IT
COMMIT
SET @SomeSortOfCounter = @SomeSortOfCounter - 1
END
END
Is it possible for the SQL Server engine to work between those internal transactions?
Upvotes: 0
Views: 1485
Reputation: 2908
simple answer is yes, as long as there is no outer transaction outside of the stored proc. also, there's no reason to add in explicit transactions. simply creating a loop and only working with a chunk of records per statement will let your tlog space get reused and you won't be forcing all of the records at one time into a temp table.
Upvotes: 0
Reputation: 9042
Option 1: Using a table in a user database
If you really need to store the data in a temporary table, build that table in a user database (ImportTemp for example or in your destination DB) then use it instead of the tempdb. In this case, the SQL Server should not use as much space in TempDB and your data will be stored persistently -> you can reuse it and you can split your loader queries into multiple batches.
Optionally you can move this table to a different filegroup to prevent concurent writing and lower the chance of interferring with other processes.
In this case, the steps are:
If you are using this import table relatively frequently, you can only truncate it before and after use instead of dropping and recreating it.
Option 2: Using an ETL tool
Use an ETL tool which can handle data in batches / buffers. If you are using SQL Server Standard or above you have the option to use SSIS (SQL Server Integration Services).
You can release unused space from data and log files using the DBCC SHRINKFILE
command:
USE [YourDatabase]
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Example
USE [tempdb]
DBCC SHRINKFILE (tempdb_data, TRUNCATEONLY)
Optionally
You can spread the TempDB files accross drives by adding additional data files to the TempDB:
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);
A related question: How to spread tempdb over multiple files?
Upvotes: 1