Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=sbcglobal.net;
  h=Received:X-Yahoo-SMTP:X-YMail-OSG:X-Yahoo-Newman-Property:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=Lal5KcbxeNiYKmeTUMsZKwXtKNyFRaCvkFvnOJDpjvbeVyXpbA/r0SWCBKCu4ojEAZAVFrEZYL/dwHNQHaPPh5vcyLGgfsSexUX5nNfDMyJVtgiUj7RU6ru5fTE5EF42d/VQPnu8hCsPMDaTp0GlpH+2IvHvbWR7wVELJE71HkI=
  ;
Message-ID: <4B155720.4030607@sbcglobal.net>
Date: Tue, 01 Dec 2009 09:49:20 -0800
From: Mike Matrigali <mikem_app@sbcglobal.net>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: bulk import issue
References: <8373996d0911280953t76264af0ja81f426c89a25612@mail.gmail.com>
In-Reply-To: <8373996d0911280953t76264af0ja81f426c89a25612@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Are you bulk importing into an empty table?

Derby has a built in optimization that can often be applied when bulk
importing into an empty table.  If the db is not in incremental backup
mode, then if you are bulk importing into an empty table it does not
have to log the changes.  It instead optimizes the abort action to just
empty the table and thus does not need log records.  This is not 
possible if there are rows in the table.

/mikem

Mike Andrews wrote:
> dear derby developers,
> 
> if i bulk import data into a table, i get much better performance if i
> do it in a single SYSCS_UTIL.SYSCS_IMPORT_TABLE statement rather than
> in multiple shots.
> 
> for example, if there are three large files "a.txt", "b.txt", and
> "c.txt", where "c.txt" is just the concatenation of "a.txt" and
> "b.txt", then
> 
> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'mytable', 'c.txt', ' ', null,null, 1)
> 
> takes much less time than the sum of:
> 
> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'mytable', 'a.txt', ' ', null,null, 1)
> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'mytable', 'b.txt', ' ', null,null, 0)
> 
> even though they result in exactly the same set of data in the table.
> 
> any ideas why? is there a way to get better performance doing it in
> multiple shots? currently my data is in several text files, and so i
> concatenate them all and run a single SYSCS_UTIL.SYSCS_IMPORT_TABLE
> for best performance.
> 
> best regards,
> mike
>