How to eliminate OR condition

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  April 7, 2015 at 5:38 pm

  #1789370

  koti.raavi (4/7/2015)

  ---

  First Apologies for delay in response:

  Firstly Thanks to

  GilaMonster,Jeff Moden,Eirikur Eiriksson,CELKO,Ed Wagner,MMartin1,MadAdmin for your valuable time

  --Jeff I'm here 🙂

  Yes, if we use 2 unions it will scan the table twice, but i have noticed couple of times UNION is much better than OR condition..that's why i have posted this..GilaMonster may be correct as both queries are executing parallel. I have noticed one thing today, index is already exist on table but its not using existing index.. Existing index is combination of columns (Multiple columns -Non Clustered Index). table is already contain clustered index too...when i executed estimated execution plan it saying create stand alone index on column....i think its problem with order of indexes, even i have used WITH INDEX option to force table to use index. but no use .. Below is the example how indexes are created on table....Thank you again...and if you have any questions please let me know

  Ex: CM_Number (Primarykey Clustered)

  CM_Number,ID,ID1, Customer Name (NonClustered Index)

  I already asked a question. 😉 Still waiting on the answer.

  --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)
  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 8, 2015 at 2:54 am

  #1789460

  On the indexing side: http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Lempster

  SSCoach

  Points: 15651

  More actions

  April 8, 2015 at 4:02 am

  #1789470

  koti.raavi (4/7/2015)

  ---

  ...what is your thoughts on this?...all where clause cafeterias are stand alone ...

  A fascinating discussion, but the most important question is surely... what is a where clause cafeteria?!

  I imagine it to be a convivial place containing TABLES, possibly with some nice VIEWS and a SELECTion of interesting items on the menu. 😀

  Regards

  Lempster

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 8, 2015 at 4:19 am

  #1789475

  koti.raavi (4/7/2015)

  ---

  Index1: ID (PK-Clustered Index)

  Index2: ID,Member_ID,Memeber_Name

  Index2 is near-useless. It's redundant with the primary key and is extremely unlikely to be used much.

  The index that the missing index suggestions are suggesting has Member_ID as the leading column, not the second column and it includes the columns c1 and c2, which Index2 doesn't have. As it stands, Index2 is far worse than a table scan for that query and so forcing SQL to use it you'll be degrading performance.

  even i have used with (Index Option) to utilize existing index (forcing table to use existing index)..thanks

  Stop using index hints. The chance of you hinting an index and improving performance is slim. Hints are for when you know exactly why the optimiser hasn't chosen the index you want, you are absolutely, completely certain that you know better than the optimiser does and you are absolutely sure that even when the data changes in the future, your index will still be better.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• koti.raavi

  SSCrazy

  Points: 2756

  More actions

  April 11, 2015 at 9:45 am

  #1790356

  GilaMonster....Thanks for your reply ..!:-)