SQL Database choices

[OhioJoe]

Anyone reading this forum should by now understand that low-level file management in .NET is slower than in Win32. That's the price we pay for .NET, I suppose.

So if you're maintaining a file-based DBF system, it seems you must kiss goodbye the speed and database performance you so painstakingly crafted during your happy years in the shrinking but sheltered DBF enclave.

Here's the good news: I've made the DBF-to-SQL switch before, In fact I even wrote about it. Wasn't that hard. And (as you would know from reading any of my many simple-minded posts on this forum) if I can do it, ANYONE can.

I'm writing to seek the group's advice on the best database choice. I know we've had this discussion before but this time I'd like to focus specifically on the following parameters:

1. One to twenty simultaneous users. Every network is Windows.
2. Record count of the main transaction table could be a million or more. In 90 percent of cases its less than 100k.
3. Distributed app, meaning it's designed to be downloaded and installed by the user.
4. Database-write transactions are few during data entry. Even with two data-entry users, there's only one or two transactions a minute.
5. However, reports are constructed by building (which means bulk-appending) temporary tables. In the past we've used temporary DBFs because of array limitations.
6. Easy installation and low maintenance is crucial.

At the moment I'm partial to SQLite because it's server-less. No need for the user to install (or for us to support) a server instance. This is huge. If you read SQLite's description of appropriate uses, it seems SQLite fits the bill. For us, at least. However I have heard past testimony from Wolfgang and others on this site about SQLite's under-performance on networks.

So if not SQLite, is there something else that offers the same low maintenance but better multiuser performance?

The new project to convert from DBF begins this week. So I'm very interested in what everyone has to say. At this point in time, after we've had some experience in XSharp.

Thanks everyone

[FFF]

if I can do it, ANYONE can.

Well, on the same level B) , as i have really no clue: If you want SqLite/no server, i wonder, where the data resides? On a NAS, on one machine of one user, or how does that work?
My vote would go to PG - even i can install it on a plain windows machine in less then five minutes.
I never did customize anything and my playing around simply worked.
It isn't hungry, i think Fabrice showed sometime ago PG running on a raspi ,-)
It's free.
There are quite some enterprises around which provide expertise and support, if you really need it.
IIRC, in 4 of the last 5 years it was the fastest growing RDBMS around, there have to be reasons for this.

[OhioJoe]

Thank you, Karl.
The data will always reside next to the program files:
c:>JoesApp
JoesAppdata
JoesAppprog

[jpmoschi]

Some time ago I evaluated SQLite for my developments and I should have discarded it due to the mathematical precision in the calculations. Perhaps for most of the developments it is not important but my need included 10 decimal places of precision which I could not obtain with that engine.
I recommend that you take it into account

[FoxProMatt]

I may be the odd man out here, but I too switched a good sized in-house FoxPro app from DBFs to (gasp...) Microsoft Sql Server Express 2016 (free, up to 10GB database). I love it. The Sql Server Express database engine and the Sql Server Management Studio (GUI IDE tool for managing the databases) are great to me. It has Stored Procedures, Data Views, foreign key rules, blah blah.

I run it on a Windows 10 Pro virtual machine running on the (free) Window Hyper-v Server 2016. With this setup I get OS snapshots of the main VM machine which makes it very safe for applying Windows updates and other system maintenance because you quickly make a OS snapshot before you apply any updates, then once updates are done and running safely, you delete the snapshots and move on.

I use many Stored Procs for queries, which allows my my FoxPro app and .Net apps of all types to call the same stored procedures and ensure they both get the same data sets without me re-writing all the queries in each app.

[Stavros Spanos]

I would also vote for MS SQL Express. We use it in hundreds of installations since 2000 having no problem. And it's kind of a standard in the market.

[wriedmann]

