[Solved] Show ETA On a Mysql function

Post your Gambas programming questions here.
Post Reply
AndyGable
Posts: 359
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

[Solved] Show ETA On a Mysql function

Post by AndyGable »

Hi All

I have the following MySQL Function that I use to copy my Product table from the Server to the Local System's database

    Dim DataResult As Result

    Global.DisplaySystemMessage("Processing Please wait...")
   
    i = 1

    $Query = Null
    $Query &= "Select "
    $Query &= "barcodenumber, "
    $Query &= "posdescription, "
    $Query &= "shelfedgelabeldescription, "
    $Query &= "salelocation, "
    $Query &= "subcatnumber, "
    $Query &= "agerestricted, "
    $Query &= "agelimit, "
    $Query &= "pricetype, "
    $Query &= "retailprice, "
    $Query &= "retailvatprice, "
    $Query &= "vatcode, "
    $Query &= "stockcontrolyn, "
    $Query &= "trackcolor, "
    $Query &= "tracksize, "
    $Query &= "print_guarantee_message, "
    $Query &= "print_guarantee_code, "
    $Query &= "displaymessage, "
    $Query &= "messagenumber, "
    $Query &= "sendtoppr, "
    $Query &= "requestserial, "
    $Query &= "itemnotallowed, "
    $Query &= "itemnotallowed_reason, "
    $Query &= "restrict_product_qty, "
    $Query &= "product_qty_allowed, "
    $Query &= "discount_not_allowed, "
    $Query &= "no_refund_allowed, "
    $Query &= "healthy_start_voucher_ok, "
    $Query &= "ask_for_qty_before_selling, "
    $Query &= "priceoverride_not_allowed "
    $Query &= "from producttable "
    $Query &= "order by barcodenumber ASC;"

    DataResult = Global.$DBCon.Exec($Query)

    If DataResult.Available = True Then
        Global.AddToListBox("Downloading data from the product table")
         
        With FMain.ProgressBar1
            .Value = 1
            .Visible = True
        End With

        For Each DataResult
            LocalSQL = Null
            LocalSQL &= "Insert into producttable ("
            LocalSQL &= "barcodenumber, "
            LocalSQL &= "posdescription, "
            LocalSQL &= "shelfedgelabeldescription, "
            LocalSQL &= "salelocation, "
            LocalSQL &= "subcatnumber, "
            LocalSQL &= "agerestricted, "
            LocalSQL &= "agelimit, "
            LocalSQL &= "pricetype, "
            LocalSQL &= "retailprice, "
            LocalSQL &= "retailvatprice, "
            LocalSQL &= "vatcode, "
            LocalSQL &= "stockcontrolyn, "
            LocalSQL &= "trackcolor, "
            LocalSQL &= "tracksize, "
            LocalSQL &= "print_guarantee_message, "
            LocalSQL &= "print_guarantee_code, "
            LocalSQL &= "displaymessage, "
            LocalSQL &= "messagenumber, "
            LocalSQL &= "sendtoppr, "
            LocalSQL &= "requestserial, "
            LocalSQL &= "itemnotallowed, "
            LocalSQL &= "itemnotallowed_reason, "
            LocalSQL &= "restrict_product_qty, "
            LocalSQL &= "product_qty_allowed, "
            LocalSQL &= "discount_not_allowed, "
            LocalSQL &= "no_refund_allowed, "
            LocalSQL &= "healthy_start_voucher_ok, "
            LocalSQL &= "ask_for_qty_before_selling, "
            LocalSQL &= "priceoverride_not_allowed) "
            LocalSQL &= "VALUES ('"
            LocalSQL &= DataResult!barcodenumber & "','"
            LocalSQL &= Replace(Replace(DataResult!posdescription, "'", "''"), ",", ",,") & "','"
            LocalSQL &= Replace(Replace(DataResult!shelfedgelabeldescription, "'", "''"), ",", ",,") & "','"
            LocalSQL &= DataResult!salelocation & "','"
            LocalSQL &= DataResult!subcatnumber & "','"
            LocalSQL &= DataResult!agerestricted & "','"
            LocalSQL &= DataResult!agelimit & "','"
            LocalSQL &= DataResult!pricetype & "','"
            LocalSQL &= DataResult!retailprice & "','"
            LocalSQL &= DataResult!retailvatprice & "','"
            LocalSQL &= DataResult!vatcode & "','"
            LocalSQL &= DataResult!stockcontrolyn & "','"
            LocalSQL &= DataResult!trackcolor & "','"
            LocalSQL &= DataResult!tracksize & "','"
            LocalSQL &= DataResult!print_guarantee_message & "','"
            LocalSQL &= DataResult!print_guarantee_code & "','"
            LocalSQL &= DataResult!displaymessage & "','"
            LocalSQL &= DataResult!messagenumber & "','"
            LocalSQL &= DataResult!sendtoppr & "','"
            LocalSQL &= DataResult!requestserial & "','"
            LocalSQL &= DataResult!itemnotallowed & "','"
            LocalSQL &= Replace(Replace(DataResult!itemnotallowed_reason, "'", "''"), ",", ",,") & "','"
            LocalSQL &= DataResult!restrict_product_qty & "','"
            LocalSQL &= DataResult!product_qty_allowed & "','"
            LocalSQL &= DataResult!discount_not_allowed & "','"
            LocalSQL &= DataResult!no_refund_allowed & "','"
            LocalSQL &= DataResult!healthy_start_voucher_ok & "','"
            LocalSQL &= DataResult!ask_for_qty_before_selling & "','"
            LocalSQL &= DataResult!priceoverride_not_allowed & "')"

            DatabaseFunctions.SendToLocalDatabase(LocalSQL)
            Dim RemaingValue As Integer = DataResult.Count - I

            FMain.ProgressBar1.Value = i / DataResult.Count
            Global.DisplaySystemMessage("Processing Record " & Trim(Format(i, "###,###,##0")) & " of " & Trim(Format(DataResult.Count, "###,###,##0")) & " Reaming to Process " & Trim(Format(RemaingValue, "###,###,##0")))
            i += 1
        Next
        
        If DataResult.Count = 1 Then 
            Global.AddToListBox("Downloaded a total of " & DataResult.Count & " record")
        Else
            Global.AddToListBox("Downloaded a total of " & DataResult.Count & " records")
        End If

    Else
        Global.AddToListBox("Nothing to Download from the product table") 
    End If
    
    With FMain.ProgressBar1
        .Value = 0
        .Visible = False
    End With

    ShowDottedLine
   
