The view V_ABM_PERSONAS is:
CREATE VIEW V_ABM_PERSONAS(
PER_IDENTI,
PER_CODIGO,
PER_CEDULA,
PER_APELLD,
PER_NOMBRE,
PER_APODOX,
PER_FECNAC,
PER_SEXOXX,
PER_CODDEP,
PER_NOMDEP,
PER_CODLOC,
PER_NOMLOC,
PER_CODBAR,
PER_NOMBAR,
PER_DIRECC,
PER_TELPAR,
PER_TELLAB,
PER_CELUL1,
PER_CELUL2,
PER_CELUL3,
PER_EMAILX,
PER_IDEPRO,
PER_NOMPRO,
PER_FECINS,
PER_CODSUC,
PER_NOMSUC,
PER_HABILI,
PER_IDELVO,
PER_NOMLVO,
PER_POSEEV,
PER_CAPVEH,
PER_CABGRU,
PER_FOTOGR,
PER_APENOM)
AS
SELECT
P.PER_IDENTI,
P.PER_CODIGO,
P.PER_CEDULA,
P.PER_APELLD,
P.PER_NOMBRE,
P.PER_APODOX,
P.PER_FECNAC,
P.PER_SEXOXX,
P.PER_CODDEP,
D.DEP_NOMBRE AS PER_NOMDEP,
P.PER_CODLOC,
L.LOC_NOMBRE AS PER_NOMLOC,
P.PER_CODBAR,
B.BAR_NOMBRE AS PER_NOMBAR,
P.PER_DIRECC,
P.PER_TELPAR,
P.PER_TELLAB,
P.PER_CELUL1,
P.PER_CELUL2,
P.PER_CELUL3,
P.PER_EMAILX,
P.PER_IDEPRO,
COALESCE(R.PRO_NOMBRE, '') AS PER_NOMPRO,
P.PER_FECINS,
P.PER_CODSUC,
COALESCE(S.SUC_NOMBRE, '') AS PER_NOMSUC,
P.PER_HABILI,
P.PER_IDELVO,
COALESCE(O.LOC_NOMBRE, '') AS PER_NOMLVO,
P.PER_POSEEV,
P.PER_CAPVEH,
P.PER_CABGRU,
P.PER_FOTOGR,
P.PER_APENOM
FROM
PERSONAS P
LEFT JOIN
DEPARTAMENTOS D
ON P.PER_CODDEP = D.DEP_CODIGO
LEFT JOIN
LOCALIDADES L
ON P.PER_CODDEP = L.LOC_CODDEP AND
P.PER_CODLOC = L.LOC_CODIGO
LEFT JOIN
BARRIOS B
ON P.PER_CODDEP = B.BAR_CODDEP AND
P.PER_CODLOC = B.BAR_CODLOC AND
P.PER_CODBAR = B.BAR_CODIGO
LEFT JOIN
PROFESIONES R
ON P.PER_IDEPRO = R.PRO_IDENTI
LEFT JOIN
SUCURSALES S
ON P.PER_CODSUC = S.SUC_CODIGO
LEFT JOIN
LOCALES O
ON P.PER_IDELVO = O.LOC_IDENTI
ORDER BY
P.PER_CEDULA;
There is an index on the column PER_CEDULA and all the tables are using its
Primary Key or an Unique Key.
CREATE INDEX IDX_PERSONAS1 ON PERSONAS(PER_CEDULA);
Greetings.
Walter.
On Fri, Mar 1, 2013 at 9:23 AM, W O <sistemas2000profesio...@gmail.com>wrote:
> Hello Dmitry
>
> Writing:
> SELECT * FROM V_ABM_PERSONAS V WHERE V.PER_IDENTI > 0 ROWS 1 TO 100
>
> The plan is:
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V P ORDER UQ_PERSONAS1 INDEX
> (PK_PERSONAS), V D INDEX (UQ_DEPARTAMENTOS)), V L INDEX (UQ_LOCALIDADES)),
> V B INDEX (UQ_BARRIOS)), V R INDEX (PK_PROFESIONES)), V S INDEX
> (UQ_SUCURSALES1)), V O INDEX (PK_LOCALES))
>
> It takes 3 seconds, 28 seconds, 41 seconds, 38 seconds, etc. No idea why
> the difference between one execution and other.
>
> Writing:
> SELECT * FROM V_ABM_PERSONAS V ORDER BY V.PER_IDENTI ROWS 1 TO 100
>
> The plan is:
> PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V P ORDER UQ_PERSONAS1, V D
> INDEX (UQ_DEPARTAMENTOS)), V L INDEX (UQ_LOCALIDADES)), V B INDEX
> (UQ_BARRIOS)), V R INDEX (PK_PROFESIONES)), V S INDEX (UQ_SUCURSALES1)), V
> O INDEX (PK_LOCALES)))
>
> It takes more then 53 minutes!!! (and counting...)
>
> Writing:
> SELECT FIRST 100 * FROM V_ABM_PERSONAS V ORDER BY V.PER_IDENTI
>
> The plan is:
> PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V P ORDER UQ_PERSONAS1, V D
> INDEX (UQ_DEPARTAMENTOS)), V L INDEX (UQ_LOCALIDADES)), V B INDEX
> (UQ_BARRIOS)), V R INDEX (PK_PROFESIONES)), V S INDEX (UQ_SUCURSALES1)), V
> O INDEX (PK_LOCALES)))
>
> PK_ are the Primary Keys
>
> UQ_ are the Unique Keys
>
> Greetings.
>
> Walter.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Fri, Mar 1, 2013 at 8:46 AM, Dmitry Yemanov <
> dim...@users.sourceforge.net> wrote:
>
>> **
>>
>>
>> 01.03.2013 8:37, W O wrote:
>>
>> > I have a table with 3.600.000 rows more or less.
>> >
>> > Writing:
>> > SELECT * FROM V_ABM_PERSONAS V WHERE V.PER_IDENTI > 0 ROWS 1 TO 100
>> >
>> > takes 2.656 seconds (less than 3 seconds)
>> >
>> > Writing:
>> > SELECT * FROM V_ABM_PERSONAS V ORDER BY V.PER_IDENTI ROWS 1 TO 100
>> >
>> > takes more than 11 minutes!!!! (bored, I did cancel the execution)
>>
>> Please show us the query plans.
>>
>> Dmitry
>>
>>
>>
>
>
[Non-text portions of this message have been removed]
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
