Could you better explain in general lines how Databases like Faircom C-tree and Btrieve provides advantages in the area of performance and flexibility compared with MSSQLServer f.e. ?

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
Hi Experts

Could you explain in general lines how databases like Faircom C-tree and Btrieve provides by its  direct, native access to the low-level microkernel via an API call substantial advantages in the area of performance and flexibility compared with MSSQLServer f.e. ?

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Your question has no answer, as asked.

1) First you start with your data.

2) You consider how data is injected into the database + retrieved from the database.

3) Then you consider the frequency of data injection/retrieval, so is your access pattern read heavy, write heavy or mixed.

4) Then you select a storage engine to address all these considerations.

Big Tip: Use MariaDB, where you can swap in/out various storage engines.

If you use one type of database, with custom SQL or non-SQL, with only one storage engine, you're locked in forever... so if you make a mistake at the beginning or in the future your data access pattern changes...

With MariaDB, you just switch to a new storage engine.

With some other custom database, you'll likely go through a major rewrite.
President and Btrieve Guru
Commented:
A low-level interface requires very little overhead to get the data stored or retrieved from the database, as it is just slinging around blobs of bytes. There is usually little in the way of data type checking or data validation -- as the database assumes that the developer knows what is being stored and has handled any "bad data" issues already.  (If you want to put a bad date like November 77th, 28345 into a Btrieve database, it'll let you.)  The actual API interface is also very simplistic in nature (i.e. in the Btrieve case, there is one function call with only 7 parameters),so it requires very little processing power to perform a database access.  In fact, for a typical Btrieve call on a moderately-powered server today, we easily see less than 40us response times when data is already in the cache -- and I have seen high-end servers handling requests in under 10us!  (In fact, I can run a Btrieve app on a Raspberry Pi that can make more than 25000 requests per second.)

If you compare this to a SQL request, you quickly see where much of the performance drag occurs.  A SQL statement to retrieve a single record must be passed through the SQL parser to pick apart the statement into tokens (SELECT, *, FROM, Table, WHERE, etc.).  The SQL engine then passes the request to the SQL compiler, which reads the system catalog or data dictionary to determine what information is being requested.  The SQL optimizer can then be called to determine if indexes are available (or not) and decide how best to access the data.  Finally, the SQL engine calls the low-level microkernel to actually retrieve the data.  Put simply, the overhead can be enormous.  

Of course, the math changes entirely when you are working on larger sets of requests.  A SQL call (from the application to the engine) can retrieve a large amount of data records with a single call.  It can return data from many linked records at the same time (via JOINs).  It can even filter unindexed data directly within the server itself, providing performance gains by leveraging the power of the server in addition to the CPU on the client.  For low-level Btrieve/C-Tree applications, the code must typically request one record at a time, going back and forth to retrieve a large data set in a loop.  This additional back and forth requires individual round-trip network requests, and now your system is bound by the latency of the network -- what used to be 10-40us is now taking 250us or more on even a fast network.  Even on a short-haul WAN where the network round trip latency is approximately 10ms, this is now 1000x slower than a high-end server would be able to handle locally.

Both SQL and no-SQL (transactional) databases have their place, of course, depending on the task at hand.  Heads-down data entry and transactional systems (i.e. POS, IIoT data collection systems, ERP, and the like) may be best served by the low-level performance, while more complicated environments like reporting and data aggregation may be best handled by SQL.  By selecting a development database solution that offers both interfaces, you can select the right tool for the job.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Bill

Thank you for so in-depht clarifications.
I  read it once time but need to read it again to try catch the complete meaning.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Eduardo FuerteDeveloper and Analyst

Author

Commented:
After a better understanding my conclusion is that Faircom C-tree/ Btrieve runs very faster when obtaining a discrete set o registers when compared to a conventional relational DB since a lot of lawyers (validation and interpreters) are supressed.
A convencional relational database has this microkernel also but it doesn't expose a low level API capable of deal with, allowing the same functionality.
If a complex join using a lot of tables is needed C-tree/ Btrieve also offers a high level interface to deal to, like the conventional DB does.
So, the product offers the best of the 02 worlds and the developer is in charge of choose the better option depending on each situation.

Let me know if I'm misconcepting something.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Reading your question, only way to know for sure is to test.

So you can use MariaDB with various storage engines, then do a performance comparison with each other product.

Since Faircom provides a Btrieve convertor, you'll likely start with Faircom.

Note: Since Faircom + Btrieve both require custom code, best to write a very stripped down test suite for each.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi David

Just to contextualize you about this question, it derivated from this previous questionquestion.

As it could be seen I'm using an ERP called Protheus that uses Faircom C-tree in conjunction to  MSSQLServer, no choice about Faircom C-tree could be done, we have to use it on that context ( an Oracle DBMS could run instead MSSQLServer f.e.).  Since I had a very poor knowledge about Faircom C-tree I asked here and get very professional clearings.

What you stated about Maria DB could be interesting in another contexts but my doubts here are direct connected to C-tree and Btrieve.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Thank you for the guidance!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial