Advise on working with XML

Question

Post reply

Advise on working with XML

jfriedl

SSC Enthusiast

Points: 120
More actions
March 4, 2013 at 3:43 pm

#290483

*advice
I have a big nasty query I have been assigned to look at that basically is pulling varchar data into a temp xml table as such the query performance is terrible at best.
--DECLARE @XmlFilter xml
DECLARE @WhereClause VARCHAR(MAX)
DECLARE @CartPrice VARCHAR(2000)
DECLARE @ExpandedCartPrice VARCHAR(2000)
DECLARE @Operator VARCHAR(10)
DECLARE @VariableID VARCHAR(10)
DECLARE @Variance VARCHAR(10)
DECLARE @Unit VARCHAR(10)
DECLARE @SqlVar VARCHAR(50) = 'ProdDesc.Map'
DECLARE @ElementCount INT
DECLARE @StateParm VARCHAR(2000)
DECLARE @UseCaEvent bit = 0
CREATE TABLE #MyParameters (TableAlias VARCHAR(200) , Parm VARCHAR(200), ParmValue VARCHAR(MAX)
, ParmFilter VARCHAR(MAX), ParmWhere VARCHAR(MAX))
DECLARE @PriceElements AS TABLE (ID INT IDENTITY(1,1),VALUE VARCHAR(50))
--******* Build Dynamic WHERE Clause *******
BEGIN
--******* Shread The XML *******
BEGIN
--XML Root Attributes
SELECT ParamValues.Parm.value('@Type[1]', 'VARCHAR(200)') QueryType
, ParamValues.Parm.value('@UserID[1]', 'VARCHAR(200)') UserID
INTO #FilterAttributes
FROM @XmlFilter.nodes('/Filter') AS ParamValues(Parm)
--Collect Query Parameters
/*NOTES:
Rating is not Used right now
*/
INSERT #MyParameters (TableAlias,Parm, ParmValue, ParmFilter )
--ProductSeller Parameters
SELECT 'ProdEx','ISBN',ParamValues.Parm.value('ISBN[1]', 'VARCHAR(Max)'),'ProdEx.ISBN = ''[VALUE]'''FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'ProdEx','MfgSKU',ParamValues.Parm.value('MfgSKU[1]', 'VARCHAR(Max)'),'ProdEx.MfgSKU = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'ProdEx','UPC',ParamValues.Parm.value('UPC[1]', 'VARCHAR(Max)'),'ProdEx.UPC = ''[VALUE]'''FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'ProdEx','VendorID',ParamValues.Parm.value('VendorID[1]', 'VARCHAR(Max)'),'ProdEx.VendorID = [VALUE]'FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'ProdEx','ProductID',ParamValues.Parm.value('ProductID[1]', 'VARCHAR(Max)'),'ProdEx.ProductID in ([VALUE])'FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'ProdEx','CVSKU',ParamValues.Parm.value('CVSKU[1]', 'VARCHAR(Max)'),'ProdEx.CVSKU like ''%[VALUE]%'''FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'ProdDesc','ProductName',ParamValues.Parm.value('ProductName[1]', 'VARCHAR(Max)'),'ProdDesc.ProductName like ''%[VALUE]%'''FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'comp','CompetitorName',ParamValues.Parm.value('CompetitorName[1]', 'VARCHAR(Max)'),'comp.CompetitorName like ''%[VALUE]%'''FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'comp','CompetitorID', ParamValues.Parm.value('CompetitorID[1]', 'VARCHAR(Max)'),'comp.CompetitorID in ([VALUE])'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'compMar','SellerState',ParamValues.Parm.value('SellerState[1]', 'VARCHAR(Max)'),'compMar.SellerState in ([VALUE])'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'compMar','Rating',ParamValues.Parm.value('Rating[1]', 'VARCHAR(Max)'),'compMar.Rating >= [VALUE]'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'compMar','MarketID',ParamValues.Parm.value('MarketID[1]', 'VARCHAR(Max)'),'compMar.MarketID in ([VALUE])'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
--dg: commenting this out, it does not seem to be used and if it comes through it will break
SELECT 'compListing','Reviews',ParamValues.Parm.value('Reviews[1]', 'VARCHAR(Max)'),'compListing.ReviewCnt >= [VALUE]'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'compListing','SuspectStatus',ParamValues.Parm.value('SuspectStatus[1]', 'VARCHAR(Max)'),'compListing.SuspectStatusID = [VALUE]'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'compMP','IgnoreInCart',ParamValues.Parm.value('IgnoreInCart[1]', 'VARCHAR(Max)'),'compMP.InCart = 0'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'CompMp','CartPrice',ParamValues.Parm.value('CartPrice[1]', 'VARCHAR(Max)'),'[VALUE] and compMP.Price > 0'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'CompMp','Condition',ParamValues.Parm.value('Condition[1]', 'VARCHAR(Max)'),'CompMp.ConditionId IN ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION
SELECT 'CompMp','PriceRankUpper',ParamValues.Parm.value('PriceRankUpper[1]', 'VARCHAR(Max)'),'compMP.PricePosition <= [VALUE]'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'CompMp','PriceRankLower',ParamValues.Parm.value('PriceRankLower[1]', 'VARCHAR(Max)'),'compMP.PricePosition >= [VALUE]'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'CompMp','SellerDT',ParamValues.Parm.value('SellerDT[1]', 'VARCHAR(Max)'),'compMP.DT between ''[VALUE]'''FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'CompMp','PriceLocationID',ParamValues.Parm.value('PriceLocationID[1]', 'VARCHAR(Max)'),'compMP.PriceLocationID in ([VALUE])'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'CompMp','PriceAttributeID',ParamValues.Parm.value('PriceAttributeID[1]', 'VARCHAR(Max)'),'compMP.PriceAttributeID in ([VALUE])'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION
SELECT 'CompMp', 'Active',ParamValues.Parm.value('Active[1]', 'VARCHAR(Max)'),'compMP.LatestData = 1'FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm)
Here is my query plan for the above statement. any suggestions would be much appreciated!
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1.1" Build="10.0.5500.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="INSERT #MyParameters (TableAlias,Parm, ParmValue, ParmFilter ) --ProductSeller Parameters SELECT 'ProdEx','ISBN', ParamValues.Parm.value('ISBN[1]', 'VARCHAR(Max)'), 'ProdEx.ISBN = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','MfgSKU', ParamValues.Parm.value('MfgSKU[1]', 'VARCHAR(Max)'), 'ProdEx.MfgSKU = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','UPC', ParamValues.Parm.value('UPC[1]', 'VARCHAR(Max)'), 'ProdEx.UPC = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','VendorID', ParamValues.Parm.value('VendorID[1]', 'VARCHAR(Max)'), 'ProdEx.VendorID = [VALUE]' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','ProductID', ParamValues.Parm.value('ProductID[1]', 'VARCHAR(Max)'), 'ProdEx.ProductID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','CVSKU', ParamValues.Parm.value('CVSKU[1]', 'VARCHAR(Max)'), 'ProdEx.CVSKU like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdDesc','ProductName',ParamValues.Parm.value('ProductName[1]', 'VARCHAR(Max)'), 'ProdDesc.ProductName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorName',ParamValues.Parm.value('CompetitorName[1]', 'VARCHAR(Max)'), 'comp.CompetitorName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorID', ParamValues.Parm.value('CompetitorID[1]', 'VARCHAR(Max)'), 'comp.CompetitorID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','SellerState', ParamValues.Parm.value('SellerState[1]', 'VARCHAR(Max)'), 'compMar.SellerState in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','Rating', ParamValues.Parm.value('Rating[1]', 'VARCHAR(Max)'), 'compMar.Rating >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','MarketID', ParamValues.Parm.value('MarketID[1]', 'VARCHAR(Max)'), 'compMar.MarketID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION --dg: commenting this out, it does not seem to be used and if it comes through it will break SELECT 'compListing','Reviews', ParamValues.Parm.value('Reviews[1]', 'VARCHAR(Max)'), 'compListing.ReviewCnt >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compListing','SuspectStatus', ParamValues.Parm.value('SuspectStatus[1]', 'VARCHAR(Max)'), 'compListing.SuspectStatusID = [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMP','IgnoreInCart', ParamValues.Parm.value('IgnoreInCart[1]', 'VARCHAR(Max)'), 'compMP.InCart = 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','CartPrice', ParamValues.Parm.value('CartPrice[1]', 'VARCHAR(Max)'), '[VALUE] and compMP.Price > 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','Condition', ParamValues.Parm.value('Condition[1]', 'VARCHAR(Max)'), 'CompMp.ConditionId IN ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankUpper',ParamValues.Parm.value('PriceRankUpper[1]', 'VARCHAR(Max)'), 'compMP.PricePosition <= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankLower',ParamValues.Parm.value('PriceRankLower[1]', 'VARCHAR(Max)'), 'compMP.PricePosition >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'C" StatementId="4" StatementCompId="18" StatementType="INSERT" StatementSubTreeCost="1278.45" StatementEstRows="4013.67" StatementOptmLevel="FULL" QueryHash="0x39653846CAF10C9C" QueryPlanHash="0x85B7E36B41C9C5DF">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan DegreeOfParallelism="1" MemoryGrant="23608" CachedPlanSize="1072" CompileTime="512" CompileCPU="512" CompileMemory="22344">
<RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="4013.67" EstimateIO="27.3643" EstimateCPU="0.00401367" AvgRowSize="9" EstimatedTotalSubtreeCost="1278.45" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="23" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="0">
<Object Database="[tempdb]" Schema="[dbo]" Table="[#MyParameters]" />
<SetPredicate>
<ScalarOperator ScalarString="[#MyParameters].[TableAlias] = [Expr1514],[#MyParameters].[Parm] = [Expr1515],[#MyParameters].[ParmValue] = [Union1512],[#MyParameters].[ParmFilter] = [Expr1516],[#MyParameters].[ParmWhere] = NULL">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Table="[#MyParameters]" Column="TableAlias" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1514" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[#MyParameters]" Column="Parm" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1515" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[#MyParameters]" Column="ParmValue" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1512" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[#MyParameters]" Column="ParmFilter" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1516" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[#MyParameters]" Column="ParmWhere" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4013.67" EstimateIO="0" EstimateCPU="0.000401367" AvgRowSize="8265" EstimatedTotalSubtreeCost="1251.08" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Union1512" />
<ColumnReference Column="Expr1514" />
<ColumnReference Column="Expr1515" />
<ColumnReference Column="Expr1516" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<Co
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

J,

A few things.

One, your code seems incomplete. There's BEGINs without ENDs, and there's no real reason to have them there.

Next, your query plan is incomplete. As you can see there's an incomplete tag at the tail of it, for starters. The best way to show us your query plan is to attach the .sqlplan you get from rt-clicking the execution plan (the actual by preference) and saving the plan. You can attach as part of your posts here.

If you can get us the complete code and actual sqlplan, that'll help tremendously. Check out the link in my signature for index/tuning help, and it'll walk you through the best way to present the information for the cleanest and most effective assistance from the volunteers here. Right now it's really tough to help you with what's there.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I don't like the look of hitting that xml over and over...

You might want to try something like this to get the data out into a tabular form, then work on it from there....

(I used my own sample data as you can see, but the query is generic)

declare @xml xml='

<root>

<group1>

<a>this is a</a>

<b>and this is b</b>

</group1>

<group2>

<x>this is x</x>

<y>and this is y</y>

</group2>

</root>'

SELECT

group_node.value('local-name(.)', 'varchar(100)') AS GroupName

, inner_node.value('local-name(.)', 'varchar(100)') AS ValueName

, inner_node.value('(./text())[1]', 'varchar(100)') AS Value

FROM @xml.nodes('root/*') x (group_node)

CROSS APPLY group_node.nodes('*') x2 (inner_node)

This produces this output:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);