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.

Mike

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
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
> and A.APP_ID = DI.REF_APP_ID
> JOIN USER_SECURITY US on US.User_Code = DI.Create_User
> where
> DI.DELETED = 0
> AND DI.App_ID = 1
> AND upper(A.Last_Name) Like 'A%'
> Order By A.Full_Name
>
> From the plan:
> PLAN SORT (JOIN (A NATURAL,DI INDEX (RDB$FOREIGN86,RDB$FOREIGN55),US INDEX
> (RDB$PRIMARY30)))
>
> 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
in
> Full_Name arising from a defective concatenation formula in a trigger,
like
>
> new.Full_Name = new.Last_Name|| ', ' ||new.First_Name|| '
'||new.Middle_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
a
> single index in the right order left-to-right (if they are short enough)
or
> individually; or index US.Name and use that for the sort.
>
> heLen
>
>
> All for Open and Open for All
> Firebird Open SQL Database · http://firebirdsql.org ·
> http://users.tpg.com.au/helebor/
> _______________________________________________________
>
>
>
> 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/
>
>
>