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
dkochenourAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Wow, 20 minutes ?

How many rows in your xml data set ? I played with over 1000 and it was seconds.

Understand about the products tag header. Can make that repeat easy enough. But first a few little timings if thats OK :

select 'starting : '+convert(varchar,getdate(),120)
DECLARE @prodXML XML

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

select 'loaded prodxml : '+convert(varchar,getdate(),120)

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

select 'select prodxml : '+convert(varchar,getdate(),120)

if object_id('tempdb..#products','u') is not null drop table #products

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)

select 'insert products : '+convert(varchar,getdate(),120)

SELECT * FROM #PRODUCTS

select 'select products (end) : '+convert(varchar,getdate(),120)

Open in new window

0
 
deightonprogCommented:
inserting is generally slow compared to selecting
0
 
dkochenourAuthor Commented:
That is obvious - But that is not the problem
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
deightonprogCommented:
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.
0
 
qasim_mdCommented:
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.
0
 
dkochenourAuthor Commented:
If you look at the query provided it is only inserting into table variables
0
 
qasim_mdCommented:
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...
0
 
dkochenourAuthor Commented:
Already tried
0
 
deightonprogCommented:
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?
0
 
qasim_mdCommented:
I still suggest to do the following:

1) shrink the database.
2) check the DB growth settings and set it to maxinum.
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
dkochenourAuthor Commented:
Thank - I will try and get back to you!
0
 
dkochenourAuthor Commented:
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 :-(
0
 
dkochenourAuthor Commented:
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>
0
 
Mark WillsTopic AdvisorCommented:
Yep, aware of that - was looking for the timing displays.
0
 
dkochenourAuthor Commented:
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!)

0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
dkochenourAuthor Commented:
Awaesone answer!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.