How to optimize a query called in code VBA

[Aaron]

I have a section of code that runs and works as expected, except it
takes too long. The idea is to return a 90 percentile time from a
large table of maintenace actions, grouped by year, site, and part
number. The datafile has 2.2M records and 301K unique year, site,
part number combos, so execution time is an issue. I set this to run
over the weekend and it only returned 60K of the 301K rows, before I
had to break the code. Stepping through it shows that opening the
querydef w/ parameters takes the longest 5-8 seconds. Any ideas on
how I can speed this up would be great!

Public Function getTRR()
'genterates TRR number for compiled data file

Dim DB As DAO.Database
Dim qryTRR, rst, TRRlu, refrst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i, j, k, TRRm As Integer
Dim strSQL As String

'make table to group distinct Fy, ORG, Niin
DoCmd.SetWarnings False
'DoCmd.OpenQuery "mk_reftbl"
'clear TRR Table
DoCmd.RunSQL ("DELETE TRR_look_up.* FROM TRR_look_up")
DoCmd.SetWarnings True

'set recordsets
Set DB = CurrentDb
Set refrst = DB.OpenRecordset("reftbl") 'table for unique
combinations
Set TRRlu = DB.OpenRecordset("TRR_look_up") 'reference table for
the output
Set qdf = DB.QueryDefs("qryTRRgrp")

Do While Not refrst.EOF
'set variable parameters to pass to the query
qdf.Parameters("paraFY") = refrst![Fiscal year]
qdf.Parameters("paraORG") = refrst![Action Org Code]
qdf.Parameters("paraNiin") = refrst![Rmvd NIIN]
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
k = 0
'pick 90th percentile
j = Int(0.9 * rst.RecordCount)
For i = 0 To j
TRRm = (rst![TRR] + 1) 'add 1 to make up the
difference with TAT
rst.MoveNext
Next
'open TRR look up table to add new records
TRRlu.AddNew
TRRlu![Fiscal year] = refrst![Fiscal year]
TRRlu![Org] = refrst![Action Org Code]
TRRlu![niin] = refrst![Rmvd NIIN]
TRRlu![TRRm] = TRRm
TRRlu.Update
Else
End If
refrst.MoveNext
Loop
TRRlu.Close
refrst.Close
DoCmd.DeleteObject acTable, "reftbl"

End Function


-and the query qryTRRgrp-

PARAMETERS paraFY Text ( 255 ), paraORG Text ( 255 ), paraNiin Text
( 255 );
SELECT FRC_M_lvl1.Fiscal_Year, FRC_M_lvl1.[Action Org Code],
FRC_M_lvl1.[Rmvd NIIN], FRC_M_lvl1.MCN, FRC_M_lvl1.TRR
FROM FRC_M_lvl1
WHERE (((FRC_M_lvl1.Fiscal_Year)=[paraFY]) AND ((FRC_M_lvl1.[Action
Org Code])=[paraORG]) AND ((FRC_M_lvl1.[Rmvd NIIN])=[paraNiin]))
ORDER BY FRC_M_lvl1.TRR;

Thanks for any help!
Aaron

 

[Dirk Goldgar]

I have a section of code that runs and works as expected, except it
takes too long. The idea is to return a 90 percentile time from a
large table of maintenace actions, grouped by year, site, and part
number. The datafile has 2.2M records and 301K unique year, site,
part number combos, so execution time is an issue. I set this to run
over the weekend and it only returned 60K of the 301K rows, before I
had to break the code. Stepping through it shows that opening the
querydef w/ parameters takes the longest 5-8 seconds. Any ideas on
how I can speed this up would be great!

Public Function getTRR()
'genterates TRR number for compiled data file

Dim DB As DAO.Database
Dim qryTRR, rst, TRRlu, refrst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i, j, k, TRRm As Integer
Dim strSQL As String

'make table to group distinct Fy, ORG, Niin
DoCmd.SetWarnings False
'DoCmd.OpenQuery "mk_reftbl"
'clear TRR Table
DoCmd.RunSQL ("DELETE TRR_look_up.* FROM TRR_look_up")
DoCmd.SetWarnings True

'set recordsets
Set DB = CurrentDb
Set refrst = DB.OpenRecordset("reftbl") 'table for unique
combinations
Set TRRlu = DB.OpenRecordset("TRR_look_up") 'reference table for
the output
Set qdf = DB.QueryDefs("qryTRRgrp")

Do While Not refrst.EOF
'set variable parameters to pass to the query
qdf.Parameters("paraFY") = refrst![Fiscal year]
qdf.Parameters("paraORG") = refrst![Action Org Code]
qdf.Parameters("paraNiin") = refrst![Rmvd NIIN]
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
k = 0
'pick 90th percentile
j = Int(0.9 * rst.RecordCount)
For i = 0 To j
TRRm = (rst![TRR] + 1) 'add 1 to make up the
difference with TAT
rst.MoveNext
Next
'open TRR look up table to add new records
TRRlu.AddNew
TRRlu![Fiscal year] = refrst![Fiscal year]
TRRlu![Org] = refrst![Action Org Code]
TRRlu![niin] = refrst![Rmvd NIIN]
TRRlu![TRRm] = TRRm
TRRlu.Update
Else
End If
refrst.MoveNext
Loop
TRRlu.Close
refrst.Close
DoCmd.DeleteObject acTable, "reftbl"

End Function


-and the query qryTRRgrp-

PARAMETERS paraFY Text ( 255 ), paraORG Text ( 255 ), paraNiin Text
( 255 );
SELECT FRC_M_lvl1.Fiscal_Year, FRC_M_lvl1.[Action Org Code],
FRC_M_lvl1.[Rmvd NIIN], FRC_M_lvl1.MCN, FRC_M_lvl1.TRR
FROM FRC_M_lvl1
WHERE (((FRC_M_lvl1.Fiscal_Year)=[paraFY]) AND ((FRC_M_lvl1.[Action
Org Code])=[paraORG]) AND ((FRC_M_lvl1.[Rmvd NIIN])=[paraNiin]))
ORDER BY FRC_M_lvl1.TRR;

Are the fields [Fiscal_Year], [Action Org Code], [Rmvd NIIN], and TRR all
indexed in the table? Indexes on the fields that you select and sort by are
the most crucial factors in the performance of most queries.

Is Fiscal_Year really a text field? If it's numeric, maybe you should
declare the parameter paraFY that way.

 

[Aaron]

Indexed? No. The tables are created from a make-table query, I need
to look up and see if you can index that way. Yes the Fiscal_Year is
a text field, i.e. FY2007.
I'm fairly new to this, only been actively using/learning Access for a
year.

 

[Dirk Goldgar]

Indexed? No. The tables are created from a make-table query, I need
to look up and see if you can index that way. Yes the Fiscal_Year is
a text field, i.e. FY2007.
I'm fairly new to this, only been actively using/learning Access for a
year.

You could execute DDL queries to create the indexes, after you run the
make-table query. Of course, those CREATE INDEX queries are going to take
some time to execute, too, so you'll have to see if you gain anything by
doing so. Or, instead of making a new table each time, you could reuse the
same table (by running a delete query and then an append query), in which
you have already defined the indexes. Whether that's feasible for you will
depend on your processing requirements, as well as whether you're bumping up
against the 2GB limit of the database file.

Looking at your code for finding the 90th percentile, I'm not convinced it
works. If I understand it properly, you're calculating a "record number"
based on the recordset's .RecordCount property, but that property is not
guaranteed to be accurate for a dynaset until you've already moved to the
end of the recordset. Have you verified that you're getting the records
that you should?

If it's wrong, you might execute a .MoveLast, .MoveFirst sequence to force
the recordset to be accurate, or maybe you could do it more efficiently with
a SELECT TOP 10 PERCENT query and moving to the last record. I don't know;
I haven't thought it completely through.

 

[Aaron]

  I have a section of code that runs and works as expected, except it
takes too long.  The idea is to return a 90 percentile time from a
large table of maintenace actions, grouped by year, site, and part
number.  The datafile has 2.2M records and 301K unique year, site,
part number combos, so execution time is an issue.  I set this to run
over the weekend and it only returned 60K of the 301K rows, before I
had to break the code.  Stepping through it shows that opening the
querydef w/ parameters takes the longest 5-8 seconds.  Any ideas on
how I can speed this up would be great!

Public Function getTRR()
  'genterates TRR number for compiled data file

   Dim DB As DAO.Database
   Dim qryTRR, rst, TRRlu, refrst As DAO.Recordset
   Dim qdf As DAO.QueryDef
   Dim i, j, k, TRRm As Integer
   Dim strSQL As String

   'make table to group distinct Fy, ORG, Niin
   DoCmd.SetWarnings False
   'DoCmd.OpenQuery "mk_reftbl"
   'clear TRR Table
   DoCmd.RunSQL ("DELETE TRR_look_up.* FROM TRR_look_up")
   DoCmd.SetWarnings True

   'set recordsets
   Set DB = CurrentDb
   Set refrst = DB.OpenRecordset("reftbl") 'table for unique
combinations
   Set TRRlu = DB.OpenRecordset("TRR_look_up") 'reference table for
the output
   Set qdf = DB.QueryDefs("qryTRRgrp")

   Do While Not refrst.EOF
       'set variable parameters to pass to the query
       qdf.Parameters("paraFY") = refrst![Fiscal year]
       qdf.Parameters("paraORG") = refrst![Action Org Code]
       qdf.Parameters("paraNiin") = refrst![Rmvd NIIN]
       Set rst = qdf.OpenRecordset()
       If rst.RecordCount > 0 Then
           k = 0
           'pick 90th percentile
           j = Int(0.9 * rst.RecordCount)
           For i = 0 To j
               TRRm = (rst![TRR] + 1) 'add 1 to make up the
difference with TAT
               rst.MoveNext
           Next
           'open TRR look up table to add new records
           TRRlu.AddNew
           TRRlu![Fiscal year] = refrst![Fiscal year]
           TRRlu![Org] = refrst![Action Org Code]
           TRRlu![niin] = refrst![Rmvd NIIN]
           TRRlu![TRRm] = TRRm
           TRRlu.Update
       Else
       End If
       refrst.MoveNext
   Loop
   TRRlu.Close
   refrst.Close
   DoCmd.DeleteObject acTable, "reftbl"

End Function

-and the query qryTRRgrp-

PARAMETERS paraFY Text ( 255 ), paraORG Text ( 255 ), paraNiin Text
( 255 );
SELECT FRC_M_lvl1.Fiscal_Year, FRC_M_lvl1.[Action Org Code],
FRC_M_lvl1.[Rmvd NIIN], FRC_M_lvl1.MCN, FRC_M_lvl1.TRR
FROM FRC_M_lvl1
WHERE (((FRC_M_lvl1.Fiscal_Year)=[paraFY]) AND ((FRC_M_lvl1.[Action
Org Code])=[paraORG]) AND ((FRC_M_lvl1.[Rmvd NIIN])=[paraNiin]))
ORDER BY FRC_M_lvl1.TRR;

Are the fields [Fiscal_Year], [Action Org Code], [Rmvd NIIN], and TRR  all
indexed in the table?  Indexes on the fields that you select and sort by are
the most crucial factors in the performance of most queries.

Is Fiscal_Year really a text field?  If it's numeric, maybe you should
declare the parameter paraFY that way.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Wow- I manually added the indexed to the reference table and that
made a significant gain in speed. So now I need to research how to
automatically create indexs after the make table function.
Thank you!

 

[Aaron]

You could execute DDL queries to create the indexes, after you run the
make-table query.  Of course, those CREATE INDEX queries are going to take
some time to execute, too, so you'll have to see if you gain anything by
doing so.  Or, instead of making a new table each time, you could reusethe
same table (by running a delete query and then an append query), in which
you have already defined the indexes.  Whether that's feasible for you will
depend on your processing requirements, as well as whether you're bumpingup
against the 2GB limit of the database file.

Looking at your code for finding the 90th percentile, I'm not convinced it
works.  If I understand it properly, you're calculating a "record number"
based on the recordset's .RecordCount property, but that property is not
guaranteed to be accurate for a dynaset until you've already moved to the
end of the recordset.  Have you verified that you're getting the records
that you should?

If it's wrong, you might execute a .MoveLast, .MoveFirst sequence to force
the recordset to be accurate, or maybe you could do it more efficiently with
a SELECT TOP 10 PERCENT query and moving to the last record.  I don't know;
I haven't thought it completely through.

Using the manually updated indexing the file is almost to completion,
thats a huge gain. This program will be only run once a quarter (I
hope!) to update a data file for another program. The main reason for
it is so my successor does not have to relearn everything I did after
I transfer. So if CREATE INDEX queries take an hour to run, it still
beats an estimated 20 days to complete the process (I don't have a
computer I could leave running that long).
The 90th percentile did work, at least in some sample testing, but
just assumoing there might be an error somewhere in there it would
best to follow your guidence and just force it with a movelast
movefrist combo.

Thank you very much!
Aaron

 

[david]

By the way, a minor optimisation for loops is this:

dim paraFY as dao.parameter
dim paraORG as dao.parameter
dim paraNiin as dao.parameter

Set qdf = DB.QueryDefs("qryTRRgrp")

set paraFY = qdf.Parameters("paraFY")
set paraORG = qdf.Parameters("paraORG")
set paraNiin = qdf.Parameters("paraNiin")

Do While Not refrst.EOF
paraFY = refrst![Fiscal year]
paraORG = refrst![Action Org Code]
paraNiin = refrst![Rmvd NIIN]

(david)

I have a section of code that runs and works as expected, except it
takes too long. The idea is to return a 90 percentile time from a
large table of maintenace actions, grouped by year, site, and part
number. The datafile has 2.2M records and 301K unique year, site,
part number combos, so execution time is an issue. I set this to run
over the weekend and it only returned 60K of the 301K rows, before I
had to break the code. Stepping through it shows that opening the
querydef w/ parameters takes the longest 5-8 seconds. Any ideas on
how I can speed this up would be great!

Public Function getTRR()
'genterates TRR number for compiled data file

Dim DB As DAO.Database
Dim qryTRR, rst, TRRlu, refrst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i, j, k, TRRm As Integer
Dim strSQL As String

'make table to group distinct Fy, ORG, Niin
DoCmd.SetWarnings False
'DoCmd.OpenQuery "mk_reftbl"
'clear TRR Table
DoCmd.RunSQL ("DELETE TRR_look_up.* FROM TRR_look_up")
DoCmd.SetWarnings True

'set recordsets
Set DB = CurrentDb
Set refrst = DB.OpenRecordset("reftbl") 'table for unique
combinations
Set TRRlu = DB.OpenRecordset("TRR_look_up") 'reference table for
the output
Set qdf = DB.QueryDefs("qryTRRgrp")

Do While Not refrst.EOF
'set variable parameters to pass to the query
qdf.Parameters("paraFY") = refrst![Fiscal year]
qdf.Parameters("paraORG") = refrst![Action Org Code]
qdf.Parameters("paraNiin") = refrst![Rmvd NIIN]
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
k = 0
'pick 90th percentile
j = Int(0.9 * rst.RecordCount)
For i = 0 To j
TRRm = (rst![TRR] + 1) 'add 1 to make up the
difference with TAT
rst.MoveNext
Next
'open TRR look up table to add new records
TRRlu.AddNew
TRRlu![Fiscal year] = refrst![Fiscal year]
TRRlu![Org] = refrst![Action Org Code]
TRRlu![niin] = refrst![Rmvd NIIN]
TRRlu![TRRm] = TRRm
TRRlu.Update
Else
End If
refrst.MoveNext
Loop
TRRlu.Close
refrst.Close
DoCmd.DeleteObject acTable, "reftbl"

End Function


-and the query qryTRRgrp-

PARAMETERS paraFY Text ( 255 ), paraORG Text ( 255 ), paraNiin Text
( 255 );
SELECT FRC_M_lvl1.Fiscal_Year, FRC_M_lvl1.[Action Org Code],
FRC_M_lvl1.[Rmvd NIIN], FRC_M_lvl1.MCN, FRC_M_lvl1.TRR
FROM FRC_M_lvl1
WHERE (((FRC_M_lvl1.Fiscal_Year)=[paraFY]) AND ((FRC_M_lvl1.[Action
Org Code])=[paraORG]) AND ((FRC_M_lvl1.[Rmvd NIIN])=[paraNiin]))
ORDER BY FRC_M_lvl1.TRR;

Thanks for any help!
Aaron