Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

Fast insert solution on PostgreSQL

PostgreSQL coldfusion bind_param postgre

  • Please log in to reply
No replies to this topic

#1 Guest_roger_*

  • Guest

Posted 11 May 2006 - 07:37 PM

Today I have been working with PostgreSQL. Although the PostgreSQL COPY command is recommended, I can't seam to get it to work with ColdFusion, and cfquery. I've tried lots of different ways of doing it.

Fast Insert Solution on PostgreSQL:

So far the fastest solution I have come up with is using PREPARE to create a temporary prepared statement (yes, I'm aware of cfqueryparam, and this method inserts with the highest speed). So here's how you do it:

PREPARE preparedInsert (int, varchar) AS
INSERT INTO tableName (intColumn, charColumn)
VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
EXECUTE preparedInsert (3,'c');
DEALLOCATE preparedInsert;
Your basically creating a function that allows you to pass variables to your insert statement. Inside the first set of parenthesis you list the types of your variables, then variables are referred to as $1, $3, etc. inside the statement.

Next you can EXECUTE the statement as many times as you need to (this can all be done inside one SQL statement, inside one cfquery tag).

Finally when I'm done, I DEALLOCATE the function, otherwise if you try to PREPARE a statement named preparedInsert again during the same connection session you will get an error.

Performance Results

I found that this method was about 20% faster than multiple INSERT statements when sent in the same SQL statement (same cfquery tag). It was about 2-5 times faster than individual INSERT statements - that is, each insert statement had its own SQL statement, or cfquery tag. Finally it was about 25% faster than using bind parameters (cfqueryparam).
  • 0

Also tagged with one or more of these keywords: PostgreSQL, coldfusion, bind_param, postgre

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download