Hi Tatiana,

stored procudures are evaluated and optimised only once (when you create the SP) and then used repeatedly.

Otherwise your database sees always a fresh SQL script, the time consuming evaluation of the request. is done each time you retrieve, ...

so for repeatedly used requests, SP are real performance boosters.

BUT: Whenever you change something in the datamodel (like indexes) you have to re-create the stored procedures in the database.

If they run out of line you are in the same mess as when you add a db-field in the model, but not in your database.

I develop without stored procedures, and only add them when the first round of application testing has been done.

Historically i've seen stored procs make a much bigger performance improvement with Sybase and MS-SQL Server than with Oracle, especially where the application commonly re-uses the same data and caching offsets the sql compilation overhead. What database are you using Tatiana?

Most database query optimisers have improved over time (e.g. query plan caching in SQL Server), and the difference is probably less than it used to be 10 years ago. For instance, I believe that SQL server can sometimes perform better when compiling a SQL query on-the-fly as it can optimise the query plan to the supplied profile, but it used to be the case that Sybase (from which SQL Server was developed) could run up to four times faster using stored procs.

In my opinion, the best option is to test performance with and without procs with your application and database, and then decide which databases you want to use stored procs with (if any), based on the results. Like Theo, I don't use them in development, as they usually need to be rebuilt when you change the database schema or Uniface database connector options (which can also impact performance, as they change the way the database connector behaves).

Regards,

David

It might also be worth noting that some people use stored procedures to control unauthorised database access, i.e. they give users the permissions to use stored procs but not to directly update the tables, in order to reduce the risk of ad-hoc database updates by power users.

Thank you David, great insight.

We are using Informix 7.11 with AIX 6.1. Most of the important queries are very complex and are actually done using views or via services written in C. So there are few native Uniface queries, not used frequently, therefore I question that the amount of work required to keep up those stored procs for all tables in the application adds any value. It is just my casual assessment, based on experience with this application that removing the stored procs  in fact improves performnce. I would like to have some measurments in place and I guess I'll have write something to that end.

Regards,

Tatiana

I have encountered the followig error in U9.03.02 (just after everything - or so I thought - tested Ok without using stored procedures):

8062 - [S] Request terminated (network failure (-25) detected).

The form is reading from a view. We never had stored procedures associated with views. All other forms reading other views run OK. But this view is based on a query with three tables self-joining and it looks like the userver can't handle it and crashes. There is no error message in the userver log. Oddly enough, as soon as the last two parms below are reinstated in the urouter asn file, Uniface is able to run the query.

USYS$INF_PARAMS=online,dirty read,multiple connection,procs,serial insert

The read statement for the view looked something like this:

read order by "field1_b, field2, field1_a, field3, field4, field5"

field1_b and field1_a are the same field in one of the tables that self-joins. If I remove field1_a from the order by, the read executes. If I only leave field1_a and removed all other fields, read fails.

Any similar incidents or insights on this topic? It hurts to think I have to go back to stored procedures just becuase of this one view is unable to work without parms proc, srial insert.

Thanks

Tatiana