[Engine-devel] Move SQL out of stored procedures

Tue Apr 2 06:54:13 UTC 2013

----- Original Message -----
> From: "Eli Mesika" <emesika at redhat.com>
> To: engine-devel at ovirt.org
> Sent: Monday, April 1, 2013 11:35:03 PM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> 
> 
> 
> ----- Original Message -----
> > From: "Laszlo Hornyak" <lhornyak at redhat.com>
> > To: "Libor Spevak" <lspevak at redhat.com>
> > Cc: "Juan Hernandez" <jhernand at redhat.com>, engine-devel at ovirt.org
> > Sent: Thursday, March 28, 2013 5:31:34 PM
> > Subject: Re: [Engine-devel] Move SQL out of stored procedures
> > 
> > 
> > 
> > ----- Original Message -----
> > > From: "Libor Spevak" <lspevak at redhat.com>
> > > To: "Itamar Heim" <iheim at redhat.com>
> > > Cc: "Juan Hernandez" <jhernand at redhat.com>, engine-devel at ovirt.org
> > > Sent: Thursday, March 28, 2013 4:04:20 PM
> > > Subject: Re: [Engine-devel] Move SQL out of stored procedures
> > > 
> > > Hi,
> > > 
> > > apart from SQL vs. stored procedures discussion, I am trying to
> > > understand what we can get if we support more databases...
> 
> Sorry for joining this discussion so late (I was in a vacation)
> anyway
> two points missing from SQL VS. SP are
> 1) security - With plain SQL we will have to handle SQL Injection

I do not understand this. What's wrong with PreparedStatement?

> 2) It is more economic to pass a call to SP than the full SQL on the wire...

Ah that is not actually happening with postgresql :) I don't know about all the specific DB's but I am quite sure most other DB does not do that either.
If you have a DataSource, like commons-dbcp, it is caching the PreparedStatements in the background. A PreparedStatement executes a 'PREPARE' command in postgresql
http://www.postgresql.org/docs/9.2/static/sql-prepare.html
After that it will only send over the name of the query plan and the parameters.

I believe it usually does not save a lot on bandwidth, for example engine's SQL statements fit in a single tcp/ip frame, but the query parser and planner needs to run only once, when you create the query plan and that is a big win. I wrote a testfor this once, quite long ago but I remember something around 10% win if the query execution was simple enough. But of course it does not matter much if you have a pile of seqscan in your query plan.

Anyway, this is kind of cool in PostgreSQL :)

> 
> 
> > > 
> > > Some points:
> > > 1. Is there a real need by end-users/customers to run it on e.g.
> > > Oracle
> > > only? (performance, stability, easier administration).
> > 
> > Usually companies have one database and they are trying to stick to that
> > one.
> > Having two doubles the resource needs, you need one more DBA team, care for
> > mirrors, backups. So it almost doubles the costs.
> 
> Generally, I agree with Alon B L , if you have to support X DBs you are not
> doubling the effort by X
> Actually, we had already experience with that when we supported both MS SQL &
> Postgres
> I believe that as we have some customers with large installations,
> performance counts and the best way (and sometimes teh only way) id the DB
> layer

Ok, then let's tell MySQL/MariaDB users to use PostgerSQL and see what happens.

