markd_mms wrote:

>--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
><iblist@t...> wrote:
>
>
>>I think you indices statistics are out of date, so the optimizer could
>>not find the better approach.
>>
>>
>
>Is there a way of telling? I've only just created this database,
>created the indexes and then imported the data.
>
>

select rdb$index_name, rdb$statistics from rdb$indices

to update the statistics use this command:
set statistics index <index_name>

run it for each index or make an Stored Procedure like this:
set term ^

create procedure Update_Index_Statistics returns (Index_Name varchar(50)) as

begin
for
select
rdb$Index_Name
from
rdb$indices
into
:Index_Name
do begin
execute statement 'set statistics index ' || index_name;
suspend;
end
end^

set term ;^
commit;

to run it use this:
select * from update_index_statistics

then commit and run again

select rdb$index_name, rdb$statistics from rdb$indices

to see the difference.

after that use the first query I tell you (the one without +0) and see
if it performs well searching on Catalogue Number and on Title, I bet it
ill be ok ! :-)

>
>
>
>>Another consideration.
>>Do you need all the outer joins ? The table slookslike normal linked
>>tables, like Item and ItemInFormat, there is a possibility that you
>>
>>
>have
>
>
>>an Item without a format specified ? (and so on...)
>>
>>
>
>I checked ITEM and ITEMINFORMAT and there aren't any entries in
>ITEMINFORMAT that aren't in ITEM and vice versa.
>
>
>

if you don't have unrelated data on the tables, use inner joins instead
of outer joins.

>TIA
>
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br