Hi Richard et al., 1. It's not thrashing - I've watched the process grow with prstat -cp `pgrep gti` and it doesn't top 100MB - as I said I've got 1GB of RAM in my workstation, with 2.5GB of free swapspace.
2/3. There are no triggers or indexes on the table - they are very simple and vanilla tables, with simple datatypes - just int's and varchar's. Donald Griggs and Jay Sprenkle have suggested transactions, which I had not been using, and I've wrapped the all 800,000 iterations into one tran and that has reduced the time down to 6 minutes - thanks Donald and Jay!! Problem fixed - it's now taking as much time to ship the data into memory as it does to ship it out of Sybase - I'm happy enough about that. I guess it could be optimised more, but this is ok. I've just read your next post about transactions viz-a-viz in-memory db's - well I see where you're coming from, but the evidence is clearly in favour of transactions here. My other thought is that opening the db with a filehandle of ":memory:" is perhaps not 100% correct - that it's somehow sneakily going off to disk somewhere - can anyone say? Thanks, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP Tel: +44 (0) 207 085 1088 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 21 December 2005 14:54 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Slowness with in-memory database > > > "CARTER-HITCHIN, David, FM" <[EMAIL PROTECTED]> wrote: > > Hi, > > > > Really hope someone out there can help me with this. > > > > I've written a C++ cache class which extracts ad-hoc data > from a Sybase > > database and inserts it into an in-memory SQLite database. > This is working > > fine but is very slow during the insert data phase. To give some > > quantitative idea of what I mean by slow: > > > > a) If I run the extract with the Sybase isql command line > utility and dump > > that to a file it takes 2/3 minutes (~800,000 rows, 100MB of data). > > b) If I run my cache class with the insert into SQLite > commented out it > > takes 2/3 minutes. > > c) Uncommenting the insert statement, the extract/insert > takes ~45 mins! > > > > Something is misconfigured as it takes ~20 times as long to > insert into an > > in-memory db, as it does to select from Sybase and store to a file. > > > > Any ideas, however slight, will be much appreciated! > > > > Idea 1: You are trashing. What OS are you running and how > much memory > is installed in the machine. > > Idea 2: Might you be doing an insert trigger that is taking > a long time? > > Idea 3: You have about 100 different indices on the table > that all have > to be updated with each insert. > > FWIW, SQLite normally does about 50000 inserts per second, not the 300 > you are seeing. So something is clearly wrong. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > *********************************************************************************** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorised and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbs.co.uk/CBFM http://www.rbsmarkets.com ********************************************************************************
