Re: [Engine-devel] Move SQL out of stored procedures

From: "Liran Zelkha" <liran.zelkha(a)gmail.com&gt; To: "Laszlo Hornyak" <lhornyak(a)redhat.com&gt; Cc: "Liran Zelkha" <lzelkha(a)redhat.com&gt;, engine-devel(a)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.

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(a)redhat.com&gt; >> To: "Eli Mesika" <emesika(a)redhat.com&gt; >> Cc: engine-devel(a)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(a)redhat.com&gt; >> To: engine-devel(a)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(a)redhat.com&gt; >>> To: "Libor Spevak" <lspevak(a)redhat.com&gt; >>> Cc: "Juan Hernandez" <jhernand(a)redhat.com&gt;, engine-devel(a)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(a)redhat.com&gt; >>>> To: "Itamar Heim" <iheim(a)redhat.com&gt; >>>> Cc: "Juan Hernandez" <jhernand(a)redhat.com&gt;, engine-devel(a)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(a)redhat.com&gt; >>>>>>> To: engine-devel(a)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(a)ovirt.org >>>>> http://lists.ovirt.org/mailman/listinfo/engine-devel >>>> >>>> _______________________________________________ >>>> Engine-devel mailing list >>>> Engine-devel(a)ovirt.org >>>> http://lists.ovirt.org/mailman/listinfo/engine-devel >>>> >>> _______________________________________________ >>> Engine-devel mailing list >>> Engine-devel(a)ovirt.org >>> http://lists.ovirt.org/mailman/listinfo/engine-devel >>> >> _______________________________________________ >> Engine-devel mailing list >> Engine-devel(a)ovirt.org >> http://lists.ovirt.org/mailman/listinfo/engine-devel >> _______________________________________________ >> Engine-devel mailing list >> Engine-devel(a)ovirt.org >> http://lists.ovirt.org/mailman/listinfo/engine-devel >> > _______________________________________________ > Engine-devel mailing list > Engine-devel(a)ovirt.org > http://lists.ovirt.org/mailman/listinfo/engine-devel