Hi,

just to explain the printqry output:

printqry measures the server, this explains, that the figures for the query
via client or directly to the server are near identically.
The only thing you can interpret for clientinstallation is the qry respond
and end cputime. This is the time, if you receive more than 1 row as
result,
between receiving the first and displaying the data. this indicates how
many
rows a select delivers. If you want the time for the whole query, you have
to
use something like dbmsinfo('_et_time').

Belonging to your other questions: no, we dont have similiar problems and
therefore never searched in that area. Perhaps you can see more swithing
on the printgca. See System Administrators Guide for explanation.
HTH
Hans




ch.borbach@axes-s
ystems.com
(Christopher) To
Sent by: info-ingres (AT) ams (DOT) org
info-ingres-bounc cc
es (AT) ams (DOT) org
Subject
[Info-Ingres] Slow Ingres Net /
12.08.2004 11:49 Client










We implement our SQL queries in our applications using embedded SQL/C
and the esqlc precompiler. We now have serious performance problems on
the client side.
Our Setup is the following (but we have similar Problems using other
setups):

Server: Win XP Pro, Ingres II, 2.6, DBMS, Net
Client: Win XP Pro, Ingres II, 2.6, Net Client

Responses to queries issued from the client are 7 times (and even
more) slower than the same query issued on the server. The load on the
server ist minimal: no one else than the client has access to it,
because it's a test environment.

We analysed the program execution using a profiler. It reports that
the most time on the client is consumed by a function called GCsync()
in OICLFNT.DLL.

The tmdemo test program from the Ingres SDK shows the same effect.

We also mesured the query answer time using II_EMBED_SET (set
II_EMBED_SET=printqry;qryfile c:\temp\output.txt) while issuing the
same queries on client and server. As far as we can see, the time
difference between a server and client query is negligible, but I'm
not sure if our interpretation of the output is correct (does anybody
knows more details about the correct interpretation of this output?)
Example:
Query text:
execute query (myquery115) 1 255 5400 5599
Query Send Time: Thu Jul 29 13:21:43 2004
Query Response Time: Thu Jul 29 13:21:43 2004
Response CPU Time: 500
Qry End CPU Time: 500

So the key questions are:
- Is anybody out there who has similar problems?
- How can these problems be solved?

Thank you!

Christopher
_______________________________________________
Info-ingres mailing list

Hi Christopher,

If I may say so:
Generally, poor performance often stems from bad application design where a series of small queries are performed (row at a time processing), each of which therefore require a round trip from the client to the server. The same can often be achieved by a single join which therefore sends one request to the server and gets a single larger response from the server. Again similar issues can occur with a series of updates on specific records where a correlated update would achieve the same in a single SQL statement. The other thing to consider would be the use of database procedures to cut down the communication required between client and server.

The gains to be had from fixing application design are often far greater than those you could expect from tweaking GCA communications parameters.

Though, assuming you are already aware about all of that and... if the problem is that large messages are being split for transmission over the net, then the maximum packet size used by the GCA can be controlled by the II_GCA_PIPESIZE environment variable.

The II_GCA_PIPESIZE environment variable is Microsoft Windows specific and is not supported on any other platform. It is used to set the size in bytes of the Named Pipes used as an IPC mechanism in Ingres on Windows. The default pipe size is 4096 bytes and the maximum is 32768 bytes. GCA imposes a 4096 byte limit on GCA message buffers, but it will concatenate them, so increasing this parameter may help.

This parameter existed on OI 1.x and II 2.0, but I do not confirm it's still present in II 2.6 due to numerous changes in GCF between 2.0 and 2.5

If this parameter is still available in 2.6, different tests should be done to define the right value to set for your applications.

HTH,
Cheers
Jean-Luc


-----Original Message-----
From: info-ingres-bounces (AT) ams (DOT) org [mailto:info-ingres-bounces (AT) ams (DOT) org]On
Behalf Of Christopher
Sent: Thursday, August 12, 2004 11:50 AM
To: info-ingres (AT) ams (DOT) org
Subject: [Info-Ingres] Slow Ingres Net / Client


We implement our SQL queries in our applications using embedded SQL/C
and the esqlc precompiler. We now have serious performance problems on
the client side.
Our Setup is the following (but we have similar Problems using other
setups):

Server: Win XP Pro, Ingres II, 2.6, DBMS, Net
Client: Win XP Pro, Ingres II, 2.6, Net Client

Responses to queries issued from the client are 7 times (and even
more) slower than the same query issued on the server. The load on the
server ist minimal: no one else than the client has access to it,
because it's a test environment.

We analysed the program execution using a profiler. It reports that
the most time on the client is consumed by a function called GCsync()
in OICLFNT.DLL.

The tmdemo test program from the Ingres SDK shows the same effect.

We also mesured the query answer time using II_EMBED_SET (set
II_EMBED_SET=printqry;qryfile c:\temp\output.txt) while issuing the
same queries on client and server. As far as we can see, the time
difference between a server and client query is negligible, but I'm
not sure if our interpretation of the output is correct (does anybody
knows more details about the correct interpretation of this output?)
Example:
Query text:
execute query (myquery115) 1 255 5400 5599
Query Send Time: Thu Jul 29 13:21:43 2004
Query Response Time: Thu Jul 29 13:21:43 2004
Response CPU Time: 500
Qry End CPU Time: 500

So the key questions are:
- Is anybody out there who has similar problems?
- How can these problems be solved?

Thank you!

Christopher
_______________________________________________
Info-ingres mailing list

<hans.weisenberger (AT) mmm (DOT) com> wrote

Sadly, this explanation is not correct. printqry *always* measures the
response time seen by the client, not the server (by which I mean the Ingres
server *process*). The query send and response times in the trace are
entirely reliable.

Any difference between two dbmsinfo('_et_time') calls is meaningless in this
situation. The client can't send the second call until the delay has
finished, and if it was not an Ingres delay--maybe the client was doing
something computationally intensive, or had other problems of its
own--looking at dbmsinfo() would mislead you into thinking it was an Ingres
delay. In fact all it would show you is the time elapsed while executing
two statements, which is not the same as the time to execute one statement.

Client CPU time is no doubt related to the number of rows processed, but
there is no practical way to interpret the difference that I know of.
Looking at the buffered I/O count (_bio_cnt) shows the amount of
client/server communication directly.

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"

fyi, in moving from ingresnet 2.0 to ingresnet 2.6 we found a 10-20% speed
improvement in some cases where lots of queries were being sent in a short
space of time. No tweaking required.

Paul


-----Original Message-----
From: Hampton, Jean Luc [mailto:JeanLuc.Hampton (AT) ca (DOT) com]
Sent: Thursday, 12 August 2004 23:56
To: Christopher; info-ingres (AT) ams (DOT) org
Subject: RE: [Info-Ingres] Slow Ingres Net / Client


Hi Christopher,

If I may say so:
Generally, poor performance often stems from bad application design where a
series of small queries are performed (row at a time processing), each of
which therefore require a round trip from the client to the server. The
same can often be achieved by a single join which therefore sends one
request to the server and gets a single larger response from the server.
Again similar issues can occur with a series of updates on specific records
where a correlated update would achieve the same in a single SQL statement.
The other thing to consider would be the use of database procedures to cut
down the communication required between client and server.

The gains to be had from fixing application design are often far greater
than those you could expect from tweaking GCA communications parameters.

Though, assuming you are already aware about all of that and... if the
problem is that large messages are being split for transmission over the
net, then the maximum packet size used by the GCA can be controlled by the
II_GCA_PIPESIZE environment variable.

The II_GCA_PIPESIZE environment variable is Microsoft Windows specific and
is not supported on any other platform. It is used to set the size in bytes
of the Named Pipes used as an IPC mechanism in Ingres on Windows. The
default pipe size is 4096 bytes and the maximum is 32768 bytes. GCA imposes
a 4096 byte limit on GCA message buffers, but it will concatenate them, so
increasing this parameter may help.

This parameter existed on OI 1.x and II 2.0, but I do not confirm it's still
present in II 2.6 due to numerous changes in GCF between 2.0 and 2.5

If this parameter is still available in 2.6, different tests should be done
to define the right value to set for your applications.

HTH,
Cheers
Jean-Luc


-----Original Message-----
From: info-ingres-bounces (AT) ams (DOT) org [mailto:info-ingres-bounces (AT) ams (DOT) org]On
Behalf Of Christopher
Sent: Thursday, August 12, 2004 11:50 AM
To: info-ingres (AT) ams (DOT) org
Subject: [Info-Ingres] Slow Ingres Net / Client


We implement our SQL queries in our applications using embedded SQL/C
and the esqlc precompiler. We now have serious performance problems on
the client side.
Our Setup is the following (but we have similar Problems using other
setups):

Server: Win XP Pro, Ingres II, 2.6, DBMS, Net
Client: Win XP Pro, Ingres II, 2.6, Net Client

Responses to queries issued from the client are 7 times (and even
more) slower than the same query issued on the server. The load on the
server ist minimal: no one else than the client has access to it,
because it's a test environment.

We analysed the program execution using a profiler. It reports that
the most time on the client is consumed by a function called GCsync()
in OICLFNT.DLL.

The tmdemo test program from the Ingres SDK shows the same effect.

We also mesured the query answer time using II_EMBED_SET (set
II_EMBED_SET=printqry;qryfile c:\temp\output.txt) while issuing the
same queries on client and server. As far as we can see, the time
difference between a server and client query is negligible, but I'm
not sure if our interpretation of the output is correct (does anybody
knows more details about the correct interpretation of this output?)
Example:
Query text:
execute query (myquery115) 1 255 5400 5599
Query Send Time: Thu Jul 29 13:21:43 2004
Query Response Time: Thu Jul 29 13:21:43 2004
Response CPU Time: 500
Qry End CPU Time: 500

So the key questions are:
- Is anybody out there who has similar problems?
- How can these problems be solved?

Thank you!

Christopher
_______________________________________________
Info-ingres mailing list



_______________________________________________
Info-ingres mailing list

I will be out of the office now until Wednesday 31st March, and will
respond to your e-mail as soon as I can upon my return.

If the matter is urgent, please raise a call via the ICT
Helpdesk.


This e-mail and any files transmitted with it are the property of the
London Borough of Havering, are confidential, may be subject to legal privilege
and are intended only for the person(s) or organisation(s) named above.
Any unauthorised use, retention, distribution, copying or disclosure
is strictly prohibited. If you receive this email in error, please notify
the sender immediately and delete this e-mail from your system.
WARNING: It is your responsibility to take all necessary steps to
ensure this e-mail and any attachments to it are free from viruses.