I am working to accomplish a task in Access, and could use some advice concerning design. I have a table with 10 fields than can have 300,000 or more records. This table represents 360 records per account number:
From the original fields, I have to calculate an additional 50 columns to arrive at the final values for each record. I am currently doing this by dividing the calculated fields into 3 separate tables, and appending to each table sequentially so that Access isn't overloaded by trying to do 40 columns of calculations for this many records.
The problem is that is takes about 7 minutes to run 100,000 records, and I could conceivably have many more.
I was thinking about loading each source field for each Acct into an array and doing all the calculations in vb, creating new arrays for the calculated fields. Does this sound like it would perform better?
Thanks for any help. I know this is probably not particularily clear.
I have a source table with @ 10 columns, each is a numeric value that will be used in further calculations. I am using expressions in an append query to populate a second table based on the 10 columns in the source table.
Most of these expressions include iif statements, some have nested iifs.
Once I run the first append query, I now have a second table whose columns are based on the original table. This second table now becomes my source for the next append query, which populates a third table with numerous expressions.
Excel is more appropriate, but the data originates in access, and the row count is much to great for excel. I was thinking VB might be more efficient in handling numerous calculations.
If it would help, I could post an excel version that would give you an idea of what I am trying to duplicate in Access.
Well, right off the bat, if you have nested iif's in your queries, that's killing your efficiency. Create a module with functions that do all of the calculating for each row and put those functions in the query. Otherwise, Access has to compile the multiple, nested functions on EVERY ROW, which doesn't hurt the average user, but obviously hurts you.
Also, creating secondary tables is not improving your efficiency, just adding time. To the DB engine, a table is the same as a query - it's a recordset pointer to a group of data. Running a query to create a table so that you then use the table in a subsequent query is 2 extra steps you don't need.
Beyond this, I can't get a clear picture of what you are trying to do, but as you said, it can probably be done more efficiently in code with recordsets instead of queries.
Thanks for the reply. I always felt like I needed to use code somehow. Calling functions in the queries should definitely help performance.
Also, I can't run all 300,000 records together. I need to run 360 records for each Account Number individually. Would the best performance come from loading recordsets into arrays, then doing the calculations on the arrays?
I can't run all 300,000 records together. I need to run 360 records for each Account Number individually.
In any case, I'm still fuzy on what you need, but here's a shot in the dark:
You can run a "totals" query on the main table that groups by Account - that gives you a list of accounts. Using "Distinct" will give you the same result - I'm not sure which is faster.
Then, you have code that pulls a recordset for each account number it gets from the query (1 at a time) and peforms that calculations in code and a second recordset that provides a destination for the calculated results.
Set RecordSetAcct for all Acct in QueryDistinctAccounts
Set RecordSetData for all records where Acct = RecordSetAcct("Acct")
Get the data from first row
Perform the calculations
Loop Until RecordSetData.EOF
Save the results to RecordSetResults
Loop Until RecordSetAcct.EOF
No matter what you do, you want as few functions in your queries as possible (a good target is NONE).
btw: Opening a large recordset is just as resource intensive as opening a large query (same difference to Jet). Using recordsets is certainly faster than using domain functions and doing the calculations in code with recordsets saves the 299,999 recompiles the query has to go through.
A couple of the calculations for each record involve values from the prior record:
For instance, in order to get the Principal balance for each month, I need to use the Funding expense for the previous month (record). In other words, The principal balance calculation for month 3 for all Accounts is based upon the prior month's Funding Expense. The only way I know to do this is to create a query with Acct, Funding Expense and PriorMO:[Month]-1, and now I have the prior months funding expense for each month for each Acct.
I just don't know how I would do this in VB.
Also, I can't sum by Acct. I am generating monthly cash flows, I am finding a return on a set of cash flows. They must be calculated separately.