asked on

Query taked seconds - Add insert and performance drops ten fold

Below is the script that we have problem with ¿ as soon we uncomment insert
Attached is the file with just 5 product nodes inside, in order to reproduce the issue (and resolve it ¿) you need to do cut and paste the same nodes and beef up the file up to 200 nodes or more.

After shredding initial XML with <products> <product> </product> <product> </product> .. <product> </product> </products> into one < product> node per record, we still need to have parent <products> tag in each of the <product> record

DECLARE @VENDOR_PRICE_FILE_IMPORT_BATCH_ID INT = 1
DECLARE @prodXML XML

DECLARE @PRODUCTS TABLE
      (
       [VENDOR_PRICE_FILE_VENDOR_PART_DATA] [xml] NOT NULL,
       [ROWNUMBER] [bigint]
      )


SELECT @prodXML = CAST(X AS XML)
FROM
(
-- REMOVE NAMESPACE ...
SELECT 
REPLACE(CAST(Y AS VARCHAR(MAX)),'xmlns="http://www.xyz.com/pf"','') AS X
-- READ XML FROM FILE
FROM OPENROWSET(     
                BULK 'C:\VENDOR.XML',     
                SINGLE_BLOB
               ) AS T(Y)
) Z

--INSERT INTO @PRODUCTS
SELECT VENDOR_PRICE_FILE_VENDOR_PART_DATA
      ,ROWNUMBER
FROM
   (       
    SELECT ed.product AS VENDOR_PRICE_FILE_VENDOR_PART_DATA
          ,ROW_NUMBER() OVER (ORDER BY @VENDOR_PRICE_FILE_IMPORT_BATCH_ID) AS ROWNUMBER
    FROM  
       (
-- Place Back NAMESPACES, Replace nil Value NAMESPACE in the Body of XML and Place Back "products" tag       
        SELECT CAST(N'<products xmlns="http://www.xyz.com/pf" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' + 
               REPLACE(CAST(result AS NVARCHAR(MAX)),
                    N' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"',
                    N'') + 
                    N'</products>' AS XML) AS product
        FROM
           (
            SELECT products.product.query(N'.') AS result
            FROM @prodXML.nodes(N'/products/*') AS products(product)
           ) p
       ) ed
   ) batch


SELECT * FROM @PRODUCTS

Open in new window

vendor.xml

deighton

inserting is generally slow compared to selecting

dkochenour

ASKER

That is obvious - But that is not the problem

deighton

why is it not the problem? If you comment the insert back in, then you have to insert those records, and then also you have to query them back again, so it will take longer.

qasim_md

Check the db growth settings of your database and increase the size to grow to 10% or higher. This might help... if it did let us know.

dkochenour

ASKER

If you look at the query provided it is only inserting into table variables

qasim_md

yes I noticed it. The table vaiables will store the data in the session or DB the query is run.

another alternate solution I see is if you are storing a smaller dataset..then use #temp table instead of table variable and see if it helps...

dkochenour

ASKER

Already tried

deighton

If it is a large import, then a table variable still can get written to disk.

how big is this import file? What are the actual times involved?

qasim_md

I still suggest to do the following:

1) shrink the database.
2) check the DB growth settings and set it to maxinum.

Mark Wills

Ummm...

With all due respect to the other experts, dont do anything with the database. That is unlikely to be the problem.

Extracting the XML nodes for Insert can be a bit of a problem, and is often slower than the select query.

I have to duck out for a few hours, but will be back later to load and test in my own database, then can post some more suggestions.

In the meantime, can you please try :

DECLARE @prodXML XML

DECLARE @PRODUCTS TABLE
      (
       [VENDOR_PRICE_FILE_VENDOR_PART_DATA] [xml] NOT NULL,
       [ROWNUMBER] [bigint] identity
      )

SELECT @prodXML = 
   (SELECT  cast(REPLACE(CAST(Y AS VARCHAR(MAX)),'xmlns="http://www.xyz.com/pf"','') as XML)
    FROM OPENROWSET( BULK 'C:\VENDOR.XML',SINGLE_BLOB ) AS T(Y)) 

INSERT INTO @products
SELECT products.product.query(N'.') AS result
FROM @prodXML.nodes(N'/products/*') AS products(product)

SELECT * FROM @PRODUCTS

Open in new window

And let me know the results ?

I dont think you need to do all that extra work everytime, and that could well be part of the difference.

dkochenour

ASKER

Thank - I will try and get back to you!

dkochenour

ASKER

