I am running the query with CursorLocation = 2, CursorType = 2, LockType = 3 to handle 610MB database with 2259207 records in table.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: Tuesday, May 05, 2009 11:45 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage How much RAM is needed to handle 610MB database with 2259207 records in table.Initially I was running Windows web server 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If I run my application once the query returns results in 80 sec and if run it again , memory usage starts growing and reaches nearly 100% ..then I get more hard page faults , the application responds slow, or system just hangs. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: Tuesday, May 05, 2009 11:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage Thanks for your response. The query runs faster than mine but still facing problem after running the page multiple times.So I think I should try with 1GB of RAM. Thanks, -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet Sent: Tuesday, May 05, 2009 9:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage Hi Kalyani, > I have 2259207 records in table using SQLite3 database. I am running > the select query to retrive records from DB > > SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, > MonitoredRef, EventRef,ToState,Priority,Acked from Events WHERE > Events.NotificationTime >= {ts '2009-05-04 14:44:10'} Order By ID > DESC LIMIT 100 > > If I run my application once the query returns results in 80 sec and > if run it again , memory usage starts growing and reaches nearly 100% > ..then I get more hard page faults , the application responds slow, or > system just hangs. I am running my application on Windows web server > 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on > ID and notificationtime (datatype timestamp) Using this is very inefficient: order by ID desc limit 100 since SQLite still has to build and sort the entire result table before limiting it. You are better off to filter using the search (ie where). For instance, if ID is the integer primary key and you don't expect deletes, you could use: SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, MonitoredRef, EventRef, ToState, Priority, Acked from Events where Events.ID >= (select max(ID) from Events) - 100 and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order by ID desc HTH, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml _______________________________________________ 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 _______________________________________________ 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
