Robert,

Try creating a compound index on T2 which combines the State, City,
Street and Address columns (in that specific order), this should help at
least a little.

Also, based on the query it would seem that you are using the Address,
Street, City and State as a key. Accordingly, you might be better
served by normalizing the structures and creating a Property/Address
table with a generator based key and then link the tenants to the
properties. This should also improve performance.


Sean
-----Original Message-----
From: Robert F. Tulloch [mailto:tultalk@...]
Sent: Friday, December 22, 2000 9:03 AM
To: ib-support@egroups.com
Subject: Re: [ib-support] selecting max date

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


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com