Load a big file full of SPs - SQL Server Forums

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

Load a big file full of SPs

Author

Topic

luiescco
Starting Member

8 Posts

Posted - 2010-07-28 : 13:02:29

Hello,
I have a file that contains 5 million calls to a SP with different data, to populate a database.

How can I do this faster? like using bulk insert but for SPs?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 13:10:52

You'll need to post some sample code and data for us to help.

If your stored procedure is transforming the data, then you should consider using BULK INSERT command to load the data into a staging table and then transforming your data between the staging table and your actual table. You should also consider using an SSIS package which can do complex logic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

luiescco
Starting Member

8 Posts

Posted - 2010-07-28 : 19:11:46

thank toy for the reply and Sorry for the late response, this is some sample of the code:

I have millions of calls like these below:

SP_InsertAllReferences 'lgr','test','Static Global Object','Define','file.c','/users/test/file.c',132,18,'file2.c','File'

SP_InsertAllReferences 'ghy','test5','function','Define','filert.c','/users/test/file.c',1332,1,'file2wer.c','Function'

The SP inserted values into 2 tables, but inside performed selects to get some IDs.

---------------------
Another thing I'm trying to do is load all data into a temp table using bulk insert and its pretty fast, but in this point I'm using SP with cursors to insert the loaded data on the temp table to the corresponding table but this process is not fast, is there a way to improve it?

Do you need more information?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 19:14:11

Your stored procedure will need to be rewritten as it's the bottleneck.

Also, you should not be using sp_ as the prefix to your stored procedures as you'll take a small performance hit on that. It's a special prefix and should be avoided since SQL Server will automatically look for the object in the master database first and then your database. It's a small performance hit, but every millisecond shed from the duration helps.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 19:14:56

You could try posting the code of the stored procedure if you want help rewriting it. The cursor has got to go if you want it fast.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

luiescco
Starting Member

8 Posts

Posted - 2010-07-28 : 19:26:34

I'm going to copy the stored procedure that uses cursors.

But in the other hand...what is a way to remove the bottleneck and improve my SP?

luiescco
Starting Member

8 Posts

Posted - 2010-07-28 : 19:38:05

This is the code
--load all

--params is the temp table

DECLARE @IDEntType BIGINT, @NewIDEntType BIGINT, @IDEnt BIGINT
DECLARE @NewIdEnt BIGINT, @IDProduct BIGINT, @NewIDProduct BIGINT, @IDBuild BIGINT

BEGIN
--First Step: Get or SET the IDRefType
SELECT @IDEntType = (SELECT IDCodeEntityType from CodeEntityTypes WHERE Name = @entKind)

BEGIN TRANSACTION
BEGIN TRY

IF (@IDEntType is NULL)
BEGIN
INSERT into CodeEntityTypes VALUES(@entKind)
SET @IDEntType = IDENT_CURRENT('CodeEntityTypes')
END
--Second Step: INSERT the entity
SELECT @IDEnt = (SELECT IDCodeEntity from CodeEntities WHERE Name = @entName and IDCodeEntityType = @IDEntType)
IF (@IDEnt is NULL)
BEGIN
INSERT into CodeEntities VALUES(@entName,@IDEntType)
SET @IDEnt = IDENT_CURRENT('CodeEntities')
END

--Third Step: Get ID BUILD AND INSERT into BuildCodeEntities
SELECT @IDBuild = (SELECT IDBuild from Builds WHERE Name = @buildName)
INSERT into BuildCodeEntities VALUES (@IDEnt,@IDBuild)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR ('Entity insert fails',16,1)
ROLLBACK
END CATCH

--cursors code

ALTER PROCEDURE [dbo].[spu_insertEntitiesTest]
AS
BEGIN
DECLARE @entName VARCHAR(255), @entKind VARCHAR(128), @buildName VARCHAR(128)
DECLARE CustList cursor FAST_FORWARD for
SELECT * from TmpParams

OPEN CustList
FETCH NEXT FROM CustList
INTO @entName, @entKind, @buildName

BEGIN TRANSACTION
BEGIN TRY
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[SP_InsertEntityInfoAllTables] @entName, @entKind, @buildName
FETCH NEXT FROM CustList INTO @entName, @entKind, @buildName
END
CLOSE CustList
DEALLOCATE CustList
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 23:18:16

Looks like you are calling yet another stored procedure, so that code would need to be posted too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-07-29 : 01:42:02

"NSERT into CodeEntityTypes VALUES(@entKind)
SET @IDEntType = IDENT_CURRENT('CodeEntityTypes')"

Do you really mean to use that, or should you be using SCOPE_IDENTITY() ?

SELECT @IDBuild = (SELECT IDBuild from Builds WHERE Name = @buildName)
INSERT into BuildCodeEntities VALUES (@IDEnt,@IDBuild

I wonder if this would be faster (if it is it will be minimal ... but every little helps ...

INSERT into BuildCodeEntities
SELECT @IDEnt IDBuild
from Builds
WHERE Name = @buildName

but that is not the underlying problem, its having to call an SProc to insert each row, one-by-one, in the loop that will be slow.

luiescco
Starting Member

8 Posts

Posted - 2010-07-29 : 10:01:35

What we should do?

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 10:16:26

Post the code for this sproc

EXEC [dbo].[SP_InsertEntityInfoAllTables]

as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 10:17:18

and tell us how the sproc "file" of executions was generated...probably from some data file?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

luiescco
Starting Member

8 Posts

Posted - 2010-07-29 : 10:18:15

this is the SP missing that you requested tkizer

ALTER PROCEDURE [dbo].[SP_InsertEntityInfoAllTables]
-- Add the parameters for the stored procedure here
@entName VARCHAR(255),
@entKind VARCHAR(128),
@buildName VARCHAR(128)
AS

DECLARE @IDEntType BIGINT, @NewIDEntType BIGINT, @IDEnt BIGINT
DECLARE @NewIdEnt BIGINT, @IDProduct BIGINT, @NewIDProduct BIGINT, @IDBuild BIGINT

BEGIN
--First Step: Get or SET the IDRefType
SELECT @IDEntType = (SELECT IDCodeEntityType from CodeEntityTypes WHERE Name = @entKind)

BEGIN TRANSACTION
BEGIN TRY

--Second Step: INSERT the entity
SELECT @IDEnt = (SELECT IDCodeEntity from CodeEntities WHERE Name = @entName and IDCodeEntityType = @IDEntType)
IF (@IDEnt is NULL)
BEGIN
INSERT into CodeEntities VALUES(@entName,@IDEntType)
SET @IDEnt = IDENT_CURRENT('CodeEntities')
END

--Third Step: Get ID BUILD AND INSERT into BuildCodeEntities
SELECT @IDBuild = (SELECT IDBuild from Builds WHERE Name = @buildName)
INSERT into BuildCodeEntities VALUES (@IDEnt,@IDBuild)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR ('Entity insert fails',16,1)
ROLLBACK
END CATCH

END

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 10:31:24

If this was a car, and it was in a wreck, and I was the insurance adjuster, I call it totaled and cut you a check....

You would be better off telling us what you are really trying to accomplish, and what the source data is, and then post all of the DDL for the tables

Describe to us, in business terms, what it is you have to accomplish

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

luiescco
Starting Member

8 Posts

Posted - 2010-07-29 : 10:41:48

OK, thank you Brett.

I have a file with millions of line like this one
entityName,entityType,ProductName

Using Bulk Insert I load all these data into a stage table called params. This process is very very fast.

The problem begins here:
I need to take all the data stored in the Stage Table and first insert the entityType in one table, then insert ProductName in another table and finally get those IDs and insert EntityName.

For this process we are using cursors and those store procedures I posted before, but its taking too much time to insert all the data from stage table to the real tables.

According to tkizer and Kristen I must rewrite store procedures to avoid cursors and many calls to SPs to insert the data, but I dont know how to do that, I'm very new using SQL Server

Is this ok? or do you need more info Brett?

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 11:58:47

OK,

so I'm guess, that when you do an INSERT you need to grab the identity value from the parent so yo ucan insert that identity as a foreign key to the next table

What version of sql server are you using?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

luiescco
Starting Member

8 Posts

Posted - 2010-07-29 : 12:47:58

SQL SERVER MANAGEMENT STUDIO 2008
and thank you very much brett for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources