Insert performance progressively deteriorating for indexless table

Question

I have a miniature SQL Server database being filled in by a mathematical model. The database contains two tables created thus:-

CREATE TABLE [dbo].[small_table](
[id_col_1] [tinyint] NOT NULL,
[id_col_2] [tinyint] NOT NULL,
[id_col_3] [int] NOT NULL,
[id_col_4] [int] NOT NULL,
[data_col_1] [real] NULL,
[date_time_added] [datetime] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[small_table] ADD  CONSTRAINT [DF_st_date_time_added]  DEFAULT (getdate()) FOR [date_time_added]

and

CREATE TABLE [dbo].[big_table](
[id_col_1] [tinyint] NOT NULL,
[id_col_2] [tinyint] NOT NULL,
[id_col_3] [int] NOT NULL,
[id_col_4] [int] NOT NULL,
[id_col_5] [int] NOT NULL,
[id_col_6] [tinyint] NOT NULL,
[id_col_7] [int] NOT NULL,
[data_col_1] [real] NULL,
[data_col_2] [real] NULL,
     ...snip...
[data_col_14] [int] NULL
) ON [PRIMARY]
GO

The model runs writing single rows to these tables whenever it thinks proper. But as the run progresses, writing into these tables takes progressively longer. Does anyone have any idea why this might be or what I should do to find out?

If I start a new run with a different value of id_col_2, the initial inserts proceed at a decent rate and the run then slows down again.

I'm using VB.Net for the application running on Windows XP.

Microsoft SQL Server Management Studio  10.50.2500.0
Microsoft Data Access Components (MDAC) 3.85.1132
Microsoft .NET Framework                2.0.50727.3643
Operating System                        5.1.2600

If I use MySQL database instead (replacing the connection and INSERT statements), the inserts proceed at a regular rate.

(In the real problem the tables have indexes but I removed them one at a time in order to find out if they were causing the problem; there are now none left and it is still occurring).

Edit

The original code used a programmatically-constructed insert statement and the ADODB.Connection.Execute method to insert the row. Since then I've tried using SQLConnection and SQLCommand objects to insert the row. This has improved performance slightly, but the progressive deterioration is still occurring.

My test database has an original file size of 130MB and an autogrowth of 10%. The live system has an original filesize of 10592MB and an autogrowth of 1MB. The problem is evident on both systems.

Edit

Recoded these inserts to use LINQ; still no better.

Answer 1

Based on your last comment, it's still unclear what the actual problem is, but I'm going to answer to spell out my recommendations a bit, as I think they could help others in the future.

It's possible that if you try to scale up the existing "working" solution, you'll run into the same problem again.

---

To improve performance of a series of single-row inserts over a period of time, I recommend switching to a batch-based INSERT strategy by buffering rows on the client. The buffer would be flushed periodically, based on either row count, elapsed time since the last flush, or both.

The recommended way to do inserts in batch for SQL Server is to use the System.Data.SqlClient.SqlBulkCopy class which uses BULK INSERT under the hood.

If you need to stay database-agnostic (or minimize changes to the existing code), a dynamic SQL strategy can be adapted to construct a larger batch of singleton INSERT statements, instead of executing one statement per batch.

This type of strategy is more efficient as it minimizes network roundtrip overhead, allowing the application to scale higher. Note that for either strategy, you'll need to tune the number of rows in a batch to maximize throughput.

---

It's possible either data or log file growth is still an issue, but I recommend using the above strategy regardless, as the file growth issues should be solved independently of how the application operates.

• Ensure there is a suitable amount of free space in both the log file and data file(s) before starting the process.
• Set the auto-growth settings to more aggressive values than the defaults (usually a reasonably large, fixed size is appropriate).
• Enable instant data file initialization to minimize the impact of data file auto-growth. On my blog, I have a post/video of what this setting does, and how to enable it. Note: this is an instance-wide setting, not a database setting.