Re: max_allowed_packet in PostgreSQL

yawnmoth wrote:
In MySQL, there's a limit on how big SQL queries can be. This limit
can be determined with the following query:

SHOW VARIABLES LIKE 'max_allowed_packet';

Is there a similar limit for PostgreSQL or any other DBMSs?

As of PostgreSQL version 7.1 (April 2001), they claim there is "no fixed limit on the length of a query string." Prior to that, the limit on SQL statements was an embarassing 8KB.

InterBase 6.0/Firebird had a limit of either 16KB or 32KB on a SQL statement. I could never find out for sure what it was. Subsequent versions of either product could have improved, I haven't used them since 2000.

Tip: if your SQL statements are long because of long character strings, try using parameterized queries. The fields in the SQL string are replaced by a single ? character, and then you can supply the length values when you execute the prepared query.

Also, is the following...

INSERT INTO a VALUES (1,23),(2,34),(4,33);

...faster than doing three seperate INSERTs? If so, is it pretty
universal or does it work on just a few DBMSs?

Yes, performance is improved by this form, where it is supported. According to "SQL Performance Tuning", only IBM DB2 and MySQL support the syntax (as of the time of this book's writing in 2003).

It helps to combine multiple statements into one, because there is naturally some amount of overhead per statement. Parsing, privilege checking, etc. When you are doing bulk inserts of thousands or millions of rows, this adds up!

Note that MySQL has another tuning parameter that affects bulk inserts. If you use MyISAM tables, see bulk_insert_buffer_size in the docs:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Also read these pages if you need to optimize bulk inserts:
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-insert-buffering.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html

Also if you are doing bulk inserts, instead of INSERT use LOAD DATA INFILE. It's much faster.

Regards,
Bill K.
.

Relevant Pages

  • Re: SELECT * or list fields?
    ... SELECT * is used in a query. ... As reagards maintenance, adding columns to the table is not so bad but ... As regards SQL statements ('query' is often used in the ...
    (microsoft.public.access.queries)
  • Re: SELECT * or list fields?
    ... As reagards maintenance, adding columns to the table is not so bad but ... Issues range from having to change code that consumes the query ... As regards SQL statements ('query' is often used in the ...
    (microsoft.public.access.queries)
  • RE: run SQL SELECT query in vba
    ... > can not construct & run simple SELECT SQL statements ... > checkmark the "Microsoft DAO 3.6 Object Library"? ... > selects criteria to query my tables by. ... > So I was thinking about writing vba code ...
    (microsoft.public.access.queries)
  • Re: Query will not populate with data
    ... Consider posting the SQL statements of your three queries... ... I then created a query that combined the two mentioned above. ... query does not populate properly. ... I need to connect to the Client table via the NFS_Data table and the ...
    (microsoft.public.access.queries)
  • PostgreSQL Equivalent of mysql_unbuffered_query
    ... liners, not bulk) in cases where you don't care about the outcome ... (Such as deleting a user account from a users table.) ... You must call pg_get_resultafter sending the query and before ...
    (comp.lang.php)