It is still taking over 20 minutes - and the manipulation needs to be there. The header needs to be at the top not with the XML. Sorry :-(

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dkochenour

ASKER

THis is what we got from your query 0- Below itr is what we need
<product>
<base>
<Element1 numitems="1">
<item index="1">38139</item>
</Element1>
<Element2 numitems="1">
<item index="1">MKR</item>
</Element2>
<Element3 numitems="1">
<item index="1">XXX356A</item>
</Element3>
<Element4 numitems="2">
<item index="1">Comm1</item>
<item index="2">Comm2</item>
</Element4>
<Element5 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element6 numitems="1">
<item index="1">DBM</item>
</Element6>
<Element7 numitems="1">
<item index="1">MKR</item>
</Element7>
<Element8 numitems="1">
<item index="1">PRO</item>
</Element8>
<Element9 numitems="1">
<item index="1">245</item>
</Element9>
<Element10 numitems="1">
<item index="1">299</item>
</Element10>
<Element11 numitems="2">
<item index="1">OS1</item>
<item index="2">ISO</item>
</Element11>
<Element12 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element13 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element14 numitems="1">
<item index="1">11</item>
</Element14>
<Element15 numitems="1">
<item index="1">Pro</item>
</Element15>
<Element16 numitems="1">
<item index="1">Physics</item>
</Element16>
<Element17 numitems="1">
<item index="1">xyz</item>
</Element17>
<Element18 numitems="1">
<item index="1">License</item>
</Element18>
<Element19 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element20 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element21 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element22 numitems="1">
<item index="1">Retail</item>
</Element22>
<Element23 numitems="1">
<item index="1">English</item>
</Element23>
<Element24 numitems="1">
<item index="1">Applications</item>
</Element24>
<Element25 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element26 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element27 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element28 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element29 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element30 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element31 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element32 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element33 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element34 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element35 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element36 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element37 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element38 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element39 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
</base>
<Extended>
<Element1 numitems="1">
<item index="1">1</item>
</Element1>
<Element2 numitems="1">
<item index="1">1</item>
</Element2>
<Element3 numitems="1">
<item index="1">0</item>
</Element3>
<Element4 numitems="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<Element5 numitems="1">
<item index="1">Program</item>
</Element5>
</Extended>
</product>

And that what we need to be produced:

<products xmlns="http://www.xyz.com/pf" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<product>
<base>
<Element1 numitems="1">
<item index="1">38139</item>
</Element1>
<Element2 numitems="1">
<item index="1">MKR</item>
</Element2>
<Element3 numitems="1">
<item index="1">XXX356A</item>
</Element3>
<Element4 numitems="2">
<item index="1">Comm1</item>
<item index="2">Comm2</item>
</Element4>
<Element5 numitems="0" xsi:nil="true" />
<Element6 numitems="1">
<item index="1">DBM</item>
</Element6>
<Element7 numitems="1">
<item index="1">MKR</item>
</Element7>
<Element8 numitems="1">
<item index="1">PRO</item>
</Element8>
<Element9 numitems="1">
<item index="1">245</item>
</Element9>
<Element10 numitems="1">
<item index="1">299</item>
</Element10>
<Element11 numitems="2">
<item index="1">OS1</item>
<item index="2">ISO</item>
</Element11>
<Element12 numitems="0" xsi:nil="true" />
<Element13 numitems="0" xsi:nil="true" />
<Element14 numitems="1">
<item index="1">11</item>
</Element14>
<Element15 numitems="1">
<item index="1">Pro</item>
</Element15>
<Element16 numitems="1">
<item index="1">Physics</item>
</Element16>
<Element17 numitems="1">
<item index="1">xyz</item>
</Element17>
<Element18 numitems="1">
<item index="1">License</item>
</Element18>
<Element19 numitems="0" xsi:nil="true" />
<Element20 numitems="0" xsi:nil="true" />
<Element21 numitems="0" xsi:nil="true" />
<Element22 numitems="1">
<item index="1">Retail</item>
</Element22>
<Element23 numitems="1">
<item index="1">English</item>
</Element23>
<Element24 numitems="1">
<item index="1">Applications</item>
</Element24>
<Element25 numitems="0" xsi:nil="true" />
<Element26 numitems="0" xsi:nil="true" />
<Element27 numitems="0" xsi:nil="true" />
<Element28 numitems="0" xsi:nil="true" />
<Element29 numitems="0" xsi:nil="true" />
<Element30 numitems="0" xsi:nil="true" />
<Element31 numitems="0" xsi:nil="true" />
<Element32 numitems="0" xsi:nil="true" />
<Element33 numitems="0" xsi:nil="true" />
<Element34 numitems="0" xsi:nil="true" />
<Element35 numitems="0" xsi:nil="true" />
<Element36 numitems="0" xsi:nil="true" />
<Element37 numitems="0" xsi:nil="true" />
<Element38 numitems="0" xsi:nil="true" />
<Element39 numitems="0" xsi:nil="true" />
</base>
<Extended>
<Element1 numitems="1">
<item index="1">1</item>
</Element1>
<Element2 numitems="1">
<item index="1">1</item>
</Element2>
<Element3 numitems="1">
<item index="1">0</item>
</Element3>
<Element4 numitems="0" xsi:nil="true" />
<Element5 numitems="1">
<item index="1">Program</item>
</Element5>
</Extended>
</product>
</products>

Mark Wills

Yep, aware of that - was looking for the timing displays.

dkochenour

ASKER

Here is what he says - Sorry I am on call right now too

It took 8 seconds on the file with 3,666 records, but this is not what we can use
We already have table with data in place and we need to insert additional records into already existing table with data!
So, instead of:

SELECT identity(bigint,1,1) as ROWNUMBER, products.product.query(N'.') as VENDOR_PRICE_FILE_VENDOR_PART_DATA
INTO #products
FROM @prodXML.nodes(N'/products/*') AS products(product)

We need to use something like this (need to adjust identity column):

INSERT INTO #products
SELECT identity(bigint,1,1) as ROWNUMBER, products.product.query(N'.') as VENDOR_PRICE_FILE_VENDOR_PART_DATA
FROM @prodXML.nodes(N'/products/*') AS products(product)

And this one is taking long time. I killed it after 15 minutes. It was a big surprise for me that there is such difference at first, but then I thought about it for some time and now it sounds logical to me (it is just landing results of select to temp table, but we know that select is fast!)

Mark Wills

The select into #products was meant to be an interim step to first unpack the XML and then once unpacked, do the insert into the real tables from the temp table. Thats why I was hoping to get the timings from the display to see if that strategy was reasonable.

Apart from the format (which we can fix) with the <products xmlns="http://www.xyz.com/pf" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> (which also causes the inclusion of the namespace for lines with xsi:nil="true")

Where are we up to with this question ?

dkochenour

ASKER

Awaesone answer!