Click here to Skip to main content
15,922,166 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
If i have created cluster index it should work faster than other.
But when i check execution plan it show me 100% cost for it.

Below is the example

Table1
Col1
Col2
Col3

CREATE CLUSTERED INDEX LK_table1 ON Table1 (Col1,
Col2,
Col3)

But i have use only col1 and col3 in join and col2 is used in query.
So it is creating issue if we use all the columns in indexes but not using it in join, that's why the cost is 100%.

What I have tried:

Not yet try anything as query is running properly, but when i check execution plan it show me 100% cost for that cluster index.
Please let me know how to reduce the cost for it.
Posted
Updated 27-Feb-24 21:56pm
v3
Comments
CHill60 28-Feb-24 4:49am    
You say "use all the columns" but you not saying in what, nor showing the join. Share the SQL you are using
Member 11776570 28-Feb-24 8:01am    
select
b.col2
from tab2 a
inner join tab1 b on b.col1=a.col1 and b.col3=a.col3
where b.col2=abc

like this if we are using.
PIEBALDconsult 28-Feb-24 10:29am    
An index is not a magic bullet which always makes everything faster. A bad index can make some things slower.
Without seeing your SQL statement and such, we can't really tell.

1 solution

Firstly, the "100% cost" just means that your clustered index scan/seek accounts for 100% of the cost of that query. It doesn't mean that the query is less efficient than it would be without that index.

Secondly, you need to design your indexes to account for the expected amount of data in the relevant tables, and the queries you expect to be executing. If you're filtering on a specific value or range on Col2, then joining to another table on (Col1, Col3), then you'll probably want to put Col2 first in the index. But you would need to test against your real tables, with a meaningful data load, to be sure.
 
Share this answer
 
Comments
Member 11776570 28-Feb-24 7:58am    
If i take the col2 in the join with filter not in where condition but with the join table, then still i need to take that column first in the index script ?
Richard Deeming 28-Feb-24 8:15am    
If it's part of the join between the two tables, what was it doing in the WHERE clause in the first place?

And if it's not part of the join, then moving it to the join's ON clause won't make any real difference. (The only exception is LEFT / RIGHT joins, where the positioning of the filter can dramatically change the output of the query.)
Member 11776570 28-Feb-24 8:25am    
col2 filter the records as per expectation, so i am using it in where clause. if i use it with join then it will matter or not for it to be first in the index script ?
Richard Deeming 28-Feb-24 9:17am    
No. As I said, moving a filter from the WHERE clause to the ON condition of a JOIN won't make any difference to the performance.

The only time it makes any difference is if you're doing a LEFT / RIGHT join, and filtering on the "optional" side of the join.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900