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
