April 7, 2015 at 5:38 pm
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
April 8, 2015 at 2:54 am
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
April 8, 2015 at 4:02 am
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
April 8, 2015 at 4:19 am
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
April 11, 2015 at 9:45 am
GilaMonster....Thanks for your reply ..!:-)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply