Too slow update (SQL Server 2000)

[yurich]

I have a real-time application that updates 1 table (T) in DB every 1 sec.
T contains 83 fields (mostly floats and bigints) and about 18000 records.
f0 is a primary key (VARCHAR).
Every 1 sec I have to update 2000 - 4000 records. Stored procedure that performs these updates gets data as XML.
Code:
================================================== ======
CREATE PROCEDURE MySP

@XML text

AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @XMLDoc int

BEGIN TRANSACTION

EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @XML

UPDATE MyTable

SET f1 = t.f1,
f2 = t.f2,
...........
f83 = t.f83


FROM CME_InstrumentsData, (SELECT *
FROM OPENXML (@XMLDoc, '/xml/s',1)
WITH CME_InstrumentsData) t

WHERE MyTable.f0 = t.f0

IF @@ERROR != 0 GOTO ErrorHandler

EXEC sp_xml_removedocument @XMLDoc


COMMIT TRANSACTION
SELECT 0
RETURN

ErrorHandler:
ROLLBACK TRANSACTION
SELECT -1
RETURN
GO
================================================== ======

It works, but update of 3000 records takes about 6 seconds and I have to do it for 1 second !

Is it possible to optimize this code ?
Is it good decision to pass data data as XML (data may be formatted to any standard format) ?

Thanks a lot,

[skhanal]

For such a requirement, don't parse the xml in stored procedure, do it through your middle tier code and run plan update in SQL Server.

If you time each statement in your sp, I think sp_xml_preparedocument and OPENXML are chewing up most of the resources.

[yurich]

Could you please, explain me with more details about update plan ?

[skhanal]

Do you have any front end for this?. If you have a .Net or like front end, you can parse the XML in the those language and put the parsed data in table variable, then use that to update the table.

I take the XML implementation in SQL Server as can do feature added to say that it handles XML, and since it is an extended procedure it is slow. So it is best to avoid for high performance requirements.

[yurich]

Do you have any front end for this?. If you have a .Net or like front end, you can parse the XML in the those language and put the parsed data in table variable, then use that to update the table.

I take the XML implementation in SQL Server as can do feature added to say that it handles XML, and since it is an extended procedure it is slow. So it is best to avoid for high performance requirements.

OK, I can parse this XML in my C# code, but how to pass parsed text to stored procedure that updating my table ?
How to put this text into the table?

[skhanal]

Rewrite the sp to acccept column values and pass column values for the update.

[yurich]

Rewrite the sp to acccept column values and pass column values for the update.

Sorry, I did not understand you...

Could you please explain me what do you mean ?

[skhanal]

CREATE PROCEDURE MySP

@f0 datatypef0, ..., @f83 datatype f3

AS

update mytable
set f1 = @f1 , ... f83 = @f83
where f0 = @f0


From your c# code, pass all 83 parameters.

[yurich]

CREATE PROCEDURE MySP

@f0 datatypef0, ..., @f83 datatype f3

AS

update mytable
set f1 = @f1 , ... f83 = @f83
where f0 = @f0


From your c# code, pass all 83 parameters.

In this case, every time you update only 1 record in mytable...
So, if I need to update 3000 records, I'll perform 3000 updates...