NOVICE: SQL Query on a Timer, Growing Memory Size

[Joe C.]

hello, thanks for reading.

this topic was probably covered in the past; if so, i apologize for
the repost, and would you kindly redirect me to the topic?

i have small app that monitors the state of a database. this is
achieved by a sql query running in timed increments, tied to the
"tick" event handler of the Timer control. basically, a sql query is
run every five seconds to get a count of items in a table.

the code used:

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick

Dim conn3 As New System.Data.SqlClient.SqlConnection(******)
Dim sqlString3 As String = "select count(recipient) from
board"
Dim adapter3 As New SqlDataAdapter(sqlString3, conn3)
Dim ds3 As New Data.DataSet

adapter3.Fill(ds3)

createTextIcon(CInt(ds3.Tables(0).Rows(0).Item(0))

adapter3.Dispose()
ds3.Clear()

End Sub

this achieves the desired result, but i noticed the memory usage
increases by about 12kb at each iteration. i imagine this is due to
the dataset [ds3] being filled. i thought that clearing the dataset
[ds3.clear()] would alleviate this issue, but it doesn't.

am i even going about this the right way? someone mentioned caching
the query. does anyone have any suggestions?

any help would be greatly appreciated.

-Joe.

 

[Kerry Moorman]

Joe,

You might consider using an SQLCommand object's ExecuteScalar method to
return the count, without using a dataadapter or a dataset.

Kerry Moorman

 

[Guest]

the code used:

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick

Dim conn3 As New System.Data.SqlClient.SqlConnection(******)
Dim sqlString3 As String = "select count(recipient) from
board"
Dim adapter3 As New SqlDataAdapter(sqlString3, conn3)
Dim ds3 As New Data.DataSet

adapter3.Fill(ds3)

createTextIcon(CInt(ds3.Tables(0).Rows(0).Item(0))

adapter3.Dispose()
ds3.Clear()

End Sub

You should use a SQLCommand object and use the "ExecuteScalar" function
to return a single value. More efficient than what you're doing.

this achieves the desired result, but i noticed the memory usage
increases by about 12kb at each iteration. i imagine this is due to
the dataset [ds3] being filled. i thought that clearing the dataset
[ds3.clear()] would alleviate this issue, but it doesn't.

Memory will keep growing until the garbage collector kicks in. How much
memory is it using it? If you let it run for a while, I'm sure memory
will stablize.

 

[Joe C.]

you're absolutely right, it occupies about 4 megs to start, then
stablizes at 7.5 megs or so.

i'll give the sqlcommand a go.

thank you very much! i sincerely do appreciate the help, i'll let you
know how it turns out.

(e-mail address removed):

the code used:

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick

        Dim conn3 As New System.Data.SqlClient.SqlConnection(******)
        Dim sqlString3 As String = "select count(recipient) from
board"
        Dim adapter3 As New SqlDataAdapter(sqlString3, conn3)
        Dim ds3 As New Data.DataSet

        adapter3.Fill(ds3)

        createTextIcon(CInt(ds3.Tables(0).Rows(0).Item(0))

        adapter3.Dispose()
        ds3.Clear()

    End Sub

You should use a SQLCommand object and use the "ExecuteScalar" function
to return a single value. More efficient than what you're doing.

this achieves the desired result, but i noticed the memory usage
increases by about 12kb at each iteration.  i imagine this is due to
the dataset [ds3] being filled.  i thought that clearing the dataset
[ds3.clear()] would alleviate this issue, but it doesn't.

Memory will keep growing until the garbage collector kicks in. How much
memory is it using it? If you let it run for a while, I'm sure memory
will stablize.

 

[SurturZ]

Generally, you should use a "Using" block with any object that has a
".Dispose" method. e.g. for your code:

Using conn3 As New System.Data.SqlClient.SqlConnection
Dim sqlString3 As String = "select count(recipient) from board"
Using adapter3 As New SqlDataAdapter(sqlString3, conn3)
Using ds3 As New Data.DataSet
adapter3.Fill(ds3)
createTextIcon(CInt(ds3.Tables(0).Rows(0).Item(0))
ds3.Clear()
End Using
End Using
End Using

This will help the garbage collector clean them up more quickly.

If you want to keep the objects around (e.g. for performance reasons) then
declare them at the module level, and .Dispose them in the Form.FormClosed
event.

 

[Joe C.]

thank you for the great advice, i've gone ahead and fixed the code as
such:

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick

Dim conn3 As New SqlConnection(******)
Dim sqlStr As String = "select count(recipient) from board"
Dim sqlComm As New SqlCommand(sqlStr, conn3)

conn3.Open()
nuTic = CInt(sqlComm.ExecuteScalar())
conn3.Close()
conn3.Dispose()

createTextIcon(nuTic)

End Sub

the above is yielding the exact same results as before, and i do
totally prefer how lean it is.

however, the memory allocation is still growing, albeit at a smaller
increment (roughly 4kb). having monitored the process through the
task manager, i noticed that once it reaches a certain size, it drops
down, then back up, just seesawing back and forth.

so, as a conclusion, i think the app, as it is, should be ok to
deploy.

thank you for all your help!

- Joe.

(e-mail address removed):

the code used:

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick

        Dim conn3 As New System.Data.SqlClient.SqlConnection(******)
        Dim sqlString3 As String = "select count(recipient) from
board"
        Dim adapter3 As New SqlDataAdapter(sqlString3, conn3)
        Dim ds3 As New Data.DataSet

        adapter3.Fill(ds3)

        createTextIcon(CInt(ds3.Tables(0).Rows(0).Item(0))

        adapter3.Dispose()
        ds3.Clear()

    End Sub

You should use a SQLCommand object and use the "ExecuteScalar" function
to return a single value. More efficient than what you're doing.

this achieves the desired result, but i noticed the memory usage
increases by about 12kb at each iteration.  i imagine this is due to
the dataset [ds3] being filled.  i thought that clearing the dataset
[ds3.clear()] would alleviate this issue, but it doesn't.

Memory will keep growing until the garbage collector kicks in. How much
memory is it using it? If you let it run for a while, I'm sure memory
will stablize.