Re^3: arranging code that works with SQL databases in Perl

Putting everything into Oracle procedures is the approach the project I'm working on right now has taken.

Can you comment on how fast ORacle stored procs are compared to SQL? I know that the query optimizer has no access to stored functions in MySQL and that things often run many times slower.

Also, how easy is it to dynamically generate SQL with stored procedures? What if you had a search form with an number of optional fields and various comparison operators? Could you build up the SQL piece-wise and then evaluate it after constructing it?

Finally, I find the stored procedure language quite a bit more wordy than Perl. It would seem that if the database knew the relations of your tables that it could generate common things you would want:

given this id, get the related info in all tables which use this id as a foreign key
instead of returning a cursor to a result set, give me the count of the result set
paginate the result set: given an offset and row count, point the return cursor there

Now, the SP approach does have its advantages, but I'm just exploring some potential cons.

Comment on Re^3: arranging code that works with SQL databases in Perl

Replies are listed 'Best First'.
Re^4: arranging code that works with SQL databases in Perl by mje (Curate) on Sep 02, 2009 at 17:04 UTC
As far as I am aware there is no disadvantage to using cursors inside oracle stored procedures or functions. In fact, the cursors can be cached. I don't use mysql so cannot comment on that. You can use dynamic sql in oracle stored procedures but it is generally not such a good idea as oracle cannot know the sql you will be running so optimization of it prior to running it is less likely. Our project uses no dynamic sql at all (we have gone out of our way to avoid it). If a procedure/function which generates and returns a cursor can take optional arguments (e.g., ones we we use in the SQL) then we default them to NULL and ensure the SQL does not perform the comparison on the column if it is NULL e.g., (where (param is NULL) or (param is not null and param = column). I don't really see any cons to using procedures in oracle and for a) speed and optimization, b) keeping logic in the database near the actual schema and data it is unmatched. However, if your only option is building dynamic sql then this is easily done in Perl.	[reply]

Replies are listed 'Best First'.

Re^4: arranging code that works with SQL databases in Perl
by mje (Curate) on Sep 02, 2009 at 17:04 UTC

As far as I am aware there is no disadvantage to using cursors inside oracle stored procedures or functions. In fact, the cursors can be cached. I don't use mysql so cannot comment on that.

You can use dynamic sql in oracle stored procedures but it is generally not such a good idea as oracle cannot know the sql you will be running so optimization of it prior to running it is less likely. Our project uses no dynamic sql at all (we have gone out of our way to avoid it). If a procedure/function which generates and returns a cursor can take optional arguments (e.g., ones we we use in the SQL) then we default them to NULL and ensure the SQL does not perform the comparison on the column if it is NULL e.g., (where (param is NULL) or (param is not null and param = column).

I don't really see any cons to using procedures in oracle and for a) speed and optimization, b) keeping logic in the database near the actual schema and data it is unmatched. However, if your only option is building dynamic sql then this is easily done in Perl.

[reply]