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