low performance

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • JensT
    #1

    low performance

    Hi ng

    I open the following recordset in a module - and the performance is
    very
    slow - anyone who has an idea to speed it up. (The table is indexed)
    Here
    is the code:

    strDatestart = Format([Forms]![frm_reklamation er]![txtStartInterva l],
    "mm/dd/yy")
    strDatestop = Format([Forms]![frm_reklamation er]![txtStopInterval],
    "mm/dd/yy")
    strPeriodeAS = "Format(DateAdd ('d', -1, [Dato]), 'ww') as Week_No"
    strPeriode = "Format(DateAdd ('d', -1, [Dato]), 'ww')"

    strSQL = "SELECT " & strPeriodeAS & ", Sum(Tbl_Product ion.figures) AS
    SumOffigures " _
    & "FROM Tbl_Production WHERE Dato >= #" & strDatestart & "# And
    Dato
    <= #" & strDatestop & "# " _
    & "GROUP BY Year([Dato]), " & strPeriode _
    & "ORDER BY Year([Dato]), " & strPeriode

    Set rst1 = db.OpenRecordse t(strSQL)

    Thanks in advance
    Jens

    Tags: None
    • Bri
      #2
      Re: low performance



      JensT wrote:
      Hi ng
      >
      I open the following recordset in a module - and the performance is
      very
      slow - anyone who has an idea to speed it up. (The table is indexed)
      Here
      is the code:
      >
      strDatestart = Format([Forms]![frm_reklamation er]![txtStartInterva l],
      "mm/dd/yy")
      strDatestop = Format([Forms]![frm_reklamation er]![txtStopInterval],
      "mm/dd/yy")
      strPeriodeAS = "Format(DateAdd ('d', -1, [Dato]), 'ww') as Week_No"
      strPeriode = "Format(DateAdd ('d', -1, [Dato]), 'ww')"
      >
      strSQL = "SELECT " & strPeriodeAS & ", Sum(Tbl_Product ion.figures) AS
      SumOffigures " _
      & "FROM Tbl_Production WHERE Dato >= #" & strDatestart & "# And
      Dato
      <= #" & strDatestop & "# " _
      & "GROUP BY Year([Dato]), " & strPeriode _
      & "ORDER BY Year([Dato]), " & strPeriode
      >
      Set rst1 = db.OpenRecordse t(strSQL)
      >
      Thanks in advance
      Jens
      >
      I'm guessing, but I would suspect that it is the Group By on a Function.
      Hmm, I'm also wondering how it even works at all. You have Year([Dato])
      in the Group By and Order BY but not in the Select part.
      Without knowing the table structure I can't say more.

      --
      Bri

        Comment

        • Smartin
          #3
          Re: low performance

          JensT wrote:
          Hi ng
          >
          I open the following recordset in a module - and the performance is
          very
          slow - anyone who has an idea to speed it up. (The table is indexed)
          Here
          is the code:
          >
          strDatestart = Format([Forms]![frm_reklamation er]![txtStartInterva l],
          "mm/dd/yy")
          strDatestop = Format([Forms]![frm_reklamation er]![txtStopInterval],
          "mm/dd/yy")
          strPeriodeAS = "Format(DateAdd ('d', -1, [Dato]), 'ww') as Week_No"
          strPeriode = "Format(DateAdd ('d', -1, [Dato]), 'ww')"
          >
          strSQL = "SELECT " & strPeriodeAS & ", Sum(Tbl_Product ion.figures) AS
          SumOffigures " _
          & "FROM Tbl_Production WHERE Dato >= #" & strDatestart & "# And
          Dato
          <= #" & strDatestop & "# " _
          & "GROUP BY Year([Dato]), " & strPeriode _
          & "ORDER BY Year([Dato]), " & strPeriode
          >
          Set rst1 = db.OpenRecordse t(strSQL)
          >
          Thanks in advance
          Jens
          >
          OK I'll stab..

          Your table is indexed -- on what column(s)?

          How many records are you parsing?

          This might be a situation where it makes sense to include a dependent
          column in your table to store the calculated year. Index that. How's the
          performance now?

          --
          Smartin

            Comment

            • JensT
              #4
              Re: low performance

              Hi Smartin

              The query parses about 347.000 records.

              The table contains 9 Fields, and is indexed on the field [Dato] - which
              is a date field.
              I'm not sure what you mean by "dependend" column, but I guess you mean
              I should update the table with a column with the weeknumber =
              Format(DateAdd( 'd', -1, [Dato]), 'ww') -

              I would just like not to do that since it is a table with almost one
              million records.

              Thanks
              Jens
              Smartin skrev:
              JensT wrote:
              Hi ng

              I open the following recordset in a module - and the performance is
              very
              slow - anyone who has an idea to speed it up. (The table is indexed)
              Here
              is the code:

              strDatestart = Format([Forms]![frm_reklamation er]![txtStartInterva l],
              "mm/dd/yy")
              strDatestop = Format([Forms]![frm_reklamation er]![txtStopInterval],
              "mm/dd/yy")
              strPeriodeAS = "Format(DateAdd ('d', -1, [Dato]), 'ww') as Week_No"
              strPeriode = "Format(DateAdd ('d', -1, [Dato]), 'ww')"

              strSQL = "SELECT " & strPeriodeAS & ", Sum(Tbl_Product ion.figures) AS
              SumOffigures " _
              & "FROM Tbl_Production WHERE Dato >= #" & strDatestart & "# And
              Dato
              <= #" & strDatestop & "# " _
              & "GROUP BY Year([Dato]), " & strPeriode _
              & "ORDER BY Year([Dato]), " & strPeriode

              Set rst1 = db.OpenRecordse t(strSQL)

              Thanks in advance
              Jens
              >
              OK I'll stab..
              >
              Your table is indexed -- on what column(s)?
              >
              How many records are you parsing?
              >
              This might be a situation where it makes sense to include a dependent
              column in your table to store the calculated year. Index that. How's the
              performance now?
              >
              --
              Smartin

                Comment

                • JensT
                  #5
                  Re: low performance

                  Hi Bri

                  Thanks for your answer.

                  The table contains a million records, 9 fields. One field is [dato]
                  which is a date field, another is figures - which is a number fields.
                  The [dato] field is indexed.

                  Thx
                  Jens
                  Bri skrev:
                  JensT wrote:
                  Hi ng

                  I open the following recordset in a module - and the performance is
                  very
                  slow - anyone who has an idea to speed it up. (The table is indexed)
                  Here
                  is the code:

                  strDatestart = Format([Forms]![frm_reklamation er]![txtStartInterva l],
                  "mm/dd/yy")
                  strDatestop = Format([Forms]![frm_reklamation er]![txtStopInterval],
                  "mm/dd/yy")
                  strPeriodeAS = "Format(DateAdd ('d', -1, [Dato]), 'ww') as Week_No"
                  strPeriode = "Format(DateAdd ('d', -1, [Dato]), 'ww')"

                  strSQL = "SELECT " & strPeriodeAS & ", Sum(Tbl_Product ion.figures) AS
                  SumOffigures " _
                  & "FROM Tbl_Production WHERE Dato >= #" & strDatestart & "# And
                  Dato
                  <= #" & strDatestop & "# " _
                  & "GROUP BY Year([Dato]), " & strPeriode _
                  & "ORDER BY Year([Dato]), " & strPeriode

                  Set rst1 = db.OpenRecordse t(strSQL)

                  Thanks in advance
                  Jens
                  >
                  I'm guessing, but I would suspect that it is the Group By on a Function.
                  Hmm, I'm also wondering how it even works at all. You have Year([Dato])
                  in the Group By and Order BY but not in the Select part.
                  Without knowing the table structure I can't say more.
                  >
                  --
                  Bri

                    Comment

                    • Bri
                      #6
                      Re: low performance

                      JensT wrote:
                      Hi Bri
                      >
                      Thanks for your answer.
                      >
                      The table contains a million records, 9 fields. One field is [dato]
                      which is a date field, another is figures - which is a number fields.
                      The [dato] field is indexed.
                      >
                      Thx
                      Jens
                      I still suspect that it is using a function in the Group By and Order
                      By. It has to call the function for every record that match the
                      criteria. It may even call it more than once per record. Its getting
                      late here so I'm a bit foggy. I'll take a look at this again tomorrow
                      and see if there is some way to make this more efficient.

                      --
                      Bri

                        Comment

                        • Smartin
                          #7
                          Re: low performance

                          Hello JensT,

                          Yes that is what I am suggesting. I think Bri is right -- you are
                          calling the Year() function on every record, possibly multiple times,
                          which is sure to slow performance.

                          It might be worth the effort to add a column to your table to store the
                          calculated value (that's what I meant by "dependent column").

                          All I can offer from my own experience is I have a situation where I
                          need to process about 350,000 rows grouping on the Month() value of a
                          Date field. It works much better when I have the Month stored as a
                          separate, calculated column. Your mileage may vary.

                          Hope this helps.

                          JensT wrote:
                          Hi Smartin
                          >
                          The query parses about 347.000 records.
                          >
                          The table contains 9 Fields, and is indexed on the field [Dato] - which
                          is a date field.
                          I'm not sure what you mean by "dependend" column, but I guess you mean
                          I should update the table with a column with the weeknumber =
                          Format(DateAdd( 'd', -1, [Dato]), 'ww') -
                          >
                          I would just like not to do that since it is a table with almost one
                          million records.
                          >
                          Thanks
                          Jens
                          Smartin skrev:
                          >JensT wrote:
                          >>Hi ng
                          >>>
                          >>I open the following recordset in a module - and the performance is
                          >>very
                          >>slow - anyone who has an idea to speed it up. (The table is indexed)
                          >>Here
                          >>is the code:
                          >>>
                          >>strDatestar t = Format([Forms]![frm_reklamation er]![txtStartInterva l],
                          >>"mm/dd/yy")
                          >>strDatestop = Format([Forms]![frm_reklamation er]![txtStopInterval],
                          >>"mm/dd/yy")
                          >>strPeriodeA S = "Format(DateAdd ('d', -1, [Dato]), 'ww') as Week_No"
                          >>strPeriode = "Format(DateAdd ('d', -1, [Dato]), 'ww')"
                          >>>
                          >>strSQL = "SELECT " & strPeriodeAS & ", Sum(Tbl_Product ion.figures) AS
                          >>SumOffigure s " _
                          >> & "FROM Tbl_Production WHERE Dato >= #" & strDatestart & "# And
                          >>Dato
                          >><= #" & strDatestop & "# " _
                          >> & "GROUP BY Year([Dato]), " & strPeriode _
                          >> & "ORDER BY Year([Dato]), " & strPeriode
                          >>>
                          >>Set rst1 = db.OpenRecordse t(strSQL)
                          >>>
                          >>Thanks in advance
                          >>Jens
                          >>>
                          >OK I'll stab..
                          >>
                          >Your table is indexed -- on what column(s)?
                          >>
                          >How many records are you parsing?
                          >>
                          >This might be a situation where it makes sense to include a dependent
                          >column in your table to store the calculated year. Index that. How's the
                          >performance now?
                          >>
                          >--
                          >Smartin
                          >

                          --
                          Smartin

                            Comment

                            Previous template Next
                            Working...