Hi,
Inserting many of rows is almost always IO bound. Converting ints and floats to text is CPU bound and really fast anyway. To speed things up first look at things like indexes, how often you need to COMMIT or using COPY. Only then look at prepared statements and binary transfer modes. Else it's simply not worth the headache.
Groeten, Arjen
On Wed, Sep 15, 2010 at 6:02 PM, Michael Hull
<mikehulluk@googlemail.com> wrote:
Hi Guys,
I have jsut started to use the libpq interface to postgre, and was
wondering if someone with some experience could explain the 'best
practices' :)
I am preparing a query, then submitting it with binary data. 6 values
are ints, and one is a float.
For the ints, I understand that since I need network byte ordering, I
have to pump them through htonl()
The same is also true for the floats I assume, so I have dones some
dirty,dirty casting, but this feels like a source for problems later
on.
It does the correct thing, but is there a better way of doing it??
Mike
char qString[] = "INSERT INTO raw_results (ca,kf,ks,lk,na,iinj,
AP1height) VALUES( $1::int4, $2::int4, $3::int4, $4::int4, $5::int4,
$6::int4, $7::float4)";
PGresult* pPreparedQuery = PQprepare(pDB, "InsertQ",qString, nParams, NULL);
float AP1Height = 3.141;
/* TROUBLE AHEAD! */
if( sizeof( float) != sizeof(int32_t) ) throw MyException();
int32_t AP_int = * ( (int32_t*) &AP1Height );
int32_t AP_intSwapped = htonl(AP_int);
float AP1HeightSwapped = *( (float*) &AP_intSwapped);
/* TROUBLE OVER */
int32_t ca= htonl(100);
int32_t kf= htonl(200);
int32_t ks= htonl(300);
int32_t lk= htonl(400);
int32_t na= htonl(500);
int32_t iinj= htonl(600);
const char* values[nParams] = {
(char*)&ca,
(char*)&kf,
(char*)&ks,
(char*)&lk,
(char*)&lk,
(char*)&iinj,
(char*)&AP1HeightSwapped,
};
int lengths[nParams] = {
sizeof(ca),
sizeof(kf),
sizeof(ks),
sizeof(lk),
sizeof(na),
sizeof(iinj),
sizeof(AP1Height),
};
int binary[nParams]={1,1,1,1,1, 1,1};
PGresult* res = PQexecPrepared( pDB, "InsertQ", nParams, values,
lengths,binary,0);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general