Urgent Memory Leak Problem Using Type Dataset. Please help! :-(

[Simon]

Hi all,

I'm having a baffling problem with a windows service that I'm working on.

Basically, I am using a typed dataset to insert a large number of rows
into an SQL Server 2005 database. But there's a memory leak that seems
to be to do with calling the data adapters update method. It's making
the memory usage go through the roof and ultimately the service crashes
after running out of memory.

I've used ".net memory profiler" to analyse the service. It tells me
that there are huge numbers of undisposed
@List<SQLParamt er>.Enumeration @ objects. I'm guessing this is a bad thing.

I don't know what to do about it though. As far as I know, the dataset
table adapter should be cleaning up after itself once the method ends.

In case it helps, a sample of my code would

.... Start a foreach loop

currentRow = tblActiveMessag es.NewActiveMes sagesRow();

currentRow.Down loadID = currentMessage. ID;
currentRow.TTUR eference = currentMessage. VehicleID;

.... Assign more properties

tblActiveMessag es.Rows.Add(cur rentRow);

.... For loop ends

da.Update(tblAc tiveMessages);

I've tried putting a using statement around both the data table and the
table adapter, but it had absolutely no effect.

Can anyone advise me on what I can do to get rid of these little buggers!

Sincerest thanks to anyone who can help - even a little bit! :-(

Simon

[Wiebe Tijsma]

Hi,

If you're using Visual Studio 2005 and inserting many records, have a
look at SqlBulkCopy.

Otherwise you could try submitting your data in batches.

Regards,

Wiebe Tijsma

HSimon schreef:

Hi all,

I'm having a baffling problem with a windows service that I'm working on.

Basically, I am using a typed dataset to insert a large number of rows
into an SQL Server 2005 database. But there's a memory leak that seems
to be to do with calling the data adapters update method. It's making
the memory usage go through the roof and ultimately the service crashes
after running out of memory.

I've used ".net memory profiler" to analyse the service. It tells me
that there are huge numbers of undisposed
@List<SQLParamt er>.Enumeration @ objects. I'm guessing this is a bad thing.

I don't know what to do about it though. As far as I know, the dataset
table adapter should be cleaning up after itself once the method ends.

In case it helps, a sample of my code would

... Start a foreach loop

currentRow = tblActiveMessag es.NewActiveMes sagesRow();

currentRow.Down loadID = currentMessage. ID;
currentRow.TTUR eference = currentMessage. VehicleID;

... Assign more properties

tblActiveMessag es.Rows.Add(cur rentRow);

... For loop ends

da.Update(tblAc tiveMessages);

I've tried putting a using statement around both the data table and the
table adapter, but it had absolutely no effect.

Can anyone advise me on what I can do to get rid of these little buggers!

Sincerest thanks to anyone who can help - even a little bit! :-(

Simon

[Laura T.]

You are doing da.AcceptChange s() after the da.Update()?

Sometimes, within the COM interop, the framework is not calling finalizers
fast enough and could result OOM errors.
If everything else fails, you might check if, after da.Update() the
following calls make it work better:

GC.Collect();
GC.WaitForPendi ngFinalizers();
GC.Collect();

This for dev code just to check if the problem is there..

"Simon" <si***@nothanks .comha scritto nel messaggio
news:ea******** ******@TK2MSFTN GP05.phx.gbl...

Hi all,

I'm having a baffling problem with a windows service that I'm working on.

Basically, I am using a typed dataset to insert a large number of rows
into an SQL Server 2005 database. But there's a memory leak that seems to
be to do with calling the data adapters update method. It's making the
memory usage go through the roof and ultimately the service crashes after
running out of memory.

I've used ".net memory profiler" to analyse the service. It tells me that
there are huge numbers of undisposed @List<SQLParamt er>.Enumeration @
objects. I'm guessing this is a bad thing.

I don't know what to do about it though. As far as I know, the dataset
table adapter should be cleaning up after itself once the method ends.

In case it helps, a sample of my code would

... Start a foreach loop

currentRow = tblActiveMessag es.NewActiveMes sagesRow();

currentRow.Down loadID = currentMessage. ID;
currentRow.TTUR eference = currentMessage. VehicleID;

... Assign more properties

tblActiveMessag es.Rows.Add(cur rentRow);

... For loop ends

da.Update(tblAc tiveMessages);

I've tried putting a using statement around both the data table and the
table adapter, but it had absolutely no effect.

Can anyone advise me on what I can do to get rid of these little buggers!

Sincerest thanks to anyone who can help - even a little bit! :-(

Simon

[glenn]

Simon

I agree with another poster, use some kind of bulk insert. In Oracle we get
28 times the performance of an adapter sourced update and I guess you'd get
something similiar in SQL Server But if you can't you may be able to
squeeze a bit more performance out of it by changing the UpdateBatchSize
property of the DataAdapter.

Glenn

"Simon" <si***@nothanks .comwrote in message
news:ea******** ******@TK2MSFTN GP05.phx.gbl...

Hi all,

I'm having a baffling problem with a windows service that I'm working on.

Basically, I am using a typed dataset to insert a large number of rows
into an SQL Server 2005 database. But there's a memory leak that seems to
be to do with calling the data adapters update method. It's making the
memory usage go through the roof and ultimately the service crashes after
running out of memory.

I've used ".net memory profiler" to analyse the service. It tells me that
there are huge numbers of undisposed @List<SQLParamt er>.Enumeration @
objects. I'm guessing this is a bad thing.

I don't know what to do about it though. As far as I know, the dataset
table adapter should be cleaning up after itself once the method ends.

In case it helps, a sample of my code would

... Start a foreach loop

currentRow = tblActiveMessag es.NewActiveMes sagesRow();

currentRow.Down loadID = currentMessage. ID;
currentRow.TTUR eference = currentMessage. VehicleID;

... Assign more properties

tblActiveMessag es.Rows.Add(cur rentRow);

... For loop ends

da.Update(tblAc tiveMessages);

I've tried putting a using statement around both the data table and the
table adapter, but it had absolutely no effect.

Can anyone advise me on what I can do to get rid of these little buggers!

Sincerest thanks to anyone who can help - even a little bit! :-(

Simon

[Cor Ligthert [MVP]]

Simon,

Be aware that as long that there is a active reference to, or from your
object, you can call dispose or whathever a million time, it wont work.

Have a look if there is not a reference to your object that you did not
disable during the process.

Cor

"Simon" <si***@nothanks .comschreef in bericht
news:ea******** ******@TK2MSFTN GP05.phx.gbl...

Hi all,

I'm having a baffling problem with a windows service that I'm working on.

Basically, I am using a typed dataset to insert a large number of rows
into an SQL Server 2005 database. But there's a memory leak that seems to
be to do with calling the data adapters update method. It's making the
memory usage go through the roof and ultimately the service crashes after
running out of memory.

I've used ".net memory profiler" to analyse the service. It tells me that
there are huge numbers of undisposed @List<SQLParamt er>.Enumeration @
objects. I'm guessing this is a bad thing.

I don't know what to do about it though. As far as I know, the dataset
table adapter should be cleaning up after itself once the method ends.

In case it helps, a sample of my code would

... Start a foreach loop

currentRow = tblActiveMessag es.NewActiveMes sagesRow();

currentRow.Down loadID = currentMessage. ID;
currentRow.TTUR eference = currentMessage. VehicleID;

... Assign more properties

tblActiveMessag es.Rows.Add(cur rentRow);

... For loop ends

da.Update(tblAc tiveMessages);

I've tried putting a using statement around both the data table and the
table adapter, but it had absolutely no effect.

Can anyone advise me on what I can do to get rid of these little buggers!

Sincerest thanks to anyone who can help - even a little bit! :-(

Simon

[Simon]

Hi All,

Thanks so much for your advice!

If I'm going to do the batch update thing, I think I might have to stop
using typed datasets.... It would seem that I'm using a (strongly typed)
"TableAdapt er" and not a "DataAdapte r" and the table adapter doesnt seem
to have the batch update property...

Am I missing something? Is it possible to do batch updates with a table
adapter as opposed to a data adapter. Alternatively, is it possible to
use my nice strongly typed data set stuff with weakly type data
adapters? Is that even a good idea?

Thanks

Simon

[Cor Ligthert [MVP]]

Simon,

In my idea not a bad idea at all.

Cor

"Simon" <si***@nothanks .comschreef in bericht
news:uN******** ******@TK2MSFTN GP05.phx.gbl...

Hi All,

Thanks so much for your advice!

If I'm going to do the batch update thing, I think I might have to stop
using typed datasets.... It would seem that I'm using a (strongly typed)
"TableAdapt er" and not a "DataAdapte r" and the table adapter doesnt seem
to have the batch update property...

Am I missing something? Is it possible to do batch updates with a table
adapter as opposed to a data adapter. Alternatively, is it possible to use
my nice strongly typed data set stuff with weakly type data adapters? Is
that even a good idea?

Thanks

Simon

[PFC Sadr]

dude write it to a text file and BULK INSERT

I mean; are you a NooB or something?

nobody should ever use ADO.net to write records in a database; I
mean-- get real

On Mar 11, 9:43 pm, Simon <s...@nothanks. comwrote:

Hi all,

I'm having a baffling problem with a windows service that I'm working on.

Basically, I am using a typed dataset to insert a large number of rows
into an SQL Server 2005 database. But there's a memory leak that seems
to be to do with calling the data adapters update method. It's making
the memory usage go through the roof and ultimately the service crashes
after running out of memory.

I've used ".net memory profiler" to analyse the service. It tells me
that there are huge numbers of undisposed
@List<SQLParamt er>.Enumeration @ objects. I'm guessing this is a bad thing.

I don't know what to do about it though. As far as I know, the dataset
table adapter should be cleaning up after itself once the method ends.

In case it helps, a sample of my code would

... Start a foreach loop

currentRow = tblActiveMessag es.NewActiveMes sagesRow();

currentRow.Down loadID = currentMessage. ID;
currentRow.TTUR eference = currentMessage. VehicleID;

... Assign more properties

tblActiveMessag es.Rows.Add(cur rentRow);

... For loop ends

da.Update(tblAc tiveMessages);

I've tried putting a using statement around both the data table and the
table adapter, but it had absolutely no effect.

Can anyone advise me on what I can do to get rid of these little buggers!

Sincerest thanks to anyone who can help - even a little bit! :-(

Simon

[RobinS]

Here's a way to create your own typed datasets, without the table adapter,
from a query (or stored procedure) against a database.

I think you can probably figure out a way to use these with a
SQLDataAdapter and SQLCommand object. You will need to create your own
SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand objects.

This creates the xsd files on my E:\ drive. After you do that, you can just
add them to your project as an existing item.

Public Sub TestXMLToXSD()
Dim cn As SqlConnection = _
New SqlConnection(M y.Settings.Nort hwindConnection String)
cn.Open()
Dim SQLString As String = _
"SELECT CustomerID, CompanyName FROM Customers;" & _
"SELECT OrderID, CustomerID, OrderDate FROM Orders"

Dim da As SqlDataAdapter = New SqlDataAdapter( SQLString, cn)
da.TableMapping s.Add("Table", "Customers" )
da.TableMapping s.Add("Table1", "Orders")
Dim ds As DataSet = New DataSet("NWData Set")
da.FillSchema(d s, SchemaType.Mapp ed)
ds.Relations.Ad d("Customers_Or ders", _
ds.Tables("Cust omers").Columns ("CustomerID "), _
ds.Tables("Orde rs").Columns("C ustomerID"))
ds.WriteXmlSche ma("E:\NWDataSe t.XSD")

Dim da2 As SqlDataAdapter = _
New SqlDataAdapter( "SELECT * FROM Customers", cn)
da2.TableMappin gs.Add("Table", "Customers" )
Dim ds2 As DataSet = New DataSet("NWCust omerDataSet")
da2.FillSchema( ds2, SchemaType.Mapp ed)
ds2.WriteXmlSch ema("E:\NWCusto merDataSet.XSD" )
cn.Close()

'create the vb files
Process.Start(" C:\Program Files\Microsoft Visual Studio 8" & _
"\SDK\v2.0\Bin\ XSD.exe", _
" E:\NWCustomerDa taSet.XSD /d /l:VB /out:e:\")
Process.Start(" C:\Program Files\Microsoft Visual Studio 8" & _
"\SDK\v2.0\Bin\ XSD.exe", _
" E:\NWDataSet.XS D /d /l:VB /out:e:\ ")
End Sub

Hope this helps.
Robin S.
----------------------------------
"Simon" <si***@nothanks .comwrote in message
news:uN******** ******@TK2MSFTN GP05.phx.gbl...

Hi All,

Thanks so much for your advice!

If I'm going to do the batch update thing, I think I might have to stop
using typed datasets.... It would seem that I'm using a (strongly typed)
"TableAdapt er" and not a "DataAdapte r" and the table adapter doesnt seem
to have the batch update property...

Am I missing something? Is it possible to do batch updates with a table
adapter as opposed to a data adapter. Alternatively, is it possible to
use my nice strongly typed data set stuff with weakly type data adapters?
Is that even a good idea?

Thanks

Simon

[Simon Harvey]

Hi Guys,

Just wanted to say thanks to everyone who has helped with my little
poblem - you've been superb.

Thanks again

Simon