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

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

Expand Messages
  • W O
    Mar 1, 2013
      Hello Mark

      Thank for your answer.

      But the strange thing is that PER_IDENTI is the Primary Key and ORDER BY
      seems not use it.

      Greetings.

      Walter.








      On Fri, Mar 1, 2013 at 4:18 AM, Mark Rotteveel <mark@...> wrote:

      > **
      >
      >
      > On Fri, 1 Mar 2013 00:37:26 -0400, W O sistemas2000profesional@...>
      > wrote:
      >
      > > Somebody knows why there are a difference so big?
      > >
      > > 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)
      > >
      > > The column PER_IDENTI is the Primary Key.
      > >
      > > Why that? Why a difference so big? Somebody knows the answer?
      > >
      > > Windows 7, Firebird 2.5.1, SuperServer
      >
      > In the first example Fireibrd just needs to find the first 100 records
      > that match V.PER_IDENTI > 0.
      > In the second example, I guess it will retrieve all rows, sort them and
      > then return the first 100 rows; ROWS probably is not optimized for an
      > (index) ordered retrieval.
      >
      > Mark
      >
      >
      >


      [Non-text portions of this message have been removed]
    • Show all 18 messages in this topic