On 03/27/2013 09:19 AM, Alon Bar-Lev wrote:
----- Original Message -----
> From: "Juan Hernandez" <jhernand(a)redhat.com>
> To: "Alon Bar-Lev" <alonbl(a)redhat.com>
> Cc: engine-devel(a)ovirt.org, "Eli Mesika" <emesika(a)redhat.com>,
"Yair Zaslavsky" <yzaslavs(a)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(a)redhat.com>
>>> To: engine-devel(a)ovirt.org
>>> Sent: Tuesday, March 26, 2013 7:34:04 PM
>>> Subject: [Engine-devel] Move SQL out of stored procedures
>>> I would like to start a discussion about the subject. I think this
>>> something we need to do if one day we want to be able to use any
>>> database other than PostgreSQL.
>> 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
> which is not the same that "any other database".
> It is very clear what is the interface of a relational database: a
> 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
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
> 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
> your proposal be maintaining different sets of stored procedures,
> written in different languages for different database management
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
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
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
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.
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.