There's always a way...

[Ascentient]

to make things work faster. I need some assistance to see if I can make this bit of code faster with another approach.

   'Recalc reorder levels.
   Dim intTotPreviousTwelveMonths As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblReOrderLevels", dbOpenDynaset)
   rs.MoveFirst
   Do While Not rs.EOF
      intTotPreviousTwelveMonths = 0
      DoCmd.Hourglass True
      'MsgBox rs!MATERIAL
      For i = 1 To 10
         intTotPreviousTwelveMonths = 
         intTotPreviousTwelveMonths + Nz(DSum("[QTY-SHIP-" & 
         i & "]", "tblBMtk", "[MATERIAL-CODE-" & i & 
         "]='" & [rs]![MATERIAL] & "' AND ([ORDER-DATE] 
         Between DateAdd(""yyyy"",-1,Now()) And Now())"), 0)
      Next
      rs.Edit
      rs!TwelveMonthSales = intTotPreviousTwelveMonths
      rs.Update
      rs.MoveNext
   Loop

The DSum has to scan 30,000 records (and growing) for each of 10 Material Code Fields in the table (created by my ingenious predecessor). You take this by the number of products that have to be calculated from tblReOrderLevels on a daily basis and it could eventually become very time consuming. I would like to simplify this if possible.

If more information is needed please let me know.

Ascent

 

[MichaelRed]

Just a knee jerk (albeit a painful one as I hit my head with the knee).

It is almost universally faster to use queries to generate recordsets from other recordsets. This is particularly true of aggregates. Generating the necessary may taks a bit of knoodling (see head scratching in other discionaries), but the results will be worth the effort. Another huge wasteland of time consumption are the domain aggregate functions (see DSum above). These are essientially wrapper functions which operate to generate -and then execute- plain old queries. If it can be done using DSum, you can write a parameter query, instantiate it as a query def and execute in in a loop -supplying the parameter(s) on the fly. This alone should cut down the execution time by a noticable ammount.

At a glance, it would seem like the total of the above might be able to be done in a single query and no code. At worst, it would require ten queries (one per field you need to sum).

All of the above will, however, not overcome the fact that you are searching 30K records for ten data sets. That generally will translate into a significant time lapse in generating results sets. The time will depend rather heavily on the topology of the system. Faster machine(s), more memory will help. Remotely located Data will hurt. Busy or slow network (for the remotely located Data) will hurt ) A LOT!

What I donot quite understand is why the record count is growing. It 'looks like' you limit the processing to the most recent year, so without a distinct and noticable increase in the transaction rate, the number of records which are actually involved should be fairly stable.

For a starting strategy, I would suggest:

copy the source data to a seperate local table. Be sure ti include the date range filter in generating this local storage, This brings the data to the local area (removing the network concerns) and minumizes the size of hte dataset to work with. DO NOT USE A QUERY BASED ON THE ORIGINAL DATA for this step.

Try to generate a single query on a single field of this loacl recordset. Conmpare these resulte to a sample run of the existing procedure(s). When you re successful with the simple query consider wheather you can do the same with with all fields in a single query or need to make several. Proceede with normal database design processes to flesh out the process to achieve your goal.




MichaelRed

 

[Ascentient]

So what you are saying is create a query that excepts parameters. The parameters that are pass is the Material-Code-#, and the Qty-Ship-#? How would I extract the SumOfQty-Ship-# from the query once it was executed?

Ascent

 

[MichaelRed]

Generally, you should not need (or even want) to extract the return value. The query should be an "Action" query (UpDAte or Append) the value(s) to the table where you would use them. in the snippet above, that appears to be "tblReOrderLevels".



MichaelRed

 

[eyeswideclosed]

Switching to ADO instead of DAO will improve performance as well.

 

[oharab]

With respect to the DLookup bits. A quick & dirty replacement can be found at

http://www.mvps.org/access/modules/mdl0012.htm.

Essentially you can just import the modules into your code and replace Dlookup (and dsum, davg, etc) with tLookup (and tsum,etc) and you, straight away, save a chunk of time.
It essentially does what MichaelRed said, creating a query and running it, but it's all wrapped up and doesn't require anything additional.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[Golom]

It looks like you are paying the price for an un-normalized table. I infer that you have fields of the form
[tt]
ORDER-DATE, QTY-SHIP-1, MATERIAL-CODE-1, QTY-SHIP-2, MATERIAL-CODE-2, ... QTY-SHIP-10, MATERIAL-CODE-10
[/tt]
If instead you had
[tt]
ORDER-DATE
SHIPNUMBER
QTY_SHIP
MATERIAL_CODE
[/tt]
Then simple SQL like

Select SUM(QTY_SHIP] As [TotPreviousTwelveMonths]

From tblBMtk

WHERE [MATERIAL_CODE] = '" & [rs]![MATERIAL] & "' "
  AND [ORDER-DATE] >= DateAdd("m",-12,Date)

would return the sum in a single query ... and probably more or less instantly if [MATERIAL-CODE] and [ORDER-DATE] are indexed.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[MichaelRed]

[qoute oharab] " ,, creating a query and running it ... " [/quote]

but my POINT was that the process of vreating and running the query is time consuming. wheather you use Domain agregates built by MS or those built by someone else, the process take a toll ...

as my doctor recently told me about a (lingering) clod "... get over it ... "



MichaelRed

 

[SuryaF]

Just an idea, first normalize it with an UNION query. You save the results in a temporary table. Then do whatever in a much faster way.
First create this UNION Query:

SELECT [QTY-SHIP-1],[MATERIAL-CODE-1] FROM [tblBMtk] WHERE [ORDER-DATE] Between DateAdd(""yyyy"",-1,Now()) And Now()
UNION ALL
SELECT [QTY-SHIP-2],[MATERIAL-CODE-2] FROM [tblBMtk] WHERE [ORDER-DATE] Between DateAdd(""yyyy"",-1,Now()) And Now()
UNION ALL
....
UNION ALL
SELECT [QTY-SHIP-10],[MATERIAL-CODE-10] FROM [tblBMtk] WHERE [ORDER-DATE] Between DateAdd(""yyyy"",-1,Now()) And Now()

Then take this and summarize it by Material Code.
The result should be a very tiny table.
Use that table for your calculations in whatever manner.

 

[Ascentient]

Thanks for the feedback everyone. If this had been a normal table, the issue would be moot. I will look at each suggestion and see what I come up with.

Ascent

 

[andyhutch119]

Is there anyway to Initialise a combo box in Access vba. I populate this witha recordset but can't seem to initialise the control which then repopulates the data.

 

[Ascentient]

Andy,
your post should be in a new thread, since it is a separate issue.

However, your statements suggests that you might need to look at the .requery method.

Ascent