using non clustered and clustered index but no effect on time execution in sql

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

i have a table with 30000 rows in it , and i make an copy with data of this table .

i have one clustered index on primary key column and three other indexes on this table

when i am execut any query on theses indexed column behalf ..i have seen no improvement on time of execution . i have tried many query ... time taken for both table (with and without indexs with same data) are same . (i tried using execution plan for time taken also)

Can any one suggest what s the problem ...how to use them ?

Posted 3-Jan-14 22:44pm

GDdixit

Add a Solution

3 solutions

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)

Aarti Meswania · Answer 1 · 2014-01-03T23:09:00

Solution 1

some tips...

you should apply index on column which you are using for WHERE clause
and if you are using joins then make sure index is applied to that table's columns' too

check execution plan if table with more data should not be scan it should be seek

Do not include unnecessary columns in select list

Happy Coding!
:)

Posted 3-Jan-14 23:09pm

Aarti Meswania

Updated 3-Jan-14 23:10pm

v2

Comments

GDdixit 4-Jan-14 5:57am

YES DEAR , I DO THE SAME AS U TOLD HERE ...

BUT THE TIME TAKEN IS SAME IN BOTH CONDITION ...
IS IT RIGHT THAT I AM CHECKING THE ESTIMATED SUBTREE COST IN EXECUTION PLAN ?

Christian Graus 4-Jan-14 6:01am

Run the SQL, and see if it's faster. The cost in the execution plan is a %, not a time.

Jörgen Andersson · Answer 2 · 2014-01-25T10:41:00

A clustered index is normally a bad idea if you have secondary indexes, because the secondary indexes cannot point to ROWIDs in the heap, they have to point to the clustered index.
This means that for EVERY row it scans in the secondary index, it needs to make an index seek in the clustered index, making it inefficient when querying many rows.

Christian Graus · Answer 3 · 2014-01-04T00:01:00

Solution 2

A clustered index is a BAD idea if your data is often deleted or changed.

Posted 4-Jan-14 0:01am

Christian Graus

Comments

GDdixit 4-Jan-14 6:09am

SO THAN WE CAN USE UNCLUSTERED INDEX ON THE COLUMN THAT ARE NOT FREQUENTLY CHANGED ?

Christian Graus 4-Jan-14 6:14am

You can use it whenever you want, but because it means all the data is stored in the cluster, it makes deletes and edits slow and expensive, so not a great idea.