Big DATASET Problems
At our company we use a Dataset to convert a selection from one of our databases to nested XML. We use about 10 stored procedures to extract the data under the control of a SHAPE command. We feed the SHAPE string to a FILL command that creates the dataset with 10 result datatables and all their datarelations.
This arrangement has worked well for us for over a year with a large amount of data, but as our database has continued to grow, we are running into an OutofMemoryException. This exception is thrown while the machine is still showing almost a gig of free memory (It’s a 3 gig machine). There is also 15 gig of free space on the C: drive.
Is there an upper limit to how much can be stored in a Dataset?
Is it dependent on other factors such as system memory or disk space?
Can you point us in a good direction for research?
Any help is appreciated…
Hi,
I've found a MSDN reference to the fact that an ADO.NET DataTable is limited to 16,777,216 rows of data.
Maybe that is your problem
check it here
Best Regards
I'm making the following assumptions - please correct / add to more closely reflect your sceanrio:
1. one or more DataTables in the vicinity of 100,000 + rows.
2. The DataTables are involved in relationships
3. DataSet.EnforceConstraint == true prior to invocation of Fill
4. .NET v2.0 is being used.
The most likely reasons for "out of memory exceptions" are:
a. After Fill has loaded the data it tries to re-enforce the constraints and in the process throws the exception. To confirm, please set DataSet.EnforceConstraint = false prior to calling Fill.
b. A table is being filled with 500,000 + rows, depending upon hte available memory and number of columns, the DataTable's column-based storage throws this error. The column-based storage grows by doubling and so at any given time, it could occupy 2-times (-1) the number of available rows, another issue, this memory has to be contiguous. One way to mitigate this issue is by setting DataTable.MinimumCapacity to the maximum number of expected rows for the large - table. This way, the contiguous memory allocation size is not left to chance - the system uses this hint and allocates apriori the correct size.
There may be other reasons as well - I think once we know the number of rows, it's column and row-size for the large-table, we can think of other reasons ...
Thanks!
Kawarjit Bedi
Thank you for your knowledgable insights.
1 2 and 4 are definitely true. The enforceconstraints was not set, so the default applies. I tried that immediately, didn't work, so moving on to your other thoughts.
Your reference to ADO 2 is interesting Do you think that ADO 1 handles this more gracefully?
I'm in the process of gathering the size data. Here is what I have so far:
Datatables
XPF - 142397 rows - 36 cols
MCT - 78424 rows - 18 cols
MCT - 164167 rows - 7 cols
NWP - 318162 rows - 9 cols
NPP - 67674 rows - 7 cols
PRD - 138821 rows - 6 cols
PRE - 162080 rows - 7 cols
PRA - 276500 rows - 20 cols
PRR - 274068 rows - 8 cols
PRL - 9272 rows - 3 cols
One wrinkle is that currently the whole thing is being run under SHAPE (I included the statement below) so the datatables don't exist until the fill command is executed.Is there any way that you know of to apply the MinmumCapacity to a datatable on the fly as part of SHAPE?
strPractSql = "SHAPE {exec dbo.xpfXPFPrctReformatSel} as XPFH" _
& " APPEND " _
& " ( {exec dbo.xpfMCTReformatSel} as MCTN " _
& " RELATE LKPSYSGRP to LKPSYSGRPLOC ) ," _
& " ( {exec dbo.xpfMCTReformatSel} as MCTI " _
& " RELATE LKPSYSGRPLOC to LKPSYSGRPLOC )," _
& " ( {exec dbo.xpfNWPReformatSel} as NWPR " _
& " RELATE LKPSYSGRPLOC to LKPSYSGRPLOC )," _
& " ( {exec dbo.xpfPRDReformatSel} as PRDS" _
& " RELATE LKPSYSGRPLOC to LKPSYSGRPLOC)," _
& " ( {exec dbo.xpfNPPReformatSel} as NPPR " _
& " RELATE LKPSYSGRPLOC to LKPSYSGRPLOC )," _
& " ( {exec dbo.xpfPREReformatSel} as PRER " _
& " RELATE LKPSYSGRPLOC to LKPSYSGRPLOC )," _
& " ( {exec dbo.xpfPRAReformatSel} as PRAD " _
& " RELATE LKPSYSGRP to LKPSYSGRP )," _
& " ( {exec dbo.xpfPRRReformatSel} as PRRG " _
& " RELATE LKPSYSGRP to LKPSYSNAME )," _
& " ({exec dbo.xpfPRLReformatSel} as PRLA " _
& " RELATE LKPSYSNAME to LKPSYSNAME )"
Dim adoPractDA As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(strPractSql, OleDbPimsConn)
Dim dsPract As New DataSet
dsPract.EnforceConstraints = False <-- Added this as partt of your suggestions
adoPractDA.Fill(dsPract, "XPFH")
Thanks for your help!
The DataTable is an in memory cache only so it is limited only the by process address space. The DataTable can in theory hold, ~(2^31) DataRow, but because of memory limitations of a 32-bit process the limit of ~(2^24) DataRow is more reasonable. That limit doesn't account for any data, constraints or views that you also have stored in memory.
The V2.0 DataTable maintains an internal index for each constraint and DataView, temporary indexes are also created for GetParentRows/GetChildRows/Select. These indexes can be shared if they filter/sort the same columns. A rough estimate for the index is ~(32 bytes * DataRowCollection.Count). Each DataView adds an additional ~(36 bytes * DataView.Count), this includes the DataRowView memory.
The DataTable.MinimumCapacity is actually ignored, so it won't be of any use.
In V2.0, on x64 there’s a 4GT mode that allows 32 bits apps to address 4GB of user space. You need to enable the LAA flag. However in V2.0 RTM, the CLR pretty much will throw OOM of any segments that are above 3GB. This was fixed in Vista so you can take advantage of 3GB-4GB address space. For a 64-bit application, the dataset takes ~1.5 more memory because of the larger pointer size.
Thanks for your help. It pointed me in the right direction. In investigating down this thread, I ran into a document on the Physical Address Extension feature of SQL Server Enterprise Edition. We had currently been running it on an standard edtion virtual server.
I had our systems people put the app on a Server with SQL Server Enterprise edition with the /PAE flag on, and that got us by the
OutofMemory Exception.
I found information on how to set the LAA flag on C#, but I couldn't find anything for setting it on VB? Is it available on VB?
Thanks again!
