Dbf/cdx to sql changing

Re: Dbf/cdx to sql changing

Postby Maurizio » Thu Jul 18, 2024 9:13 am

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
User avatar
Maurizio
 
Posts: 823
Joined: Mon Oct 10, 2005 1:29 pm

Re: Dbf/cdx to sql changing

Postby Otto » Thu Jul 18, 2024 1:02 pm

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.
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: Dbf/cdx to sql changing

Postby Enrico Maria Giordano » Thu Jul 18, 2024 1:24 pm

Otto,

100% agreed! It is a pity that market goes elsewhere... :-(
User avatar
Enrico Maria Giordano
 
Posts: 8710
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Dbf/cdx to sql changing

Postby MarcoBoschi » Thu Jul 18, 2024 2:16 pm

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
User avatar
MarcoBoschi
 
Posts: 1065
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy

Re: Dbf/cdx to sql changing

Postby Otto » Thu Jul 18, 2024 2:48 pm

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

Image

Image

Image
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: Dbf/cdx to sql changing

Postby Enrico Maria Giordano » Thu Jul 18, 2024 2:57 pm

Otto,

How can your programs consume such huge amount of memory? Mine consume something like 5 or 6 MB!
User avatar
Enrico Maria Giordano
 
Posts: 8710
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Dbf/cdx to sql changing

Postby Enrico Maria Giordano » Thu Jul 18, 2024 2:58 pm

Maybe I've misinterpreted your image, sorry.
User avatar
Enrico Maria Giordano
 
Posts: 8710
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Dbf/cdx to sql changing

Postby Otto » Thu Jul 18, 2024 3:34 pm

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
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: Dbf/cdx to sql changing

Postby Enrico Maria Giordano » Thu Jul 18, 2024 3:39 pm

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


Ok, it is normal. I wrongly looked 250 MB.
User avatar
Enrico Maria Giordano
 
Posts: 8710
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Dbf/cdx to sql changing

Postby Rick Lipkin » Thu Jul 18, 2024 4:26 pm

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
User avatar
Rick Lipkin
 
Posts: 2663
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Dbf/cdx to sql changing

Postby Otto » Thu Jul 18, 2024 7:07 pm

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
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: Dbf/cdx to sql changing

Postby Otto » Thu Jul 18, 2024 10:33 pm

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

Image

Image

Image

Image
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: Dbf/cdx to sql changing

Postby Otto » Fri Jul 19, 2024 9:45 am

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.


Image
Confidential notes: Directory "/confidential_notes/" accessible only to certain departments.
Private notes: Directory "/private_notes/" accessible only to authorized individuals or groups.
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: Dbf/cdx to sql changing

Postby vilian » Fri Jul 19, 2024 11:06 am

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.
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
User avatar
vilian
 
Posts: 972
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil

Re: Dbf/cdx to sql changing

Postby wartiaga » Fri Jul 19, 2024 12:05 pm

nageswaragunupudi wrote: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.
wartiaga
 
Posts: 210
Joined: Wed May 25, 2016 1:04 am

PreviousNext

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 60 guests