Hi, I am working with a project which uses SQLite as the database to store collected data. I am issuing the following query to retrieve data from the table. I noticed this query is taking more time as the number of records in the table increases. Is there a better way to write this query? any help is appreciated.
Query: SELECT SUM(metric_diff) FROM snapshot_master WHERE snapshot_master.metric_id = ? AND snapshot_master.timestamp_id in (select timestamp_id from timestamp_master where server_timestamp >= ? AND server_timestamp <= ? and strftime(?, server_timestamp, 'unixepoch') = strftime(?, ?, 'unixepoch')); Format string may vary depending on context. Table schema: CREATE TABLE [snapshot_master] ( [timestamp_id] INTEGER NOT NULL, [metric_id] INTEGER NOT NULL, [metric_now] TEXT, [metric_diff] TEXT, PRIMARY KEY (metric_id, timestamp_id)) CREATE TABLE [timestamp_master] ( [timestamp_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, [server_timestamp] INTEGER, [server_start_time] INTEGER, [server_uptime] INTEGER, [server_uptime_diff] INTEGER, [server_is_connected] INTEGER) Regards, Manoj _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
