VB - Virtual Memory Low [RESOLVED]

[Pobo]

Hi.

I have an application which imports a lot of data from various data sources. After the application has been running for a few hours, I start getting warnings about the virtual memory being low. Does anyone have any coding tips on how to avoid this happening or what to do to rectify the problem?


Thanks

Pobo

[RobDog888]

Always destroy you objects is probably the #1 suggestion.

Post some sample code so we can make more relevant suggestions.

[Pobo]

Too much code - wouldn't know which to post. If a global variable is used over and over and never destroyed would this create this problem. I thought that each time the variable is reset that it would clear the old 'memory'?

[Pobo]

Could the fact that I don't declare most variables or use Option Explicit cause this?

[RobDog888]

That does not help your program. If you dont declare your vars then VB declares them as a Variant. A Variant takes up more memory
then if you declared the vars as the proper type for what they are being used for. Also delcare your vars as the smallest size needed.

For ex.
Dim lCount As Long
Vs.
Dim bytCount As Byte

The first allocates more memory to hold a larger number and the second requires less since a Byte is 0 - 255. A Long is -2,147,483,648 to 2,147,483,647.

Option Explicit helps to eliminate potential bugs by forcing variable declaration.

[MartinLiss]

Could the fact that I don't declare most variables or use Option Explicit cause this?

ALWAYS use Option Explicit. You are only asking for problems if you don't.

[randem]

Pobo,

Too much code to post? Then upload your project.

[Pobo]

Randem

Why do you want the whole project?

[dglienna]

He doesn't. You want someone to have it if you expect anyone to be able to help you find the memory leak

[Pradeep1210]

Most of the objects get destroyed automatically when the reference is reset. With some others you must destroy it explicitly by setting it to nothing. They don't get destroyed automatically and keep consuming memory space until your app is closed.

[randem]

Pobo,

OK, it's your problem, Not mine. Memory leaks are a result of YOUR code. You cannot expect someone to help without seeing your code. Of course we could wave our hands over the computer to see if your problem goes away...

[penagate]

Most of the objects get destroyed automatically when the reference is reset. With some others you must destroy it explicitly by setting it to nothing. They don't get destroyed automatically and keep consuming memory space until your app is closed.

note: most... not all. You should always Set objects to Nothing after you have finished with them anyway, just to be sure.

Also, I don't know if this would help much, but if you are importing a lot of data, you could try erasing your variables after you have finished with them. Say if you have strings or arrays of strings holding the data, once you have finished with them set the strings to vbNullString or Erase the arrays. This will free up a bit of memory.

But, if you aren't even declaring your variables, then who knows what's going on

[Pobo]

Okay guys - so I should declare all my variables and set them to nothing when I've finished using them! I know, I know I'm a lazy ass!

BUT I don't think that's the problem here. When I run the application on an even slower machine with less memory and a crap spec it doesn't give me a virtual error message. I think it's got something to do with the fact that I increased the virtual memory 'initial' setting to try and gain some more peformance. I forgot I did it some three months ago. I've just reset it to the recommended size and am testing it now.

Any bets for whether this resolves the problem? So far no one has mentioned that this could have been the reason. Rather, all the pros have suggested I declare my variables. Time is critical here guys. I can refine the project later and make it nice! Right now I just need the damn thing to work!

Personally, I don't think that declaring your variables is as critical as it's cracked out to be. With the sort of power we've got in machines today and the fact that procedure-level variables destroy themselves out of scope anyway, how critical can it be?

After all this, I'll probably get back to you begging for help/advise and complaining that reseting the virtual memory size hasn't worked ...


Speak soon!

Pobo

[Pobo]

Gents

Looks like I have got a virtual memory problem that lies with the system. The knowledge base says that this is something that will happen over time and that I can defragment the paging file by switching the system on and then off! Does anyone know if there is a way to do this while the system is running? Apparently, there is one!

Pobo

[randem]

Pobo,

It would seem that if time is critical that you would just do it correctly instead of ignoring the "EXPERTS" and going off on a tangent. Your way will not fix anything, it will just posibly hide the BOMB until a later time.

[Pobo]

Right experts - I've been forced to do it your way!

Readjusting the virtual memory size didn't work but neither has declaring all my variables!

The application writes to a MySQL database every 2 seconds. It updates three fields in a single record - DataImporterActive (status), CheckDataImporterIdleInterval and LastUpdate fields.

It also reads about 10 fields from another record. Both relatively simple tasks, however I've just installed a virtual memory monitor and observed that the total free virtual memory is falling by about 1MB every 7 or so seconds!

In this process all the variables have been declared and are set to nothing at the end of the procedures. Why is the virtual memory still falling?


Pobo

[dglienna]

I think if you want us to do anything but guess, then you'll have to post your code. We've given you the most common reasons that it would fail, but obviously not all of the reasons it *could*. I'm sure it could be straightened out, but not by anyone that can't actually see the code.

[Pobo]

Excuse the line numbers they're for the error handlers!

Private Sub Timer1_Timer()


          'If glngDataImporterTaskID = 0 Then Exit Sub

        If WaitForProcess(glngDataImporterTaskID, 0) = False Then

              ' inform the user that something happened
              'NO USER - MsgBox "The Data Importer has been closed.", vbInformation

              'try to reopen the DataImporter
            On Error GoTo DATAIMPORTER_WONT_REOPEN
            glngDataImporterTaskID = Shell(gstrDataImporterPath, vbNormalFocus)
            On Error GoTo GENERAL_PROCEDURE_ERRHANDLER
        Else
            Call UpdateDataImporterActivityStatus(1)
        End If

        Call GetUpdateProcessDetails


    End Sub


Sub UpdateDataImporterActivityStatus(ByVal lngDataImporterActive As Long)

          Dim rstWrite As clsWriteRecSet


10        On Error GoTo GENERAL_PROCEDURE_ERRHANDLER

20        Set rstWrite = New clsWriteRecSet

30        rstWrite.ConnectionString = gstrPmMDatabase

40        rstWrite.SQLStatement = "UPDATE DataImporterMonitor " _
                  & "SET DataImporterActive = " & lngDataImporterActive
50        rstWrite.Query

60        rstWrite.SQLStatement = "UPDATE DataImporterMonitor " _
                  & "SET CheckDataImporterIdleInterval = '" & gstrCheckDataImporterIdleInterval & "'"
70        rstWrite.Query

80        rstWrite.SQLStatement = "UPDATE DataImporterMonitor " _
                  & "SET LastUpdate = NOW()"
90        rstWrite.Query

Set rstWrite = Nothing


End Sub

[Pobo]

Sub GetUpdateProcessDetails()

          Dim varScheduledDate As Variant
          Dim varProcessUpdateStatus As Variant
          Dim varNarrative As Variant
          Dim varScheduledStartTime As Variant
          Dim varAccountDepotsID As Variant
          Dim varSourcename As Variant
          Dim varProcess As Variant
          Dim varProcessType As Variant
          Dim varActualStartTime As Variant
          Dim varDataImportQueueUID As Variant
          Dim varDataImporterStatus As Variant
          Dim varStopDataImporter As Variant
          Dim varEstimatedRunTime As Variant
          Dim varRunTimeStandardDeviation As Variant
          Dim varEstimatedCompletionTime As Variant
          Dim varDatabaseTime As Variant


20        If gblnFreezeDataMonitor = False Then

              'from DataImportQueue
30            varProcessUpdateStatus = Empty
40            varNarrative = Empty
50            varScheduledStartTime = Empty
60            varScheduledDate = Empty
70            varAccountDepotsID = Empty
80            varSourcename = Empty
90            varProcess = Empty
100           varProcessType = Empty
110           varActualStartTime = Empty
120           varDataImportQueueUID = Empty


              'from DataImporterControl
130           varDataImporterStatus = Empty

              'from DataImporterMonitor
140           varStopDataImporter = Empty

              'from DataUpdateStats
150           varEstimatedRunTime = Empty
160           varRunTimeStandardDeviation = Empty
170           varEstimatedCompletionTime = Empty
              'from Database
180           varDatabaseTime = Empty

              'get information

              'get DataImporterControl information
              Dim rstRead As clsReadRecSet
190           Set rstRead = New clsReadRecSet

200           rstRead.ConnectionString = gstrPmMDatabase
210           rstRead.SQLStatement = "SELECT * from DataImporterControl"

220           rstRead.Query

230           If rstRead.RecSet.RecordCount > 0 Then
240               rstRead.RecSet.MoveFirst
250               varDataImporterStatus = rstRead.RecSet.Fields("DataImporterStatus").Value
260           End If


              'get DataImporterMonitor information
270           rstRead.SQLStatement = "SELECT * from DataImporterMonitor"
280           rstRead.Query

290           If rstRead.RecSet.RecordCount > 0 Then
300               rstRead.RecSet.MoveFirst
310               varStopDataImporter = rstRead.RecSet.Fields("StopDataImporter").Value
320           End If

              'get gstrPmMDatabase time
330           rstRead.SQLStatement = "SELECT NOW()"

340           rstRead.Query

350           If rstRead.RecSet.RecordCount > 0 Then
360               rstRead.RecSet.MoveFirst
370               varDatabaseTime = Format(rstRead.RecSet.Fields("Now()").Value, "dd/mm/yyyy HH:mm:ss")
380           End If


390           If varDataImporterStatus <> "Initializing" And varDataImporterStatus <> "Idle" Then

                  'get DataImportQueue information
400

410               rstRead.ConnectionString = gstrPmMDatabase
420               rstRead.SQLStatement = "SELECT * from DataImportQueue" _
                          & " where UpdateStatus <> 'Cancelled' LIMIT 1 "

430               rstRead.Query

440               If rstRead.RecSet.RecordCount > 0 Then
450                   rstRead.RecSet.MoveFirst

460                   varProcessUpdateStatus = rstRead.RecSet.Fields("UpdateStatus").Value
470                   varNarrative = rstRead.RecSet.Fields("Narrative").Value
480                   varScheduledStartTime = rstRead.RecSet.Fields("ScheduledStartTime").Value
490                   varScheduledDate = rstRead.RecSet.Fields("Date").Value
500                   varAccountDepotsID = rstRead.RecSet.Fields("AccountDepotsID").Value
510                   varSourcename = rstRead.RecSet.Fields("Sourcename").Value
520                   varProcess = rstRead.RecSet.Fields("Process").Value
530                   varProcessType = rstRead.RecSet.Fields("ProcessType").Value
540                   varActualStartTime = rstRead.RecSet.Fields("ActualStartTime").Value
550                   varDataImportQueueUID = rstRead.RecSet.Fields("DataImportQueueUID").Value

560               End If

                  'from DataUpdateStats
570               varEstimatedRunTime = Empty
580               varRunTimeStandardDeviation = Empty

590               rstRead.SQLStatement = "SELECT * from DataUpdateStats " _
                          & "where Datasource = '" & varSourcename & "'"

600               rstRead.Query

610               If rstRead.RecSet.RecordCount > 0 Then
620                   rstRead.RecSet.MoveFirst
630                   varEstimatedRunTime = rstRead.RecSet.Fields("EstimatedRunTime").Value
640                   varRunTimeStandardDeviation = rstRead.RecSet.Fields("RunTimeStandardDeviation").Value
650                   varEstimatedRunTime = Format(TimeValue(varEstimatedRunTime), "HH:mm:ss")
660                   varRunTimeStandardDeviation = Format(TimeValue(varRunTimeStandardDeviation), "HH:mm:ss")
670                   varEstimatedCompletionTime = varActualStartTime + TimeValue(varEstimatedRunTime)
680               End If
690           End If

              'clear labels
700           lblSystemTime.Caption = ""
710           lblProcessUpdateStatus.Caption = ""
720           lblScheduledStartTime.Caption = ""
730           lblAccountDepotsID.Caption = ""
740           lblSourcename.Caption = ""
750           lblProcess.Caption = ""
760           lblProcessType.Caption = ""
770           lblActualStartTime.Caption = ""
780           lblDataImportQueueUID.Caption = ""
790           lblDataImporterStatus.Caption = ""
800           lblEstimatedRunTime.Caption = ""
810           lblRunTimeStandardDeviation.Caption = ""
820           lblEstimatedCompletionTime.Caption = ""

830           On Error Resume Next
              'put values in controls

840           lblSystemTime.Caption = Format(varDatabaseTime, "dd/mm/yyyy HH:mm:ss")
850           lblProcessUpdateStatus.Caption = varProcessUpdateStatus
860           lblScheduledStartTime.Caption = Format((varScheduledDate + TimeValue(varScheduledStartTime)), "dd/mm/yyyy HH:mm:ss")
870           lblAccountDepotsID.Caption = varAccountDepotsID
880           lblSourcename.Caption = varSourcename
890           lblProcess.Caption = varProcess
900           lblProcessType.Caption = varProcessType
910           lblActualStartTime.Caption = Format(varActualStartTime, "dd/mm/yyyy HH:mm:ss")
920           lblDataImportQueueUID.Caption = varDataImportQueueUID

930           If IsNull(varNarrative) Then varNarrative = ""

940           If txtNarrative.Text <> varNarrative Then
950               txtNarrative.Text = varNarrative
960           End If

970           lblDataImporterStatus.Caption = varDataImporterStatus

980           lblEstimatedRunTime.Caption = varEstimatedRunTime
990           lblRunTimeStandardDeviation.Caption = varRunTimeStandardDeviation
1000          lblEstimatedCompletionTime.Caption = Format((DateValue(varDatabaseTime) + TimeValue(varEstimatedCompletionTime)), "dd/mm/yyyy HH:mm:ss")

1010          If varStopDataImporter = 0 Then
1020              lblStoppingDataImporter.Visible = False
1030              Timer2.Enabled = False
1040          ElseIf varStopDataImporter = 1 And _
                      lblStoppingDataImporter.Visible = False Then
1050              lblStoppingDataImporter.Caption = "STOP"
1060              lblStoppingDataImporter.Visible = True
1070              Timer2.Interval = 1000
1080              Timer2.Enabled = True
1090          ElseIf varStopDataImporter = 2 And _
                      lblStoppingDataImporter.Visible = False Then
1100              lblStoppingDataImporter.Caption = "RE-INITIALIZING"
1110              lblStoppingDataImporter.Visible = True
1120              Timer2.Interval = 1000
1130              Timer2.Enabled = True
1140          End If

1150      End If

1160      If lblDataImporterStatus.Caption = "Idle" Then
1170          lblStoppingDataImporter.Visible = False
1180          Timer2.Enabled = False
1190      End If

1200      Set rstRead = Nothing
          Set varScheduledDate = Nothing
          Set varProcessUpdateStatus = Nothing
          Set varNarrative = Nothing
          Set varScheduledStartTime = Nothing
          Set varAccountDepotsID = Nothing
          Set varSourcename = Nothing
          Set varProcess = Nothing
          Set varProcessType = Nothing
          Set varActualStartTime = Nothing
          Set varDataImportQueueUID = Nothing
          Set varDataImporterStatus = Nothing
          Set varStopDataImporter = Nothing
          Set varEstimatedRunTime = Nothing
          Set varRunTimeStandardDeviation = Nothing
          Set varEstimatedCompletionTime = Nothing
          Set varDatabaseTime = Nothing
          

1220	End Sub


‘

[Pobo]

from Write clsmodule
Sub Query

'Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection

    Dim strMsg As String
    Dim lngNumberOfRetries As Long
    'Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    conn.CursorLocation = m_CursorLocation
    conn.ConnectionString = ConnectionString 


OPEN_CONNECTION:
    conn.Open

    On Error GoTo ERROR_EXECUTING_SQLSTATEMENT
    If m_checkforbackslash = True Then
        SQLStatement = Replace(SQLStatement, "\", "\\")
    End If

    rs.Open SQLStatement, conn, m_CursorType, m_LockType

    Set RecSet = rs
    Exit Sub


'from Read clsmodule
Sub Query()

    Dim lngNumberOfRetries As Long
    Dim strMsg As String

    readError = False

    On Error GoTo ERROR_CONNECTING_TO_DATASOURCE:

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.CursorLocation = m_CursorLocation
    conn.ConnectionString = ConnectionString    

OPEN_CONNECTION:
    conn.Open

    On Error GoTo ERROR_EXECUTING_SQLSTATEMENT
    If m_checkforbackslash = True Then
        SQLStatement = Replace(SQLStatement, "\", "\\")
    End If

    rs.Open SQLStatement, conn, m_CursorType, m_LockType    

    Set RecSet = rs

    Exit Sub

[dglienna]

Post a zip file of your project. The three pages you posted might not be enough. I didn't see any Option Explicit's in there. I won't complain as it's possible that they are in your other code.

[MartinLiss]

Setting variables to Nothing most likely does nothing to save memory. BTW your use of Variants is wasteful oif memory and speed but that's not your problem. Your problem is that you are not setting your created objects like rs to Nothing.

[Jacob Roman]

Okay guys - so I should declare all my variables and set them to nothing when I've finished using them! I know, I know I'm a lazy ass!

BUT I don't think that's the problem here. When I run the application on an even slower machine with less memory and a crap spec it doesn't give me a virtual error message. I think it's got something to do with the fact that I increased the virtual memory 'initial' setting to try and gain some more peformance. I forgot I did it some three months ago. I've just reset it to the recommended size and am testing it now.

Any bets for whether this resolves the problem? So far no one has mentioned that this could have been the reason. Rather, all the pros have suggested I declare my variables. Time is critical here guys. I can refine the project later and make it nice! Right now I just need the damn thing to work!

Personally, I don't think that declaring your variables is as critical as it's cracked out to be. With the sort of power we've got in machines today and the fact that procedure-level variables destroy themselves out of scope anyway, how critical can it be?

After all this, I'll probably get back to you begging for help/advise and complaining that reseting the virtual memory size hasn't worked ...


Speak soon!

Pobo

Since you are lazy, instead of typing Option Explicit by hand, try this. Go up in the menu to Tools > Options and check off Require Variable Declaration. Now press Ok. Every new Form, Module, and Class Module you make will now have Option Explicit on top automatically.

[Pobo]

Gents

Stop waisting my time with your Option Explicit, declare all variables anal nonsense. I have worked out the problem. It's taken me two days of solid work to do it but I've done it. The ODBC driver MySQL 3.51.06 has a memory leak bug so I needed to upgrade to the latest version. Problem fixed.

Next time, I expect you gurus and top-boys to work it out sooner. Ask me a few sensible questions and work out what I'm doing wrong BUT never send me on a wild goose chase, declaring variables unnecessarily ever again! And please, don't ask me to upload the whole bloody project when it's so obvious that that's not where the problem lies!

I'm going to bed now ...

Speak soon

Pobo
Junior Poster

[penagate]

Gents

Stop waisting my time with your Option Explicit, declare all variables anal nonsense. I have worked out the problem. It's taken me two days of solid work to do it but I've done it. The ODBC driver MySQL 3.51.06 has a memory leak bug so I needed to upgrade to the latest version. Problem fixed.

Next time, I expect you gurus and top-boys to work it out sooner. Ask me a few sensible questions and work out what I'm doing wrong BUT never send me on a wild goose chase, declaring variables unnecessarily ever again! And please, don't ask me to upload the whole bloody project when it's so obvious that that's not where the problem lies!

I'm going to bed now ...

Speak soon

Pobo
Junior Poster

That just says to me, "I know best, I don't need any help", therefore, in the future, you won't get any from me.

Best wishes.

[dglienna]

If you would have posted your code last week, we would have know that you were using MySQL, seen the driver, and IMMEDIATELY found your memory leak. At least none of us lost any sleep over it. Glad you did it your own way.
Are you going to do anything about all the declared variant variables? It will speed up your program.

[randem]

Pobo,

It is you that is wasting our time. Since you expect others to do your work without seeing what you are doing. Perhaps you should just fix you own problems and not bother others with your problems.

[dee-u]

IMHO, Pobo you should be humble and thankful that people here in VBForums is trying to help you.... What you did was truly ungrateful....

[Pobo]

Guys

What are you crying about? I'm not being ungrateful I'm just having a laugh! There are some problems you've just got to sort out on your own. I was hoping that someone would be able to help with this one but I didn't really expect anyone to get it, without coming over and having a look at the whole project! - The MySQL connector was a real bummer. Anyway, we all know for next time if the someone else comes across the same problem ...


Gglienna

I am currently going through all the code and declaring all the variables but it's a massive project!!

[anotherVBnewbie]

Hmmm...I couldn't resist...seems to me that there must be something else acting up or you wouldn't be taking the time to go off on "a wild goose chase" and declaring all your variables in this massive project...lol

For everyone elses future reference..all of the ocx's I've used (mswinsock, msinet, etc) have problems with memory leaks - they're ease of use comes with a price. MsWinsck will kill an old, slow, low ram pc after a few hours even if the app isn't in continual use.

[dglienna]

Guys

Gglienna

I think that's a typo, but if it's intentional, there is a G Glienna also.
May I also suggest that you use MZTools, and Option Explicit

[Pobo]

Have been using MZTools for about six months - Carlos is a genius!

Love the Tab Assistant ...