>> On Sep 25, 2018, at 11:14 AM, dmp <da...@dandymadeproductions.com> wrote: >> >> The result for the 50K file db test of SQLite was 370.184 >> seconds. Is this a reasonable transfer speed, given the >> conditions noted?
> You haven't specified how much of that time was spent in SQLite. For all we know, > 370 seconds was spent in MariaDB and your own code, and it only took .184 sec for > SQLite to insert the rows :) > Try running your code with a profiler. Or at least wrap some quick & dirty timing > code around your functions that call SQLite. > Also, make sure to insert as many rows as possible within a transaction. Committing > a transaction in SQLite is quite expensive due to filesystem flushing. > —Jens Hello, The DB to DB code is a Java plugin for my application. It takes a query to the current database, converts that into a schema, creates a new table in the selected sink database of a comparable schema, then begins transferring data. The data transfer is setup with two threads, the source database thread loading data and the sink database thread inserting via prepared statements in a batch mode. The threads are connected via an ArrayBlockingQueue pipe. I log the pipe capacity as the process takes place. The timing given takes place at the beginning of the transfer process and stops when completed, threads join. Now from the users of the forum, I did some testing to establish a reference transfer time. After some thought I realized I could get that number by just performing a direct file transfer of the created SQLite database file from the MariaDB server to the client machine. Results 54 seconds. Upon further testing, increasing the pipe size, 250 to 1000. I was able to get the time for the SQLite test to 164.721 sec. Average pipe capacity, 85 objects. That tells me that I could do better perhaps using two loading threads. I already suspected the loading thread filler to be slower and therefore had coded it with an increased priority of one. The testing so far shows SQLite comparable to the other tested memory databases. SQLite beat out all but one with a file database. One of the databases did miserable with the pipe capacity close to saturated and a time of 1374.614 sec. Testing: Transfer 50,000 rows, (Integer, Real, 0-10k Text, 0-10k blob) Resulting SQLite file database size: 404Mb. Updated SQLite Log Database: https://github.com/danap/db_to_filememorydb/blob/master/docs/dblog/db_To_db_Parameters-log.db danap. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
