NoSQL in MySQL - This Could Be Big
lazarus / 16-Databases

----------------------------------------------------------------------
Carbert · 2011-08-13 00:34

A little context first...

I'm an old xbase programmer who has regretted the loss of the concept
of integrating the language with the database (4GLs).  With VB and
Delphi we had data components which were almost as good, but I always
bristled at having to use things like Table.FieldByName("MyField")
rather than Table.MyField.  But things were still generally good.

But then as the SQL databases and the web paradigm if stateless
connections took over, we began to lose the concept of a connected
database session.    Indeed, ADO.NET now has no option for such a
thing and I started to notice that even in Delphi/Lazarus, pessimistic
locking was far less available (depending on your database choice). 
So now, it's often not possible for a user to get a record
locked/changed error until after they've entered all their changes. 
Frustrating.

Worse, we find ourselves having to code a database language into our
native language.

So, now I'm working at a new job in a Microsoft shop using VB.NET.  I
decided to give LINQ a try for a project with some simple database
needs.  Once I finally understood the concept, I was fully prepared to
like it.  The DB schema is reflected in objects and it does a lot of
the work for you.  But when I tried to create a GUI app to display the
data I was manipulating, I was less than optimistic.  It was
slooowwww.

I started to consider all the things going on in the background.  LINQ
is statically reading the database schema (it isn't aware of changes
unless you force it to look again) and building classes that detect
changes to individual fields and when you submit changes it writes SQL
which is sent to the server which then has to parse the statements,
develop a query plan, execute it.  In the case of queries, it wants to
return ALL of the data in one shot.

How can this be less efficient than a connected dataset where the
server is just keeping track of sessions with X number of record
positions for different tables?  I began to feel as though I was
standing a long way down the wrong fork in the road.

So I started to think about how things might be different.  I searched
for ISAM implementations and found that InnoDB, now the default
storage engine for MySql, was at its core, an ISAM database (with
transaction capabilties no less).  What if we could interface with
that and bypass MySQL?

As it turns out, I was beaten to the punch.  Perhaps twice.

There appear to be two solutions that have been developed to address
this.  The first appears to be something called handlersocket.  This
is a MySQL plugin that handles request over a socket and then goes
directly to the InnoDB storage engine - bypassing MySQL's parsing.  It
is claimed that they're able to process 750,000 queries per second as
opposed to 100,000 queries/sec going through the normal MySQL path on
the same hardware.

Apparently, this news has made a big enough splash that an official
solution is in development - but through an enhancement in something
called memcached.

I don't understand it completely, but this appears to be adding
similar functionality into a memory caching plug-in so that we get the
benefit of not only low level I/O, but memory caching as well.

Unfortunately, neither of these are mature yet (and not available on
Windows servers), but the implication is that we could get back
connected datasets which massively outperform SQL queries while still
retaining the capability to do complex queries in SQL for reporting
and such.  The best of both worlds.

There's also an embedded InnoDB option which seems now to be
maintained as HailDB.  I'm unclear on some of the details and how it
might fit in.

This paradigm shift might be very significant.

So, to take this further, I started to think about what I'd like to
see.  This is just brainstorming that I haven't thought through
completely...

Imagine if we extended Lazarus so that we could include 'virtual
units'....

e.g.

Uses Customers in MyDatabase at 127.0.0.1 Alias Customer

This might create a virtual unit 'Customer' which would define fields
as read from the database at project load and/or runtime.  But it
would not be stored with the project.  The Customer class would be
created from the database during initialization.

Now we could do things like...

Customer.Seek(112);
Customer.Name := 'Acme, Inc.';
Customer.Update;

----------------------------------------------------------------------
Leledumbo · 2011-08-13 01:04

Seems like ORM to me (well... more or less)