Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete data in tables/performance issues Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 9:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
Hello,

I need an advice, I need to get data daily from one server to another, pretty much I will truncate table and insert data, what is the best way to do it,
I don't want cause performance, so what is the best way to handle it.

Thank you
Post #1340724
Posted Monday, August 6, 2012 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 12,916, Visits: 32,077
if you know it will always be a delete and truncate, vs a merge, i'd import into a new table, and when the import was complete, rename the original table or drop it,and rename the new table to the correct name;

you might need to script permissions to the table with the change, but that would minimize the blocking i'd think.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1340725
Posted Tuesday, August 7, 2012 10:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 340, Visits: 807
I am doing something similar. We need to get data from one server to the other with minimal impact to the developers deployment process and minimal bandwidth. What we opted for was a local bulk copy into another database then compress and backup / restore to the other server. Overall it was fastest compared to other methods.

Here is what I have so far. This is the part that runs on the source server. You could modify it to however you see fit (maybe restore as a different name then do a name swap at the database level). I don't have the other half completed yet.

Good luck

DECLARE
-- configurable variables
@sourceDatabase SYSNAME = '' -- source database
, @dataPath VARCHAR(255) = '' -- path to data file
, @logPath VARCHAR(255) = '' -- path to log file
, @tableListFile VARCHAR(255) = '' -- text file containing the list of tables to copy
, @destinationDataSize INT = 500 -- destination database size in MB
, @destinationLogSize INT = 50 -- destination log file size in MB

-- non-configurable variables
, @destinationDatabase SYSNAME
, @TSQL VARCHAR(MAX)

-- table specific variables
, @tableName SYSNAME
, @columnList VARCHAR(MAX)

-- initializations
SELECT
@destinationDatabase = 'Copy_' + @sourceDatabase
, @dataPath = CASE WHEN RIGHT(@dataPath, 1) <> '\' THEN @dataPath + '\' ELSE @dataPath END
, @logPath = CASE WHEN RIGHT(@logPath, 1) <> '\' THEN @logPath + '\' ELSE @logPath END

IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL
DROP TABLE ##t_TableList
CREATE TABLE ##t_TableList (
TableName SYSNAME NOT NULL
)

IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL
DROP TABLE ##t_ColumnList
CREATE TABLE ##t_ColumnList (
ColumnList VARCHAR(MAX) NOT NULL
)

SET NOCOUNT ON

BEGIN TRY
-- Cleanup
SELECT @TSQL = '' +
'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) ' +
'BEGIN ' +
'ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' +
'USE [master] DROP DATABASE [' + @destinationDatabase + '] ' +
'END'
PRINT(@TSQL)
EXEC(@TSQL)

-- Create Database
SELECT @TSQL = '' +
'CREATE DATABASE [' + @destinationDatabase + '] ' +
'ON PRIMARY ' +
'( NAME = N''' + @destinationDatabase + '_data'', FILENAME = N''' + @dataPath + @destinationDatabase + '_data.mdf'' , SIZE = ' + CONVERT(VARCHAR, @destinationDataSize) + 'MB , FILEGROWTH = 512000KB ) ' +
'LOG ON ' +
'( NAME = N''' + @destinationDatabase + '_log'', FILENAME = N''' + @logPath + @destinationDatabase + '_log.ldf'' , SIZE = ' + CONVERT(VARCHAR, @destinationLogSize) + 'MB , FILEGROWTH = 10%)'
PRINT(@TSQL)
EXEC(@TSQL)

-- Get Table List from File
SELECT @TSQL = '' +
'BULK INSERT ##t_TableList FROM ''' + @tableListFile + ''' with (FirstRow = 1)'
EXEC(@TSQL)

-- DEBUG SELECT * FROM ##t_TableList

-- Process Each Table
WHILE EXISTS ( SELECT 1 FROM ##t_TableList )
BEGIN
SELECT TOP 1 @tableName = tableName, @columnList = '' FROM ##t_TableList
DELETE FROM ##t_TableList WHERE tableName = @tableName
PRINT(@tableName)

-- Fetch Column List
BEGIN
SELECT @TSQL = 'USE [' + @sourceDatabase + ']
DECLARE @columnList VARCHAR(MAX) = ''''
SELECT @columnList = @columnList + CASE WHEN @columnList = '''' THEN ''['' + [name] + '']'' ELSE '',['' + [name] + '']'' END FROM sys.columns c WHERE c.is_computed = 0 AND OBJECT_SCHEMA_NAME([object_id]) = ''dbo'' AND OBJECT_NAME([object_id]) = ''' + @tableName + ''' ORDER BY column_id
SELECT @columnList'

INSERT INTO ##t_ColumnList (ColumnList)
EXEC(@TSQL)

SELECT @columnList = ColumnList FROM ##t_ColumnList
DELETE FROM ##t_ColumnList
-- PRINT(@columnList)
END

-- Generate Destination Schema
-- **note** Must create dummy join so identity columns are not propagated to destination database
SELECT @TSQL = 'SELECT TOP 0 ' + @columnList + ' INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] LEFT JOIN ( SELECT TOP 1 ''Dummy'' AS DummyColumn FROM master.sys.databases ) b ON b.[DummyColumn] = ''Dummy'''
PRINT(@TSQL)
EXEC(@TSQL)

-- Insert Data
SELECT @TSQL = 'INSERT INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] WITH (TABLOCK) (' + @columnList + ') SELECT ' + @columnList + ' FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] WITH (NOLOCK)'
PRINT(@TSQL)
EXEC(@TSQL)

-- Enable Table Compression
SELECT @TSQL = 'USE [' + @destinationDatabase + '] ALTER TABLE [dbo].[' + @tableName + '] REBUILD WITH (DATA_COMPRESSION=PAGE)'
PRINT(@TSQL)
EXEC(@TSQL)
END

END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

IF @@TRANCOUNT > 0
ROLLBACK TRAN
ELSE

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);

END CATCH

-- Cleanup
SELECT @TSQL = 'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) USE [' + @destinationDatabase + '] ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE USE [master] DROP DATABASE [' + @destinationDatabase + ']'
PRINT(@TSQL)
EXEC(@TSQL)

IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL
DROP TABLE ##t_TableList
IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL
DROP TABLE ##t_ColumnList

Post #1341383
Posted Tuesday, August 7, 2012 9:29 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Truncate and insert????? then why we cant go for BCP? Will it not be faster?
Post #1341665
Posted Tuesday, August 7, 2012 10:08 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 340, Visits: 807
BCP will pull data from a file. This method is to pull data from an existing table to another table.

Could also use SQLBulkCopy .net library (or SSIS).
Post #1341667
Posted Tuesday, November 13, 2012 11:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
I can't use truncate, I need to delet over 5 mil records and leave about 2mil
Post #1384212
Posted Tuesday, November 13, 2012 1:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 394, Visits: 1,976
Delete rows in batches and don't forget to rebuild the indexes after deletes based on fragmentation percentage.
Post #1384278
Posted Tuesday, November 13, 2012 6:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 23,396, Visits: 32,229
This article should help http://www.sqlservercentral.com/articles/T-SQL/67898/.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1384359
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse