Re: Client Server - Long Query Times

In <2hhbup$kto_at_crl.crl.com> rjmac_at_crl.com (Robert J. McCallister) writes:

>We are running Forms 3.0 against Version 6.0 database. When we do a
>query on one of our larger forms on the server(MIPS system) the query
>takes app 5 -10 seconds. When we run the same query on a client pc
>(386DX-40 processor) the same query takes 30 - 40 seconds.

Depending on how much work is split between Client and Server processes it could be that performance difference is due to CPU power. Of course if you're memory bound on the PC this won't help either :-)

Which MIPS system is it? Since Oracle only does integer arithmetic, a good estimate of relative speed can be found by comparing SpecInt figures. If you're doing floating point within Pro*C on the Client then you'll need to look at that comparison as well.

The MIPS chips give roughly the following (may be out of date):

Chip			SpecInt92
R3000 (33Mhz)		25
R6000 (66MHz)		41
R4000 (50/100MHz)	34 (8k/8k)
R4000 (50/100MHz)	56 (8k/8k + 1MB secondary cache)
R4400 (75/150MHz)	90 (16k/16k + 1MB secondary cache)

I estimate the 386 40MHz would be good for about 10 SpecInt92. From you're execution times, that probably means you're on an R3000 based server.

However, it must be said that most queries do not do much Client work, but send a series of SQL statements to the back-end to be processed. All retrieval and sorting is done at the back-end, and that's the same in this case. The speed of Client/Server connection needs to be sufficient for the amount of data being transfered.

One of the things that needs to be accounted for, is that when you run in Client/Server mode, Oracle is forced through an additional two layers of software code (SqlNet on Client & Sqlnet on Server). This of course places greater load on processing but the major impact is likely to be network related.

Whenever a single logical SQL statement is sent from the Client to the Server, it does not simply take one message to be sent and one to be received. The usual overhead that SqlNet (I assume you're using TCP/IP) introduces is that for every statement processed a total of seven message pairs are required . That means that whatever the network latency is, multiply that by fourteen to get an idea of how long network delay will be. This overhead is meant to be reduced with SqlNet version 2, but from benchmarks I've seen this is not significant.

Because this networking/SqlNet overhead is significant, it is important to minimise SQL statement transfer. With a tool like Sql*Forms you don't have direct control over SQL generation, so maximising SQL transfer from Sql*forms is important. The habit of Forms to enforce integrity constraints may be generating additional SQL, as well as calculated fields that use the "select from dual" instead of PL/SQL.

You didn't mention it, but I assume this on a LAN as opposed to WAN. It's still very rare to find anyone with sufficient network bandwidth, reliabilty and low-latency to do Client/Server on WAN. This is especially true when dealing with large online communities.

>We know the form is to large, but because of business issues we cannot
>rebuild the form, we can however tune/rework the form. We also realize
>we should see some improvement when we migrate to version 7.0, but that
>is few months off, and our clients will take longer to migrate to version
>7.0. The need to tune the form needs to be done quicker than the
>migration. If anyone has any ideas I would be most grateful.

The features that will help you if you are migrating will be stored procedures, triggers, shared SQL, etc. If you are currently processing a large number of statements back and forth over the network, I recommend you use all these features. Just going through Oracle 6 to Oracle 7 "migration" will not give much noticable performance improvements for a single query.

>We have some real hot forms jockies, so complicated-detailed procedures
>are no problem. Obviously we are not real learned when it comes to
>tuning issues or client-server problems, so your help will be extremely
>appreciated.
 

>Thanks in advance.
 

>Robert J. McCallister

Glad to help on those tuning issues or client-server problems,

Jon

-- 

      -m------- Jon Scott	               Tel : +44 252 373035
    ---mmm----- Pre Sales Support 
  -----mmmmm--- Pyramid Technology Ltd.        jscott_at_pyra.co.uk
-------mmmmmmm- Farnborough GU14 7PL, England.