[Engine-devel] Move SQL out of stored procedures

Eli Mesika emesika at redhat.com
Tue Apr 2 09:29:31 UTC 2013 


----- Original Message -----
> From: "Yair Zaslavsky" <yzaslavs at redhat.com>
> To: "Liran Zelkha" <liran.zelkha at gmail.com>
> Cc: engine-devel at ovirt.org
> Sent: Tuesday, April 2, 2013 10:15:06 AM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> 
> 
> 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?)

Hybrid solutions tend to waste much more time than they save ...

> 
> 
> 
> 
> 
> 
> > >> 
> > >> ----- 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
> > 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
>

More information about the Engine-devel mailing list