The ISAMStats Function

You can use the ISAMStats function, which returns information about the raw disk reads, writes, locks, and caching, to analyze and tune the performance of your application. If you want to get every last bit of performance out of your database, you may find the ISAMStats function useful when trying to substantiate timing results based on various ways of manipulating data in the database. Some typical scenarios in which you might use the ISAMStats function include:

When you’re trying to tune a query and you’re not sure how to reduce network traffic.
When you’re looking at the effect of placing explicit transactions versus letting Microsoft Jet use its internal transactions.
When you have adjusted the registry settings and you want to see how they affect the number of reads and writes to the database.
When you want to validate timings, which may vary due to external circumstances that Microsoft Jet does not control, such as varying traffic on the LAN, different processes running on the machine, and disk fragmentation. Although elapsed time for a computer may vary, the number of reads or writes generally stays the same.

The syntax for the ISAMStats function is:

ISAMStats (StatNum As Long [, Reset As Boolean]) As Long

This function returns the value of a given engine statistic as defined by StatNum. The following table lists and describes the values you can use for StatNum.

StatNum	Description

0	Number of disk reads
1	Number of disk writes
2	Number of reads from cache
3	Number of reads from read-ahead cache
4	Number of locks placed
5	Number of release lock calls

If the optional Reset argument is supplied, then the statistic defined by StatNum is reset and no value is returned. A Reset value of False is equivalent to not supplying the argument. The statistics returned apply to the whole engine, regardless of how many databases or sessions are active, including temporary databases.

The following may help you interpret the statistics returned by the ISAMStats function:

The number of disk reads and writes includes all reads and writes in all circumstances, including background read-ahead cache reads in separate threads. One read or write doesn’t necessarily equal one page: One read or write could represent many pages that were read or written simultaneously. An example of this is commands wrapped in a transaction.
There are two types of cached reads returned. The regular cache is a set of pages. For example, after executing an SQL DML statement, it’s likely that the pages read would remain in the cache. The read-ahead cache statistic shows reads that occurred so that they would be available if needed. For example, in certain scenarios, Microsoft Jet 3.5 issues a read request for more pages than it actually needs. In this way, pages can be used without actually going to disk again. The number of cache reads doesn’t include read-ahead cache reads.
The number of locks placed and released may not balance. A single call to release a lock may result in the release of many locks.

The following procedure uses the ISAMStats function to return statistics on a query. The first procedure calls two procedures shown earlier in this chapter, ResetISAMStats and PrintISAMStats:

Sub ShowStats(strDbPath As String)
	Dim dbs As Database
	Dim strSQL As String
	Dim lngVersion As Long

	' If you are using Visual Basic, make sure that the database
	' is open or that the engine is initialized before issuing
	' any ISAMStats calls. This example uses DBEngine.Version to
	' initialize the database.
	lngVersion = DBEngine.Version

	' Explicitly set the counters to zero.
	ResetISAMStats
	' Open the database.
	Set dbs = OpenDatabase(strDbPath), _
		False, False)
	' Prompt user to enter SQL statement.
	strSQL = InputBox("Enter a SQL string", "SQL Box", _
		"UPDATE Customers SET ContactName = ContactName")
	' Execute query.
	dbs.Execute strSQL, dbFailOnError
	' Print the values to the Debug window.
	PrintISAMStats strSQL
	' Reset each value.
	ResetISAMStats
End Sub