manohar s schrieb:
> I have a SQLite database which is of size 1.5 GB. The problem that it is
> taking a lot of time (12 seconds after execution of vacuum) to execute a *
> SELECT* query.
> 
> Query  :
>       SELECT metric_id, MAX(timestamp_id) AS timestamp_id_max FROM
> snapshot_master GROUP BY metric_id
> 
> I do not understand why this query has to take 12 seconds?? I tried to
> optimize by creating more indexes but no luck.
> BTW, before execution of vacuum, time taken for Query to execute is aprrox.
> 17 minutes(1074890649319 nano seconds)
> 
> I am using sftp_profile for finding out the time.
> 
> 1) What am I doing wrong here?
> 2) Is there any problem in the database design?

If we might assume your table is 1.5 GB in size and you do that query you
have to do a full table scan for it. (unless SQLite can use the PK index if
it exists to speed it up).

So your probably I/O bound, 1.5 GB in 12 seconds is about 130MB/s, sounds
like full read speed of a current harddisk.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH           Tel.:   +49 (421) 20153-80
Wiener Straße 1-3               Fax:    +49 (421) 20153-41
28359 Bremen
http://www.contact.de/          E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to