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]
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).
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
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.
Recoded these inserts to use
LINQ; still no better.