Inserts using QEPs or Vifred

In article <4m58og$agj@mailhost.qsp.co.uk> PC028@   (Liam McCauley) writes:

>In <3184E55A.3BDA@csci.csc.com>,
>Matthew Skalicky  writes:
>>I've been browsing the INGRES manuals for 6.4
>>and I came across this paragraph:
>>
>>"The key word repeated directs INGRES to encode the insert
>>and save its execution plan when it is first executed. This 
>>encoding can account for significant performance improvements
>>on subsequent executions of the same insert."
>>
>>OK call me sceptical! INSERT as far as I know does not have a
>>QEP (unless its an INSERT/SELECT) so what benefit is the repeated
>>for a straight INSERT?
>>
>>Cheers
>>Matt
>>
>>===================================================================
>>Matthew Skalicky
>>CSC-Intelicom
>>115 N. Neil Street
>>Champaign, Illinois
>>
>>Voice:   Intl+(217)351 8250
>>Email:   mskalick@chmpgn.csci.csc.com
>>===================================================================
>>
>
>None for a straight insert of the form INSERT INTO ... VALUES ...
>
>As you say, the QEP is saved when you use INSERT INTO ... SELECT ..., but
>otherwise there is no benefit.  I even benchmarked it a while back, when I was
>desperately trying to increase the performance of inserts in 6.4.
>
>Cheers,
>Liam
>-- 

Did you ever notice how VIFRED puts its data into the database?  If you
dig hard enough you will realize it writes all data to flat files and
uses COPY.  If you exceed 20 rows (your mileage may vary) or so you can
gain lots of insert performance this way.  

However, there are other problems with this solution, as rules don't 
fire, permissions may be a problem, errors are difficult to handle, etc.

Cheers,
-- 

|---------------------------------------------------|
| Michael Leo              York & Associates, Inc.  |
| mal@winternet.com        (612) 921-8083 (voice)   |
|                          Minneapolis, MN, USA     |
|---------------------------------------------------| 
| NAIUA = North American Ingres Users Association   |
|            http://www.naiua.org                   |
|                                                   |
|   Ingres FAQ is at ftp.naiua.org in /pub/ingres   |
|---------------------------------------------------|



With the help of tech support, we tracked down the limiting factor with insert
rate in 6.4: file extension.  (For BTREE tables) when a data page is filled up,
a new associated page needs to be allocated.  This is done by unix extending
the data file, which must be done *synchronously* (losing the benefit of your
write behind threads).  The row length had a direct effect on the insert rate,
(because the rows per page govern the frequency that a page will fill up).

You can get around this problem in OpenIngres by pre-allocating the space in
a BTREE table (using the modify command), although even without this, the
insert rate seems to be much faster (up to 200% on some benchmarks I've
seen).

Another solution I have considered (but unfortunately I don't have an OpenIngres
installation to play with), is to create a global temporary table (which should
be in memory if small enough), insert into this table (should be very fast),
then issue an INSERT INTO real table ... SELECT from global temp table, every
1000 records or so.  Has anyone else tried this kind of approach?

Cheers,
Liam

-- 

    Liam McCauley                Email: Liam_McCauley@QSP.co.uk
    Database Administrator       Tel  : (0191)402 3283
    Quality Software Products
    Gateshead
    UK
Ingres Q & A
To William's Home Page
Email William