sql tvp vs xml speed in stored procedure are the same??? Is my testing or logic flawed?

Question

I'm an accidental DBA charged with speeding up all our sql servers. I've got a highly used query with a horrible average worker time. I noticed it uses XML to pass data to a stored procedure. Query plan tells me it spends most of its time converting XML. Everything I've read says XML is about 33% slower than TVP. I rewrote the SP using TVP and compared times using the method:

SELECT @StartTime=GETDATE() 
exec GetTVPData3 @tvp  --or XML method
SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs] 

After many runs and averaging out the times.... TVP vs XML has XML winning by 5ms. ????? (550ms vs 545ms) Is my testing or logic flawed?

Both XML and TVP are populated before I get StartTime. I've run this on 2 different SQL test servers with similar results.

The particular code is used in a cross apply. The only difference in the SPs are:

**TVP**
CROSS APPLY (SELECT id AS ProductID, sortorder AS SortOrder  FROM @Insert_tvp) Items

**XML**
CROSS APPLY (
SELECT f.id.value('@id', 'int') AS ProductID, f.id.value('@sortorder', 'int') AS SortOrder
FROM @ProductIDs.nodes('list/p')
AS f(id)
) Items

Everything in my head tells me we need to switch to using TVP and get rid of XML. But I can't convince coders without better results.

EDIT: Adding the whole XML SP:

ALTER PROCEDURE [dbo].[ExtendedDataXML]

@HostedSiteID INT,
@ProductIDs XML = NULL,
@ImageType VARCHAR(20) = NULL
AS
BEGIN

SET NOCOUNT ON;

SELECT
    Products.ID AS ItemID,
    0 AS ItemType,
    Products.SKU,
    Products.Title,
    HSP.Slug,
    Products.Rank,
    Products.Rank AS SalesRank,
    Products.Status,
    Products.LaunchDate,
    Products.IsOnline,
    Products.IsAutoOffline,
    Products.IsSalableOnline,
    Products.IsMarketableOnline,
    Products.LeadIn, Products.LeadOut,
    COALESCE(Products.CaseQuantity, 1) AS CaseQuantity,
    COALESCE(Products.MinimumOrderQuantity, 1) AS MinimumOrderQuantity,
    Products.QuantityOnHand,
    Image.Filename, Image.Width, Image.Height, Image.Alt, Image.Title,
    Pricing.Price, Pricing.SalePrice,
    Products.TruckShipment,
    HSP.NDescription
FROM Products
JOIN HostedSites_Products HSP ON Products.ID = HSP.ProductID
CROSS APPLY (
    SELECT f.id.value('@id', 'int') AS ProductID, f.id.value('@sortorder', 'int') AS SortOrder
    FROM @ProductIDs.nodes('list/p')
    AS f(id)
) Items
OUTER APPLY (
    SELECT TOP(1) Filename, Width, Height, Alt, Title
    FROM Items_Images
    JOIN Images ON Items_Images.ImageID = Images.ID
    WHERE Items_Images.ItemID = Products.ID
    AND Items_Images.ItemType = 0
    AND Images.Type = COALESCE(@ImageType, '.4b')
) Image
OUTER APPLY (
    SELECT TOP(1) Price, SalePrice, CurrentPrice
    FROM ProductPrices
    WHERE ProductPrices.ProductID = Products.ID
    ORDER BY LoRange ASC
) Pricing
WHERE Products.ID = Items.ProductID
AND HSP.HostedSiteID = @HostedSiteID
AND HSP.Validated = 1
AND Products.IsMarketableOnline = 1
ORDER BY Items.SortOrder
END

Answer 1

CROSS APPLY means row wise execution! You are parsing your XML over and over...

Your ID-List is - as far as I understand - meant as a filter

Besides the fact, that this was much better done within an inlined TVF (syntax without BEGIN...END you might try this like this:

ALTER PROCEDURE [dbo].[ExtendedDataXML]

@HostedSiteID INT,
@ProductIDs XML = NULL,
@ImageType VARCHAR(20) = NULL
AS
BEGIN

    SET NOCOUNT ON;
    WITH IDList AS
    (
        SELECT f.id.value('@id', 'int') AS ProductID, f.id.value('@sortorder', 'int') AS SortOrder
        FROM @ProductIDs.nodes('list/p') AS f(id)
    )
    SELECT
        Products.ID AS ItemID,
        0 AS ItemType,
        Products.SKU,
        Products.Title,
        HSP.Slug,
        Products.Rank,
        Products.Rank AS SalesRank,
        Products.Status,
        Products.LaunchDate,
        Products.IsOnline,
        Products.IsAutoOffline,
        Products.IsSalableOnline,
        Products.IsMarketableOnline,
        Products.LeadIn, Products.LeadOut,
        COALESCE(Products.CaseQuantity, 1) AS CaseQuantity,
        COALESCE(Products.MinimumOrderQuantity, 1) AS MinimumOrderQuantity,
        Products.QuantityOnHand,
        Image.Filename, Image.Width, Image.Height, Image.Alt, Image.Title,
        Pricing.Price, Pricing.SalePrice,
        Products.TruckShipment,
        HSP.NDescription
    FROM Products
    JOIN HostedSites_Products HSP ON HSP.HostedSiteID = @HostedSiteID AND HSP.Validated = 1 AND Products.ID = HSP.ProductID
    INNER JOIN IDList AS Items ON Items.ProductID=Products.ProductID
    OUTER APPLY (
        SELECT TOP(1) Filename, Width, Height, Alt, Title
        FROM Items_Images
        JOIN Images ON Items_Images.ImageID = Images.ID
        WHERE Items_Images.ItemID = Products.ID
        AND Items_Images.ItemType = 0
        AND Images.Type = COALESCE(@ImageType, '.4b')
    ) Image
    OUTER APPLY (
        SELECT TOP(1) Price, SalePrice, CurrentPrice
        FROM ProductPrices
        WHERE ProductPrices.ProductID = Products.ID
        ORDER BY LoRange ASC
    ) Pricing
    WHERE Products.IsMarketableOnline = 1
    ORDER BY Items.SortOrder
END