[Engine-devel] Move SQL out of stored procedures

[Juan Hernandez]

On 03/27/2013 09:19 AM, Alon Bar-Lev wrote:
>
>
> ----- Original Message -----
>> From: "Juan Hernandez" <jhernand at redhat.com>
>> To: "Alon Bar-Lev" <alonbl at redhat.com>
>> Cc: engine-devel at ovirt.org, "Eli Mesika" <emesika at redhat.com>, "Yair Zaslavsky" <yzaslavs at 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 at redhat.com>
>>>> To: engine-devel at 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.