What's the effect of index on updates?

Question

5.00/5 (1 vote)

See more:

I have 1000000 records, sometimes i need to update my database but it takes more time for one simple update(almost 10s).I want to know if indexing my tables performs my update or there is another better solution? I am using c# for my update.

What I have tried:

I tried a classic update with c#

Posted 5-Dec-19 3:18am

Ahmedovic97

Updated 5-Dec-19 5:42am

Add a Solution

Comments

Richard MacCutchan 5-Dec-19 9:22am

"I am using c# for my update."
It would be better to use SQL. But to be serious, you need to provide more information about exactly what you are doing in your updates.

Ahmedovic97 5-Dec-19 9:26am

i am updating just one column.In SQL, Index performs my Update or not ?

[no name] 5-Dec-19 9:31am

If you have an update statement e.g. like UPDATE Tbl1 SET Tbl1.Fld1= 'Updt' WHERE Tbl1.Fld2 = 'xyz' then an index on Fld2 will definitively help.

Richard MacCutchan 5-Dec-19 9:48am

You still leave us to guess exactly what your SQL statement is, and how many records are being updated.

Ahmedovic97 5-Dec-19 9:55am

for example: update table X set clumn='R' where Id='9000'
the purpose of my update is to flag the record if there is an error

Richard MacCutchan 5-Dec-19 9:59am

It is most likely that such an update will need to scan every one of your 1,000,000 records in order to find every entry with that id; so 10 seconds isn't bad. I would suggest you index it on the Id field which may improve things.

2 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)

RickZeeland · Answer 1 · 2019-12-05T04:00:00

Solution 1

Indexes come with a cost, as they need to be updated too.
See answers here: Performance impact of updating an index in SQL Server? - Stack Overflow[^]
So you should try to keep the number of indexes to a minimum, especially when update speed is your main concern.

Posted 5-Dec-19 4:00am

RickZeeland

Updated 5-Dec-19 4:03am

v2

Dave Kreskowiak · Answer 2 · 2019-12-05T05:43:00

Quote:
In SQL, Index performs my Update or not ?

That question makes no sense at all. Indexes do not "perform any updates". Not in the sense you're using the terminology anyway.

If the id is a primary key column, the key column is already going to be indexed. There's no way to speed that up to any significant degree.

If it's not a primary key, then you can add an index to the table for that column. That will speed up searches in your "id" column dramatically.

But, you never get anything for free. When you INSERT records into the table, the index also has to be updated, so there's a performance hit on every INSERT. If you happen to update the "id" value, that's also going to trigger an update to the index.

Without the index, your statement has to do what's called a "table scan". It has to look at every row in the table, checking the '9000' against the id column in every row, looking for matches. On a million records, that's going to take quite a long time.