Re: [sqlite] query 31 days
Khamis Abuelkomboz wrote: Hi How many records contain your table? Your select command scans all the table entries and I guess that the database uses the standard index. try the following command to tell the db which index it should use: select distinct date from mytable where yearmonth = '200508' order by date Sorry I meen select distinct date from mytable where yearmonth = '200508' order by yearmonth , date
Re: [sqlite] query 31 days
Hi How many records contain your table? Your select command scans all the table entries and I guess that the database uses the standard index. try the following command to tell the db which index it should use: select distinct date from mytable where yearmonth = '200508' order by date Another possibility for this slowness is may be your database memory and temp store usage configuration. the standard memory configuration of release 2.8.16 is very slow, I had some performance problems until I had changed the memory usage configuration. I use the following configuration: PRAGMA cache_size = 6000 PRAGMA default_synchronous = OFF PRAGMA synchronous = OFF PRAGMA default_temp_store = MEMORY PRAGMA temp_store = MEMORY PRAGMA show_datatypes = OFF good luk khamis Tom Deblauwe wrote: Hello, I'm using SQLite 2.8.16, on an embedded system, and I have a question. I have a database with one table, containing all my records. Each has a field 'yearmonth' which contains for example '200508', and also a field 'date', which indicates the day of that month. I want now to make an overview of a month and see which days contain a record. I've created a combined index on 'yearmonth' and 'date'. My query to do this is: 'select distinct date from mytable where yearmonth = 200508;' This takes about 50 seconds, which is really slow to show my users(it is displayed on a webpage). I noticed that this takes a long time, because I guess almost all records are checked. My other idea was to do 31 queries(one for every day), like this: 'select ID from mytable where yearmonth = 200508 and date = 1 limit 1;'. This second method does not give me anymore speed improvement. for example: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ... ^ ^ ^ ^ the days marked with '^' contain records. To check with the second method for example day 7, it takes 2.6 seconds. Day 4 takes less, and from day 3 and lower, it is reacting immediately(like 40 ms). Day 8 and higher takes about 1 second each. When I do a random query om my database with select, and want to return 10 results for example, it takes 2.8 seconds. So getting 1 result, or getting 10 results only differs about 0.2 seconds. Is there anything I can do so things go faster? Maybe I should maintain a separate table with the monthoverview, but if it is possible to do some other solution it would be nicer ;-). Thanks for your time, kind regards, Tom, -- Try Code-Navigator on http://www.codenav.com a source code navigating, analysis and developing tool. It supports following languages: * C/C++ * Java * .NET (including CSharp, VB.Net and other .NET components) * Classic Visual Basic * PHP, HTML, XML, ASP, CSS * Tcl/Tk, * Perl * Python * SQL, * m4 Preprocessor * Cobol
Re: [sqlite] query 31 days
Perhaps you could create a table with the results (e.g. columns yearmonth, date, hasrecord=0|1) and create triggers for the main table so that if the records change, the triggers change the value in the result table. Jakub Tom Deblauwe wrote: Hello, I'm using SQLite 2.8.16, on an embedded system, and I have a question. I have a database with one table, containing all my records. Each has a field 'yearmonth' which contains for example '200508', and also a field 'date', which indicates the day of that month. I want now to make an overview of a month and see which days contain a record. I've created a combined index on 'yearmonth' and 'date'. My query to do this is: 'select distinct date from mytable where yearmonth = 200508;' This takes about 50 seconds, which is really slow to show my users(it is displayed on a webpage). I noticed that this takes a long time, because I guess almost all records are checked. My other idea was to do 31 queries(one for every day), like this: 'select ID from mytable where yearmonth = 200508 and date = 1 limit 1;'. This second method does not give me anymore speed improvement. for example: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ... ^ ^ ^ ^ the days marked with '^' contain records. To check with the second method for example day 7, it takes 2.6 seconds. Day 4 takes less, and from day 3 and lower, it is reacting immediately(like 40 ms). Day 8 and higher takes about 1 second each. When I do a random query om my database with select, and want to return 10 results for example, it takes 2.8 seconds. So getting 1 result, or getting 10 results only differs about 0.2 seconds. Is there anything I can do so things go faster? Maybe I should maintain a separate table with the monthoverview, but if it is possible to do some other solution it would be nicer ;-). Thanks for your time, kind regards, Tom, -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.dynavix.com Tel: +420 608 247197 Office: +420 224233470
[sqlite] query 31 days
Hello, I'm using SQLite 2.8.16, on an embedded system, and I have a question. I have a database with one table, containing all my records. Each has a field 'yearmonth' which contains for example '200508', and also a field 'date', which indicates the day of that month. I want now to make an overview of a month and see which days contain a record. I've created a combined index on 'yearmonth' and 'date'. My query to do this is: 'select distinct date from mytable where yearmonth = 200508;' This takes about 50 seconds, which is really slow to show my users(it is displayed on a webpage). I noticed that this takes a long time, because I guess almost all records are checked. My other idea was to do 31 queries(one for every day), like this: 'select ID from mytable where yearmonth = 200508 and date = 1 limit 1;'. This second method does not give me anymore speed improvement. for example: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ... ^ ^ ^ ^ the days marked with '^' contain records. To check with the second method for example day 7, it takes 2.6 seconds. Day 4 takes less, and from day 3 and lower, it is reacting immediately(like 40 ms). Day 8 and higher takes about 1 second each. When I do a random query om my database with select, and want to return 10 results for example, it takes 2.8 seconds. So getting 1 result, or getting 10 results only differs about 0.2 seconds. Is there anything I can do so things go faster? Maybe I should maintain a separate table with the monthoverview, but if it is possible to do some other solution it would be nicer ;-). Thanks for your time, kind regards, Tom,
