Speedtest DBF vs. LetoDB and mySQL

[Otto]

Dear freids,

hbDBSpeedTests

Our friends Angel and Diego published a speed comparison between DBF, LetoDB and mySQL.

Thank you so much to them.

It is actually as I have always experienced.

DBF is an ideal database for small applications.
Here are the first results.

https://github.com/diegofazio/hbDBSpeedTests

For your surprise DBF are not so slow respect MySQL if well managed.
All this began as Diego and Angel were curious if the myth was true and wanted real results.
So, they spent a whole afternoon creating this test series just for fun.

Due to the surprising results, they decided to publish the tests in sources,
so anyone can run them.

What does this mean?

For example, like in my case, using DBF for web pages - DBF is the fastest database.
In these results index is used, what I for web pages do not use much.
Take also in note that these samples build indexes which normally are already built.

Example a menu.

Add connection time plus execution.

I insert a screen view from my newspaper page.
This takes 1 ms to get the data for the whole megamenu.
mySQL needs 17 ms to connect and then some ms for the request.

Not to talk about the advantage of DBU over phpMyAdmin.

Angel and Diego, thank you very much again.
Your test will surely encourage many to stay with DBF which they know and used many years successfully. And help to demystify the myth.

Best regards,
Otto

[Otto]

I never thought about what would happen if you opened a DBF file 100 times.

So I will build a small test program.
I started with this test.
The first button opens the database 2000 times.
The second switch appends a data record in each work area.

[James Bott]

Otto,

Would you publish the actual search criteria for the original tests?

I have to wonder if indexes were used for the DBF test? For instance if you wanted to pull all records for a certain date range, it would make a great difference if the DBF didn't use a date index, since then it would have to pass all records in the file.

If a date index is used, then both SQL and DBF would have to return exactly the same number of records, so it is hard to figure why there would be much of a speed difference.

I have very little experience with SQL.

James

[Otto]

Hello James,

Diego posted the test source here:
https://github.com/diegofazio/hbDBSpeedTests

Best regards,
Otto

[Jimmy]

hi Otto,

how many PC are you use for that Test ?

it does not make Sence to test only with 1 x PC
DBF will not use "Share-Mode" when only 1 x PC access DBF

so you need al least 2 x "active" PC and Server to run a Test with DBF
also use UNC Path to access DBF to be sure to use same "IP-Connection" and not Lanmanager with Drive-Letter

Jimmy

[Otto]

Hello Jimmy,
Thank you for pointing out these problematics.

But this series of tests started in the mod harbour group.

Here, the DBF files are only opened "locally".
I made further tests, i.e. if - I'm not quite sure how mod harbour does this - several instances are opened.
To test this, I opened the opendbf-test.exe several times.
Each program opens the database 2000 times in 2000 workareas. There are 10,000 open workareas in total.

Access via the Internet remains as fast as ever.
I think it will remain the same if you consume a DBF as a web service.
Sure, if there are many simultaneous requests, there can be problems.
But in this case, I think we have to queue up inside the request in our programs.

Best regards,
Otto

[Jimmy]

hi Otto,

thx for you Test with several Instance which is more "real live"

on what OS is Mod_Harbour runing ? ( sorry Newbie )

[Otto]

Hello Jimmy,

In my case, the tests are on WINDOWS SERVER 2012 R2. But you can install mod harbour on all WINDOWS systems and LINUX.

More info is on

http://modharbour.org

webpage.

Best regards,
Otto

[James Bott]

Here are my DBF speed tests. In all cases I am running the EXE from the same drive as the database.

From a 1 million record DBF it takes 0.39 seconds to find 20,543 records (all the addresses in California) from a network USB drive on my LAN. I used a scope for the test.

Even if SQL is faster it could only be less than 0.39 seconds faster.

Curiously, it takes 0.65 seconds to run from my local drive. I am guessing that something else is hogging my local drive.

It took 0.78 seconds to run from OneDrive.

All of them seem fast.

When I get time, I might look at SQL speeds.

[Marc Venken]

I think that all local tests will work ok.

The speed issues are when there is networking involved. Curious is dbf is still as fast. I'm using always dbf because of habbit.

[James Bott]

Marc,

I assume you mean over the internet networking?

Note that even running from One Drive, it was still less than a second to open a browse showing a screenful of records from a subset (20,543) of 1 million records from the database.

James

[Marc Venken]

James, simple lan... I use also Ordscope, but sloooooooow sometimes. I use it in Xbrowse with the combination of

:bChange := { || SET_SCOPE_nobrowse(oBrw),('nofoto')->(DBGOTOP()),oBrw:Refresh(),oImage:Refresh(),oImage2:Refresh() }

with xbrowse getbarµ

I even copy a dbf from the server pc to my local machine in order to have better speed

Can you show your sample code for Onedrive ?

[James Bott]

Marc,

First I notice that you are calling a SETSCOPE function, and refreshing two images for every record movement. That is what is slowing down your browse. Try taking the entire :bChange line out and then test the speed. Also, if the images are on the server, then that may mean they have to be downloaded for every record move.

Note that you only need to set the scope once. That is unless you are setting the scope for another database whenever the record changes. First try a simple set scope with a database then call browse(). That should be fast. And that means it is all the other stuff you are doing is the issue, NOT the LAN or the scope for the main database, or the use of DBFs.

I just found in my speed test code that I was including the record count of those records in the scope. When I took that out, the speed to open the file and set the scope dropped to 0.0000000000 seconds running on a local drive AND when running from One Drive! The browse pops up immediately and refreshes fast.

My code is a mess right now since it was originally written for testing filters vs scopes. However, it is very simple, open the database with indexes (CDXs). Set the current index. Set the scope, then call browse(). It should happen instantly.

I don't think that DBF speed is your issue. Switching to SQL probably won't speed up your program.

James

[Marc Venken]

Marc,

Note that you only need to set the scope once. That is unless you are setting the scope for another database whenever the record changes. First try a simple set scope with a database then call browse(). That should be fast. And that means it is all the other stuff you are doing is the issue, NOT the LAN or the scope for the main database, or the use of DBFs.

James

Do you mean that the function call in the browse is at the wrong place ? somewhere outsite the browse code ?

bChange := { || SET_SCOPE_nobrowse(oBrw),('nofoto')->(DBGOTOP()),oBrw:Refresh(),oImage:Refresh(),oImage2:Refresh() }

////
STATIC FUNCTION SET_SCOPE_nobrowse(oBrw)
LOCAL cNName := fotoinde->code

DBSELECTAREA( "nofoto" )
("nofoto")->( ORDSCOPE(0, cNName ) )
("nofoto")->(ORDSCOPE(1, cNName ) )
nofoto->(dbgotop())

RETURN NIL

[James Bott]

Marc,

First, as per my tests, a DBF browse is just as fast over the internet as it is local. And it is almost instant.

Yours is not. The difference appears to be that you are linking two browses. This means that every time you display a record in the main browse you also display one or more records in the second browse. Also, you are displaying two images for each record in the secondary browse for each record movement in the main browse. Thus the second browse is slowing down the main browse a lot, especially when the entire main browse is refreshed.

The simple way to test this is to just comment out the bChange line and retest the speed of the main browse.

One way to speed the display up some is to use smaller images, either by making them visually smaller or simplifying the image, or both. You could also speed things up by displaying fewer records in the main browse.

Before I forget, whenever you change a workarea in a function you should save and restore the original workarea at the end of the function.