Hi Diego!

How many records (and fields) does this query return? And does it take
longer returning the entire result set in the second case compared to
the first or just longer to return the first few records?

Unlike desktop databases, in client/server databases like Firebird you
should always take care only to return the records of interest (more
often than not, less than 100 records). Of course, occationally more
records are required, but then it is often for some batch processing -
and then 8 seconds is reasonably fast.

Having said that, I do not understand why Firebird doesn't use the
same index for sorting as it already uses for the where clause
(RDB$PRIMARY58).

Set

--- In firebird-support@yahoogroups.com, "diegodelafuente" wrote:
> if I run this:
>
> Select mp.*, phc.*
> From Maestro_Pacientes mp
> Left Outer join Paciente_hc phc On mp.Codigo = phc.Paciente
> Where mp.Codigo >= 1
>
> the execution plan is this:
> Plan
> PLAN JOIN (MP INDEX (RDB$PRIMARY58),PHC INDEX (PK_PACIENTE_HC))
> Adapted Plan
> PLAN JOIN (MP INDEX (INTEG_526),PHC INDEX (PK_PACIENTE_HC))
>
> ------ Performance info ------
> Prepare time = 0ms
> Execute time = 32ms
> Avg fetch time = 2.67 ms
> Current memory = 1,879,652
> Max memory = 2,110,108
> Memory buffers = 2,048
> Reads from disk to cache = 586
> Writes from cache to disk = 0
> Fetches from cache = 712
>
> It runs ver fast....
> But I need the recordset order By "Codigo"
> like thist:
>
> Select mp.*, phc.*
> From Maestro_Pacientes mp
> Left Outer join Paciente_hc phc On mp.Codigo = phc.Paciente
> Where mp.Codigo >= 1 order By mp.Codigo
>
> and the execution plan is this:
> Plan
> PLAN SORT (JOIN (MP INDEX (RDB$PRIMARY58),PHC INDEX
> (PK_PACIENTE_HC)))
>
> Adapted Plan
> PLAN SORT (JOIN (MP INDEX (INTEG_526),PHC INDEX (PK_PACIENTE_HC)))
>
> ------ Performance info ------
> Prepare time = 0ms
> Execute time = 8s 266ms
> Avg fetch time = 688.83 ms
> Current memory = 1,883,264
> Max memory = 2,110,108
> Memory buffers = 2,048
> Reads from disk to cache = 15,661
> Writes from cache to disk = 0
> Fetches from cache = 1,956,941
>
> It takes 8 seconds. too much compare to the other plan.
> I see that Firebird make a sort of all the tables involved in the
> plan.
> In table (Maestros_Pacientes) i have 335.000 records. And in table
> (Paciente_Hc) I have 140.000 records.
> The performance Analisis in the second plan says that the indexed
> reads in
> each table are equal to the number of record in each one.
> Wath can i do to improve that ?
>
> Sorry for my english :) ! I speak in spanish.
>
> Thanks
>
> Diego