Dbf/cdx to sql changing

[Maurizio]

Otto,
For me the speed of SQL is absolutely not a problem, the integrity of the data and the simultaneous use of multiple users is much more important.
It seems to me that the tests carried out refer to a single table, with SQL in each select I use many tables.
I remember that with DBFs when the application was used with more than 5 users the performance dropped drastically.
With one table user the performance of DBF is exceptional.
The opportunity of the SQL language is not to use it as we were used to with DBFs, it is a change of mentality, deepening the SQL language with a few lines in a select I replace programming pages, cycles, do whiles etc.
Our generation has made history, but if we want something to remain we must adapt to new technologies.
My young collaborators agree to use FW but not to use DBF.

Regards Maurizio

[Otto]

Dear Maurizio,

Here is a paradigm shift, and that is RDP and now mod harbour.

Even if you have 5 users, it is very unlikely that more than one will open the file at the same second. You really need to rethink here.

How we use DBF - everything is only remotely accessible - it is like a single-user solution.

I have known SQL for a very long time and worked with SQL when I rewrote the program to VB6 and VB.NET.

With all the comments, I always feel like it's being implied that I don't know SQL. I have used SQL for over 10 years.
But the situation is different today. With SSDs, we have similar speeds on the hard drive as in the main memory.

But assuming that SQL is the ultimate solution is wrong. And you can see that the new database trend is NoSQL databases.

It is clear that we have to add some functions ourselves with DBF.

How do you solve incremental search in tables on the web to achieve "XBROWSE standard"? You can't load the entire database.
You have to reload with each entered character. And here it's about milliseconds.
Especially in competition with competitors, the incremental search in the tables, which is extremely fast in FIVEWIN, always gave us advantages.

For example, security:
SQL databases: A user with read and write permissions can execute SQL queries and save the results locally, which essentially equates to downloading the data.

DBF files: Access to DBF files typically requires a special program. If this program only exists on the server and the user has no direct access to the files, they must perform the queries server-side.

Here, for example, I read the entire FW WEB Server and save the content here with me.

I understand: My young collaborators agree to use FW but not to use DBF. As an employee, I would also first look after myself and stay as compatible with the job market as possible.
One can then easily switch to another company as an employee. Of course, this is not in my interest as a company owner!

I actually just want to provoke some thought about whether it makes sense to recommend everyone to switch to SQL.

I am interested in how to solve incremental search - or possibly the FW test server is not the best. Maybe we will hear real results.

Best regards,
Otto


Especially for web design, dbf is great - and there would also be a large target audience for HARBOUR and FIVEWIN

Where I have a lot of experience is web pages. for example, DBF files are excellent here. Especially the quick maintenance directly with remote access to the
web server and there with the dbf editor is extremely fast.
I used to use WordPress, but it is no comparison with dbf files. when flexibility is needed, DBF files are unbeatable.

[Enrico Maria Giordano]

Otto,

100% agreed! It is a pity that market goes elsewhere...

[MarcoBoschi]

I remember that with DBFs when the application was used with more than 5 users the performance dropped drastically.

Dear Maurizio, in my experience I have not noticed this. I've instead notice that other problems such as slowness in the lan or server may cause slowness. Not the number of users.

My personal experience.

Bye

[Otto]

Hello Marco,

in our case, it is very simple. We only send image points over the network.
The users' programs and data are all centrally located on the server. Everyone has practically a single-user solution,
but the data is shared.

You see here the separate directories for the waiter terminals, the cash registers,
and for the actual WINHOTEL, and a central data directory. Fivewin consumes very little on the server.

Best regards,
Otto

[Enrico Maria Giordano]

Otto,

How can your programs consume such huge amount of memory? Mine consume something like 5 or 6 MB!

[Enrico Maria Giordano]

Maybe I've misinterpreted your image, sorry.

[Otto]

Dear Enrico,
My consumption is actually between 5 and 20 MB for each EXE. I use xHarbour and don't link anything special.
But I have never tried to optimize memory usage since I have plenty of RAM.
What consumes a lot are Outlook (172MB) , Word (100MB), and the web browsers. If you look, there are 50 programs open in one session.