> 
> > This is why I frequently hear people asking if we plan to support XyDB in
> > the
> > future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
> > just do not want one more.
> > 
> > > What is the future of PostgreSQL?
> > > 
> > > 2. Is it decided by architectural board, what kind of databases we
> > > would
> > > like to support? (cannot support any db)
> > 
> > With a JPA we could support most mainstream relational databases, but in my
> > opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
> > maybe we do not have to think in big number of database engines.
> > This is theoretical since JPA is still on wishlist :(
> > 
> > > 
> > > 3. Are we talking about the Engine only, or there will be a need to
> > > rewrite ETL mappings and upgrade DWH database, or maybe modify
> > > JasperReports templates (simply, some DB types behave differently)?
> > > Maybe we can look at JasperSoft solution, they support more
> > > databases.
> 
> IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported
> by Jasper
> 
> > > 
> > > 4. Current full/incremental upgrade process of PostgreSQL is IMHO
> > > very
> > > good tuned (it is similar to dbmaintain.org tool - Java
> > > implementation -
> > > I used successfully on one project - after some changes of course). I
> > > do
> > > not believe we can use or easily develop general upgrade/migration
> > > tool,
> > > and XML based (I am sorry Alissa, not sure about Liquibase, I haven't
> > > studied it deeply, but there is a need to incrementally change db
> > > objects, but sometimes also to migrate data to new structures, the
> > > most
> > > flexible and quickest is to do it using native SQL, but yes, it
> > > depends
> > > on the project needs...).
> 
> I had evaluated Liquibase and I think that managing your DB upgrades via XML
> is very unfriendly and very limited as you reach complex upgrades as we had
> in the past.
> Just think of the tables in which we change the key from long to UUID , there
> is no way to do that in such tools
> 
> > > 
> > > 5. As a developer, with every new column I need to write upgrade
> > > scripts, prepare test environments and test all scenarios several
> > > times
> > > on different databases, so time-consuming.
> 
> Did it also , again , since our SQL is 90% simple , the effort of writing a
> SP for more than one DB is not so high (and you have free converters you can
> use for that)
> 
> Finally, embedded SQL in the Java code is not a good idea, it will be hard to
> maintain it and it is not advancing us in supporting more than one database.
> We have already SQL generated in the Java code on the search engine and IMHO
> this is one of the parts in the applications that needs a rewrite ...
> 
> > > 
> > > 
> > > On 27.3.2013 13:53, Itamar Heim wrote:
> > > > On 03/26/2013 08:39 PM, Alon Bar-Lev wrote:
> > > >>
> > > >>
> > > >> ----- Original Message -----
> > > >>> From: "Juan Hernandez" <jhernand at redhat.com>
> > > >>> To: engine-devel at ovirt.org
> > > >>> Sent: Tuesday, March 26, 2013 7:34:04 PM
> > > >>> Subject: [Engine-devel] Move SQL out of stored procedures
> > > >>>
> > > >>> Hello,
> > > >>>
> > > >>> I would like to start a discussion about the subject. I think
> > > >>> this is
> > > >>> something we need to do if one day we want to be able to use any
> > > >>> database other than PostgreSQL.
> > > >>
> > > >> Hello,
> > > >>
> > > >> I think that database layer is a software interface like any other
> > > >> software interface, if done properly, a dba can convert the stored
> > > >> procedure to any other database without any code change.
> > > >>
> > > >> This way the database specific implementation lives within the
> > > >> database and maintained by the designated dba.
> > > >>
> > > >> Fixups and optimizations can be done in database without touching
> > > >> the
> > > >> code.
> > > >>
> > > >> Backward compatibility layer is much simpler to implement based on
> > > >> stored procedures than complex set of views and tables.
> > > >>
> > > >> Also, accessing the database via different technologies is simpler
> > > >> if
> > > >> there is maintained database interface (stored procedures).
> > > >>
> > > >> I've seen hibernate based java applications that promised to be
> > > >> database independent but at the edges when performance counts, the
> > > >> DAO became HQL, then a special dialect and finally database
> > > >> specific
> > > >> SQLS.
> > > >
> > > > there may be db specific optimization/logic, but I don't see why we
> > > > need STPs for 80% (if not more) of the CRUD and basic queries.
> > > >
> > > > I also agree with Tal later in the thread that its a good question
> > > > if
> > > > we can't find a better solution than re-writing the sql's in the
> > > > code
> > > >
> > > > _______________________________________________
> > > > Engine-devel mailing list
> > > > Engine-devel at ovirt.org
> > > > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > > 
> > > _______________________________________________
> > > Engine-devel mailing list
> > > Engine-devel at ovirt.org
> > > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > > 
> > _______________________________________________
> > Engine-devel mailing list
> > Engine-devel at ovirt.org
> > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
> 