473,322 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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?

Nov 20 '05 #1
4 4294
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

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?

Nov 20 '05 #2
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:
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

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?


Nov 20 '05 #3
>
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

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
Nov 20 '05 #4
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:
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?

Nov 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Dave | last post by:
I tried posting this in the WinForm forum and got no hits so I am trying it here. After inserting a new data row to a DataTable that is bound to a datagrid, I am unable to change data in a row...
6
by: Hardy Wang | last post by:
Hi, I have a DataSet, returned by a stored procudure, which has only on DataTable. At this step there is no sort in DataTable. Is there is a way I can do to sort DataRows in this DataTable (not...
1
by: Arpan | last post by:
The following code snippet creates a DataSet on the fly which is then passed to a DataView so that the DataSet can finally be viewed in a DataGrid control: 'Create the empty DataSet Dim objDS As...
0
by: Bob Davies | last post by:
Hi I have a webservice that retrieves data from a database, this is then returned to the calling client application built in windows forms within a dataset, however upon attempting to create...
3
by: Datatable Dataset Datagrid help | last post by:
Hi I am somewhat confused, I am new at VB.net I use XML data, I have a datagrid, I created a datatable so that I can create a custom format like true is this graphic false is this graphic and...
17
by: A_PK | last post by:
I have problem databinding the DataGrid with DataView/DataSet after the filter... I create the following proceudre in order for user to filter as many as they want, but the following code is only...
0
by: Nathan Franklin | last post by:
Hello Guys, I have been trying to work this our for so long, but I just can't seem to find the answer. I am loading a datatable from a an access database using an oledbdataadapter. I then...
7
by: =?Utf-8?B?QWxla3MgS2xleW4=?= | last post by:
I use in my asp.net code dataset and populate datatable using dataadapter. The problem is that my code demand huge amount of memory and I am looking for way reduce this demand. At this time I...
12
by: Brian | last post by:
I want to create an In Memory dataset. not connected to any database.. but putting my own info in from code or a file.... What are the steps to do this? Where can I find some info on how to do...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.