fbpx
Welcome, Guest
Username: Password: Remember me
This public forum is meant for questions and discussions about Visual FoxPro
  • Page:
  • 1
  • 2

TOPIC:

speed of LOCATE command in X# vs VFP 16 Aug 2020 07:01 #15588

  • Loy2888's Avatar

  • Loy2888

  • Topic Author


  • Posts: 13
  • Sorry for throwing more issues here as I am trying to test out X# if moving our apps to this technology would be feasible :)

    With SET OPTIMIZE OFF, I created a small dbf table (no index) with 1000 records to test the speed of LOCATE command in both X# and VFP.
    I iterate a "locate" command in a "for loop" statement so I can obtain a proper measurement. I run the code below in both X# and VFP:
    RddSetDefault("DBFVFP") 
    SET EXCLUSIVE OFF
    LOCAL lnSERIES
    ? "Start: " + Time()
    USE "C:\TEMP\Temp.DBF" SHARED
    SELECT TEMP
    GO TOP
    FOR lnSERIES = 1 TO 1000
       LOCATE FOR SERIES = lnSERIES
    NEXT
    ? "  End: " + Time()
    WAIT
    RETURN

    Result in X# Console (VFP Dialect)
    Start: 21:18:09
      End: 21:18:17
    
    ///// elapse time is -----> 8 Seconds?????
    

    Result in VFP Command Window
    Start: 21:18:09
      End: 21:18:09
    
    ///// elapse time is way less than 1 second  (??ms) 
    

    Is there anything I am missing here to optimize this issue in X#. It takes 8 seconds for a small table that has only 1000 records? Compared to native VFP, the speed is way negligible.

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 16 Aug 2020 12:18 #15589

  • lumberjack's Avatar

  • lumberjack


  • Posts: 693
  • Hi Loy,

    Loy2888 wrote: Sorry for throwing more issues here as I am trying to test out X# if moving our apps to this technology would be feasible :)

    No reason for being sorry.

    Well first thing I would do is forget about the "LOCATE" speed, but look at what features of X# you can use to speed up your code. Alas welcome to the world of strict typing:)
    RddSetDefault("DBFVFP") 
    SET EXCLUSIVE OFF
    //LOCAL lnSERIES
    ? "Start: " + Time()
    USE "C:\TEMP\Temp.DBF" SHARED
    SELECT TEMP
    GO TOP
    FOR VAR lnSERIES = 1 TO 1000
       LOCATE FOR SERIES = lnSERIES
    NEXT
    ? "  End: " + Time()
    WAIT
    RETURN

    Let us know if that improves your time in X#.

    HTH,
    ______________________
    Johan Nel
    George, South Africa

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 16 Aug 2020 13:22 #15590

  • Loy2888's Avatar

  • Loy2888

  • Topic Author


  • Posts: 13
  • Hi lumberjack,

    Your simple code change did not improve the speed in X#. Still at 8 seconds, see my screenshot attached.

    forget about the "LOCATE" speed, but look at what features of X# you can use to speed up your code


    IMO, I would say the speed (and performance) of executing X# command would be my first preference over the speed of coding.

    In my case here, while trying to test running some modules from my native VFP apps in X# (even trying to avoid re-writing the code as much as possible), I found it verrrry slow and was able to narrowed it down into this simple "LOCATE" code scenario. I never have this issue in my existing legacy VFP app and in fact I don't even need to use index files because they only contain small no. of records. If this issue of speed continue to be the problem, this could be a major roadblock for me trying to migrate my apps to X# . But I am still hopeful for a solution here.

    Cheers,

    Loy
    Attachments:

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 16 Aug 2020 18:06 #15591

  • Chris's Avatar

  • Chris


  • Posts: 2126
  • Hi Loy,

    Yeah, I can see that as well, apparently there's a big speed difference when skipping through records (and locate is basically mostly skipping records)between X# and both VO and VFP. I will log this as a problem to be looked at, I think at least some of that speed difference can be reduced. Thanks for your report!
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 16 Aug 2020 19:00 #15592

  • Jamal's Avatar

  • Jamal


  • Posts: 224
  • Hi Chris,

    Just throwing this in the wild! Could this be related to evaluating the CodeBlock(s) for every DB row in the the dbLocate() function which kind of slow from what I have seen in various X# GitHub posts? Not sure if VFP even has CodeBlock support.

    Please Log in or Create an account to join the conversation.

    Last edit: by Jamal.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 01:09 #15595

  • Chris's Avatar

  • Chris


  • Posts: 2126
  • Hi Jamal,

    That's what I suspected as well, but then made a test with a simple do-while loop instead of using DBLocate() and this also has performance issues, so it is something more general. Possible has to do with the disk access (native compared to managed).
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 10:30 #15596

  • robert's Avatar

  • robert


  • Posts: 1862
  • Guys,
    I have done some tests with running ProcMon in the background and I can see that FoxPro caches the whole file even when the file is opened in shared mode.
    Procmon shows only ~ 15 file operations performed by FoxPro including the opening and closing of the file.

    X# on the other hand always checks for changes made by others for every operation on the workarea resulting in a lot more file operations (2 million).
    When you open the file in exclusive mode then the number of operations in X# becomes much less ( ~ 1000).

    The FoxPro behavior seems to be controlled by the value specified in the SET REFRESH TO nSeconds1 [, nSeconds2]
    nSeconds1 controls refreshes in Browse windows and memory editing
    nSeconds2 in this command specifies the number of seconds between refreshing the local memory buffers with current data from the network. The default value is 5 seconds. Setting nSeconds2 to 0 does not refresh the buffers.
    I can verify that when I change the outer loop to wait for 4 seconds. Then it does not update the buffer for every iteration.

    What this means that when a value in the shared file was updated by another user in the time between the refreshes then FoxPro may return the wrong results because it is still looking at the old data in memory.
    When you add a line to the test program to update the file (like REPLACE SERIES WITH Recno()) then FoxPro will read the relevant record again before writing and when the record is not changed then it will update the record and its internal cache.

    I can emulate the caching from FoxPro (and so can you) by adding the following line of code after opening the file.
    FConvertToMemoryStream(DbInfo(DBI_FILEHANDLE))
    This takes the FileStream object that was created when the file was opened and reads the whole file into a memory stream and then all
    operations after this will be performed on the memory stream. That makes the X# code perform with similar speed to FoxPro.
    Updates will also go to the memory stream. If you close the area then the updated memory stream will be written to disk.

    We could enable this in FoxPro mode, but I am not sure if it is a good idea to make this default for everybody.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 11:24 #15598

  • wriedmann's Avatar

  • wriedmann


  • Posts: 2344
  • Hi Robert,

    caching the read operations is a good idea.... but caching the write operations a bad one. Leaving this to the memory stream could corrupt the database file.

    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 12:08 #15600

  • g.bunzel@domonet.de's Avatar



  • Posts: 39
  • Hi all,

    ...any difference with 'SuspendNotification()' before and 'ResetNotification()' after the loop?

    Gerhard

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 12:26 #15601

  • FoxProMatt's Avatar

  • FoxProMatt


  • Posts: 409
  • Robert - You're speaking here about when the data came from a DBF. I wonder if any of what you're planning would need to behave differently when the cursor it is working in comes from a SqlExec() command where there is no local DBF. I use lots of cursors from Sql Server where I might do a LOCATE. Usually with these cursors, I never update back to the database, and if I do, I do not use TableUpdate() function, I usually just call SqlExec() and send an explicit UPDATE command via SQL commands.

    Anyway, just wanted to remind you about this other kind of data source.

    Thanks.

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 13:27 #15602

  • Chris's Avatar

  • Chris


  • Posts: 2126
  • Robert,

    Caching was what I also thought is the basic difference at first, but then tried with a single iteration in a huge file, so in uncached data, and still the difference is very big compared also to VO, not only FoxPro. I have been preparing a sample and will log it in a minute.
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    Last edit: by Chris.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 14:03 #15603

  • robert's Avatar

  • robert


  • Posts: 1862
  • Matt,

    SQLExec() results are already in memory. So this should give no problems.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 14:05 #15604

  • robert's Avatar

  • robert


  • Posts: 1862
  • Chris,

    If I remember correctly then VO caches things differently from what we do at this moment. I remember that there was a special "cache manager" in the runtime.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 14:05 #15605

  • Zdeněk Krejčí's Avatar

  • Zdeněk Krejčí


  • Posts: 16
  • I would like to note that the LOCATE command in VFP uses Rushmore optimization when possible

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 14:06 #15606

  • robert's Avatar

  • robert


  • Posts: 1862
  • Zdenek,

    We know that, but the example given does not have any indexes so no Rushmore optimization.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 17 Aug 2020 14:07 #15607

  • robert's Avatar

  • robert


  • Posts: 1862
  • Gerhard,

    SuspendNotification() and ResetNotification() are handled at the DbServer level.
    The example uses workareas / cursors directly

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 18 Aug 2020 01:11 #15626

  • Loy2888's Avatar

  • Loy2888

  • Topic Author


  • Posts: 13
  • Hi Robert/Chris,

    I tried to add
    FConvertToMemoryStream(DbInfo(DBI_FILEHANDLE))
    but it only reduced to 6 seconds from 8 seconds. Still slow compared to FoxPro at less than 1 sec.

    The reason why the speed is a big deal for me is that I have a real-time system that constantly collects (in endless loop) live status, attributes, locations, positions, etc.. of a certain moving parts on a 20 different tables that are pretty small with less than 1k of records each and index files are not even needed since LOCATE in FOXPRO is fast enough to return the real-time info.

    If I run this code in X#, the delay is adding up, and the result is nothing but a delayed information.

    I hope this needs to be considered as I have a positive outlook towards X# moving forward.

    Loy

    Please Log in or Create an account to join the conversation.

    Last edit: by Loy2888.

    speed of LOCATE command in X# vs VFP 18 Aug 2020 09:17 #15628

  • robert's Avatar

  • robert


  • Posts: 1862
  • Loy,

    We will certainly look at this.
    But I have to tell you that our first priority was and is to add all the features and make your apps work. Improving performance is not priority 1. That does not mean that we will not look at this though.


    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 18 Aug 2020 09:50 #15630

  • leon-ts's Avatar

  • leon-ts


  • Posts: 128
  • Hi all,

    I was interested in this topic.
    Run Loy's example on my computer (several runs to get the average). The test was completed in 10 seconds.
    Next, I applied Robert's recommendation with loading the stream into memory.
    FConvertToMemoryStream(DbInfo(DBI_FILEHANDLE))
    The test was completed in 1 second.

    P.S. I am developing VO/XSharp applications (not a VFP developer). But I take optimizations and application performance very seriously. Therefore, this topic is interesting to me.

    Please Log in or Create an account to join the conversation.

    speed of LOCATE command in X# vs VFP 18 Aug 2020 16:49 #15652

  • Loy2888's Avatar

  • Loy2888

  • Topic Author


  • Posts: 13
  • Hi Leon,
    Thanks for being interested on this topic as I am taking application performance very seriously as well. I have applications with heavy lifting algorithms and haven't tried them yet in X# as I came across this performance issue.

    Would you mind to test this code as well, it's just the speed of SKIP command for 10 million records, you don't need to create a table with 10 milion records, just a small table will do and go top when eof() see code below:
    ? "Start: " + Time()
    USE "C:\TEMP\Temp.DBF" SHARED
    //FConvertToMemoryStream(DbInfo(DBI_FILEHANDLE))
    SELECT TEMP
    GO TOP
    FOR VAR lnSERIES = 1 TO 10000000
        SKIP
        IF EOF()
            GO TOP
        ENDIF
    NEXT
    ? "  End: " + Time()

    I run this code in X# and it is taking forever, I mean "forever" when it exceeds more than 2 minutes...

    In native FOXPRO, running the same code took only 1 second, 2 seconds is the most...

    @Robert,

    I understand you have more priorities adding all the features in a dialect, but in my opinion, as a developer, I look first at the speed and performance before I continue. All the features can wait. I know you will eventually fix this but most serious developers with database intensive driven applications like to see the performance before they can continue.

    I introduced X# to my colleagues showing its cool features and they were very impressed at first, but when we tested X# (using our existing applications) its speed and performance in terms of accessing a table and comparing it to native FoxPro, we were kind of "hmmm... not sure about this".. Anyway, I told them to hold their breath as the X# team is working on it but not the priority right now. :)

    Loy

    Please Log in or Create an account to join the conversation.

    Last edit: by Loy2888.
    • Page:
    • 1
    • 2