1

Topic: To accelerate filling of copies of a class with the data

Hello!
At nested excel th file on the tab "Initial data" there is data which are received by means of outswapping. The task such that from this data it is necessary to receive the unique list of copies of a class (+SKU++), and then to scatter the data on appropriate copies of a class. Further on each copy there are chain calculations on days. In the given example chain calculations are not resulted and in the code in a class there are properties for results. In general, the code is written, the code works. But is but.
1. It seems to me that I not absolutely correctly wrote the code. Because, if 500 000 lines long reads out, then for filling of each copy of a class it is necessary to be transversed on all lines and if finds takes the data from current line and interposes into appropriate copies. Then long there are chain calculations in each copy though it is other question. Whether it is possible how to optimize data reading at sight, search unique, creation of copies of classes and their filling on the certain logic that worked faster?
2. After all copies of classes, but before chain calculations, that is necessity to save copies a dataful format in what that are created and filled. Then anew not to read out at sight, and to preempt from that file, where the data not simply as the relational table, and as class copies. And after carrying out of chain calculations in this file results of calculations are filled. It is possible as that to make? There is such data format?
Do not pay attention that variables in Russian, it that the code was easy in understanding.

2

Re: To accelerate filling of copies of a class with the data

ferzmikk;
Acceleration ways:
1. To throw Range in an array and further to fulfill iterations on an array, instead of on sheet.
2. To throw 500 000 lines in mdb, to hang up a key for selection of unique records and to work through KeySet Recordset.

3

Re: To accelerate filling of copies of a class with the data

Whether

ferzmikk wrote:

It is possible how to optimize data reading at sight, search unique,

There is a means of removal of counterparts. Launch it manually under record, you will receive the code which  will be more effective than yours.

ferzmikk wrote:

to preempt from that file, where the data not simply as the relational table, and as class copies. And after carrying out of chain calculations in this file results of calculations are filled. It is possible as that to make? There is such data format?

to Preempt objects in file As Binary, and to load from a file...

4

Re: To accelerate filling of copies of a class with the data

ferzmikk;
If it is a question of optimization data storage in a class copy is counter-indicative. And if to do so, not to use procedure Property Get and Property Let, and simply to declare class variables as Public.
Here comparing of time of access to class variables through Property, directly, and also to a similar variable of the user type and to a local variable.

' the code of a class the Data - to change
Public m As Date
' the code of the new unit
Type t
m As Date
End Type
Sub test ()
Const N = 5000000
Dim d As Date, d1 As Date, j&, t!, cls As Given, typ As t
Set cls = New the Data
d = Now
DoEvents: t = Timer
For j = 1 To N
Next
Debug. Print "warm-up", Timer - t
DoEvents: t = Timer
For j = 1 To N
cls. = d
d = cls.
Next
Debug. Print "class prop.", Timer - t
DoEvents: t = Timer
For j = 1 To N
cls.m = d
d = cls.m
Next
Debug. Print "class public", Timer - t
DoEvents: t = Timer
For j = 1 To N
typ.m = d
d = typ.m
Next
Debug. Print "type", Timer - t
DoEvents: t = Timer
For j = 1 To N
d1 = d
d = d1
Next
Debug. Print "local var", Timer - t
End Sub

My results:

 warm-up 0,109375
class prop. 2,4375
class public 0,453125
type 0,203125
local var 0,234375

Access to a class variable directly in 5 times faster, than through Property, and access to a variable from the user type in 2 times faster.