CPU usage 100% using VBA ADO object.

[jforero]

Hi,

The CPU usage goes up 100%.

This is the scenario:

We have ACCPAC program running its database in a server with Pervasive V8.

We have some VBA customized programs that use ADO to connect to that database as following:

Set oCon = New ADODB.Connection
oCon.ConnectionString = "Provider=PervasiveOLEDB.8.10;Data Source=DBSEAL;Location=powervault"
oCon.CursorLocation = adUseClient
oCon.Open

Set oComTra = New ADODB.Command
oComTra.CommandText = "SELECT IDCUST, IDINVC, CNTBTCH, CNTITEM, CNTLINE, IDARACCT FROM ARATR"
oComTra.CommandType = adCmdText
oComTra.ActiveConnection = oCon

Set oRecTra = New ADODB.Recordset
Set oRecTra.Source = oComTra
oRecTra.CursorType = adOpenStatic
oRecTra.LockType = adLockReadOnly
oRecTra.Open

What I have noticed is that during the time that the program is opening the ADO recordset the CPU in the server goes up 100% an all other processes in all workstations get slow.

What could be the problem?

Any help appreciated.

Thanks, Jose.

 

[zemp]

It's hard to say. You SQL wants to look at every record in the database, could it be that you have a very large data set and/or are you pulling the data across a network.

Also, the way ADO works with the SQL you have is that it pulls over all the data in the table and then processes the specific SQL request on your computer to come up with your recordset. The entire table needs to be put into memory and processed, this can easily use all of your resources. To avoid this you have to have the database engine do the processing and send over only the data you requested. The easiest way to do this is to use a stored procedure or change your cursor location to the server. However, changing your cursor location might affect other things as well.

One other thought, if you have VBA programs for ACCPAC why not consider the ACCPAC API objects. COMAPI would be best for an ACCPAC VBA macro or program. These object use the ACCPAC views and therefore the ACCPAC business logic (which ensures data intergity). The views are also set up for efficient data retrieval and makes sure that the database engine does the work it is designed to do.

zemp

 

[sblocher]

ARATR is typcially one of the largest tables since it is a transaction table. And your query doesn't include any selection criteria. The fact that the server peaks is not surprising. Other users hitting the database or using this servers resources are likely to "feel" this, too.

If you ultimately want all the records in your dataset, there's not much you can do to improve this situation. But if you can pass some criteria to the server to reduce the recordset size, that's preferable. I agree with zemp, too. You need to get the server to do the selection, not the workstaion.