Loading ...
Sorry, an error occurred while loading the content.

121035Re: [firebird-support] Re: Why the difference?

Expand Messages
  • W O
    Mar 1, 2013
    • 0 Attachment
      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 <dimitr@...
      > 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]
    • Show all 18 messages in this topic