Performance problem with "sys_context" calls

cn_wk · Post by **cn_wk** » Mon 03 Jul 2006 07:56

begin :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;

This statement is called on a TSmartQuery everytime the query is created and opened. Unfortunately I habe many queries on my forms and this statement isn't very fast either.

Checking the source I found a GetCurrentSchema call that is executing this command when "Updating Table" does not contain a schema. Since I am using public synonyms for all the tables there is never a schema.
I am using ODAC 5.70.1.33 with Delphi 7 and Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Example
Schema creator, table enkunde, public synonym enknd for creator.enkunde

with updating table = enknd, the sys_context call is executed.
with updating table = creator.enkunde, it is not executed

With ODAC 4 this problem didn't exist. Please let me know, why public synonyms aren't supported.

Thanks for your help

Plash · Post by **Plash** » Mon 03 Jul 2006 12:43

In TSmartQuery component we need to describe fields of the updating table to detect which fields in the query are editable. To do this we need to execute some queries.
TSmartQuery component supports public synonyms. It first searches for table with given name. If such table does not exist TSmartQuery component searches for synonym.
In next version of ODAC we are planning that statements used to describe the table will be executed only when first updating table data. Therefore performance will be better.

cn_wk · Post by **cn_wk** » Mon 03 Jul 2006 13:51

First, when will the new version - solving the performance problem concerning the sys_context calls - be released?

Second, caching the system information alone does not solve our problem completely, since it assumingly only works with queries belonging to data modules. Smartqueries belonging to forms will still execute the sys_context call every time being created.

We recently switched from ODAC 4 to ODAC 5.70 because of Oracle10g support and have now massive performance problems due to these sys_context calls (e.g. tabsheet changes on a page control that took 1sec under ODAC 4 take now 6-7sec due to the sys_context calls (400millisec each - with 10 or more queries on a form!).
Our customers are unwilling to accept this behaviour of our application.

We need a fast solution for this problem.
Thanks for your help.

Challenger · Post by **Challenger** » Fri 07 Jul 2006 13:50

We added functionality of caching current schema value in TOraSession not in TSmartQuery. So if all of your queries are connected to one component TOraSession the current schema value will be requested from server only once.

cn_wk · Post by **cn_wk** » Mon 10 Jul 2006 11:35

I tried to set TOraSession's property Schema, but it didn't change anything. The result is still the same: though the property "Schema" is set to "creator", every TSmartQuery still executes sys_context(CURRENT_SCHEMA).

Are there any other options to be set or is this a bug?

I would appreciate your immediate response.

Thanks.

cn_wk · Post by **cn_wk** » Mon 10 Jul 2006 11:40

PS: all queries have the same session which is located on a data module.

Devart Forums