Stop query if it is taking too long

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  October 29, 2016 at 9:43 pm

  #1909504

  Sergiy (10/29/2016)

  ---

  Eirikur Eiriksson (10/29/2016)

  ---

  Jeff Moden (10/24/2016)

  ---

  Eirikur Eiriksson (10/24/2016)

  ---

  you will need this index on the dbo.Transaction

  CREATE UNIQUE NONCLUSTERED INDEX [INDEX_NAME] ON [dbo].[Transaction]

  (

  [InvoiceUID] ASC,

  [IsSuccess] ASC,

  [TransKind] ASC,

  [TransactionDate] ASC

  )

  INCLUDE ( [CardNumber],[ReferenceNumber],[SeqCounter],[UnqReferenceNumber]);

  I came up with similar but was really torn and didn't post it... can't test it because I don't have the time to setup the test data but, it seems to me that it may have the same multi-seek problem as the original index. I was thinking that TransactionDate, TransKind, IsSuccess with InvoiceUID as a part of the include would allow for an initial seek followed by a high performance range scan.

  Using TransKind as the initial column would likely prove even faster but that would be pretty tough on inserts in the form of index page splits unless a sweet-spot FILL FACTOR was also found.

  Yes, I pondered on it and decided to post it with the question of whether the applied order of predicates was within the OP's control, obviously no answer yet on that one.

  😎

  Index with a GUID column on the 1st position will be useless for almost all kind of queries, except the ones having ColumnUID = @ParamUID.

  And they definitely have no use for "range selection" queries, like the one in the starting post.

  Not sure why I thought that only said "InvoiceID" but I absolutely agree. If it's actually a GUID, not a good especially as a Clustered PK.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)