Re: Client Server - Long Query Times

ian_at_tethys.SLAC.Stanford.EDU (Ian A. MacGregor) writes:

>In article <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.
>|>
>|> 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.
>|>
>|> 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
 

>It could be that post-query triggers are creating quite a bit of network
>traffic as they fire for each row. One possible solution is to use a
>join view and write the on-lock, on-insert, on-update, and on-delete triggers
>required.
 

> Ian MacGregor
> Stanford Linear Accelerator Center
> (415) 926-3528

Other issues:

1. Post-query: Join view will be rather complicated. Easier to rewrite your post-query triggers, combining lookups to minimize number of SELECT's for each row.
2. Inspect your code for efficient use of indexes. You might want to suppress use of some indexes (too many can be worse than none) putting expressions in your column lists (e.g. add_months(date_column,0)=value or numeric_column+0 = value or char_column || '' = value).

Disadvantage: you need to look at the distribution of your indexes, to evaluate which indexes are the best. Then you 'hardcode' use of specific indexes. When you convert to V7, you get a statistical SQL optimizer. Which will not work as well with such index suppression, and you might have to remove the index suppression again...

3. If your form was converted from Forms 2.x, you might want to rewrite your triggers to V3. Usually fairly easy, and a good way to reduce the number of SELECTs fired. SELECT .... FROM DUAL, for instance.

-- 
| Carsten Kristensen		| E-mail: ckr_at_ocsbrg.no 	|
| Offshore Computer Services AS | Tel   : +47 55 99 88 70	|
| Bergen, Norway		| Fax   : +47 55 99 88 84	|
| select std_disclaimer from opinions_not_shared_by_employer;   |