Re: not enough storage... error using GetRows

From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 02/02/05 

Date: Tue, 1 Feb 2005 19:54:49 -0800

"danreber" <danreber@discussions.microsoft.com> wrote in message
news:65DB7BCC-7759-4C9E-9133-81C4083AD0F5@microsoft.com...
> After the rows were dumped into the array the memory used was ~500MB but
> the
> array only contained ~150,000 rows.
>
> I did a select into using the sql statement in question and ran a sproc to
> get the total table size on sql server, it returned 195 MB. Considering
> that
> I had 1.55 GB of RAM available when the rows (678,099 to be exact) were
> returned...so I don't think that it can be a resource issue, I think that
> it
> is a bug in the GetRows method.
>
> I can't change the sql statement because my application is an adhoc data
> analysis application and that is just what the user selected.
>
> I may need to use the Rows, Start params to get chunks of data and dump
> the
> chunks into multiple arrays.
>
> Thanks for the response, if you have any other ideas then please let me
> know.
>
> danreber

To test I opened a server-side cursor on a table with 2,230,833 rows, each
row containing 5 integers. It took 19 minutes for GetRows to return (db
server connected via 100-base Ethernet, but only consuming 3% of network
capacity, for whatever reason.) When it was done UBound(rowarray, 2)
returned 2230833, and the prog had consumed 200MB of memory. In the
immediate window I assigned rowarray="" and it instantly freed 150MB,
however, 5 integers * 4 bytes each * 2230833 rows is only 44MB and change.
The rest was undoubtedly safe array and variant overhead.

I noticed as it was running that it was incrementally allocating
progressively more memory.

Then I changed it to a client cursor, it took 15 seconds to open the
recordset, and another 11 seconds for GetRows to return, same array size,
this time the app consuming 250 MB. Re-assigning the array freed 170MB,
closing the recordset freed another ~45MB.

What does all this mean? Good question! :-) For one thing it suggests that
server cursors calling GetRows is not a particularly happening combination.
For another it suggests that recordset data storage overhead is considerably
less than that of safe arrays of variants... Oh yeah, here we go:

    Dim v() As Variant, s() As String, o() As Object, d() As Double

    ReDim v(5, 2000000)
    MsgBox UBound(v, 2)
    ReDim v(0, 0)

    ReDim s(5, 2000000)
    MsgBox UBound(s, 2)
    ReDim s(0, 0)

    ReDim o(5, 2000000)
    MsgBox UBound(o, 2)
    ReDim o(0, 0)

    ReDim d(5, 2000000)
    MsgBox UBound(d, 2)
    ReDim d(0, 0)

The first one allocates some 180MB, the next two only allocate about 47MB
each, and the last one allocates about 90MB -- point being that variant
overhead is considerable, and must be factored in.

Back to the question, is it a resource problem or an ADO bug? My tests
suggest it is not a number-of-rows issue, and probably not a size-of-data
issue per se. That it works after several failures leads me to believe that
something is causing memory fragmentation, are you closing all the
connections/recordsets you're opening? Are you sure all of your classes are
unloading as you expect? (Classes don't always unload when set to nothing.)

Repeated allocation failures may be causing the system to compact memory,
which would explain why it works after several unsuccessful tries. It's not
just the sum total of memory that's important, the size of the largest
contiguous piece is critical as well.

Sorry to ramble on so long without giving you any definitive answers.

Good Luck,
Mark

> "Mark J. McGinty" wrote:
>
>>
>> "danreber" <danreber@discussions.microsoft.com> wrote in message
>> news:E86348EA-C999-439D-A3B5-43F29BC2C1CD@microsoft.com...
>> > More info:
>> >
>> > I tested the app in the VB IDE. I placed a breakpoint at
>> >
>> > DataArray = rsData.GetRows(adGetRowsRest)
>> >
>> > At that point I had 1.55 GB of RAM (out of 2GB) available. When
>> > GetRows
>> > was
>> > called the amount of RAM spiked to 550 MB RAM used, the error was
>> > raised
>> > and
>> > the RAM went back down to 450 MB used. I copied the line of code to
>> > the
>> > immediate window. After executing the line and getting the error three
>> > times, on the fourth time the line ran fine and the rest of the code
>> > worked
>> > without any issues.
>> >
>> > So I don't think that it is a system resources issue.
>>
>> Did you try it again after that? How much mem was committed after it
>> succeeded? I'd say it's almost certainly a resource issue.
>>
>> GetRows accepts a max rows optional parameter, experiment with that to
>> limit
>> return sizefind the threshold, and see if you can estimate the average
>> size
>> of a row in this data, to make a scientiffic estimate for memory
>> consumption
>> if/when it succeeds.... if you have image or text fields returned in this
>> set, you really need to rethink. (You likely need to do that
>> regardless.)
>>
>>
>> -Mark
>>
>>
>>
>>
>> > "danreber" wrote:
>> >
>> >> I have a client that is returning >600K rows from a sql server using a
>> >> VB
>> >> application. I am referencing ado 2.6 in the app but he has windows
>> >> xp
>> >> sp2
>> >> (I think that has ado 2.8???). When the data is fetched I use GetRows
>> >> to
>> >> poplulate an array. As soon as GetRows is called I get an error "not
>> >> enough
>> >> storage is available to complete this operation".
>> >>
>> >> I have searched the internet and I can't find much about it. I did
>> >> find
>> >> a
>> >> refernce to AppenChunck but I am not using that.
>> >>
>> >> I tested the app on my PC
>> >>
>> >> 2 GB RAM
>> >> 3.6 GHtz
>> >> Windows XP SP 2
>> >> MDAC 2.8 SP1
>> >>
>> >> and I get the same error. Does anybody have any ideas?
>> >>
>> >> Thanks
>> >>
>> >> danreber
>> >>
>>
>>
>>

Relevant Pages

  • Re: Memory Lifetime in Interop
    ... large value (so it allocates say a 128 Megabyte array), the memory allocated ... garbage collection when the function returns control to the client) and one ... From watching> the memory heap, if I set dynamic_value in the .NET code to be a rather> large value (so it allocates say a 128 Megabyte array), the memory> allocated ...
    (microsoft.public.dotnet.framework.interop)
  • Re: Framework 2.0 array redim unsatisfactory performance
    ... I am not talking about "array redim" anymore. ... ReDim simply allocates a new ... but I do not even attempt to test its memory ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Fast string operations
    ... Looping: I thought looping over arrays in managed code was "slow" ... array handling and such. ... The problem with TrimHelper is that it always returns a new string instance. ... The customer perceives this as a memory leak. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: High Memory Consumption of Classes and Arrays
    ... Only the array itself has overhead. ... memory as a reference type. ... > least consume 40 bytes of memory. ...
    (microsoft.public.dotnet.framework.performance)
  • Re: Fast linked list
    ... > amounts of memory rather than huge chunks of it. ... random insertions into a vector/dynamic array are not as slow ... to cause a cache miss. ... some hard numbers on speed differences between lists and arrays. ...
    (microsoft.public.vc.mfc)