On 2015/11/19 6:06 PM, Andrew Stewart wrote: > 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?
Congrats with the speed. An Index is basically an ordered copy of a column or set of columns (ok, it's a bit more technical than that, but it paints the picture). This means that for the two columns you have indexed, you've essentially created another list with that same 2-columns worth of data, but which is ordered (or B-Tree adapted if you will). Knowing this you can easily calculate future data size needs. Again, yes the INT keys will be technically smaller, if the reduction is worth the effort. I can make some assumptions here (but I don't really know, maybe a dev can chime in here): A date will take around 19 bytes to store (assuming your DB is set to UTF-8) plus some overhead bytes. An Int might take anywhere from 2 to 8 bytes, perhaps mostly 8 for time numbers and some overhead. I believe it is reasonable to think the storage requirement for at least the time part of the key could fall by more than 50%, so the reduction in overall Index size might be 30-40-ish percent. You will however also gain the size from the original column storage when changing to INT timestamps, so size reduction of the DB as a whole might well be worth it. If space is an absolute must, then remove the Index, add a primary key on those two columns (essentially what you now have as an index) and then declare the table WITHOUT ROWID. See: https://www.sqlite.org/withoutrowid.html That will result in the smallest size DB. > > Thanks, > Andrew S. > > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at 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. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
