RE: [sqlite] FW: Performance problem with complex where clause

[Александр Прокофьев]

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 clause    16 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.id        4.5 sec
Running query with first part of where clause and no indexes    2.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]
-----------------------------------------------------------------------------