SQL Server Performance Loss with Sequential Insert

Asked

Viewed 1,518 times

4

I have an . Net C# application that takes data via Web Service and saves it in an SQL Server 2012 database. Initial performance is 20 records per second. After having some 10000 records in the table, the performance starts 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 Internet?

Edited

Another detail, if in the middle of the process, if the table is with 15000 records and I delete it, the performance of Insert is back 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
  • 1

    You are using Prepared statements or giving one Insert at a time?

  • Primary keys are string or integer? You do the Insert in the transaction scope?

  • 2

    If everything is done in a single gigantic transaction, this may be the cause of the slowness, because it is overloading the comic. Do this in batches, "committing" the transaction to each N records and making sure to reuse the same connection to each insert, without putting and taking the connection from the pool.

  • I am using an Insert at a time, has a transaction for each Insert as it involves more tables.

  • @Marcoaurelio PK are integer

  • What is the database backup configuration? You insert one line per Insert ?

  • @gmsantos I am inserting one line at a time. Database backup setup? Which config would you like to know?

  • The options of Restore (simple or full)

  • @gmsantos where I see it?

  • "It involves more tables". Is it really INSERT that is causing slowness? You can publish your SQL commands and CREATE TABLE?

  • @Caffé tomorrow I try to get the main data to publish here

Show 6 more comments

1 answer

2


  • If the Primary key is clustered and a new record has a value earlier than those already in the database (example: last record inserted PK = 10,000, new record PK = 10) the server will need to physically rearrange the table to accommodate its new record. The server usually leaves a fragmentation between the data to avoid this reorganization, but the standard fragmentation may not be meeting its need.

  • If you concatenate the values in your INSERT instead of passing them by parameter, the database is not reusing the execution plan between one execution and another and with that you lose opportunity to have a better performance.

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

  • With the tables empty, 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 run).

  • Then 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 the slowness manifests, you can analyze the execution plan and find out why. If the slowness does not manifest, it is elsewhere than in the INSERT commands (processing a large list in memory, for example).

  • I added the DDL from the 3 main tables. PRODUCT and PRODUTO2 after 8000 records loses performance. Erase them, return the performance and then lose again. I adjusted to make the Insert in the order of PK and continued the slowness, I even took the PK to see if improved the speed of Insert.

  • @Marlon.Tiedt You can also post the INSERT command and others that are executed in the same routine (if any)?

  • @Marlon.Tiedt The table BARRAS has PK clustered by barcode? This could be causing the slowness I described in the first item of my reply. How did you remove the Pks? Recreated the table? It is guaranteed that BARRAS was also recreated without PK during testing?

  • I just tested taking the PK table BARRAS and the process declined as previous situation. Yesterday take the PKdropping the same, and today BARRAS dropped the table and recreated. I am seriously considering saving an XML and do insert select, what do you think?

  • 1

    @Marlon.Tiedt I think it’s best to find out why it’s slow. I updated my answer with a suggestion on how to identify it.

  • I analyzed the code again and found that the problem was in the table BARRAS, that before entering records erased the product bars and the bars in question, I took this and improved the speed. But I’m still not happy with the speed, because each product makes 7 to 10 Inserts, in different tables. I’m doing a test to process 250 on a single call from . Net to the bank, let’s see if improves speed.

  • 1

    @Each transaction open and close has a cost, so in fact if you do it in batches, you gain speed. You will have to find the optimal amount because committing too many records at once also has an extra cost. Well, if the problem wasn’t the Inserts maybe it’s better to remove this issue. Good luck there with your challenge.

Show 2 more comments

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.