Thread: Query precompilation?
Hi, I have an application which has an queue of data it has to insert into a table in a local database. the insert-queries syntax is all the same, and the values are the only thing that differs. The insert-query looks like this: INSERT INTO "table" VALUES(a, b, c, d, e, f, g, h) ...but I cannot insert more than 200/sec, and that is much too slow for me. Are there ways to precompile a sqlquery or do other tricks to get the *fastest* insertion-rate, since the data-queue is growing faster than 200/sec... I don't care about integrity etc! I'm using PostgreSQL 7.0.3, RH 6.2 Linux 2.2.4, and the pq library with gcc. Regards, Steffen E. Thorkildsen (PS! Please reply to my e-mail aswell.)
On Tue, 27 Feb 2001, you wrote: > Hi, > > I have an application which has an queue of data it has to insert into > a table in a local database. the insert-queries syntax is all the same, > and the values are the only thing that differs. The insert-query looks > like this: > > INSERT INTO "table" VALUES(a, b, c, d, e, f, g, h) > > ...but I cannot insert more than 200/sec, and that is much too slow for > mme. Are there ways to precompile a sqlquery or do other tricks to get the > *fastest* insertion-rate, since the data-queue is growing faster than > 200/sec... > I don't care about integrity etc! You should !-) You can find some valueable tips in the documentation: http://www.de.postgresql.org/users-lounge/docs/7.0/user/c4929.htm > I'm using PostgreSQL 7.0.3, RH 6.2 Linux 2.2.4, and the pq library with > gcc. > > > Regards, > > Steffen E. Thorkildsen > > (PS! Please reply to my e-mail aswell.)
Steffen Emil Thorkildsen <steffent@ifi.uio.no> writes:
> I have an application which has an queue of data it has to insert into
> a table in a local database. the insert-queries syntax is all the same,
> and the values are the only thing that differs. The insert-query looks
> like this:
> INSERT INTO "table" VALUES(a, b, c, d, e, f, g, h)
> ...but I cannot insert more than 200/sec, and that is much too slow for
> me.
Consider using COPY FROM STDIN instead ...
regards, tom lane
Steffen Emil Thorkildsen <steffent@ifi.uio.no> writes: > me. Are there ways to precompile a sqlquery or do other tricks to get the > *fastest* insertion-rate, since the data-queue is growing faster than > 200/sec... I don't care about integrity etc! > > I'm using PostgreSQL 7.0.3, RH 6.2 Linux 2.2.4, and the pq library with > gcc. > Apart from the COPY mentioned by Tom Lane, you should also fo through the obvious checklist: use -F to disable fsync, drop indexes(if possible), use several connections(could help if you have multiprossessor system)
(...) > > > > I don't care about integrity etc! > > You should !-) > > You can find some valueable tips in the documentation: > http://www.de.postgresql.org/users-lounge/docs/7.0/user/c4929.htm > In the docs there is this paragraph: >Disable Auto-commit > > Turn off auto-commit and just do one commit at the end. Otherwise Postgres >is doing a lot of work for each record added. In general when you are doing >bulk inserts, you want to turn off some of the database features to gain >speed. This sounds nice, but I've read a lot of postgres documents and still do not know how to disable autocommit. Is this possible? And how? Mario Weilguni -- ===================================================Mario Weilguni KPNQwest Austria GmbH Senior Engineer Web Solutions Nikolaiplatz 4 tel: +43-316-813824 8020 graz, austria fax: +43-316-813824-26 http://www.kpnqwest.at e-mail: mario.weilguni@kpnqwest.com ===================================================
Mario Weilguni wrote: > > (...) > > > > > > I don't care about integrity etc! > > > > You should !-) > > > > You can find some valueable tips in the documentation: > > http://www.de.postgresql.org/users-lounge/docs/7.0/user/c4929.htm > > > > In the docs there is this paragraph: > >Disable Auto-commit > > > > Turn off auto-commit and just do one commit at the end. Otherwise Postgres > >is doing a lot of work for each record added. In general when you are doing > >bulk inserts, you want to turn off some of the database features to gain > >speed. > > This sounds nice, but I've read a lot of postgres documents and still do not > know how to disable autocommit. Is this possible? And how? At the moment, use a BEGIN/COMMIT block around a set of insert statements. Someday we'll likely have an explicit command to affect the behavior. - Thomas