How to Reduce the data record saving time

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Reduce the data record saving time

Author

Topic

shagilgvr
Starting Member

4 Posts

Posted - 2011-08-28 : 04:47:03

Using the following SQL programming script I am saving records into 8 database, Cursor is used to fetch and insert the records into 8 DB. My concern is saving time, to save 13,000 records it taking 6 to 7 minutes. Is there anyway I can reduce the saving time

DECLARE CUR_FETCH_Data CURSOR FOR
SELECT N.[Notification_Type],ND.[Customer_ID], CU.[Mobile_No],CU.[CategoryID], N.Notification_Summary, N.[Department_ID], N.[Created_By] FROM [NO_MST_NOTIFICATION] N
INNER JOIN [NO_MST_NOTIFICATION_DETAIL] ND ON N.[Notification_ID] = ND.[Notification_ID]
INNER JOIN [NO_MST_CUSTOMER] CU ON CU.[Customer_ID] =ND.[Customer_ID]
INNER JOIN [NO_MST_CADRE]CA ON CU.Cadre_Code = CA.Cadre_Code
WHERE N.[Notification_ID] = @Notification_ID ORDER BY CA.Priority

select @RouteNumber = 1
OPEN CUR_FETCH_Data

FETCH NEXT FROM CUR_FETCH_Data Into @Notification_Type,@Customer_ID,@Mobile_No,@CategoryID,@Notification_Summary,@Department_ID,@Created_By

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Notification_Type='D')

BEGIN
if @dbCount = 1
begin
EXECUTE [ESMSNETV3_1].[dbo].GenerateTechID @TranID OUTPUT
SELECT @TranID
EXECUTE [ESMSNETV3_1].[dbo].GenerateMsgID @QueueID OUTPUT
SELECT @QueueID
SET @strSQL = '[' + @DatabaseSMS + '].[dbo].[SendNewMsgV2] ''' + @QueueID + ''',''' + @TranID + ''',N''' + @Notification_Summary + ''', ''' + @Mobile_No + ''', ' + @Department_ID + ', ''DPNotifier'', ''' + Cast(getdate() as varchar(30)) + ''', ''' + CAST(@Customer_ID as varchar(50)) + ''', ''0'', ''S'' , ''' + ISNULL(@Created_By,'') + '''' + ',' + cast(@RouteNumber as varchar(5))
EXEC(@strSQL)
--PRINT(@strSQL)
select @dbCount = @dbCount + 1
end

else if @dbCount = 2
begin
EXECUTE [ESMSNETV3_1].[dbo].GenerateTechID @TranID OUTPUT
SELECT @TranID
EXECUTE [ESMSNETV3_1].[dbo].GenerateMsgID @QueueID OUTPUT
SELECT @QueueID
SET @strSQL = '[ESMSNETV3_2].[dbo].[SendNewMsgV2] ''' + @QueueID + ''',''' + @TranID + ''',N''' + @Notification_Summary + ''', ''' + @Mobile_No + ''', ' + @Department_ID + ', ''DPNotifier'', ''' + Cast(getdate() as varchar(30)) + ''', ''' + CAST(@Customer_ID as varchar(50)) + ''', ''0'', ''S'' , ''' + ISNULL(@Created_By,'') + '''' + ',' + cast(@RouteNumber as varchar(5))
EXEC(@strSQL)
--PRINT(@strSQL)
select @dbCount = @dbCount + 1
end

else if @dbCount = 3
begin
EXECUTE [ESMSNETV3_1].[dbo].GenerateTechID @TranID OUTPUT
SELECT @TranID
EXECUTE [ESMSNETV3_1].[dbo].GenerateMsgID @QueueID OUTPUT
SELECT @QueueID
SET @strSQL = '[ESMSNETV3_3].[dbo].[SendNewMsgV2] ''' + @QueueID + ''',''' + @TranID + ''',N''' + @Notification_Summary + ''', ''' + @Mobile_No + ''', ' + @Department_ID + ', ''DPNotifier'', ''' + Cast(getdate() as varchar(30)) + ''', ''' + CAST(@Customer_ID as varchar(50)) + ''', ''0'', ''S'' , ''' + ISNULL(@Created_By,'') + '''' + ',' + cast(@RouteNumber as varchar(5))
EXEC(@strSQL)
--PRINT(@strSQL)
select @dbCount = @dbCount + 1
end

END
Same is repeated for other database 4 to 8

Regards
Shagil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-28 : 05:48:36

Are all the dbs in same server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shagilgvr
Starting Member

4 Posts

Posted - 2011-08-28 : 06:02:17

First four(1-4) database in the server-1 and the next 4 DB(5-8) in the server - 2.

Regards
Shagil

shagilgvr
Starting Member

4 Posts

Posted - 2011-08-28 : 06:07:17

Is there any alternative way other than using cursor. At Present cursor holds 13,000 Records and stores record by one. Database will be updated with other 7,000 records soon and total number of records will be 20,000. It results increase the amount of saving time. Can you advice any better solution

Regards
Shagil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-28 : 06:29:41

If you want to execute SP for each records in a resultset you've no other way than to use cursor. if you're able to rewrite it as UDF you will be able to execute it set based for each rec in resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shagilgvr
Starting Member

4 Posts

Posted - 2011-08-28 : 06:49:20

Thanks for the responds,

Concept behind saving the records is like 1st, 9th will be saved in First Database and 2nd, 10th records will be saved in second database. we can save the control flow time between each database if we store full records into first database at a time then move to second database. At present cursor holds 13,000 records, can i have any method to select 1st, 9th, 17th etc at a time from the cursor and move to first database then 2nd, 10th, 18th etc into second database.

Regards
Shagil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 00:28:19

no need of cursor do something like


USE [Server1]
INSERT INTO DB1.dbo.Table
SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY PK) AS Seq,columns...
FROM yourTable
)t
WHERE Seq % 8 = 1

INSERT INTO DB2.dbo.Table
SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY PK) AS Seq,columns...
FROM yourTable
)t
WHERE Seq % 8 = 2

....

INSERT INTO DB1.dbo.Table
SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY PK) AS Seq,columns...
FROM yourTable
)t
WHERE Seq % 8 = 4

INSERT INTO [Server2].DB1.dbo.Table
SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY PK) AS Seq,columns...
FROM yourTable
)t
WHERE Seq % 8 = 5
....
INSERT INTO [Server2].DB1.dbo.Table
SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY PK) AS Seq,columns...
FROM yourTable
)t
WHERE Seq % 8 = 0

PK is your primary key

and SErver2 is linked server created to you on server1 to server2

see below on how to create linked server

http://www.jensbits.com/2010/11/10/create-linked-server-sql-server-2008/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources