How to get sqlite-in-memory db to join query as fast as MySQL
I have a very complex sql query - the logic is simple, but I need to join 17 tables (each of 10-20 fields and 100 to 1 million records), so there are many (LEFT) JOINs and WHERE articles.
SELECT table1.column_A
table2.column_B
table3.column_C
table4.column_D
....
FROM table1
LEFT JOIN table2 ON table1.column_a = table2.column_b
JOIN table3 ON table3.column_c = table1.column_d
LEFT JOIN table4.column_e = table3.column_f
AND LENGTH(table4.column_g) > 6 AND (table4.column_h IN (123,234))
LEFT JOIN ....
....
WHERE table1.column_i = 21
AND (table1.column_j IS NULL OR DATE(table1.column_k) <> DATE(table1.column_l))
The above query only takes 5 seconds to run in MySQL. But when I run it in sqlite in-memory db (using Perl on Linux) it takes about 20 minutes. This is still acceptable.
When I add an ORDER BY clause (I need it) the execution time goes up dramatically.
ORDER BY table1.column_m, table6.column_n, table7.column_o IS NULL;
This will take 40 seconds in MySQL. In sqlite in-memory db (using Perl on Linux) I waited over an hour but it still hasn't finished.
What setting do I need to make to make the request faster? My threshold is within 1 hour.
The reason I am doing this in-memory db is because I am getting normalized data generated by SQL, but we need to end up loading data into db without SQL, so I dont want to create intermediate SQL db just to load data - this makes the code ugly and increases maintenance complexity. Also, the current deadline issue I am facing is just a one-off thing. In the future, on a daily basis, the amount of data we receive will be much less (less than 1% of what I have today)
Thanks in advance for your help!
source to share
The ORDER BY clause refers to columns from three different tables. No amount of query optimization or index creation will change the fact that the DBMS has to do the look and feel after (or how) the result set is created. If you have limited the amount of memory that SQLite can use (I am not an expert on SQLite, but I assume that it is at least possible if not required), then this may be the reason (for example, it happens through some incredible shenanigans to do the work within it). Or it just hung up. What CPU usage were you expecting at that hour? How about I / O (this is due to the fact that there was no limit on the amount of memory that SQLite can use as Sinan pointed out)?
source to share
To make your request faster, you need to make some changes to one of the following:
- request recording method
- Database table structure
- creating appropriate indexes
And all of this can be found at http://www.perlmonks.org/?node_id=273952
source to share