dcsimg
Results 1 to 11 of 11

Thread: HELP - Eliminating the Do..Until loop very slow completion

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    20

    Thumbs up HELP - Eliminating the Do..Until loop very slow completion

    Hello,
    I need a vital help here.
    I have a VB6 app that has existed for more than 7 years to which i have a few clients using it for their accounting solutions. Now my problem is a particular client have grown so big with a 15GB database size, a ledger database table of more than 4 million rows, customer size of more than 100,000, ETC.

    I have a code in that app, where customers are debited for a flat rate after checking some parameters, for a client with a small or average database, it works just fine, but for this particular client, this code sometimes does not even complete in 6 hours and its becoming a serious headache and i need help !

    The codes looks like this .......
    Code:
    Select * from table Customers order by account number
    do until rst.eof
    
       select * from table purchase where account number = rst!account number
       if found 
       A1 = "YES"
       else
       A1 = "NO"
    
       A2 = new charges on form / 100
       A3 = rst!Balance + A2
     
       select count(*) from table receipts where account number = rst!account number and month = currentmonth and year = current year
       if count > 10 then
       goto VBSTOP
       else
       CONTINUE
    
       select * from table seller where account number = rst!account number
       if found 
       A4 = "YES"
       else
       A4 = "NO"
    
    insert into table ledger (Account number, Account name, A1, A3, A4, Charges)
    insert into table Charges (Account number, Account name, Charges)
    insert into table Receipts (Account number, Account name, Charges, Receipt Number)
    update table purchase (update purchaseno = purchaseno + 1 where account number = rst!account number
    
    rst.MoveNext
    loop
    .................................................................................................... .......

    This code takes more than 6 hours to complete for the client with a very large database and i am sincerely looking for help in solving my DO....UNTIL loop headache. Is there a way to optimize this code and eliminate the do until statement or make it run a very very lot faster ?

    I would appreciate every help from the bottom of my heart.
    Last edited by Shaggy Hiker; Feb 3rd, 2018 at 08:10 PM. Reason: Added CODE tags.

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    20

    Unhappy My DO...UNTIL Code runs too SLOW to Complete

    I need a vital help here.
    I have a VB6 app that has existed for more than 7 years to which i have a few clients using it for their accounting solutions. Now my problem is a particular client have grown so big with a 15GB database size, a ledger database table of more than 4 million rows, customer size of more than 100,000, ETC.

    I have a code in that app, where customers are debited for a flat rate after checking some parameters, for a client with a small or average database, it works just fine, but for this particular client, this code sometimes does not even complete in 6 hours and its becoming a serious headache and i need help !

    The real codes is posted here thus........
    Code:
        TT1 = "GOODS%"
        GClass = "10"
        Shuss = "Interest Declaration"
        
        Set rst = HConn.Execute("Select * from tblCInterest where AccID like '" & TT1 & "' order by AccID")
        If rst.BOF Then
           MsgBox " There Is No Existing Account Under That Type", vbInformation
           Exit Sub
        Else
        
        Set rstd = HConn.Execute("Select count(*) as Countss from tblCINT where AccID like '" & TT1 & "'")
        If rstd.EOF Then
        Else
        Label6.Caption = rstd!Countss
        End If
        
        Set rstG = HConn.Execute("select Balw from tblSerialCount")
        If rstG!Balw = "" Then
           MsgBox " You Have Not Setup The Percentage", vbInformation
           Exit Sub
        Else
        End If
        
        Set rstB = HConn.Execute("select srefno from tblSerialNo2")
        If rstB!srefno = 0 Then
           MsgBox " You Have Not Setup The Parameter For Maximum Sales in a Month Per Customer !", vbInformation
           Exit Sub
        Else
        End If
        
        Set rst2 = HConn.Execute("Select * from tblSerialNumbers")
        DPP = Val(rst2!Int1) + 1
        Trump = "INT" & GClass & "/" & Val(rst2!Int1) + 1
        
        DAF:
        Set rst2T = HConn.Execute("Select * from tblLedger where GREF = '" & Trump & "'")
        If rst2T.BOF Then
        Else
        DPP = Val(DPP) + 1
        Trump = "INT" & GClass & "/" & DPP
        GoTo DAF
        End If
        
        HConn.Execute ("update tblSerialNumbers set Int1 ='" & Val(DPP) & "'")
        
        Muka = Trump
        GD = Trump
        OD1 = 0
        
        
        On Error GoTo ErrHandler
        Do Until rst.EOF
        BAL1 = 0
        BAL2 = 0
        PBar.Value = PBar.Value + 1
        'INT
        Joe = Val(Trim(Text1.Text)) / 100
        Joe6 = Val(rst!CustBaL1)
        Joe2 = Round(((Joe * rst!CustBaL1) / 12), 2)
        'WHT
        If Val(rstG!Balw) > 0 Then
        TanG = "Total Tax For " & Dew2 & " " & Year(Date)
        Tan6 = Val(rstG!Balw) / 100
        Tan5 = Round(Val(Joe2) * Tan6, 2)
        Else
        TanG = "Total Tax on Interest Paid For " & Dew2 & " " & Year(Date)
        Tan5 = 0
        End If
        
        
        Set rstB = HConn.Execute("select srefno from tblSerialNo2")
        If rstB!srefno = 0 Then
        Else
           Set rstc = HConn.Execute("select count(*) as Bety from tblSalesR where AccID = '" & rst!AccID & "' and Wmonth = '" & Joe4 & "' and Wyear = '" & Joe5 & "' ")
           If rstc!bety > Val(rstB!srefno) Then
              GoTo Dante
           Else
           End If
        End If
        
        Set rst15 = HConn.Execute("Select Blocked from tblClients where AccID = '" & rst!AccID & "'")
        If rst15.BOF Then
        GoTo Dante
        Else
         if rst15!Blocked = "YES" then
            GoTo Dante
         else
         end if
        end if
        
        Set rst2 = HConn.Execute("Select * from tblClientInterest where Acctno = '" & rst!AccID & "' and period = '" & Month(Date) & "' and IntYear = '" & Year(Date) & "' ")
        If rst2.BOF Then
        Else
        GoTo Dante
        End If
        
        Set rst15 = HConn.Execute("Select Bal101,CCode from tblClients where AccID = '" & rst!AccID & "'")
        If rst15.BOF Then
        GoTo Dante
        Else
        
        BAL1 = Val(rst15!Bal101)
        
        'MINIMUM BALANCE SETUP
        Set rstF = HConn.Execute("select Flopno from tblSerialNo2")
        If Val(rstF!flopno) >= 0 Then
           If Val(BAL1) < Val(rstF!flopno) Then
              GoTo Dante
           End If
        Else
        GoTo Dante
        End If
        
        Joe3 = Round((Val(rst15!Bal101) + Joe2) - Tan5, 2)
        Joe4 = Month(Date)
        Joe5 = Year(Date)
        
        BranchT = rst15!CCode
        End If
        
        If Val(Joe2) < 0 Then
           GoTo Dante
        End If
        
        'ELIMINATE ZERO BALANCE
        If Val(BAL1) < 0 Then
           GoTo Dante
        End If
        
        
        Set rst11 = HConn.Execute("select * from tblAcctClasses where Acct='" & DataCombo1.Text & "' and Bcode = '" & BranchT & "'")
        If rst11.BOF Then
        MsgBox " The Client Type Does Not Exist In The Database ! ", vbInformation
        GoTo Dante
        Else
        TT11 = rst11!ACCTCODE
        TUID = rst11!ACCTCODE
        End If
        
        
        
        GOVAcct = rst!AccID
        GOVRef = Muka
        
        
        'INT
        DBAL1 = Round(Val(BAL1), 2)
        HConn.Execute ("insert into tblLedger values ('" & Replace(rst!ClientName, "'", "''") & "','" & rst!AccID & "','" & Replace(DataCombo1, "'", "''") & "','" & Year(Date) & "','" & Date & "','" & Shuss & "','" & Muka & "','0','" & Val(Joe2) & "','0','0','0','','" & Replace(PostUser, "'", "''") & "','" & Format(time, "HH:MM:SS Am/Pm") & "')")
        BAL2 = Round(Val(BAL1) + Val(Joe2), 2)
        HConn.Execute ("Update tblClients set Bal101='" & BAL2 & "' where AccID = '" & rst!AccID & "'")
        
        
        If Val(DBAL1) >= 0 Then
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & TT11 & "','" & Replace(Shuss, "'", "''") & "','" & Muka & "','0','" & Val(Joe2) & "','" & Replace(PostUser, "'", "''") & "')")
        ElseIf Val(DBAL1) < 0 Then
        If Val(DBAL1) + Val(Joe2) <= 0 Then
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & C5 & "','" & Replace(Shuss, "'", "''") & "','" & Muka & "','0','" & Val(Joe2) & "','" & Replace(PostUser, "'", "''") & "')")
        GOVVal = Val(Joe2)
        PERFCHECK4
        Else
        CBAL1 = Val(DBAL1) * -1
        CBAL2 = Round(Val(Joe2) + Val(DBAL1), 2)
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & TT11 & "','" & Replace(Shuss, "'", "''") & "','" & Muka & "','0','" & Val(CBAL2) & "','" & Replace(PostUser, "'", "''") & "')")
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & C5 & "','" & Replace(Shuss, "'", "''") & "','" & Muka & "','0','" & Val(CBAL1) & "','" & Replace(PostUser, "'", "''") & "')")
        GOVVal = Val(CBAL1)
        PERFCHECK4
        End If
        End If
        
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & C6 & "','" & Shuss & "','" & Muka & "','" & Val(Joe2) & "','0','" & Replace(PostUser, "'", "''") & "')")
        
        
        'TAX
        
        BAL1 = Val(CustBaL1(rst!AccID))
        BalTax = Val(BAL1)
        BalTax2 = Round(Val(BalTax) - Val(Tan5), 2)
        
        HConn.Execute ("insert into tblLedger values ('" & Replace(rst!ClientName, "'", "''") & "','" & rst!AccID & "','" & Replace(DataCombo1, "'", "''") & "','" & Year(Date) & "','" & Date & "','" & TanG & "','" & Muka & "','" & Val(Tan5) & "','0','0','0','0','','" & Replace(PostUser, "'", "''") & "','" & Format(time, "HH:MM:SS Am/Pm") & "')")
        BAL2 = Round(Val(BAL1) - Val(Tan5), 2)
        HConn.Execute ("Update tblClients set Bal101='" & BAL2 & "' where AccID = '" & rst!AccID & "'")
        
        
        If Val(BalTax) <= 0 Then
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & C5 & "','" & TanG & "','" & Muka & "','" & Val(Tan5) & "','0','" & Replace(PostUser, "'", "''") & "')")
        GOVVal = Val(Tan5)
        PERFCHECK3
        ElseIf Val(BalTax) > 0 And Val(BalTax2) < 0 Then
        CBAL1 = Val(BalTax)
        CBAL2 = Round(Val(Tan5) - Val(CBAL1), 2)
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & TT11 & "','" & TanG & "','" & Muka & "','" & Val(CBAL1) & "','0','" & Replace(PostUser, "'", "''") & "')")
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & C5 & "','" & TanG & "','" & Muka & "','" & Val(CBAL2) & "','0','" & Replace(PostUser, "'", "''") & "')")
        GOVVal = Val(CBAL2)
        PERFCHECK3
        Else
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & TT11 & "','" & TanG & "','" & Muka & "','" & Val(Tan5) & "','0','" & Replace(PostUser, "'", "''") & "')")
        End If
        
        HConn.Execute ("insert into tblBookLedgerTemp values('" & Year(Date) & "','" & Date & "','" & C24 & "','" & TanG & "','" & Muka & "','0','" & Val(Tan5) & "','" & Replace(PostUser, "'", "''") & "')")
        
        
        HConn.Execute ("insert into tblClientInterest values ('" & Replace(rst!ClientName, "'", "''") & "','" & rst!AccID & "','" & Date & "','" & Joe2 & "','" & Joe4 & "','" & Year(Date) & "','" & Replace(PostUser, "'", "''") & "','" & Muka & "','')")
        
        
        Dante:
        rst.MoveNext
        
        Loop
        
        HConn.Execute ("insert into tblClientInterestdone values('" & Month(Date) & "','" & Year(Date) & "','" & Replace(PostUser, "'", "''") & "','" & DataCombo1.Text & "')")
        End If
        
        
        'SECOND SET
        
        Set rst15 = HConn.Execute("Select distinct AccID as AccID from tblBookLedgerTemp order by AccID ")
        If rst.BOF Then
        Else
        Do Until rst15.EOF
        
        Set rst16 = HConn.Execute("Select sum(credit) - sum(debit) as Bal from tblBookLedgerTemp where AccID = '" & rst15!AccID & "'")
        AB1 = Round(IIf(IsNull(rst16!Bal), 0, rst16!Bal), 2)
        
        Set rst2 = HConn.Execute("Select * from tblBookLedgerTemp Where AccID = '" & rst15!AccID & "' ")
        Shuss = Mudas '& " " & DataCombo1.Text
        
        If Val(AB1) > 0 Then
           HConn.Execute ("Insert into tblBookLedger values ('" & rst2!gPeriod & "','" & rst2!PostDate & "','" & rst15!AccID & "','" & Replace(Shuss, "'", "''") & "','" & rst2!GREF & "','0','" & Val(AB1) & "','" & rst2!Puser & "')")
        ElseIf Val(AB1) < 0 Then
           HConn.Execute ("Insert into tblBookLedger values ('" & rst2!gPeriod & "','" & rst2!PostDate & "','" & rst15!AccID & "','" & Replace(Shuss, "'", "''") & "','" & rst2!GREF & "','" & Val(AB1) * -1 & "','0','" & rst2!Puser & "')")
        Else
        End If
        
        rst15.MoveNext
        Loop
        End If
        
        
        MsgBox " The Interest Have Been Completed !", vbInformation
    
    
    ...........................................................................................................
    * I made an earlier post but was told to post real code to get help, that's why i am posting real code in this new thread.

    This code takes more than 6 hours to complete for the client with a very large database and i am sincerely looking for help in solving my DO....UNTIL loop headache. Is there a way to optimize this code and eliminate the do until statement or make it run a very very lot faster ?

    I would appreciate every help from the bottom of my heart.
    Last edited by Shaggy Hiker; Feb 3rd, 2018 at 08:11 PM. Reason: Added CODE tags

  3. #3
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,279

    Re: HELP - Eliminating the Do..Until loop very slow completion

    I have a feeling you might be able to compose your SQL into a single statement, but I don't have time to dig into it right now.

    Couple of general things -

    1) Do you have INDEXES on your AccountNumber fields in all the tables? If not, you should.

    2) For the "select * from table purchase where account number = rst!account number" and "select * from table seller where account number = rst!account number" queries - it looks like you are grabbing every row, but then just using the results to figure out if anything is found or not? If so, doing SELECT COUNT(*)... would be a better choice (and Found = TRUE if COUNT(*) > 0).

    3) Speaking of COUNT(*), with some databases COUNT(1) is faster. Might be worth a try.

    4) Transactions can improve performance in certain situations. It would be worth a try to start a new transaction at the beginning of the loop, and commit it before the rst.MoveNext at the bottom of the loop, you might see an improvement. It will ensure better data integrity in case of a failure anyway (for example, what happens now if something goes wrong after your first INSERT into the ledger table?). Check out the BeginTrans, CommitTrans, and RollbackTrans documentation for more information on how to use transactions.

  4. #4
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,132

    Re: HELP - Eliminating the Do..Until loop very slow completion

    Few thoughts...

    Right now it is unclear where goto VBSTOP takes you. I assume that it is bailing out of the loop. But even if it does, if you have 100,000 records in the Customers table and VBSTOP is not hit, you will be making at least 700,000 calls to the database before this block of code completes.

    Join your first 4 Selects. Doing so would eliminate 300,000 possible calls to the database. After combining the first 4 selects, convert that to a stored procedure.

    For your inserts and update, convert that to a single stored procedure. That could eliminate an additional 300,000 calls to the database.

    For the selects, do not do a Select *. Only select the columns you need.

  5. #5
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,279

    Re: HELP - Eliminating the Do..Until loop very slow completion

    Re: Transactions, I would experiment with the BeginTrans/CommitTrans within the loop vs. outside the loop to see if there is a noticeable difference, and depending on whether or not you want the entire run to rollback on failure or just the individual loop to rollback on failure.

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,221

    Re: HELP - Eliminating the Do..Until loop very slow completion

    You seem to be shoveling an awful lot of data over the network.

    Surely this could be radically optimized, either by moving procedural logic into the queries or if too complicated for that into stored procedures that run at the server?

  7. #7
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,082

    Re: HELP - Eliminating the Do..Until loop very slow completion

    I don't have time to un-scramble your pseudo-code, nor do I know what database you are using, but you can eliminate several trips to the database by simply doing something akin to the following (SQL Server air-code).

    Code:
    Select C.*, -- surely you don't need to fetch all this!!!?
              CASE WHEN P.account_number IS NULL 'NO' ELSE 'YES' END AS A1,
              CASE WHEN S.account_number IS NULL 'NO' ELSE 'YES' END AS A4
    FROM Customers C
    LEFT JOIN purchase P ON C.account_number = P.account_number
    LEFT JOIN seller S ON C.account_number = S.account_number
    order by C.account_number
    But, as others have said, a stored procedure looks like a better way to go....
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,301

    Re: My DO...UNTIL Code runs too SLOW to Complete

    you should edit the post to put the code into code tags, it would at least be readable then
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    748

    Re: My DO...UNTIL Code runs too SLOW to Complete

    This belongs in the Open Positions (Jobs) forum IMO. A One-Off project, looking for a VB 6.0 programmer with strong SQL experience that can help solve a severe performance issue when running a process against a large SQL Database or something like that. I can't imagine anyone spending serious time going line by line through this code and helping fix an issue of this magnitude for a commercial product for free. Just my opinion. If someone is so inclined, so be it.

    I'll give you one piece of feedback that you should be aware of that has nothing to do with your problem. Your variable names are problematic. Maybe they mean something to you, but now here you are posting your code for others to see, and now people that might be inclined to help you have to try to figure out what Joe, Joe3, Joe4, Joe5, Joe6, TanG, Tan5, Tan6, Shuss, Muka, and Trump are. Using meaningful variable names can obviously lead to longer variable names, but in a case where other eyes need to look at your code, they can help tremendously.
    Last edited by OptionBase1; Feb 3rd, 2018 at 06:06 PM.

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,869

    Re: HELP - Eliminating the Do..Until loop very slow completion

    Welcome to the forum. Please leave it at one thread per question. People did ask for more code, but it is fine to have it in the same thread.

    Also, I edited your posts to add [CODE][/CODE] tags. You can do this yourself by pressing the # button and pasting the code between the tags.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    20

    Re: My DO...UNTIL Code runs too SLOW to Complete

    Quote Originally Posted by OptionBase1 View Post
    This belongs in the Open Positions (Jobs) forum IMO. A One-Off project, looking for a VB 6.0 programmer with strong SQL experience that can help solve a severe performance issue when running a process against a large SQL Database or something like that. I can't imagine anyone spending serious time going line by line through this code and helping fix an issue of this magnitude for a commercial product for free. Just my opinion. If someone is so inclined, so be it.

    I'll give you one piece of feedback that you should be aware of that has nothing to do with your problem. Your variable names are problematic. Maybe they mean something to you, but now here you are posting your code for others to see, and now people that might be inclined to help you have to try to figure out what Joe, Joe3, Joe4, Joe5, Joe6, TanG, Tan5, Tan6, Shuss, Muka, and Trump are. Using meaningful variable names can obviously lead to longer variable names, but in a case where other eyes need to look at your code, they can help tremendously.
    Hey Bro, following your advice, i have posted the request for help and the whole codes i needed help with in the Open Positions (Jobs) forum, i would pay for the services. I need urgent help with it today.

    Thanks everyone that want to help....Pls goto the Open Positions (Jobs) forum and check my post and get paid for the help.

    Thanks !!!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width