November 2, 2008 at 7:11 am
Guys,
I have table which is growing on daily basis currently there are 3mill rows in this table.
I am trying to explore if it is worth archiving the records and at any point retain only records for one business day. In this process I have a question.
In terms of insert DML performance what is the difference if
A row is inserted in the same table which has 3 mill rows versus a row inserted in the same table with 1000 rows.
Any inputs/suggestions will help.
Thanks
November 2, 2008 at 7:20 am
No real difference. In either scenario you could cause a page split, which would mean moving some portion of a page to a new page.
SQL allocates in extents and pages, and inserts take place at the page level, so whether you have 1 or 1000 pages it doesn't change the performance.
Now on your disks, as you move away from the center, you could see slightly slower performance accessing the data or writing it, but since SQL tends to buffer in memory, an insert here or there won't matter.
Query performance is different. If you end up scanning large sections of the table, or index, less rows can help.
November 4, 2008 at 12:53 am
I believe it will make a difference. Let me explain.
Apart from the page split, which would happen irrespective of the number of records in a table, what you need to consider is the acceptable amount of time permitted for an insert to be committed on the table. From your query posted along with the self suggestion you made, I believe the table in question is purely used for Insert / Update statements and not used for reporting purposes.
With the increase in number of the indexes on the columns in the table, performance for inserts / updates decreases and becomes noticble when the number of records in the table is large. Indexes are only useful if records are selected [i.e. read].
In many of the production systems, for auditing / for implementing business logic triggers are built in. This degrades performance bottleneck and becomes noticble when the number of records in the table is large.
Choices are as below
As recommended by yourself archive old records to another table
Partition data on the table based on year / month / quarter
If the table is used only for insert purposes, ensure no indexes are created and if possible no triggers on the table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply