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.
Best Answer
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 usesBULK 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.