Insert too slow

Hi,

I've been running into the problem with inserting into a table in mckoi 
database with about 150k records. The create table runs like this:

CREATE TABLE REPORTS (
  number VARCHAR(40) NOT NULL,
  name   VARCHAR(100) INDEX_NONE,
  facility    VARCHAR(20) INDEX_NONE,
  location    VARCHAR(20),
  toDate    VARCHAR(20),
  reportType    VARCHAR(20),
  fromDate    VARCHAR(20) INDEX_NONE,
  prid    VARCHAR(20) INDEX_NONE,
  gpid    VARCHAR(20) INDEX_NONE,
  status    VARCHAR(2) );

This has a primary key with the following 5 coloumns:

ALTER TABLE REPORTS ADD PRIMARY KEY ( number, location, toDate, reportType, 
status );

As you can see - it is a fairly simple table, with simple ascii columns.

The problem is, after about a 100k insertions, the insert of each record 
takes incredibly long time - over 30 secs per insertion ... I think it is 
due to the full table scan that it performs to check the primary key 
constraint, for each record that is being added. Is there a way to avoid 
this, without deleting primary key constraint altogether?

* Has someone else used mckoi in this kind of a scenario, with heavy use of 
INSERT? what are the performance numbers that you have experienced? do you 
have any tips?
* Are there any workarounds to not having to perform a full table scan, with 
the multi-coloumn pk constraint?

Some specifics:
- I'm using prepared statements for insertion
- AutoCommit turned OFF (set to false)
- Commit occurs only after adding about 500-1000 records per cycle.
- transaction_error_on_dirty_select enabled; the default setting is 
unchanged.
- I've tried this with the 'dont_synch_filesystem' turned ON and OFF, with 
the same results;
- during the time of inserting about 500-1000 records (it takes about 1 hr 
for this), the CPU util of the java process is almost 100% - hovers around 
90% -
- there are no resource issues for the process; it is a dual 3.0 G machine 
with 2 G of RAM in it.

Thanks in advance for any help.
Raj

------------------------
Rajkumar Narayanaswamy
[email protected]




---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]