Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Trying to keep process speed constant

  1. #1
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658

    Trying to keep process speed constant

    Hello,

    In my program, I have a canvas section with a command button.
    When the button is clicked, I access a local database then transfer the rows of data to an excel sheet.
    So far it's all working fine except for the processing speed.

    It starts off by processing approx. 4 rows every second. Shortly afterwards, the process slows down.
    By the time the process ends, it was processing one row approx. every 1.5 seconds.

    I've tried with various amount of records (100, 1000, 5000).
    The results are all fairly similar although the more data there is, the slower it becomes.
    When I tried yesterday with 5000 records, it took almost 4.5 hours to complete.

    Any suggestions on how I can keep the processing speed consistent?

    Thanks,
    Cory
    Last edited by CGibson; 2015-02-06 at 13:57.

  2. #2
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    Here is more information on my processes:

    Code:
    scan
      scatter to ExcelExport
      WITH oExcel
        .Range("A" + ALLTRIM(str(excelRowCount))).Value = ALLTRIM(LNAME)
        ...
      ENDWITH
    
      // Activate another table
      select ...
      append blank
      gather FROM ExcelExport
      replace DATEARCHIVED with date()
      skip 0
    		
      excelRowCount = excelRowCount + 1
      progBarCount = progBarCount + 1
      Lianja.Get("page.section.pbExport").value = progBarCount
    endscan

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    There is insufficient explanation of what you are doing and where the data is coming from and going to. Is it on a network drive or what?

    obviously that's not normal behavior.

    you need to comment out sections of your code to isolate the problem.

    start with excel.

    you don't mention how many records, just show a small snippet of code.

    It would be better if you submit a ticket after you have isolated what is causing this slowness, then we can look into it.
    Last edited by barrymavin; 2015-02-06 at 17:05.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,071
    Hi Cory,

    excel is almost certainly the culprit. I would put timing in the different sections, so you can see what's actually slowing you down.

    If excel is indeed the issue, see if you can issue one command to excel that exports the data you need in csv format (or even ado-style xml). Exporting that will be a quick task, as will importing into a cursor in Lianja. From there, things should fly.

    Hank

  5. #5
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    Hi Barry,

    I'm not sure of what other information I can provide. Most of the information is provided in my first post although I will try.
    The app I created is a standalone data entry program.

    In Lianja, I am accessing a local table (stored in C:\Lianja\data\appname).
    Data is read from this table, then exported to an excel file. The excel file is stored on the local machine in C:\Lianja\apps\appname\excel.
    I save the current record using the scatter command. Once the data from the current record is exported to excel, the data from the table is then exported to an archive table using the gather command.

    At the conclusion of the scan, the file is saved as a tab delimited text file in a location set by the user.
    The source table is also cleared of all entries.

    I had also mentioned I tested with different amount of records. 5000 records took almost 4.5 hours to complete.
    This is a local table, not a virtual table. There is no sql query being performed. I just open the table and start the scan.

    If you think you still require any further information, please let me know and I will do my best to provide that information.

    Hi Hank,

    I had ?time() placed at the start of the scan but no where else.
    The output file cannot be a csv file as there will be financial data that may have commas added during the export.
    I am also using excel since the excel file is a template and the data is being placed in specific cells.
    The exported file needs to be in a certain format because it will be sent to someone else that is using a different system (possibly at a different location) for importing into their current program.

    When you suggest using one command, could you provide an example (keeping in mind that I may be exporting several thousand records).

    I will play with my code to see if I can isolate the slow down.

    Cory

  6. #6
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    For a quick test, I comments out all excel lines
    Code:
      WITH oExcel
        .Range("A" + ALLTRIM(str(excelRowCount))).Value = ALLTRIM(LNAME)
        ...
      ENDWITH
    Processing 5000 rows now took about 3 seconds, so it does seem like excel is causing the issue.

    Cory

  7. #7
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    Hi Cory

    Regarding

    The output file cannot be a csv file as there will be financial data that may have commas added during the export.
    you can specify for delimiter what you want (| $ # \ { ....).
    Did you consider XML?

    You can provide here small app zipped as simulation of main part of your code and we can play with it and find something.

    Josip

  8. #8
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    You need to familiarize yourself with Excel automation. Updating cells causes re-calculations and refreshes. There will be properties to disable that behavior but that's beyond the scope of Lianja support.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  9. #9
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    Hi Josip,

    The file should be a tab delimited text file.
    XML is not an option. The file has to be compatible with an older program that is currently in use.

    It may take a bit of work but I'll see if I can get you a sample app.


    Barry,

    I have tried to change the excel calculation methods, although it doesn't seem to work with Lianja.
    Code:
    #DEFINE xlManual -4135
    oExcel.Calculation = xlManual
    scan
       ...
    endscan
    #DEFINE xlAutomatic -4105
    oExcel.Calculation = xlAutomatic
    // ALSO TRIED oExcel.Application.Calculation = ...
    The constant definition is based on information from: http://www.tek-tips.com/viewthread.cfm?qid=1057005


    If I were to try and eliminate excel, can Lianja create tab delimited files?
    I don't know if using 'COPY TO' would be the proper command. If it was, how would I add the excel column headers and not have the file overwritten when using COPY TO?

    Thanks,
    Cory

  10. #10
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    Hi Cory,

    Try ActiveSheet.EnableCalculation
    Did you first try the same in VFP?
    Explore these links:
    http://www.tek-tips.com/viewthread.cfm?qid=1724537
    http://www.tek-tips.com/viewthread.cfm?qid=1654224
    https://www.soa.org/News-and-Publica...s42-roper.aspx
    http://www.decisionmodels.com/calcsecretsh.htm

    Maybe Tamar's book "Microsoft Office Automation with Visual FoxPro" can help you here.

    Josip

Bookmarks

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
  •  
Journey into the Cloud
Join us