SQL Server performance loss with insert in sequence

Question

SQL Server performance loss with insert in sequence

Navigation

#1 by (2 votes)

4

I have a .Net C # application that takes data via Web Service and saves it to a SQL Server 2012 database. The initial performance is 20 records per second. After having a 10000 records in the table, the performance begins to fall, gradually, reaching only 6 inserts per second.

The database has only primary key, without any constraint or index to disrupt the insert.

How can I improve the insert?

Edited

Another detail, if in the middle of the process, if the table has 15000 records and I delete it, the insert performance returns to 20 records per second.

Edited, added DDL of tables

CREATE TABLE [dbo].[PRODUTO](
    [PR_CODIGO] [int] NOT NULL,
    [PR_ABREVIATURA] [char](20) NOT NULL,
    [PR_DESCRICAO] [char](80) NOT NULL,
    [PR_DESCNOCUPOM] [char](1) NULL,
    [PR_LOTE] [char](1) NULL,
    [PR_COMPOSICAO] [char](1) NULL,
    [PR_PESAVEL] [char](1) NULL,
    [PR_PRECO] [numeric](15, 4) NULL,
    [PR_CUSTO] [numeric](15, 4) NULL,
    [PR_IMCODIGO] [int] NULL,
    [PR_STCODIGO] [int] NOT NULL,
    [PR_UMSIGLA] [varchar](3) NOT NULL,
    [PR_PSMCODIGO] [int] NULL,
    [PR_MLCODIGO] [int] NULL,
    [PR_REGSUM] [int] NULL,
    [PR_CODIGOPR] [int] NULL,
    [PR_MINIIMG] [int] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[PRODUTO] ADD [PR_VARIAVEL1] [varchar](250) NULL
ALTER TABLE [dbo].[PRODUTO] ADD [PR_EXPORTADO] [char](1) NULL
ALTER TABLE [dbo].[PRODUTO] ADD [PR_RLCODIGO] [varchar](40) NULL
 CONSTRAINT [PRODUTO_PK] PRIMARY KEY CLUSTERED 
(
    [PR_CODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


CREATE TABLE [dbo].[PRODUTO2](
    [PR_PRCODIGO] [int] NOT NULL,
    [PR_OBSERVACAO] [text] NULL,
    [PR_ATIVO] [char](1) NULL,
    [PR_TIPODISPO] [char](1) NULL,
    [PR_STPORESTADO] [char](1) NULL,
    [PR_NOVOPRECO] [numeric](15, 4) NULL,
    [PR_ORIGEM] [char](1) NULL,
    [PR_FUCODIGO] [int] NULL,
    [PR_CFCODIGO] [char](10) NULL,
    [PR_FOCODIGO] [int] NULL,
    [PR_UMCOMPRA] [varchar](3) NULL,
    [PR_DTREGISTRO] [datetime] NULL,
    [PR_DTULTALT] [datetime] NULL,
    [PR_VARIAVEL5] [char](20) NULL,
    [PR_VARIAVEL6] [char](20) NULL,
    [PR_PECOMISSAO] [numeric](15, 4) NULL,
    [PR_VARIAVEL7] [char](20) NULL,
    [PR_VARIAVEL8] [char](20) NULL,
    [PR_VALIDADE] [int] NULL,
    [PR_PESO] [numeric](15, 4) NULL,
    [PR_MOSIGLA] [varchar](3) NULL,
    [PR_DTALTPRECO] [datetime] NULL,
    [PR_PRECOANT] [numeric](15, 4) NULL,
    [PR_PECOMISSAOPRAZO] [numeric](15, 4) NULL,
    [PR_ALFA1] [varchar](40) NULL,
    [PR_ALFA2] [varchar](40) NULL,
    [PR_NUM1] [numeric](15, 4) NULL,
    [PR_NUM2] [numeric](15, 4) NULL,
    [PR_PRECOFUTURO] [numeric](15, 4) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[PRODUTO2] ADD [PR_EXIBETOUCH] [char](1) NULL
ALTER TABLE [dbo].[PRODUTO2] ADD [PR_VARIAVEL2] [varchar](250) NULL
ALTER TABLE [dbo].[PRODUTO2] ADD [PR_VARIAVEL3] [varchar](250) NULL
ALTER TABLE [dbo].[PRODUTO2] ADD [PR_VARIAVEL4] [varchar](250) NULL
ALTER TABLE [dbo].[PRODUTO2] ADD [PR_PESOBRUTO] [numeric](14, 2) NULL
ALTER TABLE [dbo].[PRODUTO2] ADD [PR_LABORATORIO] [varchar](80) NULL
 CONSTRAINT [PRODUTO2_PK] PRIMARY KEY CLUSTERED 
(
    [PR_PRCODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


ALTER TABLE [dbo].[PRODUTO2] ADD  DEFAULT ('N') FOR [PR_ATIVO]
GO

CREATE TABLE [dbo].[BARRAS](
    [BR_BARRAS] [char](20) NOT NULL,
    [BR_PRCODIGO] [int] NOT NULL,
    [BR_LOTE] [int] NULL,
    [BR_UMSIGLA] [varchar](3) NULL,
    [BR_REGSUM] [int] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BARRAS] ADD [BR_CODIGOFISCAL] [char](1) NULL
 CONSTRAINT [BARRAS_PK] PRIMARY KEY CLUSTERED 
(
    [BR_BARRAS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

sql sql-server

asked by anonymous 09.09.2014 / 21:59

1 answer

How to get a Mac-Address from a LAN IP using .NET Core on Linux? Accessing Webservice over Android - KSOAP2

score 2 · Accepted Answer

If the primary key is clustered and a new record has a value earlier than the one already in the database (example: last record entered PK = 10,000, new record PK = 10) the server will need to physically rearrange the table to accommodate your new record. The server usually leaves a fragmentation between the data to prevent this reorganization, but the default fragmentation may not be meeting your needs.
If you concatenate the values in your INSERT instead of passing them by parameter, the bank is not reusing the execution plan between one execution and another and you lose the opportunity to perform better. / p>

Edited: Added a method below to identify the reason for slowness.

With empty tables, manually run each of the INSERTs using MS SQL Server Management Studio and note the performance (run 3 times each command and note the total time of each execution).
li>
Next, fill the tables using the normal process until the slowness begins to manifest.
Now with the tables full, run the commands again comparing the performance with the first tests. If slowness manifests itself, you can review the execution plan and find out why. If the slowness does not manifest itself, it is elsewhere than in the INSERT commands (processing a large list in memory, for example).