Help Optimising SP

HI All,
I am busy importing records from excel into a SQL table.
I am using:
insert into PLImport2 select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\work\coke\Detailed P&L June RE.xls', 'SELECT * FROM
[Import1]')
The excle sheet has 49000 records in it, which normally takes about 20-22
seconds, which includes opening the excel file of course.
Which normally
I have an instead of trigger, so that I can replace certain column on input.
With the trigger running it took 1:43:20
Could someone see if this could be optimised. Or is there a better way of
doing things.
Thanks
RObert
PS Below is the table and sp. Please dont worry too much about the field
name, ie [1], as this is how it was created when come over from excel.
This acts as a staging table , and will be put into the proper table once
all the validation is taken care of.
Thanks
CREATE TABLE [dbo].[PLImport2] (
[RowID] float NULL,
[PF] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PFDesc] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Acc] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Product] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostingPeriod] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1] float NULL,
[2] float NULL,
[3] float NULL,
[4] float NULL,
[5] float NULL,
[6] float NULL,
[7] float NULL,
[8] float NULL,
[9] float NULL,
[10] float NULL,
[11] float NULL,
[12] float NULL,
[111] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[21] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[31] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[41] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[51] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[61] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[71] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[81] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[91] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[101] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[112] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[121] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[122] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[22] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[32] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[42] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[52] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[62] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[72] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[82] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[92] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[102] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[113] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[123] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[13] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[23] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[33] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[43] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[53] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[63] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[73] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[83] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[93] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[103] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[114] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[124] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[PLImport2_tri] ON [dbo].[PLImport2]
WITH EXECUTE AS 'dbo'
INSTEAD OF INSERT
AS
BEGIN
--Declare some variables that we will use in the cursor
Declare @.profitcenter varchar(50), @.Acc varchar(75), @.pf varchar(50),
@.oldAcc varchar(75), @.RowID int, @.cnt int
set @.cnt = 1
--Declare the cursor and fetch records from inerted table
DECLARE OneChange CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC
FOR SELECT RowID, PF, Acc FROM inserted
OPEN OneChange
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
--loop thour the curso performing task on each record
WHILE (@.@.FETCH_STATUS=0)
BEGIN
-- If there are no records inserted already, insert at least one
if (select count(*) from plimport2) = 0
begin
insert into plimport2 select top 1 * from inserted
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
end
--now select the last inserted record, by getting the Max of field F!
select @.pf = PF, @.oldAcc = Acc from plimport2 where RowID = (select
max(RowID) from plimport2)
--compare the results if null, replace with old value
if @.pf is null
begin
print 'PF is null'
end
else
begin
insert into plimport2 select * from inserted i where i.RowID=@.RowID
if @.profitcenter is null
set @.profitcenter = @.pf
else
begin
set @.pf = @.profitcenter
end
update plimport2 set PF=@.profitcenter where PF is null
if @.Acc is null
set @.Acc = @.oldAcc
else
begin
set @.oldAcc = @.Acc
end
update plimport2 set Acc=@.Acc where Acc is null
print 'Pf - '+@.pf+' Profic Center = '+@.profitcenter+' Dep = '+@.Acc+'
Status = '+str(@.@.FETCH_STATUS)
end
set @.cnt = @.cnt+1
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
print 'next Status = '+str(@.@.FETCH_STATUS)
end
ENDRobert,
I would consider another alternative:
1. create a staging table and populate it from Excel:
select * into staging from OPENROWSET
2. issue one set-based UPDATE followed by one set-based insert. For
instance, try this:
[url]http://sql-server-tips.blogspot.com/2006/06/mimicking-merge-statement-in-sql.html[
/url]|||Hi There,
You should give sample data ,DDL and required result to work on.
I think you may try this as Alexander suggested (I am giving SQL 2000
version)
Update those rows taht exists
Update tableX set amt=amt+(Select a from tableY where
tablex.PKID=tabley.PKID) ,col2=xy,
col3=xz
where exists(Select a from tableY where tablex.PKID=tabley.PKID)
insert those rows that doesnot exists
insert into tableX (col1,col2,col3) Select * from tabley where Not
exists(Select a from tableY where tablex.PKID=tabley.PKID)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Alexander Kuznetsov wrote:
> Robert,
> I would consider another alternative:
> 1. create a staging table and populate it from Excel:
> select * into staging from OPENROWSET
> 2. issue one set-based UPDATE followed by one set-based insert. For
> instance, try this:
> http://sql-server-tips.blogspot.com...ent-in-sql.html[
/color]