SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete data in tables/performance issues


Delete data in tables/performance issues

Author
Message
Krasavita
Krasavita
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1894
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28568 Visits: 39977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JohnnyDBA
JohnnyDBA
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 897
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


SQL Show
SQL Show
SSChasing Mays
SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 Visits: 1078
Truncate and insert????? then why we cant go for BCP? Will it not be faster?
JohnnyDBA
JohnnyDBA
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 897
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).
Krasavita
Krasavita
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1894
I can't use truncate, I need to delet over 5 mil records and leave about 2mil
muth_51
muth_51
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 2906
Delete rows in batches and don't forget to rebuild the indexes after deletes based on fragmentation percentage.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40068 Visits: 38567
This article should help http://www.sqlservercentral.com/articles/T-SQL/67898/.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search