Insert performance has seriously degraded after adding new Columns to Table

I have been searching the newsgroupps and books on line and can't find
anything that sounds like my issue. So, I'm coming here for help!
Here is what is happening:
Our app has a stored procedure that does some processing on the data.
Within the stored procedure there is are inserts into tables.
In our old version, this stored procedure completed in less than 5
minutes. 32000 rows were processed.
In our new version, the stored procedure took more than 14 hours to
complete.
The culprit was an insert into a table we added new columns to. That
was the ONLY change to the stored procedure.
Here are the table definitions:
OLD
CREATE TABLE reconcile_group (
OBJECT_ID varchar (13) NOT NULL ,
OPERATOR varchar (13) NOT NULL ,
RECONCILED_DATE datetime NOT NULL ,
CLIENT varchar (13) NOT NULL ,
REC_TYPE varchar (13) NOT NULL ,
LOCATION char (1) NOT NULL ,
ALGORITHM char (1) NOT NULL ,
VARIANCE char (1) NOT NULL ,
LATE_DEPOSIT int NOT NULL ,
IGN_TRAN_DATE char (1) NOT NULL ,
IGN_TRAN_CODE char (1) NOT NULL ,
MIS char (1) NOT NULL ,
MANAGER_STATS varchar (13) NOT NULL)
ON PRIMARY
NEW
CREATE TABLE reconcile_group (
OBJECT_ID varchar (13) NOT NULL ,
OPERATOR varchar (13) NOT NULL ,
RECONCILED_DATE datetime NOT NULL ,
CLIENT varchar (13) NOT NULL ,
REC_TYPE varchar (13) NOT NULL ,
LOCATION char (1) NOT NULL ,
ALGORITHM char (1) NOT NULL ,
VARIANCE char (1) NOT NULL ,
LATE_DEPOSIT int NOT NULL ,
IGN_TRAN_DATE char (1) NOT NULL ,
IGN_TRAN_CODE char (1) NOT NULL ,
MIS char (1) NOT NULL ,
MANAGER_STATS varchar (13) NOT NULL ,
IGN_UDF varchar (512) NULL ,
ADDITIONAL_INFO varchar (512) NULL
) ON PRIMARY
Here are the inserts:
OLD
INSERT INTO reconcile_group
(object_id,operator,reconciled_date,clie
nt,
rec_type,location,algorithm,variance,lat
e_deposit,
ign_tran_date,ign_tran_code,mis,manager_
stats)
VALUES
(@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
@.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
'F', 'F', 'T', '0')
NEW
INSERT INTO reconcile_group
(object_id,operator,reconciled_date,clie
nt,
rec_type,location,algorithm,variance,lat
e_deposit,
ign_tran_date,ign_tran_code,mis,manager_
stats,ign_udf, additional_info)
VALUES
(@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
@.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
'F', 'F', 'T', '0', 'F','')
That insert is the only code that changed in the whole procedure. When
we replace the new proc with the old one processing time goes back to
under 5 minutes.
There are no new indexes on the table. I have SET NOCOUNT ON in the
sp.
Am I missing something in the way of tuning? Does adding those 2
columns really affect the insert performance that much? What else can
i look at?
We have customers seeing the issue in production and we are able to
reproduce it in house.
We are running Sql 2000 sp3 on Windows 2003 server sp1.
Thanks for any advice.
LibbyIf the new columns are part of a clustered index (default for a primary
key), then this could impact the performance of inserts. Run DBCC SHOWCONTIG
to see what fragmentation may have resulted by altering the layout of this
table. Also, when the insert starts bogging down, check to see how much
transaction logging is going on.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"EElliott" <eelliott96@.gmail.com> wrote in message
news:1123090923.738117.74640@.z14g2000cwz.googlegroups.com...
>I have been searching the newsgroupps and books on line and can't find
> anything that sounds like my issue. So, I'm coming here for help!
> Here is what is happening:
> Our app has a stored procedure that does some processing on the data.
> Within the stored procedure there is are inserts into tables.
> In our old version, this stored procedure completed in less than 5
> minutes. 32000 rows were processed.
> In our new version, the stored procedure took more than 14 hours to
> complete.
> The culprit was an insert into a table we added new columns to. That
> was the ONLY change to the stored procedure.
> Here are the table definitions:
> OLD
> CREATE TABLE reconcile_group (
> OBJECT_ID varchar (13) NOT NULL ,
> OPERATOR varchar (13) NOT NULL ,
> RECONCILED_DATE datetime NOT NULL ,
> CLIENT varchar (13) NOT NULL ,
> REC_TYPE varchar (13) NOT NULL ,
> LOCATION char (1) NOT NULL ,
> ALGORITHM char (1) NOT NULL ,
> VARIANCE char (1) NOT NULL ,
> LATE_DEPOSIT int NOT NULL ,
> IGN_TRAN_DATE char (1) NOT NULL ,
> IGN_TRAN_CODE char (1) NOT NULL ,
> MIS char (1) NOT NULL ,
> MANAGER_STATS varchar (13) NOT NULL)
> ON PRIMARY
> NEW
> CREATE TABLE reconcile_group (
> OBJECT_ID varchar (13) NOT NULL ,
> OPERATOR varchar (13) NOT NULL ,
> RECONCILED_DATE datetime NOT NULL ,
> CLIENT varchar (13) NOT NULL ,
> REC_TYPE varchar (13) NOT NULL ,
> LOCATION char (1) NOT NULL ,
> ALGORITHM char (1) NOT NULL ,
> VARIANCE char (1) NOT NULL ,
> LATE_DEPOSIT int NOT NULL ,
> IGN_TRAN_DATE char (1) NOT NULL ,
> IGN_TRAN_CODE char (1) NOT NULL ,
> MIS char (1) NOT NULL ,
> MANAGER_STATS varchar (13) NOT NULL ,
> IGN_UDF varchar (512) NULL ,
> ADDITIONAL_INFO varchar (512) NULL
> ) ON PRIMARY
> Here are the inserts:
> OLD
> INSERT INTO reconcile_group
> (object_id,operator,reconciled_date,clie
nt,
> rec_type,location,algorithm,variance,lat
e_deposit,
> ign_tran_date,ign_tran_code,mis,manager_
stats)
> VALUES
> (@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
> @.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
> 'F', 'F', 'T', '0')
> NEW
> INSERT INTO reconcile_group
> (object_id,operator,reconciled_date,clie
nt,
> rec_type,location,algorithm,variance,lat
e_deposit,
> ign_tran_date,ign_tran_code,mis,manager_
stats,ign_udf, additional_info)
> VALUES
> (@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
> @.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
> 'F', 'F', 'T', '0', 'F','')
> That insert is the only code that changed in the whole procedure. When
> we replace the new proc with the old one processing time goes back to
> under 5 minutes.
> There are no new indexes on the table. I have SET NOCOUNT ON in the
> sp.
> Am I missing something in the way of tuning? Does adding those 2
> columns really affect the insert performance that much? What else can
> i look at?
> We have customers seeing the issue in production and we are able to
> reproduce it in house.
> We are running Sql 2000 sp3 on Windows 2003 server sp1.
> Thanks for any advice.
> Libby
>|||On 3 Aug 2005 10:42:03 -0700, EElliott wrote:

>I have been searching the newsgroupps and books on line and can't find
>anything that sounds like my issue. So, I'm coming here for help!
>Here is what is happening:
>Our app has a stored procedure that does some processing on the data.
>Within the stored procedure there is are inserts into tables.
>In our old version, this stored procedure completed in less than 5
>minutes. 32000 rows were processed.
>In our new version, the stored procedure took more than 14 hours to
>complete.
>The culprit was an insert into a table we added new columns to. That
>was the ONLY change to the stored procedure.
(snip)
Hi Libby,
The added columns require SQL Server to rebuild the pages when the rows
are updated (a bit mask with NULL markers for the new, nullable columns
has to be added, plus two length markers for the current length of the
varchar strings. And the strings themselves, of course). This can take a
while.
If I recall correctly, SQL Server won't do this for the complete table
when the ALTER TABLE statement is executed, but will postpone it until
the data is changed. I think it does the change on a page by page basis
(i.e. if one row in a page is changed, all rows are), but I don't know
that for sure. In any case, since some bytes have to be added to each
row, you'll probably run into page splits during the process as well.
Was this the first time that a major amout of rows in the table was
changed after the columns were added? If so, then this might be a
one-time problem. Next time you run the procedure, all rows have already
been converted.
Also, you mention 32000 rwos processed, but the statement you posted
will only add one single row. Are you using a cursor or other loop-based
code to process your data one row at a time? In 99% of all cases, such
code can be rewritten as setbased code, and the latter code is almost
always shorter AND quicker. If the slowness was indeed caused by the row
conversion, then a set-based approach might have caused the rebuilding
process to affect the whole table at once. Probably a lot quicker.
Finally: are there any triggers that fire on insertions to the
reconcile_group table?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||THanks for the replies so far.
I ran the dbcc showconfig and it showed some fragmentation, but it
wasn't terrible. I rebuilt the table and indexes anyway. THe process
showed no improvement.
To answer Hugo's questions:
When our app does the alter table to add the new columns, we also
update all of the rows in the table to hold a default vaule for the new
columns. So, it has already been processed once.
We do use cursors for processing the data. There's quite a bit of
other "stuff" it does, so I just pasted the relevant insert, since that
is the only thing that changed related to the stored proc.
And finally, There are no triggers on the DB at all.
We have finally gotten from the customer that they are seeing slowness
in other areas of the application as well. So, maybe this is not
isolated to one function. >:-( Thanks for telling us now! Perhaps
their DB needs some over all tuning.
If anyone has any other ideas, I'd appreciate them!
Thanks,
Libby