[Engine-devel] Move SQL out of stored procedures

Tue Apr 2 07:17:25 UTC 2013

Hi Liran,

----- Original Message -----
> From: "Liran Zelkha" <liran.zelkha at gmail.com>
> To: "Laszlo Hornyak" <lhornyak at redhat.com>
> Cc: "Liran Zelkha" <lzelkha at redhat.com>, engine-devel at ovirt.org
> Sent: Tuesday, April 2, 2013 8:37:28 AM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> 
> Hi Laszlo,
> 
> I'm currently in the process of adding a caching layer on top of
> JdbcTemplate, which would greatly reduce the number of database activities
> we have, so that would solve the last item you raised.

That's a great news! Thank you!

> I didn't mean the ORM performance is caused by the mapping. I think the
> problem lies in the fact that we will modify our code to have batch updates
> for most insert activities - a thing that is impossible in JPA/Hibernate.
> So, if we'll have some code in SQL and some in ORM - I prefer we stick all
> code to SQL…

I think you can do this with a JPAQL in JPA, but anyway, yes, some code would very likely have to be in rdbms-specific SQL statements.

> 
> On Apr 2, 2013, at 9:34 AM, Laszlo Hornyak wrote:
> 
> > Hi Liran,
> > 
> > I agree that ORM tools in general have to add some mapping overhead, but
> > that overhead is very small compared to the time needed by the database
> > interaction.
> > ORM tools sometimes generate SQL statements that we could imagine being
> > better, I do not think they are as hard for the DB as for example the ones
> > generated by searchbackend. Also, we can do rdbms specific optimizations
> > when needed.
> > Plus we could finally have some caching in ovirt engine and the code would
> > not have to read e.g. the DC record again and again. There are some more
> > like that.
> > 
> > Therefore having a JPA could improve the performance in engine.
> > 
> > Laszlo
> > 
> > ----- Original Message -----
> >> From: "Liran Zelkha" <lzelkha at redhat.com>
> >> To: "Eli Mesika" <emesika at redhat.com>
> >> Cc: engine-devel at ovirt.org
> >> Sent: Tuesday, April 2, 2013 7:24:08 AM
> >> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> >> 
> >> I also apologize for jumping in late...
> >> I think concerning SQL injection we'll be covered by using
> >> PreparedStatements. Since we're using SpringJDBC, most of our code uses
> >> PreparedStatements anyway.
> >> Concerning ORM - I feel it won't really be beneficial to us. I know of
> >> very
> >> few projects who can actually be cross-database, and just maintaining
> >> schema
> >> creation scripts for different databases can be too difficult to maintain.
> >> Also, from a performance perspective, ORM performs worse than regular SQL
> >> (or stored procedures), so it wouldn't be the direction I choose.
> >> I think we should keep using SpringJDBC with either SQL or stored
> >> procedures
> >> (doesn't really matter, whatever is easier to maintain and performs
> >> faster)
> >> and maybe add a better, more generic, RowMapper class.
> >> 
> >> ----- Original Message -----
> >> From: "Eli Mesika" <emesika at redhat.com>
> >> To: engine-devel at ovirt.org
> >> Sent: Tuesday, April 2, 2013 12:35:03 AM
> >> 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
> >> 2) It is more economic to pass a call to SP than the full SQL on the
> >> wire...
> >> 
> >> 
> >>>> 
> >>>> 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
> >> 
> >>> 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
> >> _______________________________________________
> >> 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
> 
> 