Hi,

There are typically from 1 to a few (2 to 6 at max), so yes, a GROUPing would decrease the resultant size also - good idea.

Thanks
Albert

>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
>>>>
>>>>