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