Performance Loss during Large Import

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

Import/Export (DTS) and Replication (2000)

Performance Loss during Large Import

Author

Topic

Neil_
Starting Member

13 Posts

Posted - 2008-10-02 : 01:51:12

Hi, I am trying to import a large amount of data into a database (8,000,000 recods).
The problem is that it starts off fast but slows down as data is inserted.
I have been trying to figure out why and I think I just got it.

I first check to see if the data to be inserted is unique
if yes then I insert it and return the ID value
if no then find the id value to the existing matching data and return that.
For every record the above happens 5 times as the data is normalized into 6 tables
6th table stores Historical data and relavent reference keys (returned values).

I can use a unique index to improve speed but how would I then return the correct id to use as a reference?
To give you an idea of how slow it runs it started off at 1500 records/second now at 18 hours later 0.5-1 records/second.

Neil_
Starting Member

13 Posts

Posted - 2008-10-02 : 01:58:37

Eg: CURRENTLY WHAT I AM DOING
CREATE TABLE [PERSONS]
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME] VARCHAR(255) NOT NULL,
[SURNAME] VARCHAR(255) NOT NULL
)
GO
CREATE PROCEDURE [ADD_PERSON]
@NAME VARCHAR(255),
@SURNAME VARCHAR(255),
@ID INT OUTPUT
AS
BEGIN
SET @ID = -1
SELECT @ID = [ID]
FROM [PERSONS]
WHERE (@NAME = [NAME])AND(@SURNAME = [SURNAME])
IF @ID = -1
BEGIN
EXEC FIND_NEXT 'PERSONS', @ID OUTPUT -- PROCEDURE TO FIND MAX ID+1 IN TABLE
INSERT INTO [PERSONS] VALUES(@ID, @NAME, @SURNAME)
END
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources