Why is this query slow?

• kfrancis25

  SSC Enthusiast

  Points: 182

  More actions

  January 16, 2020 at 5:52 pm

  #3716061

  #pastetheplan https://www.brentozar.com/pastetheplan/?id=SJF21m0lU

  I'm trying to figure out what's causing this query (part of a stored procedure) to be so slow. The NOLOCK hints were an earlier attempt to stop deadlocks, I'm guessing they are likely no longer necessary.

  I did add an index that helped a ton, but this is still pretty slow and I'm not sure what direction to go in next.

  This was the old plan before I added the index it craved:

  

  And this is the new/current plan:

  

• kfrancis25

  SSC Enthusiast

  Points: 182

  More actions

  January 16, 2020 at 5:53 pm

  #3716062

  Gah! Sorry, didn't mean to post twice - site timed out during the original post.

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  January 16, 2020 at 7:52 pm

  #3716107

  Here are some initial observations based on the execution plan:

  • There seems to be a large number of reads on the ClaimPaid table, and there is a residual IO warning here.  Is there an index that has the ServiceDate column as the first column?  This seems to be the main filtering criteria.
  • How many rows would there be in the @CaTs table variable?  SQL Server won't use statistics for table variables and you can see in the plan it estimates 1 row.  If this has lots of rows in it, SQL Server could have picked a poor execution plan.
  • Is there an index on the ClaimPaidDetail table that has ClaimID as the first column?  Since you are joining ClaimPaid to ClaimPaidDetail on this column it would help to have an index on this foreign key.

   

• kfrancis25

  SSC Enthusiast

  Points: 182

  More actions

  January 16, 2020 at 8:03 pm

  #3716118

  There is an index for ServiceDate on that table, although that table has a ton of indexes on it. We're very likely trying to do too much here (indexes with sensible names = us, the two new ones at the bottom are Azure performance indexes it's added and is validating):

  

  No, I don't think that @CaTs (categories) has many rows. This query is part of a larger that that's attempting to validate new claims against a set of rules of various categories. We're first determining what categories might apply, then finding relevant paid claims that might be relevant in the evaluation.

  Yes, absolutely that index exists:

  CREATE NONCLUSTERED INDEX [IX_ClaimPaidDetail_ClaimID] ON [Claims].[ClaimPaidDetail]
  (
  [ClaimID] ASC
  )