federation of many simpleton queries into one

From: Chuck Dillon <spam_at_nimblegen.com>
Date: Tue, 04 Oct 2005 13:45:32 -0500
Message-ID: <dhuij4$9o$1@grandcanyon.binc.net>

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

--

Chuck Dillon
Senior Software Engineer
NimbleGen Systems Inc. Received on Tue Oct 04 2005 - 13:45:32 CDT