Hi, Laura, On Wed, Sep 7, 2016 at 10:48 AM, Laura BERGOENS <laura.bergo...@imerir.com> wrote: > Note : I had some real queries that use to take 100 seconds to execute, and > I optimized them myself. > It looks like this : > > I have 4 tables: tableA, tableLink, tableC and tableD > tableA, Link and C have no more than 10k rows in it, and tableD around 50k > (which is not big at all right?) > The query goes like this : > SELECT DISTINCT A1.idA, A1.column1, A1.column2 > FROM tableA A1, > tableA A2, > tableLink link, > tableC C1, > tableD D1, > tableD D2, > WHERE C1.idA = A1.idA > AND C1.idD = D1.idD > AND A1.idD = D1.idD > AND A1.column2 = 'VALUE' > AND A2.idA = link.id_item_1 > AND A1.idA = link.id_item_2 > AND D2.idD = A2.idD > AND A2.idA = 100 > > This query takes 100 seconds approx. > I don't know if that can help you in any way, but the tables have been > created with a query like INSERT INTO SELECT * FROM A_View, so they have > been created from a view.
After populating the data, do create any indexes? Also, I presume that all 4 tables are created like this, not just tableA, right? BTW, if you can show the schema (no data necessary) for those 4 tables, we can see if there is a way to improve. Thank you. > As we can see, only columns from tableA A1 are selected, so most of the > joins here can be replaced with something of the form : > AND EXISTS (SELECT 1 FROM .... "test join") > > I managed to drastically reduce time execution on this query with the > EXISTS trick, and now it has a normal time execution (below 300 ms for > sure, can't tell you how much exactly) > > > I've check the EXPLAIN QUERY PLAN of the original query, and I understood > that I was scanning tables in nested loops, so that this can take some time > (in fact the product of the sizes of all the tables in the FROM clause > right?) > > Here is what i can give you for now, now i'll do some tests that you've > recommended earlier > > 2016-09-07 16:33 GMT+02:00 R Smith <rsm...@rsweb.co.za>: > >> >> On 2016/09/07 4:20 PM, Laura BERGOENS wrote: >> >>> Hi Mr. Slavin, >>> >>> As for why the query takes so long, I do know the answer ! >>> Long story short, my application does a lot of calculation and things, I >>> don't want to get into the details here, but queries are built and >>> auto-generated piece by piece. Therefore, sometimes the queries aren't >>> optimized at all. >>> I solved that issue myself by touching up the queries a bit before >>> executing them, and everything is fine now. >>> I was concerned mainly because I figured that maybe some queries were a >>> bit >>> longer to execute as they should have (let's say 200 ms instead of 100 >>> ms), >>> and I'm running a lot of queries in the app (approx 1000 per seconds). >>> >> >> Magic goalposts... >> >> "A query" taking 100s is a VERY VERY different problem to 1000 queries >> taking 100ms each. And to get technical, you shouldn't really need to >> optimize the queries (apart from avoiding the obvious silliness), you only >> need to know how to ask for the data correctly. Optimization is the job of >> the query planner in the DB engine - it should get the best fastest query >> results possible as long as it has all the information (which is what >> ANALYZE will do as others mentioned already) and as long as you provide the >> best Index for the job (which is something we might have some suggestions >> on if we know the schema and typical query is). >> >> >> I know now that there is nothing to worry about regarding my settings or >>> pragmas choices, since I get the same execution time with the sqlite3 tool >>> >> >> Maybe nothing to worry about, but that is no reason to leave it be - >> whatever the case is, if some previous version of SQLite can run it in >> 1/10th the time, there MUST be opportunity for improvement. >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Laura BERGOENS > Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan > > *Institut Méditerranéen d'Étude etde Recherche en Informatique* > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
