Hi Adomas,

Adomas Urbanavicius wrote:

> Considering, that with lots of records your search might become
> exhaustive according to perfomance, and it might be used frequently, you
> may think about
> adding indexed group field to your table, or in another table (if there
> might be more than one partial grouping criteria:with 1 to many table).
> Then, on insert/update trigger, just
> get my_group_id.

Yes, I already did this for the fields on which an index can be used,
and it speeded up. Unfortunately, about 25 other WHERE-criteria (not
mentioned in the simplified example) cannot not make use of indexes,
because they are calculations on integer-fields, not comparisons.

The problem I had was JOINING a record from a table with all other
records of the same table. This worked, but was way too slow.

I've now written an SP that first reads the search records and put all
the fields in local variables. After that I can just SELECT from the
same table and put all the local vars as parameters in the WHERE clause.


The SP with JOIN (in meta):
----------
FOR
SELECT R1, R2, R3... from ATABLE A <--- a singleton, ID in WHERE
JOIN ATABLE B
ON BooleanUDFCalc(A.R1, B.R1) AND BooleanUDFCalc(A.R2, B.R2) AND...
WHERE (A.BookID=:InputParm) AND (Comparisons that can use indexes)
INTO outputparms
DO Suspend
----------
abt 8.5 seconds
---------------

Now the SP with local vars:
----------
SELECT R1, R2, R3... from ATABLE
WHERE BookID=:INPUTParm
INTO :R1, :R2, :R3 <--- always a singleton

followed by:
FOR
SELECT BookID from ATABLE
WHERE (Comparisons that can use indexes)
AND
BooleanUDFCalc(R1,:R1) AND BooleanUDFCalc (R2, :R2) AND ...
INTO :ABookID
DO suspend

----------
about 460 ms!
----------

I only wished SP's could have array of integer als local vars....

Anyway, thank you for thinking with me!

Kind regards,
John