Faster Inserts with PostgreSQL
As I mentioned yesterday, I'm working on optimizing lots of inserts into a database, and I need solutions for different DB servers. 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
$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.
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).
Like this? Follow me ↯Tweet Follow @pfreitag
Faster Inserts with PostgreSQL was first published on August 31, 2005.
If you like reading about postgresql, insert, performance, or sql then you might also like:
- Insert Delayed with MySQL
- Order by NULL Values in MySQL, Postgresql and SQL Server
- Top 10 Reserved SQL Keywords
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL
- SQL to Select a random row from a database table
- Cheat Sheet for PostgreSQL
- Multiple Dynamic Inserts with SQL
- SQL Pagination with LIMIT and OFFSET
It's really came a long ways for the Windows platform as of version 8-- it's quite impressive.
I wonder if it is possible in PostgreSQL to get a folder containing flat files and load all files into pgsql table using something like
copy table_name from 'folder_name/*.dat' ?
INSERT INTO table_name SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 3, 'c' UNION SELECT 4, 'd' UNION ...
Combine this syntax with multiple INSERTs per transaction, and you can get quite a boost. In my case it beat the prepared statement approach by a significant margin. (N.B. I have only tested this in PostgreSQL 8.1, if that matters)
I wonder if PostgreSQL provides a faster way to construct the row-set than SELECT..UNION ?
I need to load .csv files into postgre (greenplum). Help me how to load.