Hi Joe,
you already know my reccomendation: go with PostgreSQL as you will not encounter any limitations as you may with MS SQL (Microsoft is giving for free only limited versions of its database - but that is a normal thing for a commercial company).
But, as Matt states, it comes with an excellent managment tool.
I'm working with both (and also with Oracle, MySQL and SQLite).
Wolfgang

[ic2]

Hello Joe,

Very interesting topic. I will read your "Using SQLite in Visual Objects" and check the AEF you include. I agree with you that .Net is all about losing program execution speed. .Net programs take considerably more time to start due to IL to be compiled first for example. I can also imagine that low level DBF operations in VO can not be set to work in a more controlled environment which .Net is. However, using SQL (VO or X#) should be faster than using DBF I think. Even when using ADS, a SQL query could be considerably faster than comparable VO/X# DBF statement doing the same. I think that this is the case for whatever SQL solution you choose.

Two quick remarks already about your article:

1 I think that moving from DBF to SQL for a large project is much more work than going from VO to X#. We have 1000's of DO..WHILE and FOR..NEXT's and these should all be translated to some 1 line SQL query, all of which need to be well thought about (nothing like just some copy&paste of a slightly different method). That's why we haven't done this yet.

2 I also see that you use array servers for bBrowser. We use bBrowser on ADS SQL tables, so I would say that would work too for SQLLite? We have bBrowsers running on DBF files with over a million records, don't think it's a good idea to read that amount of records in an array server first


Dick

[OhioJoe]

Thanks to Dick, Matt, Karl, Wolfgang, Stavros and Mr. Moschi:

Based on your input, I'm going to try to write for all SQL flavors by sticking with commands common to all SQL syntaxes. I'll let you know how far I get. The reason I lean toward SQLite is because the average user can download and run the app without having to initialize a separate SQL server.

To the esteemed and knowledgeable Dick: I take issue with your assertion that converting hundreds, even thousands of WHILE and FOR loops is unrealistic. Obviously loops are the heart and soul of the average VO app, and altering them seems a fearful task. But I did it once before and wrote about it on that website page:

https://www.joecurran.net/advice/vo_sqlite.htm

Seeking and Looping

Don't try to loop a SQLTable{} instance. The index order is unreliable and therefore unusable. Instead do this:

Code: Select all

IF oTable:Seek( { #ACCOUNT, #ITEMDATE}, { nAccount, DToS( dDate ) }, FALSE )
  cStatement := "SELECT * FROM (tablename) WHERE ACCOUNT=" + LTrim(Str2( nAccount, 7) ) + "' AND ITEMDATE>='" + DToS( dDate ) + "' ORDER BY ITEMDATE"

  oSelect := SQLSelect{ cStatement, GLOoCONNECTION }
  oSelect:GoTop()
  WHILE !oSelect:Eof
    // ... do whatever
    oSelect:Skip(1)
  ENDDO
  oSelect:Close()
ENDIF

In other words: the only actions that, in our experience, can reliably be performed on a SQLTable{} instance are seeking a single record, retrieving the data and saving it. Everything else is done is a SQLSelect{} instance.

As I said, this was my experience in trying to make SQLite work with VO. I found that many methods of the VO SQL classes just didn't work. I'll now try the same in X# and see where it takes me. And of course I'll report back to the group.

(And by the way, Dick: if I'm wrong about the loops, i.e. my memory has faltered, I'll say so. )

[FFF]

Joe,
i would really avoid SQLite - for the simple reason, when i google "SQLite Multiuser" i get e.g.
"SQLite is not designed to be multi-user… If you have 20 people that need concurrent access, I highly suggest finding an alternative"
OK, you wrote about 10 users, but i doubt this to make a difference
Secondly, i still wonder how you handle your data. If the 10 users share data, where does it reside? On one of the 10 worker pcs?
Re. Install - just gave it a try, got me the new PG 14beta1, (an exe), hit enter, answer where to install, where to put data, password for superuser and port to listen, all on my 5 year old laptop (ok, has SSD), stopwatch said, 5min to have a running database server.
I'd give it a try...