MySQL and Others

[Petrus Vorster]

This is just a question to throw a rock in a pond and to learn from the smart people.

I have been unsuccessfully(due to no need to) looked into moving from flat files to SQl.
What i want to know from the smart people here is:

These databases are still files on a drive somewhere right?
Therefore their respective software still access them either read/Write/Binary/input/random modes. Is that right?
Then it runs the entire database in RAM, do the calculations and amendments there and then write to disk at some point...is that right so far?

Then what, if they basically use the same language to query and append, do they continue to improve the speeds of these databases?
MSQLheatwave says they are 5400 times faster than Amazon RDS (did not even know what that is) and so we can continue.

What can you improve, other than your indexing to make such an improvement in a database?
Inside are tables, columns and rows, headers and indexes.
How do you from Oracle to Mysql to Amazon(??) and make the performance of essentially the same process so much faster?

I am just curious. Anyone in the mood to explain?

-Regards, Peter

[SeaVipe]

Hi Peter,
In case you haven't already done so, you might find these sites helpful: SQLite.org and SQLite Tutorial

[Paul Squires]

I don't have a lot of experience with Oracle and MySQL other than using MySQL for some web applications, but I can tell you with confidence that for basic desktop applications that SQLite is, by far, the fastest, smallest, and easiest to use database. All databases and indexes reside in the same physical file and the fields are variable and compact in length (not like traditional dBase files or other similar flat file databases where space is wasted). It is also onlu ONE dll that needs to be included in your application.

Also, the SQL language implementation is INCREDIBLY FAST and once you master a few simple SQL concepts, it allows you to replace hundreds of lines of BASIC procedural code with some simple SQL and code to read the results. Having used SQLite for some of my business related applications, I am kicking myself for not having chosen to learn and use it many years earlier. I challenge anyone to write a database system of similar functionality that is faster and easier to use.

Also, if you need a client/server implementation of SQLite then look at our SQLitening project (32-bit) at https://sqlitening.planetsquires.com/index.php   I have no need for client/server in my applications but many users trust SQLitening to handle their jobs. It was written by Fred Meier who shared his code with me when he discovered that I was writing a similar product. His implementation was so much better and feature complete that we decided to abandon my efforts and use his. Sadly, Fred passed away several years ago. I wish I could have known him longer as he was a very nice and gentle man with a passion for programming.
   

[Paul Squires]

With regards to Oracle, MySQL, PostgreSQL, MS SQL Server and the countless other client/server databases..... yes, they do reside on a server machine and usually encompass many multiple files to store the databases, indexes, queries, etc. These systems are not trivial but they are essential for mission critical software or software that requires a lot of concurrency.

These databases use a lot of complex logic to optimize databases and indexes when it compiles the SQL statements into a "query plan". That part of the system determines what indexes (if any) to use and then access the required tables and fields in the most efficient manner. No, the entire database is not held in memory as databases for these systems could be in the giga or terra byte range. The results of a query are handed back to the client through the open connection made by the client to the database and can be processed in chunks at a time. The goal is to prevent bottlenecks so that many clients can read and/or write to the server without any degradation in performance.

[Petrus Vorster]

Thanks gents.

Now I have a better idea what they are up too.
Paul, other than helping us, In what industry are you?
Finance, Banking, Engineering?

You have a great deal of skills and knowledge.
I wish life took me the computer way and not the way it did.
Wrong country, wrong time.

Regards, Peter

[Paul Squires]

Hi Peter, I am in the finance sector of our Federal Government here in Canada. I am very close to retirement now. I have been programming since the late 80's and was very close to choosing a computer science university path but ultimately went with accounting/finance. Programming has proven to be a great hobby for me that has helped my career. I've been able to automate many mundane tasks in my day job and have created several applications that garnered me national recognition which was pretty cool and satisfying. Much like you were able to do by creating programs that helped in your job.

As I get older my passion for programming is not as "intense" as it was in my youth. The early days of learning basically consumed my every waking thought but once you get a descent grasp on fundamentals like algorithms (and in my case, the WinAPI), then programming becomes a little routine. I try to challenge myself these days by learning other things like web based applications, ecommerce, and other languages like Python, C++, Rust, Haskell, Javascript. I am also doing a LOT more in Linux these days than Windows. 95% of my days now are using my Linux machines rather than Windows which is refreshing because Linux desktop (I use Manjaro Linux with KDE Plasma) is amazing and without all the crap that comes with Windows.

