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<SQLParamter>.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 = tblActiveMessages.NewActiveMessagesRow();

currentRow.DownloadID = currentMessage.ID;
currentRow.TTUReference = currentMessage.VehicleID;

.... Assign more properties

tblActiveMessages.Rows.Add(currentRow);

.... For loop ends

da.Update(tblActiveMessages);

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:

 

[Laura T.]

You are doing da.AcceptChanges() 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.WaitForPendingFinalizers();
GC.Collect();

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

 

[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

 

[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]

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)
"TableAdapter" and not a "DataAdapter" 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

 

[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

 

[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(My.Settings.NorthwindConnectionString)
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.TableMappings.Add("Table", "Customers")
da.TableMappings.Add("Table1", "Orders")
Dim ds As DataSet = New DataSet("NWDataSet")
da.FillSchema(ds, SchemaType.Mapped)
ds.Relations.Add("Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
ds.WriteXmlSchema("E:\NWDataSet.XSD")

Dim da2 As SqlDataAdapter = _
New SqlDataAdapter("SELECT * FROM Customers", cn)
da2.TableMappings.Add("Table", "Customers")
Dim ds2 As DataSet = New DataSet("NWCustomerDataSet")
da2.FillSchema(ds2, SchemaType.Mapped)
ds2.WriteXmlSchema("E:\NWCustomerDataSet.XSD")
cn.Close()

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

Hope this helps.
Robin S.

 

[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

 

[sloan]

Here is a way to keep strong typed datasets.
And get "bulk performance".


http://support.microsoft.com/kb/315968


Its a little raw, but the idea is there.

Pass in your DataSet.GetXml() as a [text] variable into sql server.

I've handled up to 4 meg files. (I'm not saying htis is the limit, I'm
saying this is what I've done it up to)


http://groups.google.com/group/micr...nk=st&q=OPENXML+sloan&rnum=3#c80893522ad8c365

see that post also.

 

[William \(Bill\) Vaughn]

Yes, you can expose a DataReader (in ADO.NET 2.0) from a DataTable which can
be passed to SqlBulkCopy to upload (very quickly) to SQL Server.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Here is a way to keep strong typed datasets.
And get "bulk performance".


http://support.microsoft.com/kb/315968


Its a little raw, but the idea is there.

Pass in your DataSet.GetXml() as a [text] variable into sql server.

I've handled up to 4 meg files. (I'm not saying htis is the limit, I'm
saying this is what I've done it up to)


http://groups.google.com/group/micr...nk=st&q=OPENXML+sloan&rnum=3#c80893522ad8c365

see that post also.

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<SQLParamter>.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 = tblActiveMessages.NewActiveMessagesRow();

currentRow.DownloadID = currentMessage.ID;
currentRow.TTUReference = currentMessage.VehicleID;

... Assign more properties

tblActiveMessages.Rows.Add(currentRow);

... For loop ends

da.Update(tblActiveMessages);

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

 

[Guest]

You can always extend your typed dataset and have a batch update support like
this:

http://msdn2.microsoft.com/en-us/library/kbbwt18a(vs.80).aspx

in your typed dataset, Here is one example but for select statement:

partial class ProductsTableAdapter
{
public NWTDS.ProductsDataTable GetDynamicProducts(string whereClause)
{
this.Adapter.SelectCommand = new
System.Data.SqlClient.SqlCommand("SELECT * FROM Products WHERE " +
whereClause , this.Connection);
NWTDS.ProductsDataTable pdt = new NWTDS.ProductsDataTable();
Adapter.Fill(pdt);
return (pdt);

}

}