[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Very slow query execution with SQLite Database
From:       manohar s <manoharyes () gmail ! com>
Date:       2009-01-28 8:53:21
Message-ID: a2ee36740901280053p1254a492g78ad2ab450556ab9 () mail ! gmail ! com
[Download RAW message or body]

Hi,
 I tried with concatenated index (by dropping an index which was already
present on timestamp_id) It took 12 seconds for completion, so there was no
improvement in speed.

> 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).

It does have a concatenated PK on timestamp_id and metric_id, so it does not
have to do full table scan right?

Regards,
Manohar.S

On Fri, Jan 23, 2009 at 10:31 PM, Michael Schlenker <msc@contact.de> wrote:

> 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: msc@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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[prev in list] [next in list] [prev in thread] [next in thread]