Re: Performance & PL/SQL

> lansing_at_global.gc.net (Craig D. Lansing) writes:
> We have an application running on an HP-9000 / Oracle 7 which has to join

< snip: comments about poor performance of MS Access against large RDBMS>

> to let the HP-9000 do the query processing and reduce the amount of data that
> Access must weed through. We have considered moving the data to the client at
> report execution time, but this seems like a awful lot of redundant data. Any
> suggestions would be appreciated. BTW, all Access access to the HP9k is via
> ODBC / SQL*Net.
>
> Craig D. Lansing | Much has been learned through bats
> METRO Information Services, Inc. | about radar--bombing the moon is now a
> Virginia Beach, Virginia | possibility and mighty attractive
> lansing_at_global.gc.net | because it can't bomb back. POGO 1949
> ^^^^^^^^^^^^^^^^^^^^^ Note the new address
>

>>>>
Craig,

Is the database an operational system (i.e. one that needs to be up and running at an acceptable speed)? Are the number (and complexity) of 'end-user' reports likely to grow? If 'yes' and 'yes', then perhaps you should look at 'other avenues'.

This includes warehousing the data into a separate database, which gives you the opportunity to restructure the data for ad hoc query (instead of OLTP), for example, de-normalizing some of the tables. Also, there are tools available (including Oracle's Discoverer/2000) which perform the work (where possible) on the server side, so that only the result set it passed back over the network. I can't say if these tools would do the trick, since I don't know enough about your queries.

This may be a better long-term solution than using stored procedures and tuning tricks to make performance 'acceptable'.

PS Incidentally, one of clients found the solution to a similar problem by creating an actual physical table from the view. This may work for you, but obviously, you can't create a table for each and every situation, so it's still a band-aid solution (IMHO).

Sorry I can't give you any concrete help...good luck!

Gary Wong (gary.wong_at_cyberstore.ca)
Information Technology Consultant
(604) 360-1699 (Voice)
(604) 384-1178 (Fax) Received on Tue Oct 24 1995 - 00:00:00 CET