OPENXML - T-SQL - Importing....how long?

Question

OPENXML - T-SQL - Importing....how long?

geoff hopkins

Ten Centuries

Points: 1147
More actions
June 7, 2006 at 7:17 am

#172893

I know I probably will get a lot of flack for this post but here it goes.....
i have a xml file (5.2MB) which I am importing into a database using OPENXML and T-SQL which maps to two tables, the XML should equate to around 28,000 records and needs to check if it exists already and then update/insert records..... my question (the one I am expecting flack for) ....is how long should this be taking as it seems to be taking around 20min and I am not sure if this is a reasonable processing time or not?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Marshall Smith SSCrazy Points: 2041 More actions · Answer 1

I know that mine executes in far less than that, using an XML with approximately the same size.

Are you doing the checking against the XML, and then also doing the update/insert from the XML? I highly recommend storing the XML in either a temp table, or, even better if you can and this is something you'll do repetitively, a persistent holding table.

Look over your queries for areas where you can optimize. Are you making use of existing indexes? Are existing indexes slowing your inserts and/or updates?

geoff hopkins Ten Centuries Points: 1147 More actions · Answer 2

thanks for the info....

I've simplified my sproc further to work out why it is taking soooo long but it is still really slow (~25min)

Below is the sproc simplified and xml extract for the file if you can see any glaring issues why it is so slow please let me know.... there may look like unecessary fields but this is because of cutting the code back to debug performance issues

ALTER PROCEDURE usp_Update_Instance

(@XML_DOC TEXT)

AS

--

DECLARE @INT_DOC INT

--

CREATE TABLE #TBL_TEMP(

REF INT,

DOC_ID VARCHAR(200),

DOC_NAME VARCHAR(200),

TYPE_ID BIGINT,

DOC VARCHAR(200),

SEC VARCHAR(200),

DOC_TYPE BIGINT)

--

-- CREATE INTERNAL REPRESENTATION OF THE DOCUMENT

EXEC sp_xml_preparedocument @INT_DOC OUTPUT, @XML_DOC

-- INSERT DATA INTO TEMP VARIABLE BETTER PERFORMANCE

INSERT #TBL_TEMP(

REF,

DOC_NAME,

DOC_ID,

TYPE_ID,

DOC,

SEC,

DOC_TYPE)

SELECT * FROM OPENXML(@INT_DOC, '//instance',3)

WITH (

REF INT '@mp:id',

DOC_NAME VARCHAR(200) '../@doc_name',

DOC_ID VARCHAR(200) '../@doc_ref',

TYPE_ID BIGINT '@type_id',

DOC VARCHAR(200) '@doc',

SEC VARCHAR(200) '@sec',

DOC_TYPE BIGINT '../@doc_type'

)

--

EXEC sp_xml_removedocument @INT_DOC

--

INSERT INTO TBL_INSTANCES(DOC_ID,DOC_NAME,TYPE_ID,DOC,SEC,DOC_TYPE)

SELECT DOC_ID,DOC_NAME,TYPE_ID,DOC,SEC,DOC_TYPE FROM #TBL_TEMP

--

XML Extract --- true file 86,000 lines long = 29000 records

<?xml version='1.0'?>

<root>

</instance>

</instance>

</instance>

</instance>

</instance>

</instance>

</instance>

</instance>

</instance>

</instance>

JAIKANTH NARASIMHAN Grasshopper Points: 20 More actions · Answer 3

The time taken for insertion/update also depends on the size of the table, how is it indexed etc.

If you are interested, you can also try the SQL XML 3.0 bulk copy. It should take less than a minute to upload 29000 records !!

-- Jai