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
