I have a large import. Performance is an issue. Can I instruct ElevateDB to write to the Windows filesystem but defer hard disk flushes until instructed or the connection is closed?

Integrity is not an issue as I will flush at the end of each stage and the entire stage will be repeated if there is a problem.

>>I have a large import. Performance is an issue. Can I instruct ElevateDB to write to the Windows filesystem but defer hard disk flushes until instructed or the connection is closed?

Integrity is not an issue as I will flush at the end of each stage and the entire stage will be repeated if there is a problem.<<

You will likely want to use transactions to speed things up a bit because the rows are in memory until the Commit is executed. Depending on the size of the record, you could commit the transaction after every 1,000 or 10,000 rows. There is also a "NO FLUSH" option for the commit statement that will defer the OS flush from occurring. Of course if your computer crashes, you will have to start over.

COMMIT NO FLUSH;

If other people are using the database when the importing is going on, make sure your transaction only uses the table you are importing the data to. If you don't specify the table(s) when starting the transaction, then EDB locks all the tables.

BEGIN
 START TRANSACTION ON TABLES 'Customer', 'Orders';
   -- Perform some updates to the tables
 COMMIT;
EXCEPTION
 ROLLBACK;
END

The EDB SQL manual also has a section on "Buffering and Caching" when defining a table. Keep in mind that larger buffers don't always means the table will perform faster. There is always a sweet spot when it comes to buffers and with EDB it is usually on the low side.

If the table is empty when you start to import the rows, it will be faster if the table doesn't have any indexes defined and the indexes are built after the rows have been imported.

During the import, you can execute:
   select * from configuration.serversessionstatistics
to see how much of the buffers are actually used.

You might find these commands useful too:
 select * from configuration.serversessions;
 select * from configuration.serversessionlocks;

Of course the import will run much faster on an SSD drive.

Barry

>>Barry wrote:
>>...
>>Of course the import will run much faster on an SSD drive.
>>Barry

Thanks. Lots of useful info. Yes, an SSD will probably be worth the time it will save.

<< I have a large import. Performance is an issue. Can I instruct ElevateDB
to write to the Windows filesystem but defer hard disk flushes until
instructed or the connection is closed? >>

Are you using the IMPORT TABLE statement, or doing your own custom import ?

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

<< I have a large import. Performance is an issue. Can I instruct ElevateDB
to write to the Windows filesystem but defer hard disk flushes until
instructed or the connection is closed? >>

:: Are you using the IMPORT TABLE statement, or doing your own custom import ?

:: Tim Young

Custom. No way around it; it's a legacy app.

So far the advice most likely to be followed is: use a solid state disk.

jwtm,

One other thing. If you connect to the EDB database using a local connection instead of Client/Server, it will run much faster because it won't have to deal with the TCP/IP overhead. Of course if the database is on another computer and you lose the connection to the EDB database, your table could become corrupted. But as you said earlier, you would then have to recreate the table and run the process again. Running it over a network will be slower than using a local disk so make sure you have at least 1 gigbit network if you are going to do this a lot.

An exclusive lock on the table may help speed things up a bit too.

Barry

<< Custom. No way around it; it's a legacy app. >>

No problem.  Just use a restricted transaction on the involved tables, and
do a start transaction/commit block for every X number of rows being
imported.

ElevateDB has the ability to keep the transaction locks during a commit, but
it isn't surfaced in the TEDBDatabase component.  I'll have to see about
doing so, because it makes this type of operation much easier.

Tim Young
Elevate Software
www.elevatesoft.com