CODEWITHSUNDEEP
sql-server
Kumar Vaibhav
Kumar Vaibhav

Reputation: 2652

Insertion in table becomes slower towards the end

This question is out of curiosity. I have a table in which I am inserting a lot of data. Now what happens is this - as the data in this table grows, the insertion becomes slower and slower. What could be the reasons for this? And what can I do for this situation?

There are no indexes on this table.

Thanks

Upvotes: 1

Views: 66

Answers (3)

bettersayhello
bettersayhello

Reputation: 2599

here's my take on this issue:

Are you using indexes on your table? From what I have read, if you are indeed using some indexes on your table, then the dbms has to update the index's data structure for each new record that you insert in your table. as your table gets larger, this process gets slower. this is basically a trade-off between having faster reads by using indexes but maintaining these indexes become costly when your table gets modified a lot.

this article provides more information about this.

Upvotes: 2

Twinkles
Twinkles

Reputation: 1994

Here is my attempt at speculating:

When you make changes to a database, the results are not written directly to permanent storage — the changes are done to the pages in memory and only the transaction log is written.

After a while, the database system will run out of clean pages in the memory to write to: that's when it will start flushing out the table pages to disk.

There is nothing you can do about this behaviour, it is totally intentional.

Upvotes: 1

Anthony Horne
Anthony Horne

Reputation: 2522

If you can, and up to the minute backups are not important, set your database type to SIMPLE (SQL Database Properties). It could be the transaction log growing and causing you to run through the whole thing.

Upvotes: 2

Related Questions