speed of LOCATE command in X# vs VFP

[Loy2888]

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:

Code: Select all

RddSetDefault("DBFVFP") 
SET EXCLUSIVE OFF
LOCAL lnSERIES
? "Start: " + Time()
USE "C:TEMPTemp.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)

Code: Select all

Start: 21:18:09
  End: 21:18:17

///// elapse time is -----> 8 Seconds?????

Result in VFP Command Window

Code: Select all

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.

[lumberjack]

Hi Loy,

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:)

Code: Select all

RddSetDefault("DBFVFP") 
SET EXCLUSIVE OFF
//LOCAL lnSERIES
? "Start: " + Time()
USE "C:TEMPTemp.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,

[Loy2888]

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

[Chris]

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!

[Jamal]

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.

[Chris]

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).

[robert]

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

[wriedmann]

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

[g.bunzel@domonet.de]

Hi all,

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

Gerhard

[FoxProMatt]

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.