Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 5 '13 at 6:20

This question came from our site for professional and enthusiast programmers.

2  
Isn't your database file growth set to "10%" or something (without having instant initialisation enabled msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx)? –  Filip De Vos Apr 4 '13 at 10:17
2  
The database growth settings are terrible out of the box. It creates fragmented database files and when you have a big database the filegrowth phases are going to take forever. The best is to set the growth to fixed chunks (and enable instant initialisation). In really big databases it is best to manage files and filesizes manually (where you schedule the jobs to grow the files on moments where there is no or low activity on the server). –  Filip De Vos Apr 4 '13 at 11:12
1  
@FilipDeVos Could you post your two comments as an answer? –  Mark Storey-Smith Apr 5 '13 at 8:49
1  
Inserts into heaps get progressively slower due to PFS (Page Free Space) scans when sql is trying to determine which page to insert the row into. The larger the heap, the more work it takes for sql to find a page with enough free space to hold the row. I'll try to turn this into a proper answer later today, but the solution is to create a sensible clustered index on the table. This shouldn't really be an issue unless you're in the 10+ GB plus range. What kinds of row counts do you see? –  StrayCatDBA Apr 19 '13 at 13:50
1  
Have you tried using System.Data.SqlClient.SqlBulkCopy? This will be much, much faster than singleton INSERT statements. –  Jon Seigel Apr 19 '13 at 16:57

1 Answer 1

up vote 2 down vote accepted

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.