[Engine-devel] Move SQL out of stored procedures

Tue Apr 2 07:15:06 UTC 2013

Hi all,
Sorry for my late response on the issue, I will try to cover as many issues as possible in this email and other emails

----- Original Message -----
> From: "Liran Zelkha" <liran.zelkha at gmail.com>
> To: "Laszlo Hornyak" <lhornyak at redhat.com>
> Cc: engine-devel at ovirt.org
> Sent: Tuesday, April 2, 2013 9: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.

+1 On that approach - some of us already talked about the need to have caching AT LEAST for the static parts.

> 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…

> 
> 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.

+1 on that approach - I remind you all that our data model is a bit complex - for example - we have entities that are composed of views - VM which is based on static, dynamic and statistics information.
Modeling this with hibernate is problematic.
In addition, we will have to introduce a custom mapper for pgsql uuid to either out Guid/NGuid or (as others already suggested) java.util.UUID , hence the desire to have 100% portability already breaks.
Barein mind not all databases support UUID as native types - this is something we need to think of (maybe outside the context of this discussion) - I can tell you that from what I saw so far, mssql , postgresql and h2 databases DO support it. 
In addition we have MLA related stored procedures which have to contain logic and trying to model them as JPA queries will definitely hurt performance.

If we do want to go to hibernate approach (again) as lessons from last time I would:
a. Not try to solve the complex cases - keep hibernate/JPA for relatively CRUD operations - for more complex ones - keep stored procedures (I remind you it is possible to invoke native SQL/Stored procedures from JPA).
b. Consider having a layer of objects (DTOs) that their sole purpose is to work with the JPA layer (let's say that they are in package of org.ovirt.engine.core.dal.entities) and they will map to our existing business entities.
The advantage in this approach is that our business entities (which are currently shared with frontend) will not need to be adjusted/annotated with hibernate/JPA annotations.
The disadvantages in this approach is that we will have a double group of entities - one for DAL and one for BLL/frontend (and this brings up the question on what are the plans of using the REST-API with frontend?)

> >> 
> >> ----- 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

+1 Here about NoSQL for ETL & DWH.
> >> 
> >>>> 
> >>>> 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
> 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
> 