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.
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
