From derby-user-return-10258-apmail-db-derby-user-archive=db.apache.org@db.apache.org Thu Dec 11 19:34:30 2008 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 7065 invoked from network); 11 Dec 2008 19:34:30 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 11 Dec 2008 19:34:30 -0000 Received: (qmail 8213 invoked by uid 500); 11 Dec 2008 19:34:42 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 8179 invoked by uid 500); 11 Dec 2008 19:34:42 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 8170 invoked by uid 99); 11 Dec 2008 19:34:42 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 11 Dec 2008 11:34:42 -0800 X-ASF-Spam-Status: No, hits=-4.0 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.18.6.21] (HELO gmp-eb-inf-1.sun.com) (192.18.6.21) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 11 Dec 2008 19:34:27 +0000 Received: from fe-emea-09.sun.com (gmp-eb-lb-2-fe3.eu.sun.com [192.18.6.12]) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id mBBJY6tG012037 for ; Thu, 11 Dec 2008 19:34:06 GMT Received: from conversion-daemon.fe-emea-09.sun.com by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <0KBQ00N018WH3O00@fe-emea-09.sun.com> (original mail from Knut.Hatlen@Sun.COM) for derby-user@db.apache.org; Thu, 11 Dec 2008 19:34:06 +0000 (GMT) Received: from localhost (c046D47C1.dhcp.bluecom.no [193.71.109.4]) by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <0KBQ0008E90TMV90@fe-emea-09.sun.com> for derby-user@db.apache.org; Thu, 11 Dec 2008 19:34:06 +0000 (GMT) Date: Thu, 11 Dec 2008 20:34:32 +0100 From: Knut Anders Hatlen Subject: Re: faster inserts in big tables In-reply-to: <4941499B.5090301@sun.com> Sender: Knut.Hatlen@Sun.COM To: Derby Discussion Message-id: MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-15 Content-transfer-encoding: 8BIT References: <1534204609.15814411227648807881.JavaMail.javamailuser@localhost> <20957504.post@talk.nabble.com> <4941499B.5090301@sun.com> User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/22.1 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org Rick Hillegas writes: > Hi Brian, > > In a previous response Peter Ondruška noted that you could use bulk > import to speed up your inserts if your source data lived in a > properly formatted file. > > Even if your source data does not live in a file, you can still get > bulk import speed (and the benefits of your generated key) by using > table functions--provided that you upgrade to 10.4. If you wrap your > source data in a table function, then you can bulk insert your data as > follows: > > insert into MY_TABLE( NON_GEN_COL1, ... NON_GEN_COL_N ) > select * from table( MY_TABLE_FUNCTION() ) s The internal SQL syntax allows you to add an optimizer override here (--DERBY-PROPERTIES insertMode=bulkInsert) which I think speeds it up even more, though I'm not sure exactly what it buys you. This override is used in the import code and in the SYSCS_UTIL.SYSCS_BULK_INSERT system procedure, but you're not allowed to use it in your own SQL queries, it seems. The (undocumented) SYSCS_BULK_INSERT procedure does exactly what Rick's example does, except that it also adds the optimizer override. The procedure only works on old-style table functions, though. Does anyone know what performance gains one can expect by using SYSCS_BULK_INSERT instead of INSERT INTO ... SELECT FROM? Would it be worth the effort to create a similar mechanism for the new-style table functions? -- Knut Anders