Speeding Update Process Of A Database

  • Hi,


    Did not want to hijack Jonny's thread about ways in which to update and streamline code to make it run faster. The update speed of my first attempt at a macro is woefully slow - 5 minutes for 2.5K records. I was wondering if some of you more knowledgeable folk could look at my code and make suggestions as to how it could be modified to run faster.


    When I ctrl-break out of it I usually end up in a private sub that concatenates columns A & B in Column C (see below). When it resorts the database it must keep triggering this concatenate sub which (I think) is slowing things down. Thanks go to Cringey for his suggestions in the other thread.


    Concatenate Private Sub:



    Sort Database Module:Sorts database by date & smallest balance total. Also groups records by name of pupil.



    UpdateDatabase module: Updates a balance running total in column H. Calculates smallest running balance value by record (person).


    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: Speeding Update Process Of A Database


    Upside


    I would look for repeat calls to Something.SomethingElse and use a "with/end with" wherever possible ie:


    [vba]


    with application
    calcMode = .Calculation
    updateMode = .ScreenUpdating
    .Calculation = xlManual
    .ScreenUpdating = False
    end with


    with DBASE
    .Activate
    .AutoFilterMode = False
    .Range(Range("rDataBase").Address, Range("J" & Range("A" & Rows.Count).End(xlUp).Row).Address).Sort _
    Key1:=Range("rChristName"), Order1:=xlAscending, Key2:=Range("rSurname") _
    , Order2:=xlAscending, Key3:=Range("rDate"), Order3:=xlAscending, Header:= _
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


    end with


    with application
    .Calculation = calcMode
    .ScreenUpdating = updateMode
    .Calculate
    end with
    [/vba]



    Cheers,


    dr

  • Re: Speeding Update Process Of A Database


    This doesn't make any sense--to me, at least. It looks like you're setting calcMode to application.calculation . Then at the bottom you're saying the (calcMode) application.calculation = application.calculation. The same can be said of the ScreenUpdating. Also, I don't think there's a need to calculate when it is reset to automatic calculation, as it does it right then.


    In any case, I think it should be something like this:



  • Re: Speeding Update Process Of A Database


    Go into your Visual Basic Editor, click inside your macro, change the size of the window to half-size (so that you can also see your worksheet), then hit F8 a bunch of times so you can see what's going on inside your sheet while you advance the macro one line at a time.


    If it's nothing, then perhaps you've got a lot of formulas that are taking up your system resources (e.g., Arrays, Vlookups, Info, Indirect, etc.).


    There's no way it should be taking about 5 minutes for whatever you're doing.


  • Re: Speeding Update Process Of A Database


    Thanks for the input. Stepping through the code it appears the Private Sub to concatenate is being executed after this part of the code in the UpdateDatabase() sub, which updates a balance running total in column H.


    Code
    rCell = Evaluate("IF(" & rCell.Offset(0, -1).Value & _
                ">0,SUMPRODUCT(-(" & rOne.Address & "<0)*0.5,--(" & rTwo.Address & _
                "=" & rCell.Offset(0, -5).Address & "))+SUMPRODUCT(--(" & _
                rThree.Address & ">0),--(" & rFour.Address & "=" & rCell.Offset _
                (0, -5).Address & ")," & rFive.Address & "),0)")


    The action of concatenating already joined & sorted cells seems to be slowing it down as it doesn't actually change anything on screen. Any way to stop the Private Sub() below from firing when the code above is executed?


    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: Speeding Update Process Of A Database


    Insert these two lines beginning and end of your sub:


    [vba]
    application.enableevents = false


    ...code here...


    application.enableevents = true
    [/vba]


    Cheers,


    dr

  • Re: Speeding Update Process Of A Database


    Thanks RB.


    Would you (or any other Ozgridders for that matter) mind take a look at the sub below to see if there's any 'fat' I can trim off it that will make it run faster?


    I have read somewhere that both arrays and SUMPRODUCT are slow to calculate


    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: Speeding Update Process Of A Database


    Ok modified code together with a sample workbook. For 2.5K rows it took 7 mins on my machine. I think the arrays and sumproduct are slowing things down. Also says here: http://www.ozgrid.com/VBA/VBALoops.htm to avoid loops if possible but most of the code below was produced by JimFuller and I wouldn't know where to start modifying it to remove the 2 For each rCell loops in the Update_Database module below. Any suggestions as to how to get rid of the 2 For Each rCell loops for a faster version?



    Sort_Database module now looks like this



    Update_Database module


  • Re: Speeding Update Process Of A Database


    If you can avoid arrays and the sumproduct, by all means, do so.


    If you can't, try inserting code to copy/paste-special/value immediately after the sumproduct formula is added to replace it within your loop (if that will still work for you); and then see if that doesn't speed things up a little, as at least the spreadsheet won't have to recalculate all those values constantly. Will be curious to see.


    Mark


    Quote from Upside


    I have read somewhere that both arrays and SUMPRODUCT are slow to calculate

  • Re: Speeding Update Process Of A Database


    Quote from MrkFrrl

    If you can avoid arrays and the sumproduct, by all means, do so.


    If you can't, try inserting code to copy/paste-special/value immediately after the sumproduct formula is added to replace it within your loop (if that will still work for you); and then see if that doesn't speed things up a little, as at least the spreadsheet won't have to recalculate all those values constantly. Will be curious to see.


    Mark


    Not really sure what you mean by that as the code was written by someone with lots more experience than me. Would you mind giving me an example?The macro fires when a button is pushed.

    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: Speeding Update Process Of A Database


    Upside,


    do you really need to size rOne, rTwo, rSix to 35000 rows? It is a monstrous size and having evtl less rows to analize, kills your performance.


    Could you post a more clear and completed workbook ( maybe with some of the formulas written in the spreadsheet ) so to get a better feeling of what your trying to achieve?


    filippo

  • Re: Speeding Update Process Of A Database


    Thanks, that has sped things up a LOT. To be honest there aren't any other formulae in the database itself except for a Vlookup which is fired using an on-event macro.


    The database is designed to hold the details for detention owing & served (how many hours each pupil owes is held as a running balance in row H). The J column determines their smallest balance total (representing the detention date furthest in the past).

    I am new to VBA - comments on how to improve my code are always welcome.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!