You might consider making a select procedure that has input parameters so
that all of the user requests are sent through them. I assume the output
columns are going to remain consistent. Don't be bashful about a whole lot
of different SELECT statements within IF ( ) THEN ... ELSE IF () THEN ELSE
... and so on.

One of mine has a combination that takes about 50 SELECT statements cased
out. The nice thing is there is a clean uniform search interface and the
ability to do changes to the logic right in the server code as well as
introduce custom exceptions that will alert users to invalid search
criteria.

In short, by having only a single stored procedure select statement to
prepare your users can provide all sorts of different search conditions and
no additional statement prepares are needed. Just put the values into the
input parameters and reexecute the query.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

-- We may not have it all together --
-- But together we have it all --

----- Original Message -----
From: <ibo@...>
To: <IBObjects@yahoogroups.com>
Sent: Monday, January 20, 2003 6:59 AM
Subject: Re: [IBO] How to reduce net traffic on prepare?


> Thanks Helen and Lester. I know after the first prepare the
> subsequent execute generate very small traffic (about 1K in my
> example). However I cannot use this approach due to user requirement
> (generate complex where clause criteria by checking many options on a
> form), I have to use ad hoc sql statements. I already turned on
> schema cache, but it doesn't help much. If there are no such switches
> to turn down traffic on IBO, my only option is create a separate web
> interface for remote users, which is not too attractive. Argh...:-(
>
> Rgds,
> Michael
>
>
> --- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> > At 09:30 AM 20/01/2003 +0000, you wrote:
> > >--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...>
> wrote:
> > > > At 08:13 AM 19/01/2003 +0000, you wrote:
> > > > >Dear Jason,
> > > > >
> > > > >I have measured the network traffic generated by a prepare of a
> > > > >simple query. It takes about 40Kbytes server to client, and
> 1Kbytes
> > > > >client to server. Since the query is so simple, there
> shouldn't be
> > > > >such amount of information coming from the server. Is it
> possible
> > >to
> > > > >make IBO to lower the server to client traffic?
> > > > >
> > > > >Below is the prepare process I captured in the monitor:
> > > > >
> > > > >19/1/2003 15:58:10 - /*---
> > > > >PREPARE STATEMENT
> > > > >TR_HANDLE = 4149900
> > > > >STMT_HANDLE = 4143644
> > > > >
> > > > >SELECT ID
> > > > > , NAME
> > > > > , UNITEST
> > > > > , TESTDM
> > > > >FROM DUMY
> > > > >
> > > > >PLAN (DUMY NATURAL)
> > > > >
> > > > >FIELDS = [ Version 1 SQLd 4 SQLn 30
> > > > > DUMY.ID = <NIL>
> > > > > DUMY.NAME = <NIL>
> > > > > DUMY.UNITEST = <NIL>
> > > > > DUMY.TESTDM = <NIL> ]
> > > > >
> > > > >SECONDS = 1.047
> > > > >----*/
> > > >
> > > > Hello Michael,
> > > > Could you begin by providing details of the conditions under
> which
> > >you
> > > > observed this? Since slow performance is a mix of network
> > >conditions,
> > > > metadata structure and application conditions, without
> information
> > >it is
> > > > impossible to determine what might be causing your problems.
> > >
> > >Dear Helen,
> > >
> > >The point is I know the user will use access from place to place,
> the
> > >network condidtion is sometimes good sometimes poor, I have no
> > >control over it. That is why I want to minimize the traffic
> required,
> > >so that the speed will not be too unbearable during slow
> connections
> > >(like using modem dialup). Yes, the real challenge here is to make
> > >traffic as small as possible. I just hope there is some switches in
> > >IBO that can turn down the traffic.
> >
> > Switches - not. You need a Prepare for the server to validate what
> the
> > client is asking it to do. But you should design your applications
> so that
> > a Prepare happens only once. Don't construct ad hoc SQL statements
> at
> > run-time but use parameters.
> >
> > If the metadata are stable, try using local metadata caching. It
> will add
> > some time to connecting, but will reduce the Prepare time of
> subsequent
> > queries.
> >
> > If your clients on slow connections are going to be inserting
> records, use
> > the ColumnAttributes for defaults - don't use GetServerDefaults, as
> this
> > will really add traffic.
> >
> > When timing your Prepares, be sure to do your timings *after* the
> > connection has been established, otherwise your initial prepare
> will have a
> > lot of overhead that is not repeated later.
> >
> > Of course, you know the tricks about indexing, I'm sure, even
> though your
> > demo query has no primary key.
> >
> > Are your clients actually logging into a database server across a
> dial-up
> > connection? This isn't really "on" unless they have broadband:
> this kind
> > of scenario really calls for n-tier, so that your application
> server does
> > all the querying and your clients are just lightweight terminals,
> sending
> > and receiving as few packets as possible across the wire...
> >
> > Helen