SQL Server Performance Forum – Threads Archive

Inserts getting slower and slower

Hi there, I’m having a strange (at least to me) experience with a Delphi7 app connecting to SQL Server 2k via DBExpress. My app processes one "file" that results in n "calls". The calls resulted by the file are inserted on a table with it’s own id (retrieved by an Oracle SEQUENCE kind of solution) and has the id of the file that originated it. The problem is that when processing a file (that can generate more than 20k lines of calls) the firsts inserts are quite fast (like 5 or more in a sec), but when the process goes on, this keeps getting slower and slower (apparently in an exponecial way). I.E. if the first half of the calls are inserted in 5mins. the second half takes aprox 25mins! The table that receives the inserts (the one that stores the calls) have a primary key and an index on the Id. I really can’t imagine what’s causing this incremental slowlyness…
Does someone have an idea? Ps.: The "Oracle Sequence kind of solution" is a Stored Proc that follows:
CREATE PROCEDURE DBO.GETNEXTKEY(@TABLE_NAME VARCHAR(30), @NKEY INTEGER OUTPUT)
AS
DECLARE @I BIT
BEGIN
BEGIN TRANSACTION;
SET @I = 0;
SELECT @I = 1 FROM T_SEQUENCE WHERE TABLE_NAME = @TABLE_NAME;
IF (@I = 0)
BEGIN
INSERT INTO T_SEQUENCE VALUES (@TABLE_NAME, 0);
END;
UPDATE T_SEQUENCE SET LAST_PK = LAST_PK+1 WHERE TABLE_NAME = @TABLE_NAME;
SELECT @NKEY = LAST_PK FROM T_SEQUENCE WHERE TABLE_NAME = @TABLE_NAME;
COMMIT;
END
and uses a table that follows: CREATE TABLE [T_SEQUENCE] (
[TABLE_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LAST_PK] [int] NULL ,
CONSTRAINT [PK_T_SEQUENCE] PRIMARY KEY CLUSTERED
(
[TABLE_NAME]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks in advance. Marcelo Myara

Ensure you are using compatible driver between delphi and SQL server in order to avoid any slow performance.
Also confirm the indexes on the involved tables and check execution plans.. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Why do you need to store such a "perfect" sequence in your db at all? Usually there is no place for this in the table. This is most likely a presentational issue to be solved at the client. Anyway, have you had a look at the IDENTITY property yet? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

The GetNextKey StoredProc does not give me a "perfect" sequence. It only gives me a guaranteed unique identifier everytime i call it. It’s just an unique identifier for the rows cause the data can be very repetitive and it’s a bit dificult to find a primary key for the calls table (even thinking of composite pks). I’m discarding any problem related to the driver, since in several other apps using the same driver, nothing close to this happens. " Anyway, have you had a look at the IDENTITY property yet? " How should i do it, and what should i expect to see there? (i don’t know if i really know what that is [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]) and thanks for your time… Marcelo Myara

Ok, i just read the docs for the identity property, it would help indeed if the info were just for presentation (in fact this new knowledge will help me on some reports, [8D]) but in this case, the data is the row identifier, and the autonumber column cannot be used cause i need to know the value for the new row, before the insert. In Oracle DB i would use a sequence to do it, and here i’m using this procedure, wich only forces the result to be unique for every call.

I agree with Frank that you should use identity property, but just for fun here is the code that should perform better:
CREATE PROCEDURE DBO.GETNEXTKEY_Fixed(@TABLE_NAME VARCHAR(30), @NKEY INTEGER OUTPUT)
AS
begin
BEGIN TRANSACTION; IF not exists(select * from T_SEQUENCE WHERE TABLE_NAME = @TABLE_NAME)
BEGIN
INSERT INTO T_SEQUENCE VALUES (@TABLE_NAME, 0);
END;
UPDATE T_SEQUENCE
SET @NKEY = LAST_PK = LAST_PK+1
WHERE TABLE_NAME = @TABLE_NAME;
COMMIT;
END

quote:The table that receives the inserts (the one that stores the calls) have a primary key and an index on the Id.

Does it mean you have primary key and additional index on ID? If so you should drop the index. Slowness may come from bad index design on table where you insert rows (e.g. too many indexes, no adequate fillFactor). The most often reason for such behavior is not having clustered index on id column. So to advice further we need to know table structure and indexes.

Ok guys, The problem is solved. I’m a little embarassed to tell you that the problem wasnt in the DB after all. It was in the app. Well at least you guys help me with the IDENTITY Property that will help me in one of the reports, with the knowledge that even not showing as an index on the SQL Query Analyser, the PKs ARE indexes indeed (this will let me drop a lot of indexes) and i’m re-evaluating the table for the calls to see the really need of the unique id and the getnextkey stored proc with it. Thanks a lot. Marcelo Myara

]]>