Re: C# w/ ODBC, 2.1 million list select gives empty DataSet

On 2010-01-12 23:41, Craig Ringer wrote:
> On 12/01/2010 7:34 AM, Laurent Chouinard wrote:
>
>> With the new drivers, it still climbs to 700MB or so (makes sense, 2
>> million rows is quite a bit), and then after the "0 rows" returned,
>> memory is cleared immediately. Makes sense as well, OBDC doesn't carry
>> data anymore, dataset is almost null, garbage collector is having a
>> field day.
>
> I don't use ODBC much personally and don't work on the driver - but it
> sounds to me a lot like the new driver may be running out of memory
> too, but is eating the failure (or reporting it via some side-channel)
> rather than throwing. If it is, that'd be pretty bad behavior in my
> personal opinion, but then I don't know ODBC well. Someone who does
> may be able to shed some more light.
>
> My suggestion, though: Even if you resolve this now, as things
> continue to grow you're still going to have OOM issues. Perhaps it'd
> be a good idea to use a cursor for this?
>

You're right. For some reason, it completely escaped me that the new
driver could be failing just as the previous one, but in a less
acceptable way.

Indeed, the use of a cursor seems logical when dealing with very large
quantities of data, especially considering that 2.1 million rows is
after only a year of use. My final solution must be future proof to some
extent. I tried to have my code be as database-compatible as possible so
that I can swap from Postgres to Microsoft easily depending on customer
requirements. Using a cursor means I have a PG specific way, but then
again, I could also find out how to do that in MS and call that a day. A
long one, however!

Thanks all for your inputs.

Also, thanks Hiroshi for your suggestion for the other driver, but I
will not venture into non-official releases at the moment because my
product is already in use by customers. I can't rely on experimental or
beta versions of code.

Regards,
Laurent Chouinard