Professional Web Applications Themes

Updating an 'archive' database - Microsoft SQL / MS SQL Server

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 ...

  1. #1

    Default Updating an 'archive' database

    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

    David Taylor Guest

  2. #2

    Default Re: Updating an 'archive' database

    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" <NOSPAMdaveprocesseng.com> wrote in message
    news:005901c347ca$1b391ec0$a401280aphx.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


    Narayana Vyas Kondreddi Guest

Similar Threads

  1. Updating Updating site map or archive dynamically
    By RooZam in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: September 15th, 04:45 PM
  2. updating database
    By MoparMarc in forum Coldfusion Database Access
    Replies: 6
    Last Post: July 14th, 11:41 PM
  3. Updating a Database
    By John H in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: August 19th, 10:45 PM
  4. Photo database archive (on line) ??
    By JennyC in forum Photography
    Replies: 1
    Last Post: September 12th, 06:42 AM
  5. Updating Database with ASP
    By Mithrandir webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 6
    Last Post: July 16th, 05:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139