low performance

[JensT]

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_reklamationer]![txtStartInterval],
"mm/dd/yy")
strDatestop = Format([Forms]![frm_reklamationer]![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_Production.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.OpenRecordset(strSQL)

Thanks in advance
Jens

[Bri]

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_reklamationer]![txtStartInterval],
"mm/dd/yy")
strDatestop = Format([Forms]![frm_reklamationer]![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_Production.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.OpenRecordset(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

[Smartin]

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_reklamationer]![txtStartInterval],
"mm/dd/yy")
strDatestop = Format([Forms]![frm_reklamationer]![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_Production.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.OpenRecordset(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

[JensT]

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_reklamationer]![txtStartInterval],
"mm/dd/yy")
strDatestop = Format([Forms]![frm_reklamationer]![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_Production.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.OpenRecordset(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

[JensT]

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_reklamationer]![txtStartInterval],
"mm/dd/yy")
strDatestop = Format([Forms]![frm_reklamationer]![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_Production.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.OpenRecordset(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

[Bri]

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

[Smartin]

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:

strDatestart = Format([Forms]![frm_reklamationer]![txtStartInterval],
"mm/dd/yy")
strDatestop = Format([Forms]![frm_reklamationer]![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_Production.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.OpenRecordset(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