Александр Прокофьев 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]
-----------------------------------------------------------------------------
