November 19, 2013 at 9:45 pm
how does With (Index (Index_Name)) help in fast query execution??
Does this emphasis on index mentioned during scanning?
November 19, 2013 at 10:30 pm
Short answer - it doesn't. It forces the optimiser to use an index. It's possible that it will result in worse performance if the hinted index is not the best one.
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
November 20, 2013 at 12:23 am
OK. One additional question. Suppose one non clustered index is already there on multiple columns and some of columns I am using in where clause.
Does that index help in query performance?
November 20, 2013 at 12:33 am
No where near enough information to answer that.
Depends on the columns in the index, the where clause predicates, the selectivity of the index and more.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
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
November 20, 2013 at 2:12 am
Hi,
may be this question is not related my original concern.
But I have added one non clustered index column in select list. Now performance in significantly increased.
is index helpful when we include indexed column in select list? I thought earlier that, it helps only in case of filter conditions in where clause.
November 20, 2013 at 2:25 am
Again, not enough information. Please read through the article I referenced.
If you create an index on a column that is just in the select clause, that index is probably useless.
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
November 20, 2013 at 5:54 am
mote.ajit2 (11/20/2013)
Hi,But I have added one non clustered index column in select list. Now performance in significantly increased.
Perhaps an ORDER BY clause in your query is now using the index to sort the data?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 20, 2013 at 6:13 am
Abu Dina (11/20/2013)
mote.ajit2 (11/20/2013)
Hi,But I have added one non clustered index column in select list. Now performance in significantly increased.
Perhaps an ORDER BY clause in your query is now using the index to sort the data?
Or the data is now in cache (from the first execution and the index build) and so the query is faster as a side-effect
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply