What cache_size have you specified? --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why.
>-----Original Message----- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of Jeff Roux >Sent: Monday, 30 March, 2015 03:46 >To: sqlite-users at mailinglists.sqlite.org >Subject: [sqlite] Performance issue > >Hi everyone, > >I have a daemon that collects information and stores it in a SQLite >database. The table has 1 million rows. > >This daemon is running on a HP server with 12 cores, 32 GB of RAM, >and a SSD drive. I have performance issues with some requests. For >instance, the following request takes more than 5 seconds to >accomplish with SQlite3 (in that particular case, the WHERE clause >selects all the data in the database, i.e. 1000000 rows): > >SELECT SUM(nbBytesDecompOut + nbBytesCompIn) as vol, portLan as item >FROM flows >WHERE timestamp>=1383770600 AND timestamp<=1384770600 AND protocol IN >(17, 6) >GROUP BY portLan ORDER BY vol DESC LIMIT 6; > >I have done some tests with or without "INDEXED BY" clauses and got >nearly the same results. > >I compared the performance with a mySQL and the same request takes >less than 1 second to accomplish. > >Could you give me some directions to optimize this kind of request >with SQlite3 when there is a big amount of data in the table ? I >need to increase 3 times the number of rows and the performance will >become unacceptable for my application. > >Thanks in advance. >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
