Thanks for your reply.
I've tried running versions of query that you suggested and got following
results:
Originally posted version 4.5 sec
Version with only first-part of where clause16 ms
Rewritten version without joins runs in 6.5 seconds
Version with unions runs in 15ms
Creating multi-column index (source.id, target.id) 4.5 sec
Removing all indexes for source.id and target.id4.5 sec
Running query with first part of where clause and no indexes2.5 sec
So it seems that indexes are not used at all, and that is pretty strange
I didn't state it in my original post, but this query was automatically
generated by object relational-mapper, that generates hundreds of similar
queries. I see that replacement of OR with UNION and AND with INTERSECT will
probably solve the problem, but yet it seems that using OR and AND in WHERE
clause is a better way to write queries, especially with complex nested
conditions.
Some information on db:
About 1500 records in each table
Original query returns about 10 rows
Any suggestions on why indexes are not used and how to make SQLite use them
for this query?
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 02, 2007 6:58 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] FW: Performance problem with complex where clause
Александр Прокофьев wrote:
> I'm running a query against sqlite database and it runs very slow - about
5
> seconds(on larger database it can ran for a whole minute).
>
>
>
> Select [Node_Entity].* From [Node] AS [Node_Node] JOIN [Entity] AS
> [Node_Entity] ON [Node_Node].LOCAL_ID = [Node_Entity].LOCAL_ID , [Link] AS
> [Link_Link] JOIN [Entity] AS [Link_Entity] ON [Link_Link].LOCAL_ID =
> [Link_Entity].LOCAL_ID
>
>
>
> Where ((
>
> (([Link_Link].[Target.Id]='06d15df5-4253-4a65-b91f-cca52da960fe') AND
> ([Link_Link].[Source.Id]=[Node_Entity].[Id]))
>
>
>
> OR (([Link_Link].[Target.Id]=[Node_Entity].[Id]) AND
> ([Link_Link].[Source.Id]='06d15df5-4253-4a65-b91f-cca52da960fe'))
>
> ))
>
>
>
You have an unnecessarily complicated query. First I re-wrote your query
using standard quotes and eliminated the unnecessary quotes and
brackets. I also replaced the alias names with something shorter to make
the existing query clearer.
Select ne.*
From Node AS n
JOIN Entity AS ne ON n.LOCAL_ID = ne.LOCAL_ID
JOIN Link AS l
JOIN Entity AS le ON l.LOCAL_ID = le.LOCAL_ID
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Source.Id" = ne.Id
OR l."Target.Id" = ne.Id
AND l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
Now it is clear that you are joining two tables that are not referenced
by the query at all. The node table and the second join on the entity
table (which you called link entity) are not needed. After these are
removed you have an equivalent but much simpler query. Note, I also
rearranged the terms in the second half of the OR clause to be in the
same order as the first half.
Select ne.*
From Entity AS ne
Join Link AS l
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Source.Id" = ne.Id
OR l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Target.Id" = ne.Id
It might be clearer to yet separate the two halves of this query and
combine the results using a union.
Select ne.*
From Entity AS ne
Join Link AS l on l."Source.Id" = ne.Id
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
UNION
Select ne.*
From Entity AS ne
Join Link AS l on l."Target.Id" = ne.Id
Where l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
This should run in a reasonable time given that you have indexes on
Link("Target.Id") and Link("Source.Id")
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