Best regards,
Otto

[Enrico Maria Giordano]

Dear Enrico,
My consumption is actually between 5 and 20 MB for each EXE.

Ok, it is normal. I wrongly looked 250 MB.

[Rick Lipkin]

Otto

Here is how I handle incremental search with Sql with xBrowse.. see AdoRick.prg

Code: Select all  Expand view

Static Func _Isearch( oLname1, cLname1, oBrw, oRsCust )

  cLname1 = Alltrim( oLName1:GetText() )   // GetText is the key here   as you start typing then sent to the oRsCUst:Filter

  if Empty( cLname1 )
     return .T.
  endif  

   oRsCust:Filter := "[Last Name] like '"+cLname1+"%'"       // incremental search

   oBrw:ReFresh()

Return .T.



 

See RickAdo.prg in \samples


Rick Lipkin

[Otto]

Hello Rick,

Thank you.

I would be interested in a real-world test. Could you provide a table with, for example, 20 records and measure how much time it consumes when using a database with 200,000 records to search incrementally and then scroll?

Additionally, how long does it take to establish a connection to an SQL server? Can you make persistent connections?

Thank you in advance.

Best regards,
Otto

[Otto]

Hello friends,

After no one really wanted to help, I started my MariaDB server, downloaded the database from the FW web server, and installed it on my server. I also installed my DBF file and the program to read it there. Both databases have the same fields and both databases are 258,257 records in size.

On my server, it looks like this now: DBF takes 95 ms to fully load the page, and the page with MariaDB takes 481 ms.

Best regards,
Otto

[Otto]

Speedtest (Web):
Memo fields and BLOB fields will now be removed from my databases and distributed into separate files.

/hotel_data/
/public_notes/
note1.txt
note2.txt
/confidential_notes/
note3.txt
/private_notes/
note4.txt

Use of access rights:

Public notes: Directory "/public_notes/" accessible to all employees.



Confidential notes: Directory "/confidential_notes/" accessible only to certain departments.
Private notes: Directory "/private_notes/" accessible only to authorized individuals or groups.

[vilian]

Sorry Otto,
But i think you're looking at this for a wrong way. Trying to use MariaDb/MySql as a DBF never will bring good results.
I have moved from ADS/DBF to MySql in 2016 and my only regret is not have done this before. Now i program less, many things MySql does for me and My customer can acess its data from anywhere.

[wartiaga]

Please use this revised maria01.prg

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

static aStr := { "208.91.198.197:3306,fwhdemo,gnraofwh,Bharat@1950", ;
                 "209.250.245.152,fwh,fwhuser,FiveTech@2022" }
static oCn

function Main()

   local aTables

   SET DATE ITALIAN
   SET CENTURY ON

   FW_SetUnicode( .t. )

   CursorWait()
   oCn      := maria_Connect( aStr[ 1 ], .t. )
   aTables  := oCn:ListTables()

   XBROWSER oCn:ListTables() ;
      TITLE "Dbl-Click to View Table" ;
      SHOW RECID ;
      SETUP ( ;
      oBrw:aCols[ 1 ]:bLDClickData := { |r,c,f,o| ShowTable( o:Value ) }, ;
      oBrw:bDropFiles := { |aFiles| xbrowse( aFiles ) } )

   oCn:Close()

return nil


function ShowTable( cTable )

   local oRs, nSecs  := SECONDS()

   if cTable == "custbig"
      MsgRun( "Reading " + cTable, "Please wait", { || oRs   := oCn:RecSet( cTable, -100 ) } )
   else
      MsgRun( "Reading " + cTable, "Please wait", { || oRs   := oCn:RowSet( cTable ) } )
   endif
   nSecs    := SECONDS() - nSecs

   XBROWSER oRs TITLE cTable + " (" + cValToChar( nSecs ) + ") seconds" ;
      FASTEDIT NOMODAL SHOW RECID

return nil

Works great! Thank you Nages.