----- Original Message ----- > From: "Yair Zaslavsky" <yzasl...@redhat.com> > To: "Liran Zelkha" <liran.zel...@gmail.com> > Cc: engine-devel@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.zel...@gmail.com> > > To: "Laszlo Hornyak" <lhorn...@redhat.com> > > Cc: engine-devel@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" <lzel...@redhat.com> > > >> To: "Eli Mesika" <emes...@redhat.com> > > >> Cc: engine-devel@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" <emes...@redhat.com> > > >> To: engine-devel@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" <lhorn...@redhat.com> > > >>> To: "Libor Spevak" <lspe...@redhat.com> > > >>> Cc: "Juan Hernandez" <jhern...@redhat.com>, engine-devel@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" <lspe...@redhat.com> > > >>>> To: "Itamar Heim" <ih...@redhat.com> > > >>>> Cc: "Juan Hernandez" <jhern...@redhat.com>, engine-devel@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" <jhern...@redhat.com> > > >>>>>>> To: engine-devel@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@ovirt.org > > >>>>> http://lists.ovirt.org/mailman/listinfo/engine-devel > > >>>> > > >>>> _______________________________________________ > > >>>> Engine-devel mailing list > > >>>> Engine-devel@ovirt.org > > >>>> http://lists.ovirt.org/mailman/listinfo/engine-devel > > >>>> > > >>> _______________________________________________ > > >>> Engine-devel mailing list > > >>> Engine-devel@ovirt.org > > >>> http://lists.ovirt.org/mailman/listinfo/engine-devel > > >>> > > >> _______________________________________________ > > >> Engine-devel mailing list > > >> Engine-devel@ovirt.org > > >> http://lists.ovirt.org/mailman/listinfo/engine-devel > > >> _______________________________________________ > > >> Engine-devel mailing list > > >> Engine-devel@ovirt.org > > >> http://lists.ovirt.org/mailman/listinfo/engine-devel > > >> > > > _______________________________________________ > > > Engine-devel mailing list > > > Engine-devel@ovirt.org > > > http://lists.ovirt.org/mailman/listinfo/engine-devel > > > > _______________________________________________ > > Engine-devel mailing list > > Engine-devel@ovirt.org > > http://lists.ovirt.org/mailman/listinfo/engine-devel > > > _______________________________________________ > Engine-devel mailing list > Engine-devel@ovirt.org > http://lists.ovirt.org/mailman/listinfo/engine-devel > _______________________________________________ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel