Subject Re: [IBO] Slow lookups over remote connect
Author Bob
Thanks Geoff for the reply. You're right, the SELECT * FROM TABLENAME is
costly, and it appears that's where the bulk of my delay is. Your other
hints will help me speed it up even further.

Regarding the application, the remote sites currently connect to home office
via Citrix (Term Svcs), but we had considered setting up in each remote site
a local server with the database replicating to each site from the home
office. This way the users are connecting to a local database. Problem with
Citrix is that if and when the WAN links go down (which happens
occasionally), Citrix connectivity is obviously cut off and the remote sites
cannot function, people sit idle, and money goes down the drain.

Do you have anything to say about replication? To be honest, I don't know
anything yet except that it is theoretically possible. Does it sound like an
appropriate solution or am I barking up the wrong tree?

Thanks again.


BTW, looks like you forgot the ".au" on your website link below.

"Geoff Worboys" <geoff@...> wrote in message
> Hi Bob,
> There is a lot to consider when building an application that needs to
> operate over a slow link. Personally I prefer a thin-client solution
> to avoid/minimise such problems (MS Terminal Server etc).
> However IBO does have ways and means of making slow links work
> acceptably...
> One of the first areas of potential problem is application startup -
> or more specifically the first use of the connection. This can cause
> IBO to fetch lots of information about the database schema (metadata).
> To avoid excessive delays IBO's TIB_Connection component has a
> SchemaCacheDir property which can cache the necessary information
> between connections - so the large delay happens only on the first
> connection, and then the local cache is used on subsequent
> connections.
> Please read the online help for that property for additional
> information. Note that the property should not be defined while you
> are still developing (other than for testing the property itself)
> because there will be no benefit if you are constantly updating the
> database metadata.
> Any lookup datasets (attached to ib_lookupcombo) can be optimised
> using the POS attribute inside the ordering links. eg.
> Field_Name=ITEM=1;POS=3
> says that the lookup does not proceed until 3 characters have been
> input. POS=0 does a sort of automated optimisation, but POS=2 or
> greater can be very beneficial when performing lookups on large
> datasets over slow links.
> If you anticipate the user may wish to scroll a dataset that has a
> large number of lookups then you may find it useful to try my
> LookupEnh component ( which
> supports automatically disconnecting lookup datasets when not
> required.
> I suggest using search mode rather than searching links. So that the
> user is encouraged to always select the minimum number of records to
> meet their requirement. IBO makes it easy to setup and the benefits
> can be huge. (See the contacts sample app).
> As you already hinted; a statement like SELECT * FROM ATABLE is not
> appropriate over a slow link (or at any time with client/server).
> Always setup to select only the fields that are actually required.
> Experiment with FetchWholeRows as either true or false. Depending on
> the circumstances this can lead to good performance benefits. For
> example small rows sizes or small result sets (lookup datasets) often
> benefit from FetchWholeRows=true, while large row sizes often benefit
> from false.
> And on a more general note... Client/Server is generally well suited
> to slow links - provided you can get used to designing appropriately.
> Perform work at the server whereever possible (stored procedures and
> triggers), design your client app to retrieve the minimum amount of
> data possible from the server, setup your forms to encourage users to
> search for specific records rather than retrieving entire datasets and
> scrolling.
> hth
> --
> Geoff Worboys
> Telesis Computing
> IB Objects - direct, complete, custom connectivity to Firebird or
> without the need for BDE, ODBC or any other layer.
> - your IBO community resource for Tech Info
> keyword-searchable FAQ, community code contributions and more !
> Your use of Yahoo! Groups is subject to