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

12 Mar 2007 4:43 AM

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

12 Mar 2007 9:41 AM

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:
Show quote

> 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

12 Mar 2007 9:56 AM

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..

Show quote

"Simon" <si***@nothanks.com> ha scritto nel messaggio
news:ea0QCEGZHHA.4008@TK2MSFTNGP05.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<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

12 Mar 2007 10:31 AM

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

Show quote

"Simon" <si***@nothanks.com> wrote in message
news:ea0QCEGZHHA.4008@TK2MSFTNGP05.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<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

13 Mar 2007 5:41 AM

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

Show quote

"Simon" <si***@nothanks.com> schreef in bericht
news:ea0QCEGZHHA.4008@TK2MSFTNGP05.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<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

13 Mar 2007 9:27 AM

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

13 Mar 2007 6:37 PM

Cor Ligthert [MVP]

Simon,

In my idea not a bad idea at all.

Cor

Show quote

"Simon" <si***@nothanks.com> schreef in bericht
news:uNghmHVZHHA.5044@TK2MSFTNGP05.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)
> "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

14 Mar 2007 12:25 AM

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.
----------------------------------
Show quote

"Simon" <si***@nothanks.com> wrote in message
news:uNghmHVZHHA.5044@TK2MSFTNGP05.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)
> "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

13 Mar 2007 8:17 PM

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.com> wrote:
Show quote

> 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

15 Mar 2007 9:59 AM

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

16 Mar 2007 6:54 PM

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/microsoft.public.sqlserver.programming/browse_frm/thread/e06936618e2c1077/c80893522ad8c365?lnk=st&q=OPENXML+sloan&rnum=3#c80893522ad8c365

see that post also.

Show quote

"Simon" <si***@nothanks.com> wrote in message
news:ea0QCEGZHHA.4008@TK2MSFTNGP05.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<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

17 Mar 2007 12:49 AM

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)
-----------------------------------------------------------------------------------------------------------------------

Show quote

"sloan" <sl***@ipass.net> wrote in message
news:%23DomAy$ZHHA.4668@TK2MSFTNGP04.phx.gbl...
>
>
> 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/microsoft.public.sqlserver.programming/browse_frm/thread/e06936618e2c1077/c80893522ad8c365?lnk=st&q=OPENXML+sloan&rnum=3#c80893522ad8c365
>
> see that post also.
>
>
>
>
> "Simon" <si***@nothanks.com> wrote in message
> news:ea0QCEGZHHA.4008@TK2MSFTNGP05.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<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
>
>

9 Apr 2007 3:26 PM

Reza Alirezaei

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);

        }

    }

--
Reza Alirezaei /Senior IT Consultant
Blog: http://blogs.devhorizon.com/reza

Show quote

"Simon" wrote:

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