Solved

Query taked seconds - Add insert and performance drops ten fold

Posted on 2011-03-16
18
317 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now