Optimizing insert performance

Hi there.
We're using SQL Server 2000 on a Windows 2000 box with dual
hyperthreaded Intel CPUs, raided hard disks and 2GB RAM. It should
certainly be able to handle our currently fairly modest demands. We
have all the latest security patches. Parallelism is disabled.
We're currently experiencing unacceptable slowness for inserts to a
particular table, Account_Transaction (see the DDL at the end of this
message). As you can see, the main features are a BIGINT auto-increment
primary key, and three foreign keys. This table currently has 7 million
rows and another million are inserted each month. We are currently
unhappy with the length of time it takes to insert into this table as
this operation is at the heart of our application.
Here is an example insert, called from within a stored procedure:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(
@.invoice_id,
@.transaction_type_id,
@.qty,
@.amount,
@.amount_commission,
@.tax_percentage,
@.currency_code_home,
@.points,
@.line_id,
@.comment,
@.balance,
@.external_id
)
Looking at this query's SP:StmtCompleted event from the profiler we can
see the query took 375ms to complete and performed 76 reads (and
apparently zero writes, although the data was definitely inserted). We
think this is far too long.
What is strange though is that if we run the exact same statement
OUTSIDE of the calling procedure in query analyzer, substituting
literals for the variables, then it apparently completes in just 63ms,
although it performs the same number of writes:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
If we now try the same two things on our test database, running on a a
bog-standard desktop machine, with only 300000 rows in the
Account_Transaction table, then both queries execute in similar times of
around 70ms.
So my question is - what is causing the slowness, and how can we speed
it up? How do we make the insert operation from within the SP take the
same amount of time as it takes when run on its own?
Any help appreciated,
Mike C
CREATE TABLE [dbo].[Account_Transaction] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[invoice_id] [bigint] NOT NULL ,
[transaction_type_id] [int] NOT NULL ,
[qty] [int] NOT NULL ,
[amount] [money] NOT NULL ,
[tax_percentage] [decimal](9, 3) NULL ,
[commission] [float] NOT NULL ,
[currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_id] [int] NOT NULL ,
[points] [int] NOT NULL ,
[comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[balance_before] [money] NOT NULL ,
[balance_after] [money] NULL ,
[external_id] [bigint] NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
[msrepl_tran_version]
GO
CREATE INDEX [IX_Account_Transaction] ON
[dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] ADD
CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[Account_Invoice] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
(
[line_id]
) REFERENCES [dbo].[Line] (
[id]
),
CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
(
[transaction_type_id]
) REFERENCES [dbo].[Transaction_Type] (
[id]
)
GOMike
1) Make sure that your table has no triggers
2) Try drop the indexes just prior to INSERTING and re-create them after
Another thing might help that if you do it by using SP which accepted
parameters try to re-assign these parameters to local variable
CREATE PROC myProc
@.par INT
AS
DECLARE @.lpar INT
SET @.lpar =@.par
INSERT INTO Table VALUES (@.lpar )
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GO|||It sounds like Mike is speaking of single transactional inserts and not bulk
inserts, so dropping / recreating indexes on the table would not be
practical.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Unqxm%23HGHA.528@.TK2MSFTNGP12.phx.gbl...
> Mike
> 1) Make sure that your table has no triggers
> 2) Try drop the indexes just prior to INSERTING and re-create them after
>
> Another thing might help that if you do it by using SP which accepted
> parameters try to re-assign these parameters to local variable
>
> CREATE PROC myProc
> @.par INT
> AS
> DECLARE @.lpar INT
>
> SET @.lpar =@.par
> INSERT INTO Table VALUES (@.lpar )
>
>
> "Mike Chamberlain" <none@.hotmail.com> wrote in message
> news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
>|||Consider if the stored procedure is re-compiling each time it is executed.
This could explain the overhead when executing the insert from the procedure
while it still performs the same number of writes as the test insert from
Query Analyzer.
INF: Conditions for Stored Procedure Recompilation
http://support.microsoft.com/defaul...kb;en-us;104445
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/defaul...kb;en-us;308737
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GOsql