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

SQL Performance question

Expand Messages
  • Michael Vilhelmsen
    Hi I have a table containing some 33.879.139 records. If I do a simple select like this: Select Dato from Transaktioner Where VareFrvStrNr= 090179 AND Art=11
    Message 1 of 4 , Feb 1, 2012
      Hi

      I have a table containing some 33.879.139 records.

      If I do a simple select like this:


      Select Dato from Transaktioner
      Where
      VareFrvStrNr='090179'
      AND Art=11
      And Transaktioner.Afdeling_ID<>'99999'


      it fecthes 11 records within 0.3 seconds.
      It will use the index on VareFrvStrNr according to the plan.
      PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))



      If I instead uses this SQL


      Select Min(Dato)As MinimuSTG from Transaktioner
      Where
      VareFrvStrNr='090179'
      AND Art=11
      And Transaktioner.Afdeling_ID<>'99999'

      It takes at least 3 seconds. Often more.
      Now the plan is like this:
      PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))


      The TRANS_DATO index has almost no duplicates.
      The TRANS_VAREFRVSTRNR can have some dublicates.



      Why is there such a big difference is the performance?
      can I do anything to optimize this?

      Michael
    • Svein Erling Tysvær
      Hi Michael! ... I don t know, though I guess 090179 isn t the VareFrvStrNr with the highest number of duplicates and since Firebird doesn t (yet) use
      Message 2 of 4 , Feb 1, 2012
        Hi Michael!

        >I have a table containing some 33.879.139 records.
        >
        >If I do a simple select like this:
        >
        >
        >Select Dato from Transaktioner
        >Where
        > VareFrvStrNr='090179'
        > AND Art=11
        > And Transaktioner.Afdeling_ID<>'99999'
        >
        >it fecthes 11 records within 0.3 seconds.
        >It will use the index on VareFrvStrNr according to the plan.
        >PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))
        >
        >If I instead uses this SQL
        >
        >Select Min(Dato)As MinimuSTG from Transaktioner
        >Where
        > VareFrvStrNr='090179'
        > AND Art=11
        > And Transaktioner.Afdeling_ID<>'99999'
        >
        >It takes at least 3 seconds. Often more.
        >Now the plan is like this:
        >PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))
        >
        >The TRANS_DATO index has almost no duplicates.
        >The TRANS_VAREFRVSTRNR can have some dublicates.
        >
        >Why is there such a big difference is the performance?

        I don't know, though I guess 090179 isn't the VareFrvStrNr with the highest number of duplicates and since Firebird doesn't (yet) use histograms (I think), the optimizer doesn't know that using TRANS_DATO will slow down this query. If you used a common VareFrvStrNr (in this sense, NULL IS a value :o), then I guess the slow query might be quicker than the quick one to fetch all rows.

        >can I do anything to optimize this?

        Well, I would guess a variant of the old +0 trick would help:

        Select Min(Dato+0) As MinimuSTG from Transaktioner Where ...

        HTH,
        Set
      • Vander Clock Stephane
        it s depend where you filter will be the most evident ie : on VareFrvStrNr or on Dato ... for exemple it their is zillions reccord with VareFrvStrNr = 090179
        Message 3 of 4 , Feb 1, 2012
          it's depend where you filter will be the most evident
          ie : on VareFrvStrNr or on Dato ...

          for exemple it their is zillions reccord with VareFrvStrNr = '090179'
          then it's better to
          use the index on Dato and read the data in the dato order still will see
          one row with
          VareFrvStrNr = '090179'

          on the other way, if few rec with VareFrvStrNr = '090179', it's better
          for the engine
          to retrieve ALL the record with VareFrvStrNr = '090179' and look in it
          the low dato ...

          ok firebird as this great hability to use 2 index :) but not always so good
          this what he try to do in your query

          the firebird engine can not be all the time cleaver to know with
          strategy to use !

          so in this way you can specify the plan in you query :)


          On 2/1/2012 2:49 PM, Michael Vilhelmsen wrote:
          >
          > Hi
          >
          > I have a table containing some 33.879.139 records.
          >
          > If I do a simple select like this:
          >
          > Select Dato from Transaktioner
          > Where
          > VareFrvStrNr='090179'
          > AND Art=11
          > And Transaktioner.Afdeling_ID<>'99999'
          >
          > it fecthes 11 records within 0.3 seconds.
          > It will use the index on VareFrvStrNr according to the plan.
          > PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))
          >
          > If I instead uses this SQL
          >
          > Select Min(Dato)As MinimuSTG from Transaktioner
          > Where
          > VareFrvStrNr='090179'
          > AND Art=11
          > And Transaktioner.Afdeling_ID<>'99999'
          >
          > It takes at least 3 seconds. Often more.
          > Now the plan is like this:
          > PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))
          >
          > The TRANS_DATO index has almost no duplicates.
          > The TRANS_VAREFRVSTRNR can have some dublicates.
          >
          > Why is there such a big difference is the performance?
          > can I do anything to optimize this?
          >
          > Michael
          >
          >


          [Non-text portions of this message have been removed]
        • Michael Vilhelmsen
          the +0 is great :) I keep forgetting this. Thank you.
          Message 4 of 4 , Feb 1, 2012
            the +0 is great :)

            I keep forgetting this.

            Thank you.

            --- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
            >
            > Hi Michael!
            >
            > >I have a table containing some 33.879.139 records.
            > >
            > >If I do a simple select like this:
            > >
            > >
            > >Select Dato from Transaktioner
            > >Where
            > > VareFrvStrNr='090179'
            > > AND Art=11
            > > And Transaktioner.Afdeling_ID<>'99999'
            > >
            > >it fecthes 11 records within 0.3 seconds.
            > >It will use the index on VareFrvStrNr according to the plan.
            > >PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))
            > >
            > >If I instead uses this SQL
            > >
            > >Select Min(Dato)As MinimuSTG from Transaktioner
            > >Where
            > > VareFrvStrNr='090179'
            > > AND Art=11
            > > And Transaktioner.Afdeling_ID<>'99999'
            > >
            > >It takes at least 3 seconds. Often more.
            > >Now the plan is like this:
            > >PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))
            > >
            > >The TRANS_DATO index has almost no duplicates.
            > >The TRANS_VAREFRVSTRNR can have some dublicates.
            > >
            > >Why is there such a big difference is the performance?
            >
            > I don't know, though I guess 090179 isn't the VareFrvStrNr with the highest number of duplicates and since Firebird doesn't (yet) use histograms (I think), the optimizer doesn't know that using TRANS_DATO will slow down this query. If you used a common VareFrvStrNr (in this sense, NULL IS a value :o), then I guess the slow query might be quicker than the quick one to fetch all rows.
            >
            > >can I do anything to optimize this?
            >
            > Well, I would guess a variant of the old +0 trick would help:
            >
            > Select Min(Dato+0) As MinimuSTG from Transaktioner Where ...
            >
            > HTH,
            > Set
            >
          Your message has been successfully submitted and would be delivered to recipients shortly.