I Have a problem

I Build the next code with vb6 SP5 and SQL 7 SP4 Windows 2000 SP2 MDAC 2.7 refresh.
The timer interval is 500 ms (.5sec)

Sub Timer1_Timer
Dim Rs As Recordset
'---
Set Rs = New Recordset
Rs.CursorLocation = adUseClient
Rs.LockType = adLockPessimistic
Rs.CursorType = adOpenKeyset
Rs.ActiveConnection = m_cnn 'Some connection open
Rs.Source = SQL 'some query
Rs.Open
Set Rs.ActiveConnection = Nothing
Rs.Close
Set Rs = Nothing
End Sub

After minutes hours or day (relationship with the type of query) this code begin use resource in memory that increase until the computer crash!!!

¿my code have errors?
I Think no.

are there any dll or component to update?

You can have this bug and not know about this because you shut down you computer day to day, and the memory is free in the shut down.


I try code with open and close the connection and same result.
What I can do?


Thanks
Gustavo

Depending on the specifications of your system (Motherboard, RAM type/amout, FSB, CPU speed/type, etc), you are going to get different results.

But, my question is this: why do you need to open two recordsets per second?

Change your LockType to adLockBatchOptimistic, and your
CursorType to adOpenStatic. No guarantees with a .5 second
refresh because you are causing the VB process' memory to
become very fragmented. A solution that might make more sense
would be to also have a recordset object at module level or
higher scope and not repeatedly creating and destroying
recordset objects.

Rezner: you think in one rs open and only do requery for example without close? but why open and close fault?
Thanks

Thinker:

OK. I Try your idea.
Thanks

I try with requery but this useless because the sql ever change and with the recordset open the source property is read only.

I try with the rs declared at module level an only i do open and close in the recordset with this properties
locktype=adlockbatchoptimistic
cursortype=adopenstatic

but this too leak memory!!!!

The only way that I get successful is with store procedure, but I want avoid the for now because this means big change in my system.

Exist any form get successful with the recordset?

Please helpme and very thanks.

Looking back at your code, I see this line...
Rs.ActiveConnection = m_cnn 'Some connection open
This concerns me very much. What is m_cnn? If it is a connection
object, the line should be...
Set Rs.ActiveConnection = m_cnn 'Some connection open
The way you have it, I am concerned it is reopening a new
connection for each time the recordset is opened. That would
indeed cause the problems you describe.

The code is changing to the next
'=======================
Begin code
Private m_cnn as connection
Private m_rs as recordset

Form_Load
Set m_cnn = New Connection
m_cnn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=PII350;User ID=sa;PassWord=xxxxxx;InitialCatalog=dbtxt;"
m_cnn.Open
m_cnn.DefaultDatabase = "dbtxt"
Set m_Rs = New Recordset
m_Rs.CursorLocation = adUseClient
m_Rs.LockType = adLockPessimistic
m_Rs.CursorType = adOpenKeyset
m_Rs.ActiveConnection = m_cnn
End sub

Timer1_Timer
'---
Dim SQL As String
'---
'---
'---
SQL = ... query variable'---

m_Rs.Source = SQL
m_Rs.Open
'---
'Show information in a grid for example
'---
m_Rs.Close
Set Rs = Nothing
End Sub
End code
'=======================


Read 1
=========
Time Use of memory (task manager)
13.12 132.908 Program init
13.13 137.788
13.14 144.048
13.15 150.000
13.16 155.764


Read 2
=========
Time Use of memory
16.09 174.492 Inicia la ejecución el programa
16.13 181.004
16.14 186.896


Read 3
=========
Time Use of memory
16.17 190.764 Inicia la ejecución el programa
16.18 190.848
16.19 190.864
16.20 190.916
16.21 190.980
16.22 191.020
16.23 191.040


Ideas???


Thanks Gustavo

Do you really need a 1/2 second interval for your Timer? What exactly are you trying to accomplish?

You completely ignored what I said about your connection. I will
try to explain further. When you say m_Rs.ActiveConnection =
m_cnn, you are assigning the default property of the connection
object (which is the ConnectionString) to the ActiveConnection
property. This forces the Recordset object to create and open a
new connection rather than use the existing connection. You are
creating around 120 new connections each minute. If you use
Set m_Rs.ActiveConnection = m_cnn, then you are reusing the
existing connection each time. This has to be at least a large part
of your problem.

Sorry Thinker,
The corrections to my code post the date 01-13-2003 06:49 is only add one set?
Set m_Rs.ActiveConnection = m_cnn ?
Thanks you
Machaira:
This software control a collection of 200 cars, that they move for the city, doing alls more 3000 trip/day. One operator need know without delay the position of each car in each zone for the best distribution...

Thanks for you interest

Yes, use Set.
Are you really trying to display this in a grid and refreshing every
.5 second? What is the code you use to display in the grid? It
could be leaving object references around.

I find it hard to believe that a delay of a second or two is going to make much difference to the operator. Even changing the timer to a 1 second interval might help.

Yes Machaira Perhaps I can set 1 or 2 second

My tests...
In short the last change to my code are:

level module
m_cnn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=PentiumIII500;User ID=sa;PassWord=xxxx;InitialCatalog=dbtxt;"
m_cnn.Open
m_cnn.DefaultDatabase = "dbtxt"
Set m_Rs = New Recordset
m_Rs.CursorLocation = adUseClient
m_Rs.LockType = adLockPessimistic
m_Rs.CursorType = adOpenKeyset
Set m_Rs.ActiveConnection = m_cnn


SUb Timer3_Timer
Dim SQL As String
Timer3.Enabled = False

SQL=... SQL cambiante

m_Rs.Source = SQL
m_Rs.Open SQL
'...hacer algo ej. poblar una grilla...
m_Rs.Close
Set Rs = Nothing
Timer3.Enabled = True
End Sub

I test the code 4 hours 39 minutes and the use of memory fell (down). This is a good news.
The network have 2 pc

Server
======
Pentium II 350
Windows 2000 Server SP2
SQL 7 SP4

Cient
=====
Pentium III 500
Windows 2000 Server SP2
VB6 SP5 MDAC 2.6 SP1


I do the same test but all over Pentium III 500 (client and server) that have installed too SQL 7 SP4.

Surprise!
In 5 or 10 minutes the use of memory increase, increase, increase


real information of today
Hora Recursos
...
16.20 201.944
16.22 204.140
16.23 207.324
16.24 213.340
16.25 220.344
...


I not understand

any idea?


Thanks
Gustavo

So it is dependent on which computer it runs on? It works fine on
the server, but on the client it takes up more and more memory?
What does this comment mean and what code does it represent?
'...hacer algo ej. poblar una grilla...

Yes Thinker, the only solution that I find is use store procedure, I test with store procedure and the use of memory no increase.
But I wanted understand why with RS.Open use resource memory. This problem can be find by others programmers, and no is reported in the Knoledge base, (I can't find it).

Thanks. Gustavo

Try changing the line Set Rs = Nothing in the timer subroutine to:

Set m_Rs = Nothing

This may require moving the following code:

Set m_Rs = New Recordset
m_Rs.CursorLocation = adUseClient
m_Rs.LockType = adLockPessimistic
m_Rs.CursorType = adOpenKeyset
Set m_Rs.ActiveConnection = m_cnn

to the beginning of the timer subroutine.

I not understand why the you change.

Thanks

I think it is because in your previous code updates, you do not have a variable called Rs. Maybe you changed the variable from your first example from Rs to m_Rs. I cannot see why you would have 2 recordsets performing essentially the same function, so I have to assume you meant to rename it. If I am wrong, then I apologize, but renaming mishaps happen all the time.

Thinker : Rook at my iron fists!

OK TiKiMoN, i change Rs by m_Rs, i undersand now.
I do the test and answer you.


Thanks
Gustavo
Ponisio