Looking for some performance advice before I go testing this myself.
I'm porting some code that's currently running with SQLite as its DB
engine over to postgres. SQLite will still be an option so I need to
maintain compatibility across both engines. I've run into the common
postgres problem of having no direct equivalent method to 'INSERT OR
IGNORE'.
The method that must be used is as follows:
CREATE TABLE test_table (property TEXT PRIMARY KEY, value TEXT);
SQLite: INSERT OR IGNORE INTO test_table VALUES('prop','val');
Postgres: INSERT INTO test_table SELECT 'prop','val' WHERE NOT EXISTS
(SELECT 1 FROM test_table WHERE property='prop');
The Postgres syntax works fine on SQLite, but I'm wondering if there's
any major performance difference between the two. 'EXPLAIN'ing the two
shows 23 steps for INSERT OR IGNORE, and 53 steps for the WHERE NOT
EXISTS method, but without setting up a large test I can't determine if
there's a significant difference between the two.
My options are to either switch on the DB type, or just go with the
compatible query, but the performance will be the determining factor.
Thanks for any info that can be provided.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
