June 10, 2008 at 2:03 pm
hi guys i have an xml file with 20k rows i need to either insert or update in a table. My table right now has 1 k rows, looks like this:
DisID Integer (identity column)
DistributorID numeric(10,0),
ManufacturerNUM numeric(10,0),
LocationNUM nvarchar(50),
DistributorDesc nvarchar(100)
the only clustered id is the LocationNUM, a unique value.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Distributor_InsertUpdateLocations]
(
@DistributorID numeric(10,0),
@ManufacturerNUM numeric(10,0),
@LocationNUM nvarchar(50),
@DistributorDesc nvarchar(100)
)
AS
BEGIN
DECLARE @LOCATIONID AS numeric(10,0)
SELECT
@LOCATIONID = Location_ID
FROM
[dbo].Distributor_Locations
WHERE
Location_NUM = @LocationNUM --clustered index
AND
Manufacturer_NUM = @ManufacturerNUM
AND
Distributor_ID = @DistributorID
IF @LOCATIONID > 0
BEGIN
UPDATE [dbo].Distributor_Locations
SET
Distributor_ID = @DistributorID,
Manufacturer_NUM = @ManufacturerNUM,
Last_Update_DT = GETDATE(),
Location_NUM = @LocationNUM,
Distributor_Desc = @DistributorDesc,
WHERE Location_ID = @LOCATIONID
END
ELSE
BEGIN
INSERT INTO [dbo].Distributor_Locations
(
Distributor_ID,
Manufacturer_NUM,
Create_DT,
Location_NUM,
Distributor_Desc
)
VALUES
(
@DistributorID,
@ManufacturerNUM,
GETDATE(),
@LocationNUM,
@DistributorDesc
)
END
END
But my stored procedure is taking around 3 hours to execute. How can i optimize this?
June 10, 2008 at 2:17 pm
Can you extract the XML file into a flat table? A temp table or a real table, either one?
It looks to me like this proc is stepping through the XML row-by-row and running one update/insert at a time.
If you can either query the data into a flat format, or put it in a table, you can do the whole thing in two steps, one update, one insert. Will probably only take a few seconds (at most) with only 20k rows.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2008 at 2:35 pm
great idea, i will try first creating a temp table before the transactions, if that doesn't help then i am going to create a table in my db.
thanks a lot!, last question is it good i created the clustered index in that field? is it helping me?
June 11, 2008 at 1:19 am
I'm a little confused... you say "the only clustered id is the LocationNUM, a unique value". What do you mean by that? There is a clustered unique index on this single column? Then it wouldn't make sense... because you check on ather columns as well and as far as I can see, if LocationNUM is the same but DistributorID is different, you want to insert this row.
Could you please post the definition (CREATE INDEX statement) of the index you are asking about?
Actually, with the type of query you are running I think that it doesn't make any significant difference whether the index is clustered or not. Clustered index generally helps best where you need to select ranges of values... for example "... WHERE time_started > @time", or "SELECT TOP 100 ... ORDER BY indexed_column".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply