Multiple-Valued Parameters

Peter W. DeBetta — Mon, 12 Feb 2007 21:43:00 GMT

So I was doing some research on which is the best way to pass multiple-valued parameters into a stored procedure or user-defined function when I found some interesting results. I had always used a string-based user-defined function to parse a delimited list of values and I knew I could use XML to do the same thing, yet I had never compared the two side-by-side to see which is a better choice.

So which was faster? Well, using one of many available string-based UDFs, I had unexpected results. I thought that the XML version would be equivalent if not slower, but alas, I was wrong. The XML-based UDF consistently performed about 35% faster than the string-based inline table-valued UDF and about 65% faster than string-based table-valued UDF I tested.

I will be following-up this post soon with a post about various XML-based techniques that I tested…

One last item of note – this post was published (tags and all) from Word 2007.

As for now, here are the various functions used in the tests:

CREATE FUNCTION dbo.fnString2IntList(@sData varchar(8000), @sDelim char(1))
RETURNS @tList TABLE (ListValue int)
AS
BEGIN
    DECLARE @sTemp int, @nPos int, @nPos2 int
    IF (Len(RTrim(LTrim(@sData))) = 0) RETURN

    SET @nPos = CharIndex(@sDelim, @sData, 1)

    IF @nPos = 0 OR @nPos = Len(@sData)
     BEGIN
        SET @sTemp = CAST(SubString(@sData, 1, (Len(@sData) - Sign(@nPos))) as int)
        INSERT INTO @tList VALUES(@sTemp)
        RETURN
     END

    SET @sTemp = CAST(SubString(@sData, 1, @nPos - 1) as int)
    INSERT INTO @tList VALUES(@sTemp)

    WHILE @nPos > 0
     BEGIN
        SET @nPos2 = CharIndex(@sDelim, @sData, @nPos + 1)
        IF (@nPos2 = 0) SET @sTemp = CAST(SubString(@sData, @nPos + 1, Len(@sData)) as int)
        ELSE SET @sTemp = CAST(SubString(@sData, @nPos + 1, ABS(@nPos2 - @nPos - 1)) as int)

        INSERT INTO @tList VALUES(@sTemp)
        SET @nPos = CharIndex(@sDelim, @sData, @nPos + 1)
     END
    RETURN
END
GO

CREATE FUNCTION dbo.fnString2IntList2(@sData varchar(8000), @sDelim char(1))
RETURNS TABLE
AS
    RETURN
    (WITH csvtbl(i,j)
    AS
    (
        SELECT i = 1, j = CHARINDEX(@sDelim, @sData + @sDelim)
        UNION ALL
        SELECT i = j + 1, j = CHARINDEX(@sDelim, @sData + @sDelim, j + 1)
        FROM csvtbl
         WHERE CHARINDEX(@sDelim, @sData + @sDelim, j + 1) <> 0
    )
    SELECT CAST(SUBSTRING(@sData, i, j - i) as int) AS ListValue
    FROM csvtbl)
GO

/*    Assumes XML is as such
    <list>
        <i>1</i>
        <i>23</i>
    </list>
    etc...

    Uses minimal xml markup to keep input size as small as possible
*/
ALTER FUNCTION dbo.fnXml2IntList(@xmlList xml)
RETURNS TABLE
AS
    RETURN (SELECT tList.ListValue.value('.', 'int') AS ListValue
            FROM @xmlList.nodes('list/i') AS tList(ListValue))
GO

Medians, Actual Query Cost, and Statistics

Peter W. DeBetta — Wed, 20 Dec 2006 10:48:00 GMT

I was going to comment on Adam's post "Medians, ROW_NUMBERs, and performance" (which was a follow-up to Aaron's post "Take the high road to middle ground") and I realized that I needed to show some code and alas, comments are not so nice about formatting code, so I am posting a follow-up post...

At the PASS Summit back in November, Itzik and I were examining this issue of the QP doing the table spool when one would think it shouldn't need to and we were both stumped as to why. But further examination revealed some strangeness: Even though the logical reads for Ben-Gan's solution is quite high, the actual query cost is less than Celko's solution. It makes one wonder: Which is the better indicator of better perfromance - actual query cost or statistics such as time and IO? I have seen people use actual query cost as the basis for choosing one solution over another. Is using actual query cost a valid way of deciding which solution to choose? Like I said, it makes one wonder....

For Ben-Gan's solution, I also was dissatisfied with the QP's choice in doing the table spool. And so I decided to create a variation on Ben-Gan's solution that calculated the count for each CustomerID separately. Although the logical reads is higher than Celko's solution, it is only 748 as compared to the 703 of Celko's solution; and better yet, the execution time is [on average] just slightly better and the query cost is always better (37% versus 63%). Put a supporting index in place (CustomerID, TotalDue) and this solution's logical reads drops to 121 (versus 76 for Celko's) but is consistently better in execution time and actual query cost (12% versus 88%)

Here is the query:

;with c as
  (SELECT CustomerId , TotalDue,
    Row_Number()
      OVER (PARTITION BY CustomerId ORDER BY TotalDue) as rn
FROM Sales.SalesOrderHeader)
, d as
(SELECT CustomerId ,
    (COUNT(*)+1) /2 as cnt1,
    (COUNT(*)+2) /2 as cnt2
FROM Sales.SalesOrderHeader
GROUP BY CustomerID)
SELECT c.CustomerId, Avg(c.TotalDue) AS Median
FROM C
INNER JOIN d
ON c.CustomerId = d.CustomerId AND c.rn IN (d.cnt1, d.cnt2)
GROUP BY c.CustomerId

I agree with Adam that cost-based optimization is far from perfect. But I don't like the inconsistency in the various results of STATISTICS IO, STATISTICS TIME, and actual query cost. And so what is the best way to decide which query performs better and which will scale better: STATISTICS IO, STATISTICS TIME, actual query cost, a combination of all of these, or perhaps something else (such as a detailed analysis of the actual query plans)?

Peter DeBetta's SQL Programming Blog : Performance

Multiple-Valued Parameters

Medians, Actual Query Cost, and Statistics