V4RB - Speed of Stored Procedures

[Bart Pietercil]

Hi Kim,

On 4-mei-07, at 07:41, Kim Kohen wrote:

> On 04/05/2007, at 2:42 PM, Claudius Sailer wrote:
>
>> 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.

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


Bart