ADO Update queries

[cable]

I've got the following code, and its taking a while to run, so I was checking to see if there is anything I can change in the .execute's to help the speed.
Or optimize the update queries in anyway, or maybe the tables?

The connection is an access 97 database, and on 21000 rows this takes 94secs to run.

        'outstanding balance
        If rs!OutBalance Then
            sSQL = "UPDATE " & rs!Tablename & " SET [OutBalance]=[Amount] WHERE [FlowDate]>=#" & sSight
            gcxCool.Execute sSQL, lAff, adCmdText
            LogFile "Prog", rs!Tablename & " has " & lAff & " amounts outstanding"
        End If
        
        'sight balance
        sSQL = "UPDATE " & rs!Tablename & " SET [Balance]=[Amount] WHERE [FlowDate]=#" & sSight
        gcxCool.Execute sSQL, lAff, adCmdText
        LogFile "Prog", rs!Tablename & " has " & lAff & " amounts at 0 days"
        
        '7 day
        sSQL = "UPDATE " & rs!Tablename & " SET [Balance7]=[Amount] WHERE [FlowDate] BETWEEN #" & sSight & " AND #" & s7Days
        gcxCool.Execute sSQL, lAff
        LogFile "Prog", rs!Tablename & " has " & lAff & " amounts at 7 days"
        
        '30 day
        sSQL = "UPDATE " & rs!Tablename & " SET [Balance30]=[Amount] WHERE [FlowDate] BETWEEN #" & sSight & " AND #" & s30Days
        gcxCool.Execute sSQL, lAff
        LogFile "Prog", rs!Tablename & " has " & lAff & " amounts at 30 days"
        
        'guesstimate the 90 day value?
        If rs![90DayCalc] Then
            sSQL = "UPDATE " & rs!Tablename & " SET [Balance90]=[Balance30]*3"
            gcxCool.Execute sSQL, lAff
            LogFile "Prog", rs!Tablename & " 90 day amount is 30 day amount x 3"
        Else
            sSQL = "UPDATE " & rs!Tablename & " SET [Balance90]=[Amount] WHERE [FlowDate] BETWEEN #" & sight & " AND #" & s90Days
            gcxCool.Execute sSQL, lAff
            LogFile "Prog", rs!Tablename & " has " & lAff & " amounts at 90 days"
        End If

 

[cable]

Right,

The table structure is flowdate, amount, balance, balance7, balance30 and balance90.

I did have a go at a single query, but balance7 is updated based on different flowdate to balance, so I got lost trying to write it.

Yes the trialing # is in the date string var, because it was doing a dateadd, converting to US format, every run and it only needed to do this once.

I doubt the database is normallized, there isn't any real relation between the tables.

There are multiple tables with the fields above, but they are different data sets, so other fields in the table might not match, and the fields might not be in the same order.

The system is recieving data from a number of different sources, and you can add/remove sources without needing to alter the code. so things like the rs!outbalance and the 90daycalc are bool flags to tell the code to to certain things.

So would adding an index to the table help anyways? would indexing flowdate help?

Could I create the query def and then run it? would that help?

This part of the code is my largest time drain (takes 94s for 21k rows), the other, csv import is 20s for 21k. I've not got a feel for the total number of tables/rows this is going to be run on yet, so it might end up not being practical to optimize this bit, but I was just checking for any simple changes i can make.

 

[cable]

thanks for that, will add the index, and keep an eye on it...if it stays at the current speed I'll be ok I think