Hi Liran,
----- Original Message ----- > From: "Liran Zelkha" <[email protected]> > To: "Laszlo Hornyak" <[email protected]> > Cc: "Liran Zelkha" <[email protected]>, [email protected] > 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" <[email protected]> > >> To: "Eli Mesika" <[email protected]> > >> Cc: [email protected] > >> 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" <[email protected]> > >> To: [email protected] > >> Sent: Tuesday, April 2, 2013 12:35:03 AM > >> Subject: Re: [Engine-devel] Move SQL out of stored procedures > >> > >> > >> > >> ----- Original Message ----- > >>> From: "Laszlo Hornyak" <[email protected]> > >>> To: "Libor Spevak" <[email protected]> > >>> Cc: "Juan Hernandez" <[email protected]>, [email protected] > >>> Sent: Thursday, March 28, 2013 5:31:34 PM > >>> Subject: Re: [Engine-devel] Move SQL out of stored procedures > >>> > >>> > >>> > >>> ----- Original Message ----- > >>>> From: "Libor Spevak" <[email protected]> > >>>> To: "Itamar Heim" <[email protected]> > >>>> Cc: "Juan Hernandez" <[email protected]>, [email protected] > >>>> 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" <[email protected]> > >>>>>>> To: [email protected] > >>>>>>> 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 > >>>>> [email protected] > >>>>> http://lists.ovirt.org/mailman/listinfo/engine-devel > >>>> > >>>> _______________________________________________ > >>>> Engine-devel mailing list > >>>> [email protected] > >>>> http://lists.ovirt.org/mailman/listinfo/engine-devel > >>>> > >>> _______________________________________________ > >>> Engine-devel mailing list > >>> [email protected] > >>> http://lists.ovirt.org/mailman/listinfo/engine-devel > >>> > >> _______________________________________________ > >> Engine-devel mailing list > >> [email protected] > >> http://lists.ovirt.org/mailman/listinfo/engine-devel > >> _______________________________________________ > >> Engine-devel mailing list > >> [email protected] > >> http://lists.ovirt.org/mailman/listinfo/engine-devel > >> > > _______________________________________________ > > Engine-devel mailing list > > [email protected] > > http://lists.ovirt.org/mailman/listinfo/engine-devel > > _______________________________________________ Engine-devel mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-devel
