Query is very slow with index seeks

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.

avatar image By BrianG 1 asked Apr 24 at 05:53 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Aaron Bertrand ♦ 1.7k answered Apr 26 at 07:23 PM
more ▼
(comments are locked)
avatar image BrianG Apr 27 at 03:14 PM

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+'%' SELECT CustomerPOSCompanyUID AS TempUID FROM dbo.CustomerPOSCompanyXref (NOLOCK) WHERE POSCustomerNumber like @CustomerNumber;

avatar image BrianG Apr 27 at 03:15 PM

Looks like it did not paste the way I wanted, line 2 I mentioned above commenting out is: SET @CustomerNumber = '%'+@CustomerNumber+'%'

avatar image Aaron Bertrand ♦ Apr 27 at 04:04 PM +

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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Apr 24 at 05:53 PM

Seen: 69 times

Last Updated: Apr 27 at 04:06 PM