Devart Forums

When using synonyms in TSmartQueries, the resulting performance is very poor compared to regular tables.

I am using Oracle 10g enterprise edition 10.2.02.0 with Delphi 7 and ODAC 5.70.1.34.
I have 2 TSmartQueryies qryENMES and qryENMVA located on a data module which are both connected to the same session TOraSession, also located on a data module. The session uses "Net" to connect.
With the session, I tried both leaving the "schema"-property empty and also setting it to "CREATOR", but there was no change in the result.

I have tables enmesseinheit, enzaepunkt, enmesverbrauchsarten belonging to the schema creator and public synonyms:
enmva for creator.enmesverbrauchsarten
enzpt for creator.enzaepunkt
enmes for creator.enmesseinheit

I open the queries like this:

qryENMES.Close;
qryENMES.Open;
qryENMVA.Close;
qryENMVA.Open;

The resulting trace is:
13.35.01.796
--------------------------------------------------------------------------------
select * from ENMES, ENZPT
where mes_id=8210755
and ENMES.zpt_id = ENZPT.zpt_id

13.35.02.187
--------------------------------------------------------------------------------
select * from enmva
where mes_id = 8210755
order by vba_nr

13.35.02.500
--------------------------------------------------------------------------------

Which means, the first query takes 391 msecs, the second one 313 msecs. This is very poor performance. Executing the statements with TOAD, the first query takes 62 msec, the second one 16 msecs.

Now I replaced the public synonyms in the beforeOpen-event by schema.table. The resulting trace is:

13.36.49.843
--------------------------------------------------------------------------------
select * from creator.enmesseinheit MES, creator.enzaepunkt ZPT
where mes_id=8210755
and MES.zpt_id = ZPT.zpt_id

13.36.49.890
--------------------------------------------------------------------------------
select * from creator.enmesverbrauchsarten
where mes_id = 8210755
order by vba_nr

13.36.49.906
--------------------------------------------------------------------------------

Which means, the first query takes 47 msecs, the second one 16 msecs. This is perfectly ok, about the same result TOAD or SQLPlus has.

Unfortunately our whole application is based on public synonyms and cannot be switched to schema.table easily.

I would appreciate your immediate help in this matter.

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 additional queries, which increases the time of opening the query.
You can use TOraQuery component instead of TSmartQuery, and performance should be better.

Replacing TSmartQuery by TOraQuery is a big problem since all SQLInsert, Delete or Update - Statements have to be generated and added in the application.

The problem is not reading the meta data in TSmartQuery itself. The problem is that accessing the meta data using the synonym is terribly slow (it wasn't so with ODAC 4 and Oracle 9i, we're using ODAC for more than 5 years now - there was no performance problem back then).

Both statements below (313 msecs for the synonym, 16 msecs for schema.table) are updatable TSmartQueries connected to a data source and a DBGrid, so the meta data access takes place in both cases and can't be the problem. It has to be the public synonym, which somehow may have a performance leak in the new ODAC version.

Please check this again.


13.35.02.187
--------------------------------------------------------------------------------
select * from enmva
where mes_id = 8210755
order by vba_nr

13.35.02.500
--------------------------------------------------------------------------------

13.36.49.890
--------------------------------------------------------------------------------
select * from creator.enmesverbrauchsarten
where mes_id = 8210755
order by vba_nr

13.36.49.906
--------------------------------------------------------------------------------

In present version of ODAC we use different from ODAC 4 way to detect which fields in the query are editable. These changes were made because the way used in ODAC 4 doesn't work correctly in some cases.