Data Insertion very slow... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Data Insertion very slow...

Author

Topic

milgurung@hotmail.com
Starting Member

3 Posts

Posted - 2009-02-10 : 04:02:50

Hi,

I have a got two tables and the relation is one to many - meaning a single entry in the main table will have exact 12 records in the secondary table.

Data to these tables are fed from external CSV file which is uploaded from web application. Just to clarify, the slowness is not due to file upload. File upload is completed instantly.

Total Number of Rows I have been testing is 7,000 records in main table and 7000 * 12 = 84000 records in secondary table.

The loading process creates a dynamic insert statements and are passed to stored procedure to execute in a batch of 1000 records at a time.

SQL Server is taking around more than 40 minutes to insert 7,000 + 84,000 = 91,000 records which not acceptable.

Could someone please hint me how can I make the date insertion process faster?

The table scripts for Main table and secondary table are:

CREATE TABLE [dbo].[TblBudget_Exception](
[BudgetExceptionID] [int] IDENTITY(1,1) NOT NULL,
[BudgetBatch] [int] NOT NULL,
[Budget_DATETIME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Budget_Client] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Budget_Business] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Budget_Division] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Budget_Brand] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Budget_BusinessPillar] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Budget_BusinessPillar2] [varbinary](50) NULL,
[Budget_BusinessPillar3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Budget_Market] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MarketingPlanNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MarketingPlanName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PlanStatus] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CampaignType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CampaignTypeGroup] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostElement] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsumerActivityID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsumerActivityName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EntryDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PlanStartDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PlanEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RevisionDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CurrencyCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExceptionNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLD] [bit] NOT NULL CONSTRAINT [DF__TblBudget___HOLD__2EB2A197] DEFAULT ((0)),
[Record_RevNo] [int] NOT NULL CONSTRAINT [DF__TblBudget__Recor__2942188C] DEFAULT ((1)),
[Record_Entered] [datetime] NOT NULL CONSTRAINT [DF__TblBudget__Recor__2A363CC5] DEFAULT (getdate()),
[Record_User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__TblBudget__Recor__2B2A60FE] DEFAULT (suser_sname()),
[Record_Updated] [datetime] NOT NULL CONSTRAINT [DF__TblBudget__Recor__2C1E8537] DEFAULT (getdate()),
CONSTRAINT [PK__TblBudget_Except__2DBE7D5E] PRIMARY KEY CLUSTERED
(
[BudgetExceptionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[TblBudgetMonth_Exceptions](
[BudgetMonthExceptionID] [int] IDENTITY(1,1) NOT NULL,
[BudgetBatch] [int] NULL,
[Budget] [int] NOT NULL,
[BudgetMonth] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetYear] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetInitial] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetRevised] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetCommitted] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetActual] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLD] [bit] NOT NULL CONSTRAINT [DF_TblBudgetMonth_Exceptions_HOLD] DEFAULT ((0)),
[Record_RevNo] [int] NOT NULL CONSTRAINT [DF__TblBudget__Recor__355F9F26] DEFAULT ((1)),
[Record_Entered] [datetime] NOT NULL CONSTRAINT [DF__TblBudget__Recor__3653C35F] DEFAULT (getdate()),
[Record_User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__TblBudget__Recor__3747E798] DEFAULT (suser_sname()),
[Record_Updated] [datetime] NOT NULL CONSTRAINT [DF__TblBudget__Recor__383C0BD1] DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [NBSFairfield2005]
GO
ALTER TABLE [dbo].[TblBudgetMonth_Exceptions] WITH CHECK ADD CONSTRAINT [FK_TblBudgetMonth_Exceptions_tblDataBatch] FOREIGN KEY([BudgetBatch])
REFERENCES [dbo].[tblDataBatch] ([BatchID])
GO
ALTER TABLE [dbo].[TblBudgetMonth_Exceptions] WITH CHECK ADD CONSTRAINT [fkBudgetMonthsExBudget] FOREIGN KEY([Budget])
REFERENCES [dbo].[TblBudget_Exception] ([BudgetExceptionID])

INSERT statements are generated from the asp.net code and passed to a stored procedure which simply executes it using EXEC (@InsertStatement)

Thanks a lot.

Milan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 04:09:59

Of course the insert will take time of you do them one by one.
Do a single insert for all records! Think set-based.

E 12°55'05.63"
N 56°04'39.26"

jbp_j
Starting Member

24 Posts

Posted - 2009-02-10 : 04:12:26

hi,

In u r insert statement before and after use
set nocount on
set nocount off respectively.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-10 : 04:44:52

quote:
Originally posted by jbp_j

hi,

In u r insert statement before and after use
set nocount on
set nocount off respectively.

This is not speeding up 91,000 inserts one by one!

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 04:59:34

You could make a trigger for main table to automatically insert the related 12 records into secondary table for you.
That's one step to speed things up. That way you only need to insert 7000 records. The other 84000 are inserted automatically.
Or write a stored procedure which inserts records into secondary table and call the stored procedure from client to insert 7000 records.

But your best bet would be uploading the file into a staging table and have everything done automatically for you with one stored procedure. All it would take is 2 insert statements and about 5-10 seconds.

E 12°55'05.63"
N 56°04'39.26"

milgurung@hotmail.com
Starting Member

3 Posts

Posted - 2009-02-10 : 05:09:08

Hi,

Basically the generated SQL Inserted are batched and invoked stored procedure to excute. A batch of 12000 sql statements are executed at a time.

Pls let me know is there any way it can be done faster.

Thanks.
Milan G

quote:
Originally posted by Peso

Of course the insert will take time of you do them one by one.
Do a single insert for all records! Think set-based.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 05:10:02

quote:
Originally posted by milgurung@hotmail.com

Pls let me know is there any way it can be done faster.

See my other response...

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 05:54:54

you could also put the insertion logic in procedure and use OUTPUT operator to grab the inserted values from main table to temporary table and use it to populate the 12 records onto secondary table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources