Subject Re: [ib-support] Can anyone help me with this painfully slow query?
Author M Tuttle
Greeting Helen,

Thanks so much for providing me with a little insight as to where I was
going wrong with this. After your recommendations this query now takes
approximately 13 seconds to generate the result list instead of 10:30
minutes and with a few adjustments to the database ( Adding UPPERCASE name
fields and then indexing and running a query against them) I think I can cut
it down even further. I for one appreciate all the effort you put into your
replies on this group. I always read what you have to say and have learn a
lot. So I for one simply want to say THANK YOU.


----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <>
Sent: Saturday, June 22, 2002 7:48 PM
Subject: Re: [ib-support] Can anyone help me with this painfully slow query?

> Another case of mixing implicit and explicit joins and jumbling JOIN
> criteria up in the WHERE clause...
> 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
> AND DI.App_ID = 1
> AND upper(A.Last_Name) Like 'A%'
> Order By A.Full_Name
> From the plan:
> This is slow because Table A has no indexes that can be used for the
> sort. You have two columns joining it to table DI, which references it
> through a Foreign key so (hopefully) App_ID is the primary key of table
> DI. But it could be that you have two FKs referring to the same table and
> neither refers to the PK. Could be a pipeline that needs cleaning up.
> Since you have Full_Name as a column in addition to the three individual
> columns, if it is "Last_Name first" then index that column and use it for
> the sort - but don't rely on this if there's a likelihood you have NULLs
> Full_Name arising from a defective concatenation formula in a trigger,
> new.Full_Name = new.Last_Name|| ', ' ||new.First_Name|| '
> This will store NULL in all cases where any single element is missing.
> If you can't use Full_Name, index the other three name columns, either as
> single index in the right order left-to-right (if they are short enough)
> individually; or index US.Name and use that for the sort.
> heLen
> All for Open and Open for All
> Firebird Open SQL Database · ·
> _______________________________________________________
> To unsubscribe from this group, send an email to:
> Your use of Yahoo! Groups is subject to