Hi:

> I don't know about the SP being better or worse (at least it is consistent)
> but check the index's on the table and the plan the query uses, also put the 5
> year window into the subquery(if you dont mind trying to work out logic :-)

The plan uses the existing indexes:

PLAN (T2 INDEX (PROPADD,PROPSTREET,PROPCITY,PROPSTATE))
PLAN SORT (SORT ((T1 INDEX (PROPADD))))

and the changes:

SELECT DISTINCT ID, MGRID, CAST(CAST(ADRESS_TEN AS INTEGER) AS
CHAR(7)) AS ADRESS_TEN, STNAME_TEN, CITY_TEN, STATE_TEN,
SUBSTR(ZIP_TEN,1,5) AS ZIP_TEN, MAXDATE
FROM TENANTS T1
WHERE ADRESS_TEN >0 AND
MAXDATE = (SELECT MAX(T2.MAXDATE) FROM TENANTS T2
WHERE T2.MAXDATE >= CURRENT_DATE - 1827 AND
T2.ADRESS_TEN = T1.ADRESS_TEN AND
T2.STNAME_TEN = T1.STNAME_TEN AND
T2.CITY_TEN = T1.CITY_TEN AND
T2.STATE_TEN = T1.STATE_TEN)

Order By STATE_TEN Asc, CITY_TEN asc, STNAME_TEN Asc, ADRESS_TEN
Asc , MAXDATE ASC

reduce execution time down to ~ 12 seconds.

Perhaps the better way is to insert the resulting dataset into
another cleared table and do this in background thread at app
start up. That table would then be available for the app.

Removing the order by had no effect on the execution time.


Thanks.

Best
regards