Chuck Dillon wrote:
>
> We have a system with an interpreted front end and an 8i on Solaris back
> end. The interpreted language maps data from the RDB to the client
> layer at the "cell" to "field" level. IOW, a cell of a table
> corresponds to a field at the client layer. A client may have dozens of
> fields corresponding to dozens of "cells" in many tables each of which
> might use different information to identify the row. The glue code, in
> Pro*C, currently uses singleton round trip selects to populate the
> client data structure and singleton update/inserts to store changes.
>
> Performance of update/inserts is not a problem but performance of
> instantiating and refreshing the client data is critical. I need to
> speed the query process up.
>
> Fictional example of singleton queries:
> select columnA from tableA where pkey = 123; ## goes into fieldA
> select columnB from tableA where pkey = 123; ## goes into fieldB
> select columnA from tableB where pkey = 123; ## goes into fieldC
> select columnA from tableC where pkey = 321; ## goes into fieldD
> ...
>
> IOW, a serial process of querying the RDB "cell" associated with a
> field. The interpreter makes no assumptions about where the data is
> stored so each field's value potentially comes from a different table
> and the row is possibly keyed on a different column.
>
> To speed this up I'd like to reduce the number of round trips by
> combining the singletons into far fewer larger queries and I know I can
> do it in a brute force way, for example...
>
> select 1, columnA from tableA where pkey = 123
> union all
> select 2, columnB from tableA where pkey = 123
> union all
> select 3, columnA from tableB where pkey = 123
> union all
> select 4, columnA from tableC where pkey = 321
> union all
> ...
> order by 1;
> Then fetch the field values one at a time. I would impose a maximum
> query length and make multiple round trips when required.
>
> I could also develop much more complex code on the client side to try
> and reduce the brute force query to more efficient forms. For example,
> by combining references to tables. That's essentially an attempt to
> write an optimizer on the client side. I hesitate to go there.
>
> My primary question is, what kind of payback can I reasonably expect to
> get from time invested in code to optimize the query? Am I likely going
> to end up with something significantly faster than the brute force
> method at the end of the day?
>
> Of course, I'm open to suggestions of other approaches.
>
> Thank you for your time...
>
> -- ced
I doubt you will find support for RDB here.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Tue Oct 04 2005 - 16:05:28 CDT
