Re: Insert too slow

The bottleneck here will indeed be with the composite primary key. 
Currently Mckoi does not support multi-column indexes so the uniqueness 
test of the primary key when the data is inserted will be expensive for 
large amounts of data.  My suggestion would be to not use a composite 
primary key for tables with a lot of data until multi-column indexes are 
supported.

Toby.

Rajkumar Narayanaswamy wrote:

> 
> 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]