Optimization of appendices in Access

Optimization of hardware

Each module Access possesses set of opportunities for adjustment of the appendix, but it is impossible to count on any success in work if the computer on which the appendix is carried out, is become outdated or has not enough operative memory. Microsoft in the summary to Access specifies the minimal system requirements for a computer. But practice shows, that similar resources suffice only on that the program that refers to "has begun to breathe", and here normally to work, requirements should be increased seriously.

If to choose between modernization of the processor and installation of the RAM of greater volume, follows select the RAM. As is known, memory does not happen, and Access much, as well as any other serious database, for the normal work demands a significant memory size

It is necessary to clear on a regular basis a basket and to delete time files (especially files Internet and e-mail!). A database demands significant volume of disk space, and these files can absorb weight of a place still before the user will have time to realize it

Not regular compression of a database will be superfluous. Since version Access 2000 it can be adjusted automatically. Service – Parameters – the general – To compress at closing. The matter is that in Access there is "basket", and at removal of record do not leave, and are marked as removed (a sign *), that is « become in turn on removal ». As in Access there are latent system tables in which parameters of forms are stored. The form have removed, and parameters have remained. Then they certainly will leave, but it then. For now we have "dust" - unnecessary data. For this reason often beginning(starting) developers are surprised, when after removal records from base its size why that not so changes. Especially it concerns(touches) fields of type OLE where figures of a format .bmp are stored. Have hammered in base figures, it "was inflated" up to several tens in Mb, have removed figures – and the size has remained. An output – compression bases. As it is noticed, that at regular compression the base to become steadier - "flies" less often.

It is recommended to disconnect Journal (Magazine) in Outlook. Magazine Outlook generates record at each start and an output from the appendix. This magazine can become very much to greater and absorb disk space and the processor time, necessary for the appendix

It is necessary to use the released operative memory. Appendices it is very good operative memory, but reluctantly it release. It is not necessary to start some appendices simultaneously. In general, it is desirable, that the machine(car) on which the base is established, it has been focused first of all on work with base – it is not necessary to put on it unnecessary programs. Especially it concerns machines which are used as "servers" at a file-server construction of network base. And that sometimes quite often on such "server" work as on the client, moreover start any "heaped up" appendix, and not one, and then are surprised, why suddenly the network base began "to brake".

At many developers computers much more high-speed, than at users. It should be considered by development of the appendix, and to test it by "weaker" machine. As, if you develop the appendix, not knowing precisely what Office will be at the user, not superfluous will test it on all versions on which it can be presumably established. Is better to establish by the machine some operational systems and on everyone corresponding version Office is will give the "purest" result of testing. As if you reinstall Office it is necessary to remember, that many « serious appendices » appendices "litter" if to not tell more, in the system register. And the the appendix is more serious", the it is more. Therefore after removal of the program, not superfluous will clean the register, for example, by means of RegCleaner, and even manually through RegEdit.exe and "to finish" that remains through Norton Utilities Integrator.

Estimation of productivity

The desire to look is natural, that optimization has given, the appendix how much more quickly began to work. API Windows offers the timer which traces time in milliseconds. Function timeGetTime () measures a time interval from the moment of start Windows. As it uses other hardware counter returns time to within a millisecond. Using timeGetTime (), it is possible to insert a line of a code before performance of any critical operation and to receive very exact measurement of time which was required for end of action. Two things are necessary for use of call API: the announcement of function and a global variable for storage of time of start of the timer. In section of announcements of the module it is necessary to enter next three lines:

     Private Declare Function a2ku apigettime Lib "winmm.dll" Alias "timeGetTime" () As Long
     Dim Ingstartingtime As Long

Then it is possible to create the subroutine for start of hours and function of a stop of hours

     Sub a2kuStartClock()
          Ingstartingtime = a2ku_apigettime()
     End Sub

     Function a2kuEndClock()
          a2kuEndClock = a2ku_apigettime() - Ingstartingtime
     End Function

And here an example of use of these functions:

' Definition of time of performance of inquiry

     Sub QueryTimer(strQueryName As String)
     Dim db As Database
     Dim qry As QueryDef
     Dim rs as Recordset
          Set db = CurrentDb()
          Set qry = db.QueryDefs(strQueryName)
' Start of hours. a2kuStartCloc
          Set rs = qry.OpenRecordset()
' the Stop of hours and a conclusion of result in a window of debugging
          Debug.Print strQueryName & " executed in:  " & a2kuEndClock & " milliseconds"
          rs.Close
     End Sub

For achievement of the greatest accuracy of measurement it is necessary to place a call of procedures a2kuStartClock and a2kuEndClock as it is possible more close to a site of considered(examined) procedure.

Though definition of time plays not last role, but only on the basis of this information to judge productivity of the appendix during development. As already it was spoken above, it is usual at the developer more high-speed computer, so testing not absolutely "pure", in fact the timer cannot inform, how the appendix on other computer with a smaller memory size or more a disk will be carried out. For more exact supervision over the appendix it is possible other not documentary function — ISAMStats. Function ISAMStats is not documentary and not supported, therefore the information which it informs, it is possible to use only as the general instructions. At start function makes measurement of six important influencing on operations. It counts up all references of reading and record on a disk, references of reading in a cache, advancing reading, accommodation of blocking and clearing of blocking. The given function can be used in Access 2000 and even in earlier versions Access. Syntax of function very simple:

     DBEngine.ISAMStats(an option,[reset])

    There are six possible(probable) values for argument an option:

The unessential parameter of reinstallation allows to reinstall separate counters on zero value. To take advantage of the given function for an estimation of productivity, it is necessary or to subtract(deduct) one indications from previous, or to reinstall the counter on zero value and then you-lift an estimation. We shall consider(examine) one of ways of use of function ISAMStats for an estimation of productivity.

     Sub PrintStats()
     Dim i As Integer
          Debug.Print
          Debug.Print "Disk Reads:  " & ISAMStats (0, Falsa)
          Debug.Print "Disk Writes:  " & ISAMStats(1, False)
          Debug.Print "Cache Reads:  " & ISAMStats(2, False)
          Debug.Print "Read-Ahead Cache Reads:  " & ISAMStats(3, False)
          Debug.Print "Locks Placed:  " & ISAMStats(4, False)
          Debug.Print "Locks Released: " & ISAMStats(5, False)
          For i = 0 To 6
               ISAMStats i, True
          Next
     End Sub

PrintStats can give some representation about why one technique works more quickly, than another. Besides this information can help to make a choice between two approaches which performance borrows an identical time interval. Having looked on obtained data, it is possible to define, how the different hardware configuration or a greater data set can affect productivity of this or that approach. The considered functions are useful only at comparison of one technique with another. Tthat, it is possible to receive reliable results only in the event that to average data of many tests lead under different conditions. The given two functions it is possible to unite with ease in one for carrying out of similar repeating tests.

Creation of communications between tables in the designer (the scheme of data)

In previous clauses it was already spoken about importance of the correct task of communications between tables. I shall add one more remark: Jet (the mechanism of databases - the center almost everything, that occurs in the appendix of databases Access) learns about existence of parities first of all from this window. Jet can use all this for creation of more effective plan of optimization at inquiries to data. It considerably raises productivity.

Increase of speed of performance of inquiries

Here the basic recommendations:

It is recommended to create indexes for all fields which will be used for definition of criterion of selection

In recorset a set it is not necessary to display any superfluous columns. Processing and each column borrows(occupies) additional time

It is recommended to abstain from the use of complex(difficult) expressions in inquiries

It is necessary to avoid function IIF (). IIF () estimates both true, and false values before giving out result. If to carry out the given operation for each record, it can strongly affect productivity.

Whenever possible it is necessary to use operator Between for reduction of quantity of lines in a set instead of operators " more than " and " less than ".

It is recommended to experiment whenever possible with the subordinated inquiries instead of ssociations or complex conditions OR. The optimum choice depends on many discrete factors, and only experiment will help(assist) to solve, what approach to use.

Instead of SQL-operators in a code it is recommended to use the kept inquiries with parameters. Jet has already compiled inquiries with parameters and has created for them the plan of performance. Use of the compiled and kept inquiries eliminates necessity of an estimation and optimization of SQL-line. Access compiles the SQL-lines used as a source of records or a source of lines for forms, reports or elements of management, therefore they remain untouched. Therefore it is recommended to use the compiled inquiries always

The author: Admin It is added: 20.01.2007