Greetings!
Pardon the long message. First, I hope you and all yours are well, and as soon as you read this post, please go and wash your hands. :-)
Long story short, I created a tool that reports on projects. They have an AccessDB that is slow, but I created an SQLite tool to make my own reports, etc. All is working great, but I have a few queries that are getting slower and slower. I run ANALYZE every Sunday night, and it's still just as slow.
A little history...<br>
We export data in Excel from a global project's tool, and we import only those that belongs to our department into and use SQLite to report on various criteria. As things were running well, they requested more reports, and now, daily exports and imports, to the point that we are importing daily and, sometimes two or three times in a day. Imagine this scenario...
```
create table t0 (a, b, c, d, e, idate, PRIMARY KEY (a, idate));
insert into t0 (a, b, c, d, e, idate) values ('p001', 'Project I', 1000.0, 'a', 3, '2018-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'Project II', 2039., 'a', 3, '2018-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p003', 'Project III', 30023.0, 'b', 4, '2018-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p001', 'Project I', 1010.0, 'b', 4, '2018-03-01');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'Project II', 2039.0, 'c', 5, '2018-03-01');
insert into t0 (a, b, c, d, e, idate) values ('p003', 'Project III', 30023.0, 'd', 5, '2018-03-01');
...
...
insert into t0 (a, b, c, d, e, idate) values ('p001', 'Project I', 900.0, 'e', 5, '2020-03-20');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'Project II', 3423.0, 'd', 6, '2020-03-20');
insert into t0 (a, b, c, d, e, idate) values ('p003', 'Project III', 40000.0, 'a', 6, '2020-03-20');
insert into t0 (a, b, c, d, e, idate) values ('p001', 'Project I', 900.0, 'f', 7, '2020-03-21');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'Project II', 3500.0, 'g', 6, '2020-03-21');
insert into t0 (a, b, c, d, e, idate) values ('p003', 'Project III', 40000, 'h', 6, '2020-03-21');
```
As you can see, these import change a few time in the life of the project, but not daily. There are also about 56 fields, and I have thought of optimizing the data, but that would take lots of code changes, and extra effort. Although, that may need to be done in the future, but for right now, I just want to be able to minimize the query response time. I have also set an INDEX for a and idate:
```
CREATE INDEX IF NOT EXISTS a_idate_idx on t0 (a,idate);
```
The queries are related to searches on values other than a or idate. For example,
```
SELECT * FROM t0 as rec
WHERE rec.a IN
(
SELECT a FROM t0 WHERE 1=1
AND lower(rec.b) LIKE '%ioc-net%'
)
AND
rec.idate =
(SELECT MAX(idate) FROM t0 WHERE a = rec.a)
GROUP BY rec.a;
```
When I run this query on the real data, this is the result of .timer on:
```
Run Time: real 243.291 user 240.125000 sys 0.765625
```
I have tried to use .expert, but it tells me that (no new indexes) are needed. Right now the total number of records for t0 is 56984. Yearly, we do about 140 projects per year. So, there is a lot of duplicated data assigned to a, and idate. Any thoughts on how to make this scenario faster? Thanks for your help.
josé