Max, I agree with what you are saying. To eliminate such stuff, this is what I have done: I created schema for multiple tables with no indexing, no joins, no foreign key constraints. Basically I wanted to keep it as simple as possible for inserts to be faster. Then I simulated the inserts with as minimum C code as possible. I had around 50 inserts within each transaction (between BEGIN and END). Also I had synchronous=OFF and had my journaling also turned off (even tried with MEMORY based journaling). So basically I eliminated all external dependencies that I could think of to make it faster.
I simulated for thousands of inserts to see how the system performs on heavy load conditions. I printed the time consumption stats periodically. In such a test case, I see that there is a considerable improvement seen. But what I observed is as I kept running simulations for longer periods, the inserts were taking lot more time in later stages than they were taking initially. For ex, If I ran for 200,000 inserts, first 20,000 inserts were done in 9 secs, but last 20,000 inserts (from 180,000th to 200,000) took almost 110 secs. It is more than 10 times than what it was initially. These results were consistent across all iterations of simulation I did. So I am afraid this won't scale as I keep inserting into same db file. So my question is, Is this behavior expected? Am I doing something wrong? Is my assumption that time to insert is proportional to size of the db file correct? Since I am simulating with the same application code and with no schema complications, I am assuming there should not be many external dependencies that will affect the system in the long run. Your help is appreciated... Thanks Raghu -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Max Vlasov Sent: Wednesday, December 16, 2009 2:38 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] BACK API Questions On Wed, Dec 16, 2009 at 9:30 AM, Raghavendra Thodime <rthod...@sipera.com>wrote: > I did try using batch of transactions with synchronous=OFF PRAGMA set. The > performance improved slightly. But as db file started to grow larger and > larger in size, the performance degraded considerably. Is it expected? Or Is > there a work around for this? > If you did everything according this topic: (19) INSERT is really slow - I can only do few dozen INSERTs per second ( http://www.sqlite.org/faq.html#q19 ) from FAQ, you probably have extra overload with your development language/library. Also be aware that you will probably never get the same speed as the general copying of same amount of data with the file system routines since the db engine have to deal with indexes and other extra job. So the performance high or low might be a subjective estimate. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
