Обсуждение: C# w/ ODBC, 2.1 million list select gives empty DataSet

Hi everyone,

I've been using the ODBC driver for Windows along with my C# (Framework 2.0) for a few years now and it's been working very nicely.

Until now.

One of my customer's database has grown to considerable size over time, and in particular, one report he attemps to do in my software gives an empty report.

Upon investigation, I determined that the SELECT I send to the postgresql gets processed properly and it probably hands over about 2.1 million rows as the response. After that, I use the "Fill" method as instructed in the "HOW TO" section in the ODBC documentation to fill the data into a DataSet object.

After considerable amount of work (almost the same as when I do that same query in PgAdmin III, about 110 seconds), the DataSet gets initialized empty, with zero tables in it. No errors, no exceptions. Everything moves along as if nothing happened.

I just upgraded to the latest ODBC drivers (dated december 2009) and it's not changing my issue.

Am I hitting a limit of some sort? I played with the "ConnectionTimeout" values of OdbcCommand and OdbcDataAdapter, didn't seem to change.

Here's a selected sample of the code I use:



            OdbcCommand odcQuery = new OdbcCommand(sSQLQuery);

            using (OdbcConnection dbcPostGresConnection = new OdbcConnection(_dpDBSettings.GetConnectionString()))
            {
                try
                {
                    odcQuery.Connection = dbcPostGresConnection;
                    dbcPostGresConnection.Open();

                    switch (emCurrentType)
                    {
                        case emSqlQueryType.CREATE:
                        case emSqlQueryType.DELETE:
                        case emSqlQueryType.UPDATE:
                        case emSqlQueryType.INSERT:
                        case emSqlQueryType.ALTER:
                        case emSqlQueryType.DROP:
                        {
                            _kNumberOfAffectedRecordsFromLastQuery = odcQuery.ExecuteNonQuery();
                            break;
                        }

                        case emSqlQueryType.SELECT:
                        {
                            OdbcDataAdapter oddaDatasetFiller = new OdbcDataAdapter(odcQuery);
                            oddaDatasetFiller.Fill(_dsLastResults);
                           
                            if (_dsLastResults.Tables.Count > 0)
                            {
                                _kNumberOfAffectedRecordsFromLastQuery = _dsLastResults.Tables[0].Rows.Count;
                            }
                           
                            break;
                        }
                    }

                    _bLastQuerySucceeded = true;
                }
                catch... etc all possible catches ahead.



Anyone has a suggestion or ideas? I've searched the web, mailing lists and documentation as I could, nothing seems to be said about this particular issue.

One interseting thing I might add, I was previously using a very old ODBC driver, from 2006. This one caused an exception on the .Fill() method, which was "ERROR [HY000] Out of memory while reading tuples". Now I'm assuming that since I upgraded to the new driver, not having this exception means that I'm not running out of memory anymore, right?
   
Even more details: with the original 2006 drivers, I could see my application (the .EXE) climb up to 700MB of private bytes/virtual size in Task Manager until eventually crashing and then not releasing that memory. So initially, I was investigating a memory leak issue.

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.

Thanks for any suggestions or input from anyone.

Regards,

Laurent Chouinard

Laurent Chouinard laurent.chouinard@sem.ca

SEM inc. 3610 Valiquette, St-Laurent, QC, H4S 1X8 Tel: 514-334-7569 / 1-888-334-7569 Fax: 514-334-5922

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?

--
Craig Ringer

Laurent Chouinard wrote:
> Hi everyone,
>
> I've been using the ODBC driver for Windows along with my C# (Framework
> 2.0) for a few years now and it's been working very nicely.
>
> Until now.
>
> One of my customer's database has grown to considerable size over time,
> and in particular, one report he attemps to do in my software gives an
> empty report.
>
> Upon investigation, I determined that the SELECT I send to the
> postgresql gets processed properly and it probably hands over about 2.1
> million rows as the response. After that, I use the "Fill" method as
> instructed in the "HOW TO" section in the ODBC documentation to fill the
> data into a DataSet object.
>
> After considerable amount of work (almost the same as when I do that
> same query in PgAdmin III, about 110 seconds), the DataSet gets
> initialized empty, with zero tables in it. No errors, no exceptions.
> Everything moves along as if nothing happened.
>
> I just upgraded to the latest ODBC drivers (dated december 2009) and
> it's not changing my issue.

Hmmm, 2.1 millions of rows are too many. Possibly "Out of memory
  while reading tuples" error occurs but the driver forgets to handle
it in some cases. Please try the drivers on testing for 8.4.0201 at
   http://www.geocities.jp/inocchichichi/psqlodbc/index.html .
You may be able to improve the memory usage by checking the "Use
Declare/Fetch" option.

regards,
Hiroshi Inoue

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