Maybe this helps:

Statement: SELECT DI.Reference_No_1,
DI.Reference_No_2,
DI.Document_Name,
DI.Document_No,
DI.Document_Date,
DI.Create_User,
DI.Create_Date,
DI.Ref_App_ID,
A.Full_Name,
A.First_Name,
A.Middle_Name,
A.Last_Name,
US.Name
FROM Document_Index DI

JOIN Account A on A.Reference_No_1 = DI.Reference_No_1
AND A.APP_ID = DI.REF_APP_ID
AND upper(A.Last_Name) Like 'A%'

JOIN USER_SECURITY US on US.User_Code = DI.Create_User

WHERE DI.DELETED = 0
AND DI.App_ID = 1

Order By A.Last_Name, A.First_Name, A.Middle_Name

=====

M Tuttle wrote:

>
> Greetings All,
>
> IB 6.01
>
> Please look at the SQL statement below which takes over ten minutes to
> return a result set.
>
> I ran this in IBConsole.
>
> During the execution of this SQL statement my CPU usage shoots up to 98% and
> stays there until a result set is returned.
>
> Doing simply selects like these are instant:
>
> select * from account where Last_Name like '%A%'
> select * from account where Last_Name like '%A%' and App_ID =1
>
> Here is the SQL statement that takes over ten minutes...
>
> SELECT DI.Reference_No_1,
> DI.Reference_No_2,
> DI.Document_Name,
> DI.Document_No,
> DI.Document_Date,
> DI.Create_User,
> DI.Create_Date,
> DI.Ref_App_ID,
> A.Full_Name,
> A.First_Name,
> A.Middle_Name,
> A.Last_Name,
> US.Name
> FROM Document_Index DI
> JOIN Account A on A.Reference_No_1 = DI.Reference_No_1
> JOIN USER_SECURITY US on US.User_Code = DI.Create_User
> WHERE A.APP_ID = DI.REF_APP_ID
> AND DI.DELETED = 0
> AND DI.App_ID = 1
> AND upper(A.Last_Name) Like 'A%'
> Order By A.Last_Name, A.First_Name, A.Middle_Name
>
> Here is the Plan...
>
> Statement: SELECT DI.Reference_No_1,
> DI.Reference_No_2,
> DI.Document_Name,
> DI.Document_No,
> DI.Document_Date,
> DI.Create_User,
> DI.Create_Date,
> DI.Ref_App_ID,
> A.Full_Name,
> A.First_Name,
> A.Middle_Name,
> A.Last_Name,
> US.Name
> FROM Document_Index DI
> JOIN Account A on A.Reference_No_1 = DI.Reference_No_1
> JOIN USER_SECURITY US on US.User_Code = DI.Create_User
> WHERE A.APP_ID = DI.REF_APP_ID
> AND DI.DELETED = 0
> AND DI.App_ID = 1
> AND upper(A.Last_Name) Like 'A%'
> Order By A.Last_Name, A.First_Name, A.Middle_Name
>
> PLAN SORT (JOIN (A NATURAL,DI INDEX (RDB$FOREIGN86,RDB$FOREIGN55),US INDEX
> (RDB$PRIMARY30)))
>
> Any here is the statistics...
>
> Execution Time (hh:mm:ss.ssss) 00:10:28.0894
> Starting Memory 9380852
> Current Memory 9763854
> Delta Memory 383002
> Number of Buffers 0
> Reads 17069
> Writes 491
> Plan (See Above)
>
> If any other info is needed please don't hesitate to ask.
>
> Thanks
>
> Mike
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/