
ADO Causes 100% Memory Usage/Increases in VM Usage
I'd like to get some help on what to try.
I've got an application that reads message periodically from an MSMQ queue
and logs the messages to SQLServer7. The problem I have is that after the
application has been running for a while (a day or more), the system cpu
usage will go to 100% or I will get an error message that the machine is low
on virtual memory. I've tracked the problem down to ADO. My logging
application opens one connection and one recordset (rs.Open "TableName",
connection, adOpenForwardOnly, adLockBatchOptimistic, adCmdTable). I call
rs.Addnew strFieldList, strFieldValues repeatedly throughout the execution
of the application. The memory used by my logging application grows
steadily over time and seems to be related to the number of rows in the
table. If I use a loop to insert records as fast as I can, I get to about
300,000 rows and the system goes to 100% cpu usage an basically stops
inserting records while it thrashes.
Any ideas about what is going wrong? Doesn't somebody else have a process
that runs for long periods of time inserting thousands of records?
Thanks for any help you can give,
Curtis
Sample Code:
Note: My sample app can insert rows based on a timer event or use a
"For" loop so both modes of operation are shown in this sample code
Private Sub Form_Load()
lngRecCnt = 0
OpenStuff
End Sub
Private Sub OpenStuff()
'Open the connection.
Set objADOConnection = New Connection
objADOConnection.ConnectionString = _
"Provider=SQLOLEDB;Password=myPWD;initialcatalog=MyCatalog;Persist
Security Info=True;User ID=MyID;"
objADOConnection.Open
Set objRS = New ADODB.Recordset
objRS.CursorLocation = adUseClient
objRS.Open "StateChanges", objADOConnection, adOpenForwardOnly,
adLockBatchOptimistic, adCmdTable
End Sub
Private Sub CloseStuff()
objRS.Close
Set objRS = Nothing
objADOConnection.Close
Set objADOConnection = Nothing
End Sub
Private Sub InsertRow()
Dim varFields As Variant, varRecordData As Variant
Dim strSQL As String
' Set fields
varFields = Array("PropertyName", "PropertyValue", "Comments")
varRecordData = Array("TestName", "TestValue", "TestComment")
' Add new record
objRS.AddNew varFields, varRecordData
SetProcessWorkingSetSize GetCurrentProcessId(), &HFFFFFFFF, &HFFFFFFFF
' strSQL = "Insert into StateChanges
(""PropertyName"",""PropertyValue"",""Comments"") values
('TestProperty','TestValue','This is a test')"
'' Debug.Print strSQL
' Set objRS = objADOConnection.Execute(strSQL)
lngRecCnt = lngRecCnt + 1
Form1.lblRecordCount.Caption = lngRecCnt
' If lngRecCnt Mod 1000 = 0 Then
' CloseStuff
' SetProcessWorkingSetSize GetCurrentProcessId(), &HFFFFFFFF,
&HFFFFFFFF
' OpenStuff
' End If
DoEvents
End Sub
Private Sub Form_Unload(Cancel As Integer)
CloseStuff
End Sub
Private Sub Timer1_Timer()
InsertRow
End Sub