Solved

Query taked seconds - Add insert and performance drops ten fold

Posted on 2011-03-16
18
330 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:dkochenour
  • 8
  • 4
  • 3
  • +1
18 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 35148902
inserting is generally slow compared to selecting
0
 

Author Comment

by:dkochenour
ID: 35148945
That is obvious - But that is not the problem
0
 
LVL 18

Expert Comment

by:deighton
ID: 35148980
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 4

Expert Comment

by:qasim_md
ID: 35150579
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
 

Author Comment

by:dkochenour
ID: 35150596
If you look at the query provided it is only inserting into table variables
0
 
LVL 4

Expert Comment

by:qasim_md
ID: 35150645
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
 

Author Comment

by:dkochenour
ID: 35150660
Already tried
0
 
LVL 18

Expert Comment

by:deighton
ID: 35154923
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
 
LVL 4

Expert Comment

by:qasim_md
ID: 35155176
I still suggest to do the following:

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

Expert Comment

by:Mark Wills
ID: 35180389
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
 

Author Comment

by:dkochenour
ID: 35180410
Thank - I will try and get back to you!
0
 

Author Comment

by:dkochenour
ID: 35180986
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 35183159
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
 

Author Comment

by:dkochenour
ID: 35183852
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35185827
Yep, aware of that - was looking for the timing displays.
0
 

Author Comment

by:dkochenour
ID: 35196870
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35229036
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
 

Author Closing Comment

by:dkochenour
ID: 35391806
Awaesone answer!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question