CODEWITHSUNDEEP
sqlsql-serverodbcquery-optimizationbulk-load
Max
Max

Reputation: 73

Speed up mass inserts for SQL Server without direct application access?

I have an application in a proprietary programming language. The database access is handles via ODBC connections to an SQL Server 2019. There is one use case in the application in which prepared statements are used to dump thousands of inserts into the same table. Unfortunately I cannot change the inserts themselves as the application's object relationship mapping handles the inserts and that is a "black box". The only options I have is to provide the ideal environment for these inserts to thrive. The layout of the table is as follows:

CREATE TABLE [dbo].[CostTable](
    [Deleted] [bit] NOT NULL,
    [TimeEntry] [datetime] NOT NULL,
    [UserEntry] [int] NOT NULL,
    [GUID] [varchar](50) NOT NULL,
    [Costs1] [decimal](18, 2) NULL,
    [Costs2] [decimal](18, 2) NULL,
    [Costs3] [decimal](18, 2) NULL,
[Costs4] [decimal](18, 2) NULL,
[Costs5] [decimal](18, 2) NULL,
[Costs6] [decimal](18, 2) NULL,
[Costs7] [decimal](18, 2) NULL,
 CONSTRAINT [PK_CostTable] PRIMARY KEY NONCLUSTERED 
(
    [GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

There are also a few indexes applied to this table. Unfortunately I cannot change the format of the primary key and the value itself is created by the application. It is an alphanumeric key. A numeric key would most likely be better but I cannot do that. Would it help to turn off check constraints and turn them on after all the inserts? Are there any other options available? Is there something I can adjust in the ODBC driver? I am using the SQL Server Native Client 11.0. The ODBC driver can be changed as required.

Upvotes: 0

Views: 59

Answers (1)

Fevzi Kartal
Fevzi Kartal

Reputation: 226

You can modify the configuration & structure of the database & table to speed up the same application process.

Checking

The application might calculate some values from other tables with some queries before writing to the table. These queries might need to be optimized.

Before bulk load process investigate what the application send to Sql Server with Sql Profiler. Then look which steps takes longer

Bulk Load optimization

If these is a one time jop or a scheduled job then to optimize the inserting speed.

1.) Take a full backup. To release the allocated space of transaction logs.

2.) Change recovery model to Simple.

3.) Check and Increase the Autogrowth size of your log & database files. 512, 1024 MB like the picture below.

This will decrease the database - transaction log file's growth frequency.

After scheduled process you can set to an ideal disk value. Lets say 256 MB.

enter image description here

4.) Disable all indexes, except Primary Key.

5.) Disable all constraint with no check.

6.) Within Sql Server dont put every thing in the same disk/driver. Separete into data files into own, transaction log files into own. If it is possible add temp db files in to seperate dedicated fast disks.

Upvotes: 0

Related Questions