Subject | Re: [IBO] How to reduce net traffic on prepare? |
---|---|
Author | Michael Fung <ibo@cmsweb.com> |
Post date | 2003-01-22T02:47:15Z |
Thanks Jason, that's a good workaround.
Rgds,
Michael
--- In IBObjects@yahoogroups.com, "Jason Wharton" <jwharton@i...>
wrote:
Rgds,
Michael
--- In IBObjects@yahoogroups.com, "Jason Wharton" <jwharton@i...>
wrote:
> You might consider making a select procedure that has inputparameters so
> that all of the user requests are sent through them. I assume theoutput
> columns are going to remain consistent. Don't be bashful about awhole lot
> of different SELECT statements within IF ( ) THEN ... ELSE IF ()THEN ELSE
> ... and so on.cased
>
> One of mine has a combination that takes about 50 SELECT statements
> out. The nice thing is there is a clean uniform search interfaceand the
> ability to do changes to the logic right in the server code as wellas
> introduce custom exceptions that will alert users to invalid searchto
> criteria.
>
> In short, by having only a single stored procedure select statement
> prepare your users can provide all sorts of different searchconditions and
> no additional statement prepares are needed. Just put the valuesinto the
> input parameters and reexecute the query.requirement
>
> 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
> > (generate complex where clause criteria by checking many optionson a
> > form), I have to use ad hoc sql statements. I already turned onswitches
> > schema cache, but it doesn't help much. If there are no such
> > to turn down traffic on IBO, my only option is create a separateweb
> > interface for remote users, which is not too attractive. Argh...:-(
> >wrote:
> > Rgds,
> > Michael
> >
> >
> > --- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...>
> > > At 09:30 AM 20/01/2003 +0000, you wrote:of a
> > > >--- 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
> > > > > >simple query. It takes about 40Kbytes server to client, andplace,
> > 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
> > themake
> > > >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
> > > >traffic as small as possible. I just hope there is someswitches in
> > > >IBO that can turn down the traffic.what
> > >
> > > Switches - not. You need a Prepare for the server to validate
> > theapplications
> > > client is asking it to do. But you should design your
> > so thatstatements
> > > a Prepare happens only once. Don't construct ad hoc SQL
> > atIt
> > > run-time but use parameters.
> > >
> > > If the metadata are stable, try using local metadata caching.
> > will addGetServerDefaults, as
> > > 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
> > thisthe
> > > will really add traffic.
> > >
> > > When timing your Prepares, be sure to do your timings *after*
> > > connection has been established, otherwise your initial preparea
> > 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
> > dial-upterminals,
> > > 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
> > sending
> > > and receiving as few packets as possible across the wire...
> > >
> > > Helen