Hop Scotch

IniTech's fashion division, IniGarment, launched their new accounting solution by hiring "best in class" highly-paid-consultants (HPCs). The system launched, collapsed under the weight of the first week of use, hardware was thrown at the problem in an absolute crisis panic, and the end result was that they had a perfectly serviceable accounting package that was overbudget and supported by expensive HPCs.

It wasn't sustainable. So out the HPCs went, and in came a pile of salaried employees. Jeff was one of those. His first six weeks at IniGarment were spent trying to understand the nest of stored procedures, quick hacks, and ugly choices. One of the first puzzles Jeff decided to look into was an invoice uploading step.

They used a turn-key eCommerce package to handle sales, and the accounting system needed to generate invoices and upload them to the eCommerce package. This relatively simple task took 30 minutes to generate and upload a single invoice, and that was on a good day. On bad days, it could take nearly an hour. So Jeff set out to figure out why.

In the middle of a 700+ line stored procedure, Jeff found the query which generated the report. The whole process required big piles of temporary tables (which, instead of being true temp tables, were created/dropped with each execution), and ugly group-bys and subqueries. Still, even with all that, it was just a SQL query, right?

INSERT INTO ApiInvoiceItemReports(BrandCode,InvoiceNumber,InvoiceLineNumber,OrderNumber,ProductNumber,ProductName,SeasonCode,SeasonDescription,DivisionCode,DivisionDescription,ColorCode,ColorDescription,GenderCode,GenderDescription,DimensionCode,SizeScaleCode,ProductCategoryCode,ProductCategoryDescription,InvoicedQuantity,ProductInvoicedPrice,StatusCode,ObjectState)SELECTNULL AS BrandCode,dbo.TRIM(T.invno) AS InvoiceNumber,MIN(T.[lineno]) AS InvoiceLineNumber,dbo.TRIM(I.OrderNumber) AS OrderNumber,dbo.TRIM(LEFT(T.ItemNo, 12)) AS ProductNumber,MAX(dbo.TRIM(S.Title)) AS ProductName,MAX(dbo.TRIM(T.season)) AS SeasonCode,MAX(dbo.TRIM(T.season)) AS SeasonDescription,MAX(compno) AS DivisionCode,MAX(compno) AS DivisionDescription,substring(dbo.trim(T.ItemNo),13, CASE WHEN charindex('-',dbo.trim(T.ItemNo)) > 0 THEN charindex('-',dbo.trim(T.ItemNo))-13 ELSE 3 END) AS ColorCode,MAX(ISNULL(dbo.TRIM(C.descrip), '')) AS ColorDescription,MAX(dbo.TRIM(Gender.GenderCode)) AS GenderCode,MAX(dbo.TRIM(Gender.GenderCode)) AS GenderDescription,NULL AS DimensionCode,MAX(sizrange) AS SizeScaleCode,MAX(dbo.TRIM(Category.CategoryCode)) AS ProductCategoryCode,MAX(dbo.TRIM(Category.CategoryCode)) AS ProductCategoryDescription,SUM(qtyord) AS InvoicedQuantity,MAX(unitpr) AS ProductInvoicedPrice,'S' AS StatusCode,1 AS ObjectStateFROM Arytrans TINNER JOIN #InvoiceReportsData I ON I.InvoiceNumber = T.invnoINNER JOIN aastock S ON S.ItemNo = T.ItemNoLEFT JOIN aacolor C ON C.code = substring(dbo.trim(T.ItemNo), 13, 5)LEFT JOIN((SELECT ItemNo, Code AS GenderCode, NULL AS CategoryCode FROM rsoption WHERE [Key] = 'GENDER') AS GenderFULL OUTER JOIN(SELECT ItemNo, NULL AS GenderCode, Code AS CategoryCode FROM rsoption WHERE [Key] = 'PRODUCTCATEGORY') AS CategoryON Gender.ItemNo = Category.ItemNo)ON Gender.ItemNo = T.ITEMNO OR Category.ItemNo = T.ITEMNOWHERE T.compno = 9AND T.ItemNo IS NOT NULL AND T.ItemNo <> ''AND T.Custstyle IS NOT NULL AND T.custstyle <> ''AND T.WebOrderid IS NOT NULL AND T.WebOrderid <> ''AND T.WebStyle IS NOT NULL AND T.WebStyle <> ''GROUP BY T.invno, I.OrderNumber, T.itemno

Now, it's not terribly surprising that this query didn't perform well, but Jeff was surprised where the performance bottleneck was. Specifically, it was this inner join: INNER JOIN aastock S ON S.ItemNo = T.ItemNo, and the related field access: MAX(dbo.TRIM(S.Title)) AS ProductName.

Looking at the execution plan, Jeff discovered that the combination of the group-bys, and the MAX access meant that this was triggering a full tablescan of aastock for each result row. That was certainly going to hurt performance, but aastock wasn't that large a table- 165,000 rows- so that didn't quite explain what was going on.

But aastock wasn't actually a table. It was a view. Defined as:

 CREATE view [LOCALSQL].VENDOR.[dbo].[aastock] as select * from [REMOTESQL].ERP.[dbo].aastock

This was getting close to explaining the massive performance hit. aastock was a view to a table in a remote database. So each one of those full table scans needed a network hop. But wait, how was the remote table defined? Turns out, it's a view as well:

 create view [REMOTESQL].ERP.[dbo].[AASTOCK] as select * from [REMOTESQL].ERP.[dbo].[syn_AASTOCK];

But wait, what's syn_AASTOCK? That sounds like a synonym. What does it point at?

 CREATE SYNONYM [REMOTESQL].ERP.[dbo].[syn_AASTOCK] FOR [ERP_stage1].[dbo].[AASTOCK]

" it's a reference to yet another remote server, their ERP system. As it turns out, the HPCs couldn't figure out how to avoid deadlocks with their mirrored replication, so their "fix" was to create synonyms which point to an ERP replica, and then swap which replica the synonym pointed at depending on the hour, cycling through ERP_stage1, ERP_stage2, ERP_replA, etc.

The result is that this query did a full tablescan for each row which included two network hops. No one knew that it was doing this, and it certainly wasn't documented anywhere. Once Jeff understood the path, he could refactor the entire stored procedure to clean up the access path using common table expressions and a cross apply to restrict the way functions got applied. With that, the query went from many minutes of execution to a handful of seconds.

Jeff briefly contemplated looking into the issues with deadlocks in replication, but decided that wasn't the next problem to solve when there were other queries that could be tweaked to increase performance without opening that box of horrors.

[Advertisement] Continuously monitor your servers for configuration changes, and report when there's configuration drift. Get started with Otter today!