Thread: C++, Postgres , libpqxx huge query

Hello experts. I have posted this question on stack overflow, but I did not
get any detailed answer, so I thought I should cross post here. My
apologies.

I have to execute an SQL query to Postgres by the following code. The query
returns a huge number of rows (40M or more) and has 4 integer fields: When I
use a workstation with 32Gb everything works but on a 16Gb workstation the
query is very slow (due to swapping I guess). Is there any way to tell the
C++ to load rows at batches, without waiting the entire dataset? With Java I
never had these issues before, due to the probably better JDBC driver.

try {
        work W(*Conn);
        result r = W.exec(sql[sqlLoad]);
        W.commit();

        for (int rownum = 0; rownum < r.size(); ++rownum) {
            const result::tuple row = r[rownum];
            vid1 = row[0].as<int>();
            vid2 = row[1].as<int>();
            vid3 = row[2].as<int>();
            .....

    } catch (const std::exception &e) {
        std::cerr << e.what() << std::endl;
    }

I am using PostgreSQL 9.3 and there I see this
http://www.postgresql.org/docs/9.3/static/libpq-single-row-mode.html, but I
do not how to use it on my C++ code. Your help will be appreciated.

EDIT: This query runs only once, for creating the necessary main memory data
structures. As such, tt cannot be optimized. Also, pgAdminIII could easily
fetch those rows, in under one minute on the same (or with smaller RAM) PCs.
Also, Java could easily handle twice the number of rows (with
Statent.setFetchSize()
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize%28int%29)
So, it is really an issue for the libpqxx library and not an application
issue. Is there a way to enforce this functionality in C++, without
explicitly setting limits / offsets manually? Also, is there another driver
I should use with C++ that offers this kind of functionality?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/C-Postgres-libpqxx-huge-query-tp5802330.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 04 May 2014, at 10:57, alexandros_e <alexandros.ef@gmail.com> wrote:

> Hello experts. I have posted this question on stack overflow, but I did not
> get any detailed answer, so I thought I should cross post here. My
> apologies.
>
> I have to execute an SQL query to Postgres by the following code. The query
> returns a huge number of rows (40M or more) and has 4 integer fields: When I
> use a workstation with 32Gb everything works but on a 16Gb workstation the
> query is very slow (due to swapping I guess). Is there any way to tell the
> C++ to load rows at batches, without waiting the entire dataset? With Java I
> never had these issues before, due to the probably better JDBC driver.
>
> try {
>        work W(*Conn);
>        result r = W.exec(sql[sqlLoad]);
>        W.commit();
>
>        for (int rownum = 0; rownum < r.size(); ++rownum) {
>            const result::tuple row = r[rownum];
>            vid1 = row[0].as<int>();
>            vid2 = row[1].as<int>();
>            vid3 = row[2].as<int>();
>            .....
>
>    } catch (const std::exception &e) {
>        std::cerr << e.what() << std::endl;
>    }
>
> I am using PostgreSQL 9.3 and there I see this
> http://www.postgresql.org/docs/9.3/static/libpq-single-row-mode.html, but I
> do not how to use it on my C++ code. Your help will be appreciated.

I think the section of relevance is: http://www.postgresql.org/docs/9.3/static/libpq-async.html

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Hi,

On 2014-05-04 01:57:43 -0700, alexandros_e wrote:
> I have to execute an SQL query to Postgres by the following code. The query
> returns a huge number of rows (40M or more) and has 4 integer fields: When I
> use a workstation with 32Gb everything works but on a 16Gb workstation the
> query is very slow (due to swapping I guess). Is there any way to tell the
> C++ to load rows at batches, without waiting the entire dataset? With Java I
> never had these issues before, due to the probably better JDBC driver.

Try looking into either using a serverside cursor or COPY.

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-05-04 01:57:43 -0700, alexandros_e wrote:
>> I have to execute an SQL query to Postgres by the following code. The query
>> returns a huge number of rows (40M or more) and has 4 integer fields: When I
>> use a workstation with 32Gb everything works but on a 16Gb workstation the
>> query is very slow (due to swapping I guess). Is there any way to tell the
>> C++ to load rows at batches, without waiting the entire dataset? With Java I
>> never had these issues before, due to the probably better JDBC driver.

> Try looking into either using a serverside cursor or COPY.

Another possibility is libpq's recently-introduced row-at-a-time mode:

http://www.postgresql.org/docs/9.3/static/libpq-single-row-mode.html

though I'm not sure how effectively that's supported by libpqxx.

            regards, tom lane

To answer my own question, I adapted How to use pqxx::stateless_cursor class
from libpqxx?

try {
        work W(*Conn);
        pqxx::stateless_cursor<pqxx::cursor_base::read_only,
pqxx::cursor_base::owned>
                cursor(W, sql[sqlLoad], "mycursor", false);
        /* Assume you know total number of records returned */
        for (size_t idx = 0; idx < countRecords; idx += 100000) {
            /* Fetch 100,000 records at a time */
            result r = cursor.retrieve(idx, idx + 100000);
            for (int rownum = 0; rownum < r.size(); ++rownum) {
                const result::tuple row = r[rownum];
                vid1 = row[0].as<int>();
                vid2 = row[1].as<int>();
                vid3 = row[2].as<int>();
                .............
            }
        }
    } catch (const std::exception &e) {
        std::cerr << e.what() << std::endl;
    }





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/C-Postgres-libpqxx-huge-query-tp5802330p5802392.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.