ADO Causes 100% Memory Usage/Increases in VM Usage 
Author Message
 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



Sun, 15 Dec 2002 03:00:00 GMT
 ADO Causes 100% Memory Usage/Increases in VM Usage

You could try issuing an INSERT query across the connection instead of
opening a cursor.

You may even want to track how frequently the record adding occurs and judge
if keeping the connection open for the duration really saves much overhead
in terms of performance.

You could even try detaching the recordset until shut-down and then
reconnect for the batch update.

Michael


Quote:
> 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



Mon, 16 Dec 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Adding memory to SQL server causes 100 percent CPU usage

2. mssearch causes 100% CPU usage

3. msssearch is causing 100% CPU usage

4. MSSEARCH Causing 100% CPU Usage

5. 100% proc usage and memory paging

6. Seemingly unrelated change causes increased resource usage

7. Memory usage increases

8. Increased server process memory usage for Oracle 8i

9. Increase in memory usage for Client lib 10.0.2 under SCO

10. Increasing memory usage on SQL Server

11. Increasing memory usage

12. SQL2000 continuesly increases Memory usage


 
Powered by phpBB® Forum Software