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

[Manny]

Hi,

We are using Ingres (2.6/0604) and C on a unix platform to generate
some files.
The SQL I am using in my .SC module is slow and I am hoping for some
suggestions on improving it.

Table Structure - All tables are Btree Unique.

table_1 	150,000 rows
============================
release_id	                char 5	                key 1
active_date	date		key 2
text_1                       char 5

table_2 	2 million rows
===============================
release_id	                char 5		key 1
active_date	date		key 2
segment1	                integer
segment2	                integer
segment3	                integer
text_2                       char 5

table_3 	250,000 rows
========================
release_id	                char 5		key 1
active_date	date		key 2
text_3                       char 5

table_4		100 rows
===========================
segment_id	integer

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)
I don't require duplicates hence the use of 'UNION' rather than 'UNION
ALL'

And here's the SELECT query.....
----------------------------------------------------
SELECT a.release_id,
       a.active_date,
       a.text_1,
       a.text_2,
       b.segment1,
       text_3
INTO   :l_rec
FROM   table_1 a,
       table_2 b,
       table_3 c
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)
AND a.release_id = b.release_id
AND a.release_id = c.release_id
AND a.active_date = b.active_date
AND a.active_date = c.active_date
AND b.segment1 = <:loop_segement_id>
UNION
SELECT a.release_id,
       a.active_date,
       a.text_1,
       a.text_2,
       b.segment2,
       text_3
FROM   table_1 a,
       table_2 b,
       table_3 c
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)
AND a.release_id = b.release_id
AND a.release_id = c.release_id
AND a.active_date = b.active_date
AND a.active_date = c.active_date
AND b.segment2 = <:loop_segement_id>
UNION
SELECT a.release_id,
       a.active_date,
       a.text_1,
       a.text_2,
       b.segment3,
       text_3
FROM   table_1 a,
       table_2 b,
       table_3 c
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)
AND a.release_id = b.release_id
AND a.release_id = c.release_id
AND a.active_date = b.active_date
AND a.active_date = c.active_date
AND b.segment3 = <:loop_segement_id>
ORDER BY text_3
----------------------------------------------------

Can anyone suggest a quicker/more efficient way of doing this?

Thanks in advance.

Manny