August 22, 2007 at 4:53 pm
Heh, you got me, that would be easier...
September 29, 2008 at 5:04 am
I am using approach mentioned in post to do a master detail insert with following query,
INSERT INTO LISTDTL(ListDtl_ListHdrID,ListDtl_ProductID,ListDtl_ISBN_Number) SELECT @ListHdr_ID,P.PROD_PRODUCT_ID,ISBN.ISBNNumber FROM PRODUCT P INNER JOIN (SELECT ParamValues.ID.value('.','VARCHAR(20)') as ISBNNumber FROM @ValidISBN.nodes('/VALIDISBN/value') as ParamValues(ID) ) ISBN ON P.PROD_NUMBER=ISBN.ISBNNumber
where,
@ValidISBN='VALIDISBN Value 89869855898955 /value /VALIDISBN'
(This XML string. I could not write in proper XML as it was showing blank if it is written in proper XML format. This string is created in front end of application and is set to sql command parameter when using ExecuteNonQuery)
If @ValidISBN contains 7000 records, my insert query takes around 1 min or more.
How can I improve performance of my query.
September 29, 2008 at 9:01 am
Hi,
Well, XML is not thought for performance. If you are processing 7000+ rows and you want real performance, you should use CSV files or similar files with much less overhead. You can even gain performance by having a fast XML -> CSV parser first in your chain.
Otherwise, try other T-SQL XML parsing approaches (like with OPENXML or similar) to find out which one is faster.
September 29, 2008 at 9:09 am
Agreed with Hans. On large XML documents, OPENXML is found to be performing better than XQuery methods.
.
October 1, 2008 at 3:00 am
Thanks all for your reply. I reverted to CSV string and used a function to return table from it. This table is used in my join query. I was amazed to see CSV query completing insert in just 14 sec in comparison to 2:05 sec using XML string. Still not able to get why XML query is taking such a long time.
October 1, 2008 at 4:14 am
XML data is much more descriptive than CSV. XML is designed with a lot of other goals too. I agree with you that in this case a CSV approach may give you better performance. When transfering large volumns of data, XML may not be the best choice.
.
October 1, 2008 at 5:35 am
The only thing I might be able to add to it is to see if using the new "APPLY" operator in your join to your Table function will speed things up even more. I have had some cases where this worked much better, again, it all depends.
Your Xml parsing might be sped up by making a Schema for it and then applying that Schema to the incoming Xml. I've even had Procs that could accept multiple versions and different Schemas.
Ex.
CREATE PROC ParseSomething
(
@data Xml
)
AS
Declare @Data_v1 Xml(MySchema_v1)
TRY
Set @Data_v1 = @data
-- At this point, if the Xml does not conform to the Schema,
-- there will be an error or the @Data_v1 will be Null
-- TODO: Do your parsing off the @Data_v1 NOT your original @data
-- this way the Xml parser has to do less thinking now that there is a valid schema
END TRY
CATCH
SELECT
ERROR_NUMBER()
END CATCH
June 3, 2010 at 4:40 am
Jacob,
Thanks for the article - very helpful. How would you extend this so that you could extract header and item data for more than one sales order? For example, say that your source XML was <salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
<lineItems>
<item itemNumber="A001" qty="10" rate="10.5" />
<item itemNumber="A002" qty="20" rate="11" />
<item itemNumber="A003" qty="30" rate="13" />
</lineItems>
</salesOrder>
<salesOrder orderNumber="100002" customerNumber="JAC002" orderDate="01-01-2008">
<lineItems>
<item itemNumber="A004" qty="60" rate="15.5" />
<item itemNumber="A005" qty="70" rate="21" />
<item itemNumber="A006" qty="80" rate="33" />
</lineItems>
</salesOrder>
Using this code...SELECT
y.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,
x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber,
x.item.value('@qty[1]','int') AS Qty,
x.item.value('@rate[1]','float') AS Rate
FROM @OrderInfo.nodes('//item') AS x(item)
CROSS APPLY @OrderInfo.nodes('//salesOrder') AS y(header)
ORDER BY OrderNumber
... I get a listing of both order numbers but with ALL the ItemNumbers rather than just the ItemNumbers that belong to each OrderNumber
OrderNumberItemNumberQtyRate
100001 A001 1010.5
100001 A002 2011
100001 A003 3013
100001 A004 6015.5
100001 A005 7021
100001 A006 8033
100002 A001 1010.5
100002 A002 2011
100002 A003 3013
100002 A004 6015.5
100002 A005 7021
100002 A006 8033
The result should be just 6 rows, 3 for each order number.
Lempster
June 3, 2010 at 5:08 am
How about this?
DECLARE @x XML
SELECT @x = '
<salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
<lineItems>
<item itemNumber="A001" qty="10" rate="10.5" />
<item itemNumber="A002" qty="20" rate="11" />
<item itemNumber="A003" qty="30" rate="13" />
</lineItems>
</salesOrder>
<salesOrder orderNumber="100002" customerNumber="JAC002" orderDate="01-01-2008">
<lineItems>
<item itemNumber="A004" qty="60" rate="15.5" />
<item itemNumber="A005" qty="70" rate="21" />
<item itemNumber="A006" qty="80" rate="33" />
</lineItems>
</salesOrder>'
SELECT
y.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,
x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber,
x.item.value('@qty[1]','int') AS Qty,
x.item.value('@rate[1]','float') AS Rate
FROM @x.nodes('/salesOrder') AS y(header)
CROSS APPLY header.nodes('lineItems/item') AS x(item)
ORDER BY OrderNumber
/*
OrderNumber ItemNumber Qty Rate
-------------------- ---------- ----------- ----------------------
100001 A001 10 10.5
100001 A002 20 11
100001 A003 30 13
100002 A004 60 15.5
100002 A005 70 21
100002 A006 80 33
*/
.
June 3, 2010 at 5:22 am
Brilliant!! Thanks very much 🙂
I find this XML stuff a real struggle.
Lempster
June 3, 2010 at 5:33 am
Glad to know it helped. You are right, most people find it bit difficult when they start working with XML..but that will go away soon.
May be the XQuery workshops can help: http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx
.
December 23, 2011 at 2:01 am
Whooaa!! great improvement using XQuery. Will follow the XQuery workbench proposed by you on beyondrelational.com website to get a better understanding on how it works. I get confused using the .nodes, header, etc.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply