Re: Getting even more insert performance (250m+rows/day)

Daniel J. Luke wrote:
> On May 24, 2006, at 4:02 PM, Dave Dutcher wrote:
>> If you can live with possible database corruption, you could try
>> turning Fsync off. For example if you could just reinsert the data
>> on the off chance a hardware failure corrupts the database, you
>> might get a decent improvement.
>
> I tried, but I didn't see much of an improvement (and it's not really
> acceptable for this application).
>
>> Also have you tried creating the index after you have inserted all
>> your data? (Or maybe copy already disables the indexes while
>> inserting?)
>
> The data gets inserted in batches every 5 minutes and I potentially
> have people querying it constantly, so I can't remove and re-create
> the index.
>
are the batches single insert's, or within a big transaction?

I.E., does the inserts look like:
INSERT
INSERT
INSERT

or

BEGIN
INSERT
INSERT
INSERT
COMMIT

If the former, the latter is a big win.

Also, what release(s) are you running?

LER

--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry(dot)Rosenman(at)pervasive(dot)com
Web: www.pervasive.com