On 03/27/2013 09:19 AM, Alon Bar-Lev wrote:
----- Original Message -----
From: "Juan Hernandez" <jhernand@redhat.com> To: "Alon Bar-Lev" <alonbl@redhat.com> Cc: engine-devel@ovirt.org, "Eli Mesika" <emesika@redhat.com>, "Yair Zaslavsky" <yzaslavs@redhat.com> Sent: Wednesday, March 27, 2013 9:52:35 AM Subject: Re: [Engine-devel] Move SQL out of stored procedures
On 03/26/2013 07:39 PM, Alon Bar-Lev wrote:
----- Original Message -----
From: "Juan Hernandez" <jhernand@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.
You probably mean "any other database that supports stored procedures", which is not the same that "any other database".
Right.
It is very clear what is the interface of a relational database: a set of relations with a set of restrictions.
We can find a lot of definitions, there is a trend no of nosql... which falls into the above...
I talking about relational databases, most self called nosql databases are not relational.
This way the database specific implementation lives within the database and maintained by the designated dba.
I don't now exactly what you mean by "dba", but if you mean "database administrator" I really don't see typical database administrators rewriting stored procedures provided by a product to suite their own database management system.
Maybe by "dba" you mean "the developer of the persistence layer". Will your proposal be maintaining different sets of stored procedures, written in different languages for different database management systems?
Yes, this what I mean.
In my opinion doing that is a waste of resources when you can do just one persistence layer with standard SQL.
Fixups and optimizations can be done in database without touching the code.
Seems that you think that stored procedures aren't "code". What are they then?
Yes they are, stored procedure are code, which is database dependent, with pre-defined interface to the external world.
It doesn't need to be database dependent. In fact most of the stored procedures that we use today are just plain wrappers for SQL statements that are perfectly standard SQL (there are exceptions, of course). The pre-defined interface of the database to the external world should be well structured data, with restrictions that ensure consistency.
The language you chose depends on database capabilities, for Oracle and DB2 you can write stored procedure in Java...
So it would be acceptable to write that logic in Java and deploy it inside the database, but it isn't acceptable to write that same logic in java and deploy it inside the application?
This code is part of application, it is maintained within the same release milestones, commits etc.
Yes, the n versions of the stored procedures are maintained and kept in sync. Far from ideal.
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).
It also means that you make the database a procedural system, and it shouldn't be. A database should not contain logic, only data. Logic changes quite frequently and data needs to survive for a long long time.
This is your definition... I think the opposite... database should contain logic, it is procedural system. Implementing the logic within database enable you to enjoy the performance provided by the database, and simplifying your application.
This logic is part of your code, a change in application derives a change in the database layer as well, nothing prevents you in changing logic.
That is very wrong, in my opinion. A relational database is not a place for your logic and it is not a procedural system. The SQL language is a declarative language, not procedural, and the nature of the querying concept, even without the SQL language, is declarative, not procedural. The procedural languages have been added to relational databases as after thought extensions, and they have proven to be very good tools to lock users to their database vendors. Ask anyone who has tried to escape from Oracle's PL/SQL (or any other similar thing). Regarding performance it is much more relevant to reduce the number of queries sent to the database and to improve the performance of complex queries than having/not having them in stored procedures. Regarding complexity, I don't really see how a SQL query is more complex than a call to a stored procedure than in turns executes the same SQL query, I would say the opposite: the stored procedure is just an additional step, an additional thing to maintain, so it increases complexity. Anyhow, look at most of the stored procedures that we currently have and you will see that they just wrap a plain SQL statement. And when something is changed, adding a new column, for example, a lot of things have to change: the call from the data access layer, the signature of the procedure, and the query inside. Most of the time this stored procedures just get in the middle without any added value.
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.
I've seen exactly the opposite, if that matters.
Yes, I've seen the opposite in simple applications.
I've seen the opposite in very complex applications, when it comes to performance and scalability.
And I understand the need of the developers to control everything, not distributing logic to other components and technologies which are out of reach of the common developer. The undesired dependency with dba (data model layer developer) for every change in schema or entity.
The fact that SQL statements are out of stored procedures doesn't mean that they have to be maintained by a different type of developer. If they are currently maintained by relational database specialists they can continue to be maintained by relational database specialists once they are outside of the stored procedures.
When optimization, porting or backward/forward compatibility is required the problem falls at the developer's side and usually solved with less knowledge or flexibility.
Just wanted to step in and write that...
As I know what most of the Java developer will probably prefer.
Bottom line, it is all a question of how complex our data model is, and what performance we need out of the data layer. If the model is simple and the performance are insignificant, by all mean, use hibernate.
This is wrong. Hibernate is a good solution exactly when it comes to performance and scalability, there is where it shines, if used correctly. Anyhow, the subject is not Hibernate (or any other ORM solution) versus stored procedures, it is moving the SQL code out of stored procedures, with or without Hibernate.
While we discuss that, I think that best if statistics (or any data which is rolling) will be written to nosql database instead of sql database as there is much less overhead, storage and the need for vacuum and such, it is not a matter of postgesql/mysql I have experience with Oracle and DB2 not coping with rolling data.
Thanks, Alon.
-- Dirección Comercial: C/Jose Bardasano Baos, 9, Edif. Gorbea 3, planta 3ºD, 28016 Madrid, Spain Inscrita en el Reg. Mercantil de Madrid – C.I.F. B82657941 - Red Hat S.L.