[Petrus Vorster]

A few years ago you suggested a SQl database tool that creates databases, edit etc.
Quick table creation and a clean simple interface.
I had it for a year or so, lost interest and removed it.

I am unable to find any record of what that was.
I am thinking that perhaps with all the turmoil our country and organization is in, having another skill may be something I may need soon.

Can you remember what tool that was?

Regards, Peter

[Paul Squires]

Could be one of these because I have used them all at one point in time:

https://sqlitebrowser.org/

https://sqlitestudio.pl/

https://www.sqliteexpert.com/

There are many free programs like these available on the internet.

[Joerg B.]

Good morning all

It is interesting that one or the other career paths are similar.

I first learned a trade and later studied supply and disposal technology on this basis.
In addition to the actual lectures for my current profession, I took several semesters of vocational and occupational pedagogy as well as computer science just out of interest. But the era of programming with punch cards was fortunately already over.
It was the time of Intel's 8086/8088 with 4,77Mhz, a hard disk with 10MB and debug: G=C800:5 command...... and the C64 *lol*

Only the "older" among us still know what to do with this command. And for the younger ones among us..... that is no longer possible today.

For the "broad masses" computer science was still quite new and developers were probably all nerds.
At that time there were mainly only C, Pascal and GW-Basic. 
C++, Java, PHP etc. did not exist yet....  :-)

They have made mankind happy only later.

In the meantime many things have developed rapidly in this area.
Tools like WinFBE and Josè's Framework could only be dreamed of.

I did not follow the advice of my lecturer "absolutely" to change into the computer science subject. I didn't want to have learned a profession and deepened it in a course of study in order to start all over again at the end by changing my studies. Not with a wife and baby at home.

Maybe a mistake. But I do not know. :-)

The acquired knowledge in computer science I have also only used professionally. Partly also for smaller very special programs which were published in professional journals only.

Due to job and other honorary activities there is hardly any time left for this hobby in the meantime.
I still have about four years until I could officially retire.

Whether I then again deeper into the field of computer science let me see........
Then possibly again other things are more important.

Everything has its time!

It is actually off topic.... I know

[Petrus Vorster]

Found a nice little free tool where you can practice and create all the sqlite commands.
DB Browser for SQlite.
This should help me understand the processes and the go back and test them in one of my PB projects.

-regards, Peter

[Paul Squires]

I did not follow the advice of my lecturer "absolutely" to change into the computer science subject. I didn't want to have learned a profession and deepened it in a course of study in order to start all over again at the end by changing my studies. Not with a wife and baby at home.

Maybe a mistake. But I do not know. :-)

I have often thought that had I pursued a formal path into computer science that maybe I would not have enjoyed programming as much as I have over the years. I think that if programming was my "job" then maybe I would not have lost passion for it after a short while. However, since it was a hobby of mine, the enjoyment has now lasted over 30 years. The freedom of learning programming at my own pace and not having projects dictated to me by an employer has allowed me to continue to enjoy programming rather than getting bored of it.

[Petrus Vorster]

That is true Paul.
My nephew is a programmer for a big company in the country. Writes serious stuff like Tax systems, DMV systems & Retail accounting platforms.
He told me that they have constant meetings and arguments on modules that doesn't work and who has to fix it, or fixing their bank switch is a whole episode of decisions, meetings & arguments. No room for growth or fun.
It's a daily job for him and his hobby is sheep farming, while he is a qualified software engineer!

Strange world.
-Peter

[Petrus Vorster]

Turned out that due to the work Paul and Josè had put in through the years ,that SQLITE is much easier to implement than what I anticipated.

In one evening my first queries were running correctly.
the SQlite website has a number of commands that I tried and using the small little setup Paul gave me, it returned the data correctly and quickly.
Making small adjustments also worked.

Obviously there will be issues and problems in the future, but I am overjoyed that took only a little while to get to grips with the most basic functions.

-Regards, Peter