Updating an 'archive' database

[David Taylor]

I have a VB app that used Access locally and a Sybase
server to "archive" the local records. I am now
converting it to using SQL Server/Desktop Engine locally
and SQL Server for the archive. The local database
contains a field for storing the rec_id (identity) field
from the archive database. The problem is, the updates to
the archive database are taking a VERY LONG time to
execute, like 10-15 minutes for updating 500 records or so.

The Transact-SQL code to run the update for one of the
tables is shown below. Initially, I tried joining the
tables (local to archive) but that took even longer
because of the number of records in the archive database.

Is there a better way to do this?

Thanks for any tips.


Dave Taylor


CREATE PROCEDURE UpdateAssaysArchive AS
IF EXISTS(SELECT * FROM [Assays] WHERE (updated = 1))
BEGIN
DECLARE @rec_id AS int, @ar_id AS int, @source AS varchar
(50), @timestamp AS datetime
DECLARE @cu AS real, @fe AS real, @s AS real, @sio2 AS
real, @cao AS real
DECLARE @al2o3 AS real, @mgo AS real, @as AS real, @pb AS
real, @bi AS real
DECLARE @se AS real, @sb AS real, @fe3o4 AS real, @so4 AS
real, @moisture AS real
DECLARE @status AS int, @reviewed AS bit, @manual AS bit,
@updated AS bit
DECLARE @protected AS bit

DECLARE cur CURSOR FORWARD_ONLY FOR
SELECT * FROM [Assays] WHERE updated = 1
FOR UPDATE OF ar_id, updated;
OPEN cur;
FETCH NEXT FROM cur INTO
@rec_id, @ar_id, @source, @timestamp, @cu, @fe, @s,
@sio2, @cao, @al2o3,
@mgo, @as, @pb, @bi, @se, @sb, @fe3o4, @so4,
@moisture, @status, @reviewed,
@manual, @updated, @protected;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ar_id = 0
BEGIN
INSERT INTO [KUCLABSQL].[FurnaceDB].[dbo].
[fcf_assays](
[source], [timestamp], [cu], [fe], [s],
[sio2], [cao],
[al2o3], [mgo], [_as], [pb], [bi], [se],
[sb], [fe3o4],
[so4], [moisture], [status], [reviewed],
[manual], [updated],
[protected])
VALUES (
@source, @timestamp, @cu, @fe, @s,
@sio2, @cao, @al2o3,
@mgo, @as, @pb, @bi, @se, @sb, @fe3o4,
@so4, @moisture,
@status, @reviewed, @manual, 0,
@protected);
UPDATE [Assays] SET
ar_id = (SELECT MAX(arec_id) FROM
[KUCLABSQL].[FurnaceDB].[dbo].[fcf_assays]),
updated = 0 WHERE CURRENT OF cur;
END;
ELSE
BEGIN
UPDATE [KUCLABSQL].[FurnaceDB].[dbo].
[fcf_assays] SET
[source] = @source, [timestamp] =
@timestamp, [cu] = @cu,
[fe] = @fe, [s] = @s, [sio2] = @sio2,
[cao] = @cao, [al2o3] = @al2o3,
[mgo] = @mgo, [_as] = @as, [pb] = @pb,
[bi] = @bi, [se] = @se,
[sb] = @sb, [fe3o4] = @fe3o4, [so4] =
@so4, [moisture] = @moisture,
[status] = @status, [reviewed] =
@reviewed, [manual] = @manual,
[protected] = @protected
WHERE (arec_id = @ar_id);
UPDATE [Assays] SET updated = 0 WHERE CURRENT
OF cur;
END;
FETCH NEXT FROM cur INTO
@rec_id, @ar_id, @source, @timestamp, @cu,
@fe, @s, @sio2,
@cao, @al2o3, @mgo, @as, @pb, @bi, @se, @sb,
@fe3o4, @so4,
@moisture, @status, @reviewed, @manual,
@updated, @protected;
END;
CLOSE cur;
DEALLOCATE cur;
END;

GO

[Narayana Vyas Kondreddi]

David, you are using a cursor and archiving the data row by row, which is
bound to be slow. I'd suggest you use SET based updates. Here's a pointer to
start with: [url]http://vyaskn.tripod.com/sql_archive_data.htm[/url]

--
HTH,
Vyas, MVP (SQL Server)
[url]http://vyaskn.tripod.com/[/url]


"David Taylor" <NOSPAMdave@processeng.com> wrote in message
news:005901c347ca$1b391ec0$a401280a@phx.gbl...
I have a VB app that used Access locally and a Sybase
server to "archive" the local records. I am now
converting it to using SQL Server/Desktop Engine locally
and SQL Server for the archive. The local database
contains a field for storing the rec_id (identity) field
from the archive database. The problem is, the updates to
the archive database are taking a VERY LONG time to
execute, like 10-15 minutes for updating 500 records or so.

The Transact-SQL code to run the update for one of the
tables is shown below. Initially, I tried joining the
tables (local to archive) but that took even longer
because of the number of records in the archive database.

Is there a better way to do this?

Thanks for any tips.


Dave Taylor


CREATE PROCEDURE UpdateAssaysArchive AS
IF EXISTS(SELECT * FROM [Assays] WHERE (updated = 1))
BEGIN
DECLARE @rec_id AS int, @ar_id AS int, @source AS varchar
(50), @timestamp AS datetime
DECLARE @cu AS real, @fe AS real, @s AS real, @sio2 AS
real, @cao AS real
DECLARE @al2o3 AS real, @mgo AS real, @as AS real, @pb AS
real, @bi AS real
DECLARE @se AS real, @sb AS real, @fe3o4 AS real, @so4 AS
real, @moisture AS real
DECLARE @status AS int, @reviewed AS bit, @manual AS bit,
@updated AS bit
DECLARE @protected AS bit

DECLARE cur CURSOR FORWARD_ONLY FOR
SELECT * FROM [Assays] WHERE updated = 1
FOR UPDATE OF ar_id, updated;
OPEN cur;
FETCH NEXT FROM cur INTO
@rec_id, @ar_id, @source, @timestamp, @cu, @fe, @s,
@sio2, @cao, @al2o3,
@mgo, @as, @pb, @bi, @se, @sb, @fe3o4, @so4,
@moisture, @status, @reviewed,
@manual, @updated, @protected;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ar_id = 0
BEGIN
INSERT INTO [KUCLABSQL].[FurnaceDB].[dbo].
[fcf_assays](
[source], [timestamp], [cu], [fe], [s],
[sio2], [cao],
[al2o3], [mgo], [_as], [pb], [bi], [se],
[sb], [fe3o4],
[so4], [moisture], [status], [reviewed],
[manual], [updated],
[protected])
VALUES (
@source, @timestamp, @cu, @fe, @s,
@sio2, @cao, @al2o3,
@mgo, @as, @pb, @bi, @se, @sb, @fe3o4,
@so4, @moisture,
@status, @reviewed, @manual, 0,
@protected);
UPDATE [Assays] SET
ar_id = (SELECT MAX(arec_id) FROM
[KUCLABSQL].[FurnaceDB].[dbo].[fcf_assays]),
updated = 0 WHERE CURRENT OF cur;
END;
ELSE
BEGIN
UPDATE [KUCLABSQL].[FurnaceDB].[dbo].
[fcf_assays] SET
[source] = @source, [timestamp] =
@timestamp, [cu] = @cu,
[fe] = @fe, [s] = @s, [sio2] = @sio2,
[cao] = @cao, [al2o3] = @al2o3,
[mgo] = @mgo, [_as] = @as, [pb] = @pb,
[bi] = @bi, [se] = @se,
[sb] = @sb, [fe3o4] = @fe3o4, [so4] =
@so4, [moisture] = @moisture,
[status] = @status, [reviewed] =
@reviewed, [manual] = @manual,
[protected] = @protected
WHERE (arec_id = @ar_id);
UPDATE [Assays] SET updated = 0 WHERE CURRENT
OF cur;
END;
FETCH NEXT FROM cur INTO
@rec_id, @ar_id, @source, @timestamp, @cu,
@fe, @s, @sio2,
@cao, @al2o3, @mgo, @as, @pb, @bi, @se, @sb,
@fe3o4, @so4,
@moisture, @status, @reviewed, @manual,
@updated, @protected;
END;
CLOSE cur;
DEALLOCATE cur;
END;

GO