Including application-generated data in an SQL query
Date: 2000/02/14
Message-ID: <888qjk$ccq$1_at_nnrp1.deja.com>#1/1
I've got a question about how to combine a set of data generated by
application code into an SQL query.
As background: I'm working on a problem tracking system that supports
full text search in combination with normal field-based criterea. This
allows a search like: find all reports that were entered in January 2000
that contain the words "system crash".
In order to do this, the application generates the set of IDs for
reports that match the full text criterea (using it's own text index)
and stores this list in a temporary table for combination with the SQL
query. I can then add a "WHERE EXISTS (SELECT * FROM ...)" from the
temporary table to the ordinary query.
The problem with this approach is the insert performance for the
temporary table - the text index lookup is fast, but inserting the
results one at a time into the temporary table is relatively slow (at
least when the client and server are communicating over the network). If
the text lookup returns a large number of matches, say five to six
hundred items, then the inserts start to take longer than the rest of
the query put together.
SQL doesn't seem to support any kind of array operations where I could
insert a large set of IDs in one go. Does anybody have a suggestion as
to how I can improve the performance of this kind of combined query? It
will have to be more or less standard SQL, because we don't have
anything fancy here :-)
database client is Oracle Rdb V7.0-31 on OpenVMS 7.1
database server is Oracle Rdb V7.0-0 on OpenVMS 7.1-2
Any assistance appreciated,
Sent via Deja.com http://www.deja.com/
Ray.
Before you buy.
Received on Mon Feb 14 2000 - 00:00:00 CET