Hi Albert,

Do you have many files per plaintiff or just one? I think you can add group by plaintiff_id into your cursor if you can have more than 1 file opened at the same time per one person.

>Hi Naomi,
>
>Made the changes and that indeed worked better and faster.
>
>Shows though when you get into testing against live data, how performance can be affected by the correlated subquery part. Here is what I used to eliminate files that were still "open" (denoted by no closing date on the file); my original code that was the last part of the WHERE portion:
>
>

>NOT EXISTS ;
>         ( SELECT File_ID FROM PartFile PF WHERE PF.Plaintf_ID == Plaintif.Plaintf_ID AND EMPTY(PF.Date_Closd) ) 
>

>
>I replaced that by first getting a list of open files before running the main query and then these were used in a subquery instead:
>
>

>SELECT ;
>   Plaintf_ID ;
>   FROM PartFile ;
>   WHERE EMPTY(Date_Closd) ;
>   ORDER BY Plaintf_ID ;
>   INTO CURSOR OpenFiles
>

>
>and then I changed the correlated subquery to reference the temp OpenFiles cursor instead:
>
>

>NOT EXIST ( SELECT Plaintf_ID FROM OpenFiles WHERE OpenFiles.Plaintf_ID == Plaintif.Plaintf_ID )
>

>
>This cut the execution time down from about 15 seconds to 5 - probably because OpenFiles results in only about 3700 records whereas the PartFile table is about 25,000 records and so a lot less records to scan through on the subquery. Not a big deal as this query is to be run about once a week overnight - have to decide which is easier to understand for the next person.
>
>Albert
>
>
>>I am not sure about your where clause, but I would re-write these two:
>>
>> ( SELECT COUNT(*) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS NumApps, ;
>> ( SELECT MAX(Apps.LastPaymnt) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS LastPay_Dt ;
>>
>>as
>>

>>INNER JOIN (select count(*) as NumApps, Max(Apps.LastPaymnt) as LastPay_Dt, FIle_ID
>>from Apps GROUP BY File_ID) X ON PartFile.File_ID = X.File_ID
>>

>>
>>and would just use X.NumApps and X.LastPay_Dt in the main query
>>
>
>>>SELECT ;
>>> PartFile.File_ID, Plaintif.Plaintf_ID, Plaintif.Lastname, Plaintif.Firstname, PartFile.Date_Opend, PartFile.Date_Closd, ;
>>> PartFile.Partner, PartFile.Our_FileNo, PartFile.Folder_ID, PartFile.TrackNotes, ;
>>> ( SELECT COUNT(*) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS NumApps, ;
>>> ( SELECT MAX(Apps.LastPaymnt) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS LastPay_Dt ;
>>> FROM Plaintif JOIN PartFile ;
>>> ON Plaintif.Plaintf_ID == PartFile.Plaintf_ID ;
>>> WHERE PartFile.FileLocked = .T. AND ;
>>> PartFile.NeverPurge = .F. AND ;
>>> NOT EMPTY(PartFile.Folder_ID) AND ;
>>> NOT EMPTY(PartFile.Stru_Amt) AND NOT EMPTY(PartFile.Date_Fund) AND ;
>>> NOT EMPTY(PartFile.Date_Closd) AND PartFile.Date_Closd < ldCutOffDate AND ;
>>> EMPTY(PartFile.Archv_Date) AND PartFile.Archv_Type # "P" AND ;
>>> NOT ( Plaintif.EstateFile = .T. AND EMPTY(Plaintif.EstateClsd) ) AND ;
>>> NOT EXISTS ;
>>> ( SELECT App_ID FROM Apps WHERE Apps.File_ID == PartFile.File_ID AND ;
>>> ( EMPTY(Apps.LastPaymnt) OR Apps.LastPaymnt >= ldCutOffDate ) ) ;
>>> ORDER BY Plaintif.Lastname, Plaintif.Firstname ;
>>> INTO CURSOR ( lcCursorName ) READWRITE
>>>
>>>