CODEWITHSUNDEEP
c++postgresqllibpqxx
Alexandros
Alexandros

Reputation: 2200

C++, Postgres , libpqxx huge query

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?

Upvotes: 4

Views: 3201

Answers (3)

shemake
shemake

Reputation: 101

Cursors are a good place to start. Here's another cursor example, using a do-while()

     const std::conStr("user=" + opt::dbUser + " password=" + opt::dbPasswd + " host=" + opt::dbHost + " dbname=" + opt::dbName);                                            

      pqxx::connection conn(connStr);
      pqxx::work txn(conn);
      std::string selectString = "SELECT id, name FROM table_name WHERE condition";

      pqxx::stateless_cursor<pqxx::cursor_base::read_only, pqxx::cursor_base::owned> 
      cursor(txn, selectString, "myCursor", false);

      //cursor variables
      size_t idx = 0;       //starting location
      size_t step = 10000;  //number of rows for each chunk
      pqxx::result result;
      do{
        //get next cursor chunk and update the index
        result = cursor.retrieve( idx, idx + step );
        idx += step;

        size_t records = result.size();
        cout << idx << ": records pulled = " << records << endl;

        for( pqxx::result::const_iterator row : result ){
          //iterate over cursor rows
        }
      }
      while( result.size() == step ); //if the result.size() != step, we're on our last loop
      cout << "Done!" << endl;

I'm iterating over approximately 33 million rows in my application. In addition to using a cursor, I used the following approach:

  1. Split the data into smaller chunks. For me, that was using bounding boxes to grab data in a given area.
  2. Construct a query to grab that chunk, and use a cursor to iterate over it.
  3. Store the chunks on the heap and free them once you're done processing the data from a given chunk.

I know this is a very late answer to your question, but I hope this might help someone!

Upvotes: 1

Alexandros
Alexandros

Reputation: 2200

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;
    }

Upvotes: 1

ArtemGr
ArtemGr

Reputation: 12547

Use a cursor?

See also FETCH. The cursor will use it for you behind the scenes, I gather, but just in case, you can always code the streaming retrieval manually with the FETCH.

Upvotes: 2

Related Questions