kidgordones
New Member
I am trying to optimize a stored procedure that relies on a few \[code\]XML\[/code\] type parameters for variable input. Analysis of the execution plan shows a high cost associated with accessing the data in these parameters. Given:\[code\]DECLARE @FirstNameXML XML;\[/code\]In the format of \[code\]<FirstNames> <Name>John</Name> <Name>Joe</Name></FirstNames>\[/code\]There are a few operations I need to perform (assuming the parameter is non-null):Count of Names supplied\[code\]SET @FirstNameCount = (SELECT COUNT(FirstNameValues.Name.value('.','VARCHAR(50)')) FROM @FirstNameXML.nodes('/FirstNames/Name') AS FirstNameValues(Name))\[/code\]If count = 1\[code\]...AND First_Name LIKE (SELECT TOP(1) FirstNameValues.Name.value('.','VARCHAR(50)') + '%' FROM @FirstNameXML.nodes('/FirstNames/Name') AS FirstNameValues(Name))...\[/code\]Else if count > 1\[code\]...AND First_Name IN(SELECT FirstNameValues.Name.value('.','VARCHAR(50)') FROM @FirstNameXML.nodes('/FirstNames/Name') AS FirstNameValues(Name))...\[/code\]I have tried a few optimizations:Changing the count query to:\[code\]SET @FirstNameCount = (SELECT CAST(CAST(@FirstNameXML.query('count(/FirstNames/Name)') AS VARCHAR(10)) AS INT)\[/code\]Changing the count == 1 query to:\[code\]...AND First_Name LIKE (SELECT @FirstNameXML.value('(/FirstNames/Name)[1]', 'VARCHAR(50)') + '%')...\[/code\]Somehow the stored procedure runs even slower after the optimizations even though the changes seem to have reduced the cost in the execution plan. This leaves me with a few questions:[*]Am I misunderstanding the optimizations I've implemented? [*]Is there a more efficient way to go about this as a whole? (XML parameter based querying)