Updating database index

Tags:
Database
Database index strategy
Database management
Indexing
SQL Database
when i create a table with nearly 100 rows, database index gets update to accomodate those 100 rows. Can i find out the time taken by the index to get updated from previous state to a state to accomodate those 100 rows?

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you have a good database engine and server, it should be very hard to answer this question.

If you use “bulk load” transactions, then you can possibly measure the time from the “commit” until the records are visible to another process, but this would be pretty tricky.

If you aren’t using bulk load transactions, then the index overhead is largely buried inside the row transactions. Most index overhead is occurring in memory, so it is very fast – the physical updates to the file system are buffered through the caching mechanism, so timing the completion of those writes is very difficult.

What is the purpose of trying to find this time? What question are you really trying to answer?

If you absolutely need to know the cost of index overhead, then you can compute an average cost using the following approach:

1. Create the data table with NO indexes.
2. Write records to the table and measure the time it takes to write the data. I would normally do this in a few common sizes, e.g. 500 or 1000 records. Repeat this several times – I would probably populate at least several hundred thousand records, capturing the apparent time for each block.
3. Drop the table.
4. Create the data table again, but with the index.
5. Repeat step 2 and measure the times again.
At the end, the difference between the times measured in step 5 and the times measured in step 2 will give you the “cost” of the index overhead.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    What database platform?
    66,280 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following