Ryan,
I tried just having an index on the fwParameterID - still taking 160
seconds to get 2 parameters for a day.
I tried adding a second index on dateTime - did not appear to change
the speed.
Removed the second index and modified the first index to having both
fwParameterID and dateTime - speed now fast (.008 seconds).
Size of the database has doubled. Going to have to watch this, but
need the speed.
Would storing the dateTime as Integer make the database and indexes
smaller?
Thanks,
Andrew S.
-----Original Message-----
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith
Sent: Thursday, November 19, 2015 7:40 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] 10 minute Avg
On 2015/11/19 4:49 PM, Andrew Stewart wrote:
> Hi,
> Had a question regarding what I am trying to do. One thing that I have
> noticed is that it is slow to do this. I do not have any indexes created and
> there is no primary index on this table.
> I am using a 'DateTime' variable for the date/time. I understand this
> translates to a Numeric. It appears to be getting handled as a string, but
> not sure. Would it be any better if I stored the date/time as a Integer
> (64bit value). This would be using the C routine for generating a date/time
> based upon the __time64 type (number of seconds since jan 1 1970 0:0:0).
While it will technically be faster, there is not much gains by opting for an
INT date, and you will be making it harder to specify intervals and the like.
SQLite's handling of dates are rather fast, so I wouldn't suggest that as a
first optimization. (Perhaps later - if you absolutely need to kill another few
milliseconds).
The first improvements would be proper indices on that table. Not too many,
because that will slow down the Inserts (which I assume you do a lot of), but
enough to allow the Query planner to get the data very fast.
It's hard to guess at the best indices - we usually make all the ones we think
would be needed, then do the queries that we will need, see which of them the
query planner output says it is using, and then drop the other unused indices.
On your table, and to make those queries very fast, my suggestion would be
adding a Primary Key on:
dataStreamRecord(fwParameterID, dateTime)
This assumes A - you are able to recreate the schema and copy the values over,
and B - you will never have more than one entry for the same fwParameter in the
same millisecond. If this is a possibility, then rather simply create a normal
Index and not a Unique/Primary one.
An alternate, if you have some other queries too, is to create separate indices
on fwParameterID and dateTime which can simply be added using this SQL:
CREATE INDEX dStream_fwParam ON dataStreamRecord(fwParameterID); CREATE INDEX
dStream_DTStamp ON dataStreamRecord(dateTime);
That /should/ sort out the speed completely. Let us know if it is still
unacceptably slow.
As an aside, are you using some DB manager to do all this? It might be easier
with some visual designers and one-click schema changes (such as adding a
primary key) using one of those. May I suggest some good free ones such as
SQLitespeed from:
http://www.sqlc.rifin.co.za/ which allows backups to be made instantly
(a must before trying any changes) and visual schema designers if you use
windows, or perhaps DB Browser for SQLite:
https://github.com/sqlitebrowser/sqlitebrowser/ with similar
functionality if you are using Linux / Mac.
Good luck,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information,
intended only for the person(s) named above. If you are not the intended
recipient, you are hereby notified that any disclosure, copying, distribution,
or any other use of this email is strictly prohibited. If you have received
this transmission by error, please notify us immediately by return email and
destroy the original transmission immediately and all copies thereof.
