How to improve insert statement run time? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to improve insert statement run time?

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-10-28 : 12:14:22

Hello All,

The below stored procedure insert new records in a table. The problem is that it insert a little of 3 million records daily which takes several hours to complete running. My question is does anyone know how I can improve the run time dramatically? Please advice. Thanks.

CREATE PROCEDURE [dbo].[sp_Events]

AS

BEGIN

Declare @ID int
Declare @CREATED_DATE datetime
Declare @LENGTH int
Declare @EVENT varchar(50)
Declare @START int
Declare @INTERVAL int
Declare @AMOUNT int

DECLARE @START_TIME int
DECLARE @EVENT_AMOUNT int
DECLARE @EVENTID int

DECLARE @EVENT_START_TIME int
DECLARE @EVENT_INTERVAL int

--Open Curosr for Clients
Declare MyClient Cursor For Select [Id],[Start],[Amount] FROM [Events] GROUP BY [Id],[Start],[Amount]
Open MyClient
Fetch Next From MyClient Into @EVENTID,@START_TIME,@EVENT_AMOUNT

WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @EVENT_START_TIME = @START_TIME
SELECT @EVENT_INTERVAL = @START_TIME

--Open Cursor for List of Event
Declare MyEvents Cursor For Select * From [Events] WHERE Id=@EVENTID ORDER BY [INTERVAL]
OPEN MyEvents

Fetch Next From MyEvents Into @ID, @CREATED_DATE,@LENGTH,@EVENT,@START,@INTERVAL,@AMOUNT

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO MyEvents ([EventId],CREATED_DATE,LENGTH,EVENT,START,[INTERVAL],AMOUNT) VALUES(@ID,@CREATED_DATE,(@INTERVAL - @EVENT_START_TIME),'Open',@START,(@EVENT_START_TIME-@EVENT_INTERVAL),@AMOUNT)
INSERT INTO MyEvents ([EventId],CREATED_DATE,LENGTH,EVENT,START,[INTERVAL],AMOUNT) VALUES(@ID,@CREATED_DATE,@LENGTH,@EVENT,@START,@INTERVAL,@AMOUNT)
SELECT @EVENT_START_TIME = @INTERVAL + @LENGTH
SELECT @EVENT_INTERVAL = @INTERVAL
Fetch Next From MyEvents Into @ID, @CREATED_DATE,@LENGTH,@EVENT,@START,@INTERVAL,@AMOUNT
END
Close MyEvents
Deallocate MyEvents

if @EVENT_START_TIME <> (@START_TIME + @EVENT_AMOUNT)
BEGIN
INSERT INTO MyEvents ([EventId],CREATED_DATE,LENGTH,EVENT,START,[INTERVAL],AMOUNT) VALUES(@ID,@CREATED_DATE,((@START_TIME + @EVENT_AMOUNT) - @EVENT_START_TIME),'Open',@START,@EVENT_START_TIME,@AMOUNT)
END

--Close Cursor for List of Event
Fetch Next From MyClient Into @EVENTID,@START_TIME,@EVENT_AMOUNT
END
Close MyClient
Deallocate MyClient
--Close Curosr for List of Agent
END
GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-28 : 13:13:17

(1) See if you can do without the CURSOR
(2) You can drop any indexes/disable constraints on the table before the insert
(3) Recreate the index/enable the constraints after the script.

I think it is the cursor'ing through fro 3 mill rows that is more to be looked into than the indexes/constraints. If you are doing a bulk insert then the indexes/constraints can help.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pras2007
Posting Yak Master

216 Posts

Posted - 2007-10-28 : 20:15:38

Thanks for your response dinakar, but do you or anyone else know how to rewrite the stored procedure without using cursor? Please advice. Thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 20:37:54

Something is missing from that procedure, is that everything?

You are insert values into MyEvents and MyClient, but those cursors are closed...I don't see where anything gets inserted to the final table?

Seems to me that it could be done with a simple Insert Statement using a simple Select query to append records to the table. You can definitely do this in bulk, without having to the cursors.

WHat is a sample data and desired result to be inserted into the final table?

Other than a few calculated columns using the same data..are there any other manipulations or restrictions?

I have gone through that sp a few times, and I can't see where it is doing anything other cycling through all the record, populating the cursor data sets and the ending. Maybe I can't see it very well, but where do any records get inserted to the main table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources