Cluster index taking 100% cost in SQL

Question

0.00/5 (No votes)

See more:

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 27-Feb-24 21:54pm

Member 11776570

Updated 27-Feb-24 21:56pm

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2024-02-27T22:54:00

Solution 1

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.

Posted 27-Feb-24 22:54pm

Richard Deeming

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.