[Info-Ingres] Slow running queries....

[Karl & Betty Schendel]

At 3:49 AM -0700 5/24/07, Manny wrote:
>[snip] I am looping through table_4 (segment table) and in each iteration I
>am executing the following piece of SQL in order to retrieve the
>required data (this data is populated in an array to be used later in
>the program)

A QEP would be useful, so we don't have to guess at what query
plan the optimizer has decided on.

Are there optimizedb statistics on any or all of the where-clause columns?

A few specific notes:

>I don't require duplicates hence the use of 'UNION' rather than 'UNION
>ALL'

On the other hand, if the data is such that it cannot produce
duplicates, you're better off with UNION ALL.  A regular
UNION requires a duplicates-removal sort at the end, and that
can sometimes adversely affect the query plan fragments underneath.

>WHERE a.active_date in (SELECT MAX(d.active_date)
>                                   FROM table_1 d
>                                   WHERE d.active_date <= <:Passed in Date>
>                                   AND d.release_id = a.release_id
>                                   GROUP BY d.release_id)

That GROUP BY is either wrong or unnecessary.  The subselect is a
scalar subselect, meaning that it is expected to return exactly
one value.  (under strict ANSI it is *required* to return exactly
one value, but normally Ingres will compile query plans where
that requirement is not checked.)  Anyway, if the grouping
could result in multiple output groups, the query is
semantically wrong;  and if the grouping always results
in exactly one maximum, then it doesn't need to be there at all.

REPEATED may or may not help some, depending on the
sensitivity of the data to the loop control segment ID.

Of course, all of the above is just patching around the
real problem, which is that you are apparently issuing
the query 100 times inside a loop.  The real fix is
to rethink the SQL so as to get rid of the segment
loop.  Unfortunately I don't have the time to spare
to actually solve the problem for you (at least,
not for free...!), but the approach I would try
first would be to build a temp table (session temp)
of release id/active date combos from table_2
by selecting on the appropriate segment id's.  You
can then extract the proper active date using
table 1, and do one big select and retrieval
into your result array.

Karl