Subject Re: [IBO] How to reduce net traffic on prepare?
Author Michael Fung <ibo@cmsweb.com>
Thanks Jason, that's a good workaround.

Rgds,
Michael


--- In IBObjects@yahoogroups.com, "Jason Wharton" <jwharton@i...>
wrote:
> 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@c...>
> 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