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'))
))
If I remove a part of where clause
" OR (([Link_Link].[Target.Id]=[Node_Entity].[Id]) AND
([Link_Link].[Source.Id]='06d15df5-4253-4a65-b91f-cca52da960fe'))"
It runs in 16 ms. All .Id fields are indexed.
For me it looks like a bug in optimizer or like created indexes are not used
for some reason
Database file with sample data can be downloaded from here -
http://slil.ru/24319807 (350 kb)
Database structure is
CREATE TABLE [Entity] (LOCAL_ID bigint NOT NULL , CHANGE_TYPE tinyint NULL ,
[Id] UNIQUEIDENTIFIER NULL , :);
CREATE INDEX [IEntity_Id] ON [Entity]([Id]);
CREATE INDEX [IEntity_Version.Time] ON [Entity]([Version.Time]);
CREATE INDEX [IEntity_Version.ChangedBy.Id] ON
[Entity]([Version.ChangedBy.Id]);
CREATE INDEX [IEntity_Type.Id] ON [Entity]([Type.Id]);
CREATE UNIQUE INDEX [IEntity_LOCAL_ID] ON [Entity](LOCAL_ID);
CREATE TABLE [Link] (LOCAL_ID bigint NOT NULL , [Source.Id] UNIQUEIDENTIFIER
NULL , [Source.Id$C] bit NOT NULL DEFAULT 0 , [Target.Id] UNIQUEIDENTIFIER
NULL , [Target.Id$C] bit NOT NULL DEFAULT 0);
CREATE INDEX [ILink_Source.Id] ON [Link]([Source.Id]);
CREATE INDEX [ILink_Target.Id] ON [Link]([Target.Id]);
CREATE UNIQUE INDEX [ILink_LOCAL_ID] ON [Link](LOCAL_ID);
CREATE TABLE [Node] (LOCAL_ID bigint NOT NULL , [NodeName] NTEXT NULL , :);
CREATE INDEX [INode_Owner.Id] ON [Node]([Owner.Id]);
CREATE UNIQUE INDEX [INode_LOCAL_ID] ON [Node](LOCAL_ID);