End


Does anyone know how I can show a ETA on the screen (example Processing Record 1 of 12,999 & " Reaming to Process 12,998 (ETA 05:00 Mins)

or could someone point me in the direction so I can read up on how to show a Est time remaining

I was working on 2 seconds a record so that would be 12999 *2 = 25,998 then divide by 60 (minute) = 433.3 would that be mins or hours ?
Last edited by AndyGable on Thursday 22nd December 2022 9:27pm, edited 1 time in total.
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Show ETA On a Mysql function

Post by Quincunxian »

The theory is that in data processes that take a long time, there can be many factors that will effect the actual time taken.
The source(Server) or recipient(Workstation), may have other background tasks that can take up cycle time and so your
solution for an ETC (Estimated time of completion) needs to be self adjusting.


START = Now() 'Get the start time.
Read a set number of records ' say 2 records ?
END = Now()
DURATION = DateDiff(START,END,Gb.Second) 'Record how long this takes in seconds - you could change to Gb.Milliseconds to suit?

ETC = DURATION * Records Remaining 'In seconds

Update Progress bar with records read and ETC converted to readable time.

That's the theory anyway...
Cheers - Quin.
I code therefore I am
AndyGable
Posts: 359
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: Show ETA On a Mysql function

Post by AndyGable »

Hi @Quincunxian

Thank you for that example I will see what I can can come up with

if i have something say like 56,003 seconds how would i convert that into minutes so I could display it on screen
User avatar
thatbruce
Posts: 161
Joined: Saturday 4th September 2021 11:29pm

Re: Show ETA On a Mysql function

Post by thatbruce »

You need to understand query planning.
For each query you send to the database it has to a) parse the query and formulate the most efficient approach, b) process the query and c) output a response. Around this wrap the latency due to network/other processes being scheduled etc. so
1) Updating the local database record by record is the most inefficient way to do this possibly imaginable as a) and c) are repeated each time
2) Basing a guestimate on one or two records will be inaccurate as in that case a) can sometimes be the major "cost" of the entire transaction.

thatbruce
Have you ever noticed that software is never advertised using the adjective "spreadable".
User avatar
BruceSteers
Posts: 1523
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: Show ETA On a Mysql function

Post by BruceSteers »

AndyGable wrote: Thursday 22nd December 2022 5:31pm Hi @Quincunxian

Thank you for that example I will see what I can can come up with

if i have something say like 56,003 seconds how would i convert that into minutes so I could display it on screen
you can use \ and % operators
\ gives the division without the remainder
% gives the remainder

the following will give a string like 15h33m23s for the 56003 seconds and omit showing any zero numbers

Public Sub SecsToMins(iSecs As Integer) As String

  Dim h, m, s As Integer, sStr As String

  m = iSecs \ 60
  s = iSecs % 60

  If m >= 60 Then
    h = m \ 60
    m = m % 60
  Endif

  If h Then sStr = h & "h"
  If m Then sStr &= m & "m"
  If s Then sStr &= s & "s"
  Return sStr

End

If at first you don't succeed , try doing something differently.
BruceS
AndyGable
Posts: 359
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: Show ETA On a Mysql function

Post by AndyGable »

BruceSteers wrote: Thursday 22nd December 2022 7:22pm
AndyGable wrote: Thursday 22nd December 2022 5:31pm Hi @Quincunxian

Thank you for that example I will see what I can can come up with

if i have something say like 56,003 seconds how would i convert that into minutes so I could display it on screen
you can use \ and $ operators
\ gives the division without the remainder
% gives the remainder

the following will give a string like 15h33m23s for the 56003 seconds and omit showing any zero numbers

Public Sub SecsToMins(iSecs As Integer) As String

  Dim h, m, s As Integer, sStr As String

  m = iSecs \ 60
  s = iSecs % 60

  If m >= 60 Then
    h = m \ 60
    m = m % 60
  Endif

  If h Then sStr = h & "h"
  If m Then sStr &= m & "m"
  If s Then sStr &= s & "s"
  Return sStr

End

Bruce that was just what I needed
Image

I know this is not fully accuate but it is just a guide :D and it works great thanks
User avatar
Cedron
Posts: 156
Joined: Thursday 21st February 2019 5:02pm
Location: The Mitten State
Contact:

Re: Show ETA On a Mysql function

Post by Cedron »

AndyGable wrote: Thursday 22nd December 2022 5:31pm if i have something say like 56,003 seconds how would i convert that into minutes so I could display it on screen
Here is an alternative way:

    Dim d As Date = 56003 / 86400  ' Seconds per Day 
    Print Format(d, "hh:nn:ss")
.... and carry a big stick!
Post Reply