Should I use a Stored Procedure to execute a complex SELECT query?

Question

I'm working on what is turning out to be a fairly complex SELECT query. I have several hierarchical queries being nested in a single SELECT and it is getting to be quite difficult to manage.

I'm running into a few places where my inline views need to be executed in more than one place, so it seems like a reasonable idea to execute those once at the beginning of a stored procedure and then do some iteration over the results as needed.

I'm wondering if there are any reasons why I should not, or could not, execute an Oracle Stored Procedure, called via my PHP code, and return as an OUT parameter the resultset. I've tended to use SPs only to do updates/deletes/inserts but the sheer size and complexity of this query seems like it needs to be broken down.

If there aren't any technical problems with this, any comments on whether it is good or bad practice?

Answer 1

Im working on what is turning out to be a fairly complex SELECT query. I have several hierarchical queries being nested in a single SELECT and it is getting to be quite difficult to manage.

Ok, but why a stored procedure? Why not create a view instead?

I'm running into a few places where my inline views need to be executed in more than one place, so it seems like a reasonable idea to execute those once at the beginning of a stored procedure and then do some iteration over the results as needed.

Again - excellent use case for a view.

I'm wondering if there are any reasons why I should not, or could not, execute an Oracle Stored Procedure, called via my PHP code, and return as an OUT parameter the resultset. If there aren't any technical problems with this, any comments on whether it is good or bad practice?

Well, I don't want to start a religous war, and I do not want to suggest the arguments against apply to your case. But here goes:

• one reason why I tend to avoid stored procedures is portability - by that I mean mostly database portability. Stored procedure languages are notoriously unportable across dbs, and built-in libs like Oracle packages make things worse in that respect.
• stored procedures take some additional processing power from your database server. this makes it harder to scale the application as a whole: if the capacity of your db server is exhausted due to stored procedures, and you need to upgrade harware or even buy an extra oracle software license because of that, I would not be a happy camper, especially if I could have bought cheap webserver/php boxes instead to do the computing.

Reasons where I would go for stored procedures:

• language portability. If database portability is not so much an issue, but you do want to reuse logic across multiple applications, or have to ability to code in different languages, then stored procedures may save you writing language specific database invocation code.
• complex permission scenarios. stored procedures give you uan extra level of permissions, since you can execute the procedure with the privileges of the definer or owner of the stored procedure. Sometimes this solves problems where a user needs to work with some tables, but cannot be allowed direct access to them.
• saving rountrips: if you have to deal with complex, multistatement transactions, putting them in a stored procedures saves rountrips between the app and the db, because there is only one rountrip needed to execute the stored procedure. sometimes this can get you more performance.

I want to stress again that in all these scenarios, I would still advise to not put all your procedural logic in stored procedures. databases are best at storing and retrieving data, languages like php/java/perl/pick your poison are better at processing it.

Answer 2

You could look at subquery factoring which may improve the readability of the query. One risk of breaking up a single SQL query into a more procedural solution is you lose read consistency. As such you want to be pretty sure that someone changing data while your procedure runs won't break it. You may want to lock a table fore the duration of the procedure call. It seems drastic, but if you are pretty sure that the data is static and if there would be ugly side-effects if it wasn't, then it is a solution.

Generally if an SQL statement is complex enough, it probably isn't portable between databases anyway, so I wouldn't worry about that aspect.

Views can be a good option to hide complexity, but the downside to hiding complexity is that people start doing things that seem 'simple' but are really complex and don't work as desired. You also get another object to consider for grants etc. [Edit: As Roland commented, this applies equally to stored procedures, views, object types etc.]

If you expect to return a large resultset, you should consider a pipelined table function. That way you can avoid having the entire resultset in the Oracle session at the same time.

Answer 3

If you are using the same inline view many times, its a good candidate for with clause

Answer 4

PHP can handle resultsets returned from stored procedures, by using Ref Cusrors. The Oracle+PHP Cookbook has an example.

So there are no technical impediments but as you can see from the various answers there are some philosophical aspects to your question. I think we can agree that if you are already wrapping some SQL statements in stored procedures - which you are - then you are not drastically compromising the portability of your system by extending "updates/deletes/inserts" to include selects.

The pertinent question then becomes "should you embed use a stored procedure for this particular query?" The answer to which hinges on precisely what you mean by:

the sheer size and complexity of this query seems like it needs to be broken down.

Deconstructing a big query into several smaller queries and then stitching results together in PL/SQL is seductive, but should be approached with caution. This can degrade the performance of your application, because PL/SQL has more overheads than SQL. Making your query more readable is not a good enough reason: you need to be certain that the complexity has a real and adverse effect on the running of your code.

A good reason for using a stored procedure rather than a view might be if you want to extend the applicability of the query by using bind variables or dynamic SQL in the body of the query.

A definitive answer to your question requires more details regarding the nature of your query and the techniques you are thinking of using to simplify it.