Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
Can I defer disk flushes? |
Tue, Mar 4 2014 5:10 PM | Permanent Link |
jwtm | 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. |
Tue, Mar 4 2014 8:42 PM | Permanent Link |
Barry | >>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 |
Wed, Mar 5 2014 12:15 AM | Permanent Link |
jwtm | >>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. |
Tue, Mar 11 2014 8:08 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Tue, Mar 18 2014 10:55 PM | Permanent Link |
jwtm | "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. |
Wed, Mar 19 2014 12:38 AM | Permanent Link |
Barry | 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 |
Wed, Mar 19 2014 10:32 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
This web page was last updated on Monday, May 6, 2024 at 01:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |