Printable View
:) 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
Always destroy you objects is probably the #1 suggestion.
Post some sample code so we can make more relevant suggestions.
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'?
Could the fact that I don't declare most variables or use Option Explicit cause this?
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.
ALWAYS use Option Explicit. You are only asking for problems if you don't.Quote:
Originally Posted by Pobo
Pobo,
Too much code to post? Then upload your project.
Randem
Why do you want the whole project?
He doesn't. You want someone to have it if you expect anyone to be able to help you find the memory leak
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.
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...
note: most... not all. You should always Set objects to Nothing after you have finished with them anyway, just to be sure.Quote:
Originally Posted by Pradeep1210
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 :sick:
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
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
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.
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
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.
Excuse the line numbers they're for the error handlers!
Code: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
Code: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
‘
Code: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
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.
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.
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.Quote:
Originally Posted by 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
Quote:
Originally Posted by Pobo
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.
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.
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.
IMHO, Pobo you should be humble and thankful that people here in VBForums is trying to help you.... What you did was truly ungrateful....
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!!
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.
Quote:
Originally Posted by Pobo
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
Have been using MZTools for about six months - Carlos is a genius!
Love the Tab Assistant ...