Hi Igor, I'm working on providing those data, meanwhile I have to emphasize on how those tables are created, maybe there is something here :
The idea is that i run the application using a given database. I want this database to be untouched as for its data, so i'm making a clone of it in memory. Then I create views, let's call them viewA, viewB, viewC and viewD with the statement CREATE VIEW etc... Then I create real tables in the in-memory clone that contain the content of the views : INSERT INTO tableA SELECT * from viewA This way I can work on real tables, without altering my original database. I'm trying to give as much intel as I can, and maybe the fact that I am using views a bit, and creating real tables thanks to those views, maybe there is a performance issue I'm not aware of or something like that. 2016-09-07 17:39 GMT+02:00 Igor Korot <ikoro...@gmail.com>: > 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 > -- 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
