1

We have a tool that serves our clients live events, the tool is used to place orders and over a few days processes hundreds of thousands items, so whilst its large, nothing unusual.

We have a reporting platform that sits along side this which queries the ordering sites databases, primarily using views for speed and efficiency.

We came across a critical issue which was causing the dashboard to suddenly stop working and we narrowed the issue down to how it was querying the XML in a tables column. The XML is used for ancillary information that isn't (rightly or wrongly) needed in it's own column.

We found the following query caused it, but interestingly, only when a ORDER BY was applied.

IF '{{RECREATE_SQL_VIEWS}}' = 'TRUE' And EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerLineItemsReport]')) 
 DROP VIEW [dbo].[CustomerLineItemsReport]  

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerLineItemsReport]')) 
 EXEC dbo.sp_executesql @statement = N'
 CREATE VIEW [dbo].[CustomerLineItemsReport] 
 As 
 Select NEWID() AS Id, 
        LineItems.CustomerNumber, 
        LineItems.Cluster,
        LineItems.MarketId, 
        LineItems.MarketPricingRegion,
        LineItems.OrderNumber, 
        LineItems.Status, 
        LineItems.LineItemSku, 
        LineItems.ProductSku,
        LineItems.SizeCode,
        LineItems.SubmittedTicks, 
        LineItems.Quantity, 
        pp.CustomerPrice / 1000 * LineItems.Quantity AS CustomerPrice, 
        pp.RetailPrice / 1000 * LineItems.Quantity AS RetailPrice, 
        pp.PricingRegion, 
        LineItems.OrderItemObjectId,
        LineItems.ProductType,
        LineItems.ShipTo as OrderShipTo
 FROM   (SELECT o.CustomerNumber, 
                d.Cluster,
                o.OrderNumber,
                o.DataObjectDefinition.value(''(//ShipTo/text())[1]'', ''nvarchar(max)'') AS ShipTo,
                o.Status, 
                o.DataObjectDefinition.value(''(//DateFinalised/text())[1]'', ''bigint'') AS SubmittedTicks,
                oi.SizeCode, 
                oi.ProductSku, 
                sc.Code, 
                CASE WHEN oi.SizeCode <> '''' THEN oi.ProductSku + ''/'' + sc.Code ELSE oi.ProductSku END AS LineItemSku, 
                oi.Quantity,
                oi.DataObjectID AS OrderItemObjectId,
                oi.ProductType,
                lm.MarketId,
                lm.PricingRegion As MarketPricingRegion
         FROM dbo.DataObject_DSS_API_OrderItem AS oi 
         INNER JOIN dbo.DataObject_DSS_API_Order AS o ON o.DataObjectID = oi.OrderDataObjectId 
         INNER JOIN dbo.DataObject_DSS_Core_Customer As d ON d.CustomerNumber = o.CustomerNumber 
         INNER JOIN dbo.DataObject_Core_LocalMarket As lm ON d.LocalMarket = lm.DataObjectID 
         LEFT OUTER JOIN dbo.DataObject_Site_Core_SizeCode AS sc ON oi.SizeCode = sc.DataObjectDefinition.value(''(//DisplayName/text())[1]'', ''nvarchar(max)'')) AS LineItems 
         INNER JOIN {{PRICING_DATABASE}}.dbo.DataObject_Pricing_ProductPrice AS pp ON LineItems.LineItemSku = pp.ProductId 
'

So when a SORT BY was applied to the above view, the fields which were searching XML would return NULL, because they're using relative paths in the XML selector, when the below was applied, it didn't.

The interesting thing was, this was working fine up until a certain size on the data set.

IF '{{RECREATE_SQL_VIEWS}}' = 'TRUE' And EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerLineItemsReport]')) 
 DROP VIEW [dbo].[CustomerLineItemsReport]  

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerLineItemsReport]')) 
 EXEC dbo.sp_executesql @statement = N'
 CREATE VIEW [dbo].[CustomerLineItemsReport] 
 As 
 Select NEWID() AS Id, 
        LineItems.CustomerNumber, 
        LineItems.Cluster,
        LineItems.MarketId, 
        LineItems.MarketPricingRegion,
        LineItems.OrderNumber, 
        LineItems.Status, 
        LineItems.LineItemSku, 
        LineItems.ProductSku,
        LineItems.SizeCode,
        LineItems.SubmittedTicks, 
        LineItems.Quantity, 
        pp.CustomerPrice / 1000 * LineItems.Quantity AS CustomerPrice, 
        pp.RetailPrice / 1000 * LineItems.Quantity AS RetailPrice, 
        pp.PricingRegion, 
        LineItems.OrderItemObjectId,
        LineItems.ProductType,
        LineItems.ShipTo as OrderShipTo
 FROM   (SELECT o.CustomerNumber, 
                d.Cluster,
                o.OrderNumber,
                o.DataObjectDefinition.value(''(/dataObject/fields/ShipTo/text())[1]'', ''nvarchar(max)'') AS ShipTo,
                o.Status, 
                o.DataObjectDefinition.value(''(/dataObject/fields/DateFinalised/text())[1]'', ''bigint'') AS SubmittedTicks,
                oi.SizeCode, 
                oi.ProductSku, 
                sc.Code, 
                CASE WHEN oi.SizeCode <> '''' THEN oi.ProductSku + ''/'' + sc.Code ELSE oi.ProductSku END AS LineItemSku, 
                oi.Quantity,
                oi.DataObjectID AS OrderItemObjectId,
                oi.ProductType,
                lm.MarketId,
                lm.PricingRegion As MarketPricingRegion
         FROM dbo.DataObject_DSS_API_OrderItem AS oi 
         INNER JOIN dbo.DataObject_DSS_API_Order AS o ON o.DataObjectID = oi.OrderDataObjectId 
         INNER JOIN dbo.DataObject_DSS_Core_Customer As d ON d.CustomerNumber = o.CustomerNumber 
         INNER JOIN dbo.DataObject_Core_LocalMarket As lm ON d.LocalMarket = lm.DataObjectID 
         LEFT OUTER JOIN dbo.DataObject_Site_Core_SizeCode AS sc ON oi.SizeCode = sc.DataObjectDefinition.value(''(/dataObject/fields/DisplayName/text())[1]'', ''nvarchar(max)'')) AS LineItems 
         INNER JOIN {{PRICING_DATABASE}}.dbo.DataObject_Pricing_ProductPrice AS pp ON LineItems.LineItemSku = pp.ProductId 
'

So the difference between the two queries, one queries the DataObjectDefinition fields using //ShipTo/text() and another is absolute, /dataObject/fields/ShipTo/text()

What made this worse, because this view searched an entire table which we use elsewhere for processing orders, SQL cached the results so the orders generated contained NULL values in those XML columns, when it shouldn't have done.

Anyone got any idea why?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.