Devart Forums

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

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.

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.

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.

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.

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