Re: Help - Forms 3.0 bind variables

Bryan,
As to whether what you're seeing indicates bad performance, you should monitor the library cache activity (see Admin Guide, Ch. 21) - this will tell you how performance is in that area.

If the library cache needs tuning, and the sql statements you're seeing are down to default forms processing (i.e. using enter query mode) then I'd say that trying to find a work around for that in all your forms would be far more time-consuming and expensive than increasing the memory available to the library cache.

If enter-query mode is not the cause of the sql statements, there are some forms coding considerations you might want to look at. In coding a form, Three ways of influencing what records are selected by a forms query are:

1. Code a "where... order by.." clause on the block.
2. In a master-detail relationship, code a join condition in the detail block definition.
3. Code a pre-query trigger for the block.

It turns out that methods 2 & 3 put actual data values into the sql statements that go into the shared sql area, while 1 puts a bind variable identifier.

i.e. 2 & 3 result in statements like:

  select col1, col2 from table1 where col1 = '01234'; and   select col1, col2 from table1 where col1 = '34557';

whereas 1 results in:

  select col1, col2 from table1 where col1 = :1;

So multiple queries in a form that uses method 2 or 3 will result in multiple similar sql statements in the cache.

Multiple usage of a form that uses method 1, however, will store only one occurrence of the statement because of the use of the generic variable identifier.

So if you have a heavily used form that uses master-detail relationships, it might pay to go back and replace the default 'ENFORCE KEY' mechanism for the detail block with a WHERE... clause on the detail block definition.

Hope this helps,
Steve Turner Received on Tue Feb 20 1996 - 00:00:00 CET