Query is very slow with index seeks

BrianG 2017-04-24 17:53:29

I have this query that takes about 2 seconds to complete. All indexes are using seeks except one scan, but this scan has the lower cost than the seeks. I have rebuilt indexes, updated column statistics, adjusted MAXDOP from 1 to 4 (4 CPUs on server) and none of those move the execution time. Just wanted to check if you see anything obvious that I am missing. Thanks.

Aaron Bertrand 2017-04-26 19:23:07
The only things I can see are that

(a) the compiled parameter for CustomerNumber is an explicit value and the runtime parameter is a wildcard

(b) some of the clustered index seeks could be slightly more efficient if you had non-clustered indexes that covered only the columns required, though whether it's worth maintaining those depend on workload balance. For example the clustered index seek at Node 45 could be a more efficient nc index seek if the following index were present:

CREATE INDEX [IX_CustomerPOSCompanyXref_CustomerUID_CompanyUID] 
  ON [CustomerSource].[dbo].[CustomerPOSCompanyXref]
([CustomerPOSCompanyUID] DESC, [POSCustomerType] ASC, [POSCustomerNumber] ASC, 
  [CustomerUID] ASC, [CompanyUID] ASC)
INCLUDE ([POSCustomerCategory], [POSCustomerParentCategory])

(c) I don't know that parallelism is helping you here, I might shift back to maxdop 1

(d) the Table I/O tab is telling – even though the plan shows there is only a single seek on CustomerPOSCompanyXref, there's clearly more going on here, because that registers 44,611 reads. I think this is a case where the CTE messes things up, so I might try this:

SELECT CustomerPOSCompanyUID AS TempUID INTO #t 
  FROM dbo.CustomerPOSCompanyXref (NOLOCK) 
  WHERE POSCustomerNumber like @CustomerNumber
  GROUP BY CustomerPOSCompanyUID;

Then join to #t instead of the CTE. I might be tempted to try additional unraveling of the next CTE. This may give the optimizer a better opportunity to come up with a good plan, knowing the number of company IDs it will deal with, and hopefully eliminate all of the reads that I believe are caused by the complex cascading of CTEs.

BrianG 2017-04-27 15:14:09
Thank you very much for responding to this, If have changed to temp tables and get the same results and maxdop 1 takes this to 40 seconds. I have narrowed it down to this, if I comment out line 2 below the 44,611 reads go to 4 and the duration from 1981ms to 28ms. The problem is the business requirements are that the users need to be able to search with wildcards at both ends. Any ideas on how this line can be handled better while keeping this requirement?

DECLARE @CustomerNumber VARCHAR(100) = '2629123210'
SET @CustomerNumber = '%'+@CustomerNumber+'%'
FROM dbo.CustomerPOSCompanyXref (NOLOCK)
WHERE POSCustomerNumber like @CustomerNumber;

BrianG 2017-04-27 15:15:36
Looks like it did not paste the way I wanted, line 2 I mentioned above commenting out is:
SET @CustomerNumber = '%'+@CustomerNumber+'%'
Aaron Bertrand 2017-04-27 16:04:07
Wildcard searches make things hard, because if you're looking for a pattern that starts anywhere but the beginning of the string (or ends anywhere but the end of the string), a traditional index can't be used to help, and a full scan must be performed because every single value in the table has to be inspected to see if it contains your pattern. I recently wrote about a very cumbersome way to solve this, but you could also look into other solutions such as full-text search (depending on how the CustomerNumber is contained in the string) or 3rd party products like Lucene or ElasticSearch. Or enhancing the design so that an additional column is used to store the portion of the POSCustomerNumber value that you will likely search on.
BrianG 2017-04-27 16:06:51
OK, I will read through your article. Thank you very much for your time.