V4RB - Speed of Stored Procedures

[Ruslan Zasukhin]

On 4/5/07 9:53 AM, "Bart Pietercil" <bart.pietercil at gmail.com> wrote:

Hi All,

>>> at the moment I have in RB a function where I build my SQL-
>>> Commands. After that I send the build Select-Statement to the
>>> database an generate a cursor. Whats happen with the speed, when I
>>> store this SQL-Commands as stored procedure in the database and
>>> call it with variables to get a cursor again. is this then faster
>>> or slower then the old way?
>>> Or doesn't it make sense to use stored procedures?
>> €€€€
>> Following up on this, I have an app where I save two rows for every
>> insert.
>> 
>> One goes to the main table and keeps a single copy of the record
>> and the other goes to a 'history' table where versions of the
>> entries are kept. I simply do two inserts now and I'd be interested
>> if a stored procedure or trigger would be faster than calling two
>> inserts statements.
> 
> 
> Seems to me it would not only be faster but also way easier to code.

In general right.

> On Insert MainTable
>    store old record in historytable
> end
> 
> Have a trigger on insert History table where you timestamp every
> inserted record
 
> I think you can even send along a variable with the name of the
> person that did the insert so that you can store this along
> 
> If you would do the same for update and delete triggers your history
> table will be a through log table which with a bit of programming
> could even be used to do rollbacks

** Triggers and stored procedures **

Are good BECAUSE

1) your business-logic is stored INSIDE of vdb

    why this is good? Because now, even if you will open db in Valentina
Studio, and do insert into T1, this can fire trigger which will do insert
into e.g. T2 to keep your data integrated.

    Or some developers develop in the same time in RB and say Director or
C++. Now there is no need repeat the same logic in RB code and in C++ code.
It will go into DB itself.


2) Triggers are EVENTS.
    
    You can use e.g. BEFORE INSERT trigger to do some checks of values
    and even correct them.

    e.g. You want that f1 was in range 0..100,
    make trigger which check this and if needed correct f1 value.
    then only INSERT will happens

    and again -- this logic will work magically for ANY ADK and
    even in Valentina Studio. Because it is inside of DB.


3) SPEED

    You can win A LOTS if you work with Valentina Server,
    BECAUSE stored procedures and Triggers (which also are stored procedures
    in fact, just they called automatically on some event) allow you do
    some operations on SERVER-side without any network communication.

    So the main WIN in speed -- is to REDUCE network operation.


4) SPEED of ADKs....

    well, I think here also can be expected some win in speed, because
    stored procedure is the same SQL string, parsed by engine into
    C++ structures, and later works pure C++ code. There is no communication
            RB <=> V4RB <=> Engine



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]