Dataset, Datatable,Dataview,Datagrid - release memory

I am loading a weeks worth of web logs into a dataset using Imports Microsoft.Data.Odbc
These are text - fixed length fields so I have written a schema for them. The adapter fill looks like this
Dim dt As New DataTable()
Dim cnString As String
Dim adapter As New OdbcDataAdapter()
Dim qs1 As String = "Select * from dl" 'first part of query

....
....
....

While ReadDate < DTEnd 'make query and fill adapter for each selected period
query = Format(ReadDate, "yyyyMMdd") + "#txt" 'construct query
nqs1 = Replace(qs1, "*", "'" + DepName + "' as TName,*") 'add department name to record
query = nqs1 + query 'add first part to query
Try
adapter.SelectCommand = New OdbcCommand(query, conn)
adapter.Fill(ds, TableName) 'get records from logs
Catch ex As Exception
EMsg += query + vbCrLf + cnString + vbCrLf + ex.ToString + vbCrLf + vbCrLf
MsgBox(EMsg)
End Try
ReadDate = DateAdd(DateInterval.Day, 1, ReadDate) 'inc process date
frmMon.Label6.Text = ds.Tables(TableName).Rows.Count.ToString
frmMon.Label6.Refresh()
End While

I then set the table to a view and then bind to a datagrid
dv = ds.Tables(0).DefaultView
frmDG.DataGrid1.DataSource = dv

This all works very well --- until I call this sub again

If I change the date values and run this process again, the app hangs trying to execute the adapter.fill

I modified the app so that a new table "TableName" is sent each time I run the process and this works well ---- but

The memory utilization just keeps increasing until I stop the app. (approx 20Mb per pass.

So I have tried some of the following

ds.Tables.Remove(tn)
ds.Tables(tn).Dispose()
Me.ds.Tables(0).Rows.Clear()
Me.ds.Clear()
Me.dt.Rows.Clear()
Me.dt.Clear()

I found that if I donnot attach the dv to a datagid then I could reload the dataset without changing the name of the datatable

Anyone have ideas on how to release the memory being used to hold the data from the last adapter.fill?

re: Dataset, Datatable,Dataview,Datagrid - release memory

Hi Slaprade,

Before I answer go to deep in your question, when there is a process as you
show it is normal that the memory increases. It will be freeed when the
Garbage Collector GC starts. However because this is a process withouth that
the control is given to something else, this can take something longer.

The GC starts when there is time or that there is memory needed. Therefore
my question is first, what is the problem with using available memory?

Cor

[color=blue]
> I am loading a weeks worth of web logs into a dataset using Imports[/color]
Microsoft.Data.Odbc[color=blue]
> These are text - fixed length fields so I have written a schema for them.[/color]
The adapter fill looks like this[color=blue]
> Dim dt As New DataTable()
> Dim cnString As String
> Dim adapter As New OdbcDataAdapter()
> Dim qs1 As String = "Select * from dl" 'first part of query
>
> ...
> ...
> ...
>
> While ReadDate < DTEnd 'make query and fill adapter for[/color]
each selected period[color=blue]
> query = Format(ReadDate, "yyyyMMdd") + "#txt"[/color]
'construct query[color=blue]
> nqs1 = Replace(qs1, "*", "'" + DepName + "' as[/color]
TName,*") 'add department name to record[color=blue]
> query = nqs1 + query 'add first part to query
> Try
> adapter.SelectCommand = New OdbcCommand(query,[/color]
conn)[color=blue]
> adapter.Fill(ds, TableName) 'get records from logs
> Catch ex As Exception
> EMsg += query + vbCrLf + cnString + vbCrLf +[/color]
ex.ToString + vbCrLf + vbCrLf[color=blue]
> MsgBox(EMsg)
> End Try
> ReadDate = DateAdd(DateInterval.Day, 1, ReadDate) 'inc[/color]
process date[color=blue]
> frmMon.Label6.Text =[/color]
ds.Tables(TableName).Rows.Count.ToString[color=blue]
> frmMon.Label6.Refresh()
> End While
>
> I then set the table to a view and then bind to a datagrid
> dv = ds.Tables(0).DefaultView
> frmDG.DataGrid1.DataSource = dv
>
> This all works very well --- until I call this sub again
>
> If I change the date values and run this process again, the app hangs[/color]
trying to execute the adapter.fill[color=blue]
>
> I modified the app so that a new table "TableName" is sent each time I run[/color]
the process and this works well ---- but[color=blue]
>
> The memory utilization just keeps increasing until I stop the app. (approx[/color]
20Mb per pass.[color=blue]
>
> So I have tried some of the following
>
> ds.Tables.Remove(tn)
> ds.Tables(tn).Dispose()
> Me.ds.Tables(0).Rows.Clear()
> Me.ds.Clear()
> Me.dt.Rows.Clear()
> Me.dt.Clear()
>
> I found that if I donnot attach the dv to a datagid then I could reload[/color]
the dataset without changing the name of the datatable[color=blue]
>
> Anyone have ideas on how to release the memory being used to hold the data[/color]
from the last adapter.fill?[color=blue]
>[/color]

re: Dataset, Datatable,Dataview,Datagrid - release memory

Well - as long as memory is available there is no big problem - After 6 iterations through this routine (6 weeks of data) I read approx 500Mb of memory in use.. I am concerned that if I had to run this app for a years worth of weeks that a significant amount of time would be spent in disk writes

But more to the point - I quess I just like to keep my apps as trim and fast as possible - More a Felix Unger thing

"Cor Ligthert" wrote:
[color=blue]
> Hi Slaprade,
>
> Before I answer go to deep in your question, when there is a process as you
> show it is normal that the memory increases. It will be freeed when the
> Garbage Collector GC starts. However because this is a process withouth that
> the control is given to something else, this can take something longer.
>
> The GC starts when there is time or that there is memory needed. Therefore
> my question is first, what is the problem with using available memory?
>
> Cor
>
>[color=green]
> > I am loading a weeks worth of web logs into a dataset using Imports[/color]
> Microsoft.Data.Odbc[color=green]
> > These are text - fixed length fields so I have written a schema for them.[/color]
> The adapter fill looks like this[color=green]
> > Dim dt As New DataTable()
> > Dim cnString As String
> > Dim adapter As New OdbcDataAdapter()
> > Dim qs1 As String = "Select * from dl" 'first part of query
> >
> > ...
> > ...
> > ...
> >
> > While ReadDate < DTEnd 'make query and fill adapter for[/color]
> each selected period[color=green]
> > query = Format(ReadDate, "yyyyMMdd") + "#txt"[/color]
> 'construct query[color=green]
> > nqs1 = Replace(qs1, "*", "'" + DepName + "' as[/color]
> TName,*") 'add department name to record[color=green]
> > query = nqs1 + query 'add first part to query
> > Try
> > adapter.SelectCommand = New OdbcCommand(query,[/color]
> conn)[color=green]
> > adapter.Fill(ds, TableName) 'get records from logs
> > Catch ex As Exception
> > EMsg += query + vbCrLf + cnString + vbCrLf +[/color]
> ex.ToString + vbCrLf + vbCrLf[color=green]
> > MsgBox(EMsg)
> > End Try
> > ReadDate = DateAdd(DateInterval.Day, 1, ReadDate) 'inc[/color]
> process date[color=green]
> > frmMon.Label6.Text =[/color]
> ds.Tables(TableName).Rows.Count.ToString[color=green]
> > frmMon.Label6.Refresh()
> > End While
> >
> > I then set the table to a view and then bind to a datagrid
> > dv = ds.Tables(0).DefaultView
> > frmDG.DataGrid1.DataSource = dv
> >
> > This all works very well --- until I call this sub again
> >
> > If I change the date values and run this process again, the app hangs[/color]
> trying to execute the adapter.fill[color=green]
> >
> > I modified the app so that a new table "TableName" is sent each time I run[/color]
> the process and this works well ---- but[color=green]
> >
> > The memory utilization just keeps increasing until I stop the app. (approx[/color]
> 20Mb per pass.[color=green]
> >
> > So I have tried some of the following
> >
> > ds.Tables.Remove(tn)
> > ds.Tables(tn).Dispose()
> > Me.ds.Tables(0).Rows.Clear()
> > Me.ds.Clear()
> > Me.dt.Rows.Clear()
> > Me.dt.Clear()
> >
> > I found that if I donnot attach the dv to a datagid then I could reload[/color]
> the dataset without changing the name of the datatable[color=green]
> >
> > Anyone have ideas on how to release the memory being used to hold the data[/color]
> from the last adapter.fill?[color=green]
> >[/color]
>
>
>[/color]

re: Dataset, Datatable,Dataview,Datagrid - release memory

>[color=blue]
> But more to the point - I quess I just like to keep my apps as trim and[/color]
fast as possible - More a Felix Unger thing[color=blue]
>[/color]
Exactly and therefore it should not be that there is useless cleaning up of
memory in processing time, better is to do it when the computer is in idle
state or when it is really needed, what I pointed you on in my previous
message how it is done by managed code what is a big benefit of dotNet.

However my idea, when you not agree feel free for that.

Cor

re: Dataset, Datatable,Dataview,Datagrid - release memory

Two points here:
1. Which version MDAC you have? Version 2.7 has some bugs, so Fill() hanged (with ODBCAdapter). It was repaired in ver 2.7 sp1 or sp2. Current version is 2.8.
2. Try to encapsulate code so that you create and use objects (as local variables) in subroutine and dont use them outside this subroutine. If subroutine ends and no objects are assigned to outside objects/variables then garbage collector clears them. But if subroutine gives some ponters outside (eg for form object) then try sometimes set them null (or none in VB).
Sorry, if I talked about wrong thing -- I dont see whole code here ;)

Indrek

"slaprade" wrote:
[color=blue]
> I am loading a weeks worth of web logs into a dataset using Imports Microsoft.Data.Odbc
> These are text - fixed length fields so I have written a schema for them. The adapter fill looks like this
> Dim dt As New DataTable()
> Dim cnString As String
> Dim adapter As New OdbcDataAdapter()
> Dim qs1 As String = "Select * from dl" 'first part of query
>
> ...
> ...
> ...
>
> While ReadDate < DTEnd 'make query and fill adapter for each selected period
> query = Format(ReadDate, "yyyyMMdd") + "#txt" 'construct query
> nqs1 = Replace(qs1, "*", "'" + DepName + "' as TName,*") 'add department name to record
> query = nqs1 + query 'add first part to query
> Try
> adapter.SelectCommand = New OdbcCommand(query, conn)
> adapter.Fill(ds, TableName) 'get records from logs
> Catch ex As Exception
> EMsg += query + vbCrLf + cnString + vbCrLf + ex.ToString + vbCrLf + vbCrLf
> MsgBox(EMsg)
> End Try
> ReadDate = DateAdd(DateInterval.Day, 1, ReadDate) 'inc process date
> frmMon.Label6.Text = ds.Tables(TableName).Rows.Count.ToString
> frmMon.Label6.Refresh()
> End While
>
> I then set the table to a view and then bind to a datagrid
> dv = ds.Tables(0).DefaultView
> frmDG.DataGrid1.DataSource = dv
>
> This all works very well --- until I call this sub again
>
> If I change the date values and run this process again, the app hangs trying to execute the adapter.fill
>
> I modified the app so that a new table "TableName" is sent each time I run the process and this works well ---- but
>
> The memory utilization just keeps increasing until I stop the app. (approx 20Mb per pass.
>
> So I have tried some of the following
>
> ds.Tables.Remove(tn)
> ds.Tables(tn).Dispose()
> Me.ds.Tables(0).Rows.Clear()
> Me.ds.Clear()
> Me.dt.Rows.Clear()
> Me.dt.Clear()
>
> I found that if I donnot attach the dv to a datagid then I could reload the dataset without changing the name of the datatable
>
> Anyone have ideas on how to release the memory being used to hold the data from the last adapter.fill?
>[/color]