Hi

I don't see what version of FB are you using.but:



Instead Word_1.data LIKE 'SCHAAF%' I would use Word_1.data STARTING with
'SCHAAF'



Regards,

Paul





From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of swestner
Sent: Saturday, August 02, 2008 4:37 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Query runs 9 minutes - how to set indexes to
optimize it



Hello,

we use Delphi and Bold For Delphi to access a Firebird-DB. Bold is an
OR-mapper whichs converts our OCL into SQL.

In our program we do a complex search which produces the following
SQL:

SELECT IwadisObje_1.BOLD_ID, IwadisObje_1.BOLD_TYPE
FROM IwadisObject IwadisObje_1 JOIN Word Word_1 ON
(IwadisObje_1.BOLD_ID = Word_1.indexedObject)
WHERE (Word_1.data LIKE 'SCHAAF%')
AND ((IwadisObje_1.BOLD_TYPE = 132) or (IwadisObje_1.BOLD_TYPE =
131))
AND (NOT(((SELECT COUNT(*)
FROM GlobalContext GlobalCont_1 JOIN IwadisObject IwadisObje_2 ON
(GlobalCont_1.BOLD_ID = IwadisObje_2.prototypeGlobalcontext)
WHERE IwadisObje_1.BOLD_ID = IwadisObje_2.BOLD_ID
) > 0)))
AND (NOT(EXISTS (SELECT User__1.BOLD_ID
FROM User_ User__1 JOIN BannedObjects_User BannedObje_1 ON
(BannedObje_1.bannedUsers = User__1.BOLD_ID), IwadisObject
IwadisObje_3, BusinessClassesRoot BusinessCl_1
WHERE IwadisObje_1.BOLD_ID = IwadisObje_3.BOLD_ID
AND BannedObje_1.bannedObjects = IwadisObje_3.BOLD_ID
AND User__1.BOLD_ID = BusinessCl_1.BOLD_ID
AND (BusinessCl_1.iwadisID = 'CBE46788-4495-43C0-B130-E050040598E2')
)))
AND (NOT((EXISTS (SELECT User__2.BOLD_ID
FROM User_ User__2 JOIN IwadisObject IwadisObje_4 ON (User__2.BOLD_ID
= IwadisObje_4.privateUser)
WHERE IwadisObje_1.BOLD_ID = IwadisObje_4.BOLD_ID
) and EXISTS (SELECT User__3.BOLD_ID
FROM User_ User__3 JOIN IwadisObject IwadisObje_5 ON (User__3.BOLD_ID
= IwadisObje_5.privateUser) JOIN BusinessClassesRoot BusinessCl_2 ON
(User__3.BOLD_ID = BusinessCl_2.BOLD_ID)
WHERE IwadisObje_1.BOLD_ID = IwadisObje_5.BOLD_ID
AND (BusinessCl_2.iwadisID <> 'CBE46788-4495-43C0-B130-E050040598E2')
))))
AND (not(((IwadisObje_1.visibility <> 2) and (not(EXISTS (SELECT
Client_1.BOLD_ID
FROM Client Client_1 JOIN IwadisObject IwadisObje_6 ON
(Client_1.BOLD_ID = IwadisObje_6.creatorClient) JOIN
BusinessClassesRoot BusinessCl_3 ON (Client_1.BOLD_ID =
BusinessCl_3.BOLD_ID)
WHERE IwadisObje_1.BOLD_ID = IwadisObje_6.BOLD_ID
AND ((((((((((((((((((((BusinessCl_3.iwadisID = '68D3C5EE-8EDC-4E5C-
BA90-88D495596910') or (BusinessCl_3.iwadisID = '9B58C591-3FF4-4E14-
A9E0-F2EE49F4844E')) or (BusinessCl_3.iwadisID = '8374F54C-A890-4537-
B343-A7F55C5DC060')) or (BusinessCl_3.iwadisID = 'C8A7D593-B28B-4A31-
94A6-E6C7367CA8CF')) or (BusinessCl_3.iwadisID = '6B590600-C19C-4678-
B073-5BE22776C744')) or (BusinessCl_3.iwadisID = '549E4270-5532-48EF-
9311-61D3EA92F22E')) or (BusinessCl_3.iwadisID = '345F364D-0B9B-4A51-
B5B7-376F467A168D')) or (BusinessCl_3.iwadisID = 'CD4A6A54-C0D1-4344-
B2B8-243AF7173A7A')) or (BusinessCl_3.iwadisID = 'F8C410C0-DE0D-4536-
A72C-37AA8D6972DB')) or (BusinessCl_3.iwadisID = '23B7773C-1049-4761-
BE46-8734B359E309')) or (BusinessCl_3.iwadisID = 'BE3D0C31-E882-4EF5-
9B4A-7E9EE2159E9C')) or (BusinessCl_3.iwadisID = 'B12387B7-9B46-4EAB-
B2CD-EA6B99465611')) or (BusinessCl_3.iwadisID = '158E33BE-9270-408A-
AE9F-D986661C780F')) or (BusinessCl_3.iwadisID = '35E449B2-C973-478D-
A31C-F4FE6572B60E')) or (BusinessCl_3.iwadisID = '3C3162B7-B8E1-4A17-
B975-EE0C5DBABA63')) or (BusinessCl_3.iwadisID = 'D57BAD0A-821B-4155-
9B38-1061DDA5A67B')) or (BusinessCl_3.iwadisID = 'E0B9D7F7-48CA-4DB9-
A8B8-DD5AC9D9F9AE')) or (BusinessCl_3.iwadisID = '93A29955-FC9D-4A36-
9AB1-6BA572B44B82')) or (BusinessCl_3.iwadisID = '9BF80633-0541-4B4D-
A925-89881546C5AB')) or (BusinessCl_3.iwadisID = '21DAA5BB-BBCB-4885-
9CD4-230C004D8F82'))
))))))
AND (NOT(EXISTS (SELECT User__4.BOLD_ID
FROM User_ User__4 JOIN BannedObjects_User BannedObje_2 ON
(BannedObje_2.bannedUsers = User__4.BOLD_ID), IwadisObject
IwadisObje_7, BusinessClassesRoot BusinessCl_4
WHERE IwadisObje_1.BOLD_ID = IwadisObje_7.BOLD_ID
AND BannedObje_2.bannedObjects = IwadisObje_7.BOLD_ID
AND User__4.BOLD_ID = BusinessCl_4.BOLD_ID
AND (BusinessCl_4.iwadisID = 'CBE46788-4495-43C0-B130-E050040598E2')
)))
AND (NOT((EXISTS (SELECT User__5.BOLD_ID
FROM User_ User__5 JOIN IwadisObject IwadisObje_8 ON (User__5.BOLD_ID
= IwadisObje_8.privateUser)
WHERE IwadisObje_1.BOLD_ID = IwadisObje_8.BOLD_ID
) and EXISTS (SELECT User__6.BOLD_ID
FROM User_ User__6 JOIN IwadisObject IwadisObje_9 ON (User__6.BOLD_ID
= IwadisObje_9.privateUser) JOIN BusinessClassesRoot BusinessCl_5 ON
(User__6.BOLD_ID = BusinessCl_5.BOLD_ID)
WHERE IwadisObje_1.BOLD_ID = IwadisObje_9.BOLD_ID
AND (BusinessCl_5.iwadisID <> 'CBE46788-4495-43C0-B130-E050040598E2')
))))
AND (not((((IwadisObje_1.visibility = 0) and (not(EXISTS (SELECT
Role__1.BOLD_ID
FROM Role_ Role__1 JOIN RoleObjectsHolder RoleObject_1 ON
(Role__1.BOLD_ID = RoleObject_1.role_), IwadisObject IwadisObje_10,
BusinessClassesRoot BusinessCl_6
WHERE IwadisObje_1.BOLD_ID = IwadisObje_10.BOLD_ID
AND RoleObject_1.BOLD_ID = IwadisObje_10.holder
AND Role__1.BOLD_ID = BusinessCl_6.BOLD_ID
AND
((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((BusinessCl_6.iwadisID
= '1D8FCCC3-52C3-4292-B21E-900BEBFDB61E') or (BusinessCl_6.iwadisID
= 'A6DA4CC6-4514-4313-89A5-78B5C6DC7594')) or (BusinessCl_6.iwadisID
= 'E7E017C9-C0ED-4F00-877E-87A73C7E82E2')) or (BusinessCl_6.iwadisID
= 'E7F945BE-0821-478B-8624-A67158C60E7F')) or (BusinessCl_6.iwadisID
= 'B10E4580-B162-4827-96C6-99F582CE0632')) or (BusinessCl_6.iwadisID
= 'EC68D385-42C3-4D6D-BFB6-63A74C0DB0E2')) or (BusinessCl_6.iwadisID
= 'BDA02AC6-D306-4900-A566-5C9CCC6D7CCA')) or (BusinessCl_6.iwadisID
= '607D9D60-5049-450A-BFB9-D5E3CB3BA10A')) or (BusinessCl_6.iwadisID
= 'E5EF26D2-1C49-41B6-A695-C93A5047CE88')) or (BusinessCl_6.iwadisID
= '00FB5425-5E14-4467-AEBE-D03ACC723ACC')) or (BusinessCl_6.iwadisID
= 'AB1C6F2D-01C4-42D4-87FC-081CD0B74863')) or (BusinessCl_6.iwadisID
= 'A879E175-CF15-4F8E-80EE-E1A14A4650BE')) or (BusinessCl_6.iwadisID
= '26F0266C-D566-44A6-8048-287F5DBC9AF2')) or (BusinessCl_6.iwadisID
= 'E21C6F0E-DB18-40E2-9891-A55D35FE4A70')) or (BusinessCl_6.iwadisID
= 'FF7080A2-3274-496F-A63F-367E0C22E29A')) or (BusinessCl_6.iwadisID
= '5939A987-0610-4310-ADDB-EC3D2220FE3D')) or (BusinessCl_6.iwadisID
= 'DD8F8E69-46E2-4D6B-9E6E-F1A9ABEAEC85')) or (BusinessCl_6.iwadisID
= '64BA4B03-AD83-4D0A-BDE1-CA04CD7357AB')) or (BusinessCl_6.iwadisID
= '6AF6A0F6-0D56-4B36-BA3B-265985436C67')) or (BusinessCl_6.iwadisID
= '9FEAF7CE-9D1C-4D85-A5DD-24C46FCBF0A1')) or (BusinessCl_6.iwadisID
= 'F29E8765-42F0-4C15-AECC-581FC7D83869')) or (BusinessCl_6.iwadisID
= 'C0DE8AD1-A0B5-4B6D-B493-94FB069074B5')) or (BusinessCl_6.iwadisID
= '0F4D6DE8-5DE9-4BF4-9E0C-A9DF0CD15E5C')) or (BusinessCl_6.iwadisID
= '201CA2F1-4F47-4528-9D53-F7CB5C848019')) or (BusinessCl_6.iwadisID
= '53C302DE-83FF-448F-9FFD-6A4A865B570D')) or (BusinessCl_6.iwadisID
= 'C7465A78-9C8D-44D1-B9A9-EF6A29DCDBDB')) or (BusinessCl_6.iwadisID
= '6693B56C-5B29-4EEC-BCC7-0975F4655BDC')) or (BusinessCl_6.iwadisID
= 'E66E7E6B-2B1E-4387-9CE1-CFAF9A86EA71')) or (BusinessCl_6.iwadisID
= '3AAB53ED-8649-4EDD-AE83-A0AF2EBAED80')) or (BusinessCl_6.iwadisID
= '6E5802E5-D4C7-47E3-9A2E-D7FD5057D25F')) or (BusinessCl_6.iwadisID
= 'BF5B75B7-C8B9-4966-B981-9B27990CFF2E')) or (BusinessCl_6.iwadisID
= '4578DD58-6960-4C3C-8FC0-E644FBE819E5')) or (BusinessCl_6.iwadisID
= 'E782A2CF-8915-47A7-B72E-3B40808584AE')) or (BusinessCl_6.iwadisID
= 'AF7677D3-02C3-44AD-A939-445DEC9C4214')) or (BusinessCl_6.iwadisID
= '17EFE29A-5457-4777-A89E-78B373EF56FB')) or (BusinessCl_6.iwadisID
= 'AE51FCE0-7907-4CCD-8667-E240CE2FC2BD')) or (BusinessCl_6.iwadisID
= 'ED57E4DF-4521-4926-8051-B1F9187C0E5A')) or (BusinessCl_6.iwadisID
= 'D87EC895-928C-41AE-B3ED-66E75C48B8A0')) or (BusinessCl_6.iwadisID
= 'AD3957DC-CAE0-4ABA-9A8A-0A47C496C4BB')) or (BusinessCl_6.iwadisID
= '40639C06-573B-45C8-8C3B-7DA5D5994982')) or (BusinessCl_6.iwadisID
= '4907B94B-4B60-4A46-82EF-E05B30C3AAF9')) or (BusinessCl_6.iwadisID
= '37D79ABE-3865-4B22-97DF-C2E17639C511')) or (BusinessCl_6.iwadisID
= 'C5C86613-F424-4742-A9C0-8C260C113FEF')) or (BusinessCl_6.iwadisID
= '87681A33-C766-4999-B677-52116D5831D5')) or (BusinessCl_6.iwadisID
= 'C7F8E9310F014095B893F46497371C37')) or (BusinessCl_6.iwadisID
= '86010DB3-9D2A-4DE6-ABBA-3B2C4B32801A')) or (BusinessCl_6.iwadisID
= '3AFD66B8-8E07-4FE8-9068-AB9A4D06FB74')) or (BusinessCl_6.iwadisID
= '10CB94F5-7977-4CE5-BF5F-7A8AB8183024')) or (BusinessCl_6.iwadisID
= '084A1633-5A6C-4561-98F4-426CCD62C677')) or (BusinessCl_6.iwadisID
= '6F3CA423-16C6-493C-8D2C-1D18CDEAE34E')) or (BusinessCl_6.iwadisID
= '90CFD874-56B3-4C7F-8968-469C612BC045')) or (BusinessCl_6.iwadisID
= '3222066A-16DF-4768-828E-BDB6C96C2CE4')) or (BusinessCl_6.iwadisID
= '4CD045BA-81E4-480B-BE90-C62D4FB51392')) or (BusinessCl_6.iwadisID
= '2B416D72-5839-4053-8BA4-DE543891B8F9')) or (BusinessCl_6.iwadisID
= '33D714D8-A777-4A27-B263-88488981F654')) or (BusinessCl_6.iwadisID
= '4C77CAE8-2ADA-46A1-9024-FCF2FE75FCF2')) or (BusinessCl_6.iwadisID
= 'B725665E-9C93-4AD4-8E51-C94A40CE929B')) or (BusinessCl_6.iwadisID
= '7FDF3D63-16FE-49F3-AFC2-1BCA1EB790E0')) or (BusinessCl_6.iwadisID
= 'F0CEA3D6-9B5A-4C9F-90F9-DA2F263FAF69')) or (BusinessCl_6.iwadisID
= 'C05AC12B-FAB0-4348-9AB4-4BD0C7F6F242')) or (BusinessCl_6.iwadisID
= '6261349D-7939-4AE2-87A4-4C4E280D6551')) or (BusinessCl_6.iwadisID
= '7B41C74F-27DA-4A97-AC88-17C7420D1512')) or (BusinessCl_6.iwadisID
= '00077ED1-63BB-493F-9705-EEE2D79DB638')) or (BusinessCl_6.iwadisID
= 'BB0432F1-55E8-4870-B53E-48FD2A7D335A')) or (BusinessCl_6.iwadisID
= 'D8D39BAA-AD5E-4DDB-906C-F815104CAB40')) or (BusinessCl_6.iwadisID
= 'FB287846-C77C-4F79-9095-9ABD8C196C7F')) or (BusinessCl_6.iwadisID
= '8C335F4A-ED01-49A8-8233-3CEC9CC7B2C3')) or (BusinessCl_6.iwadisID
= 'CB2DFB0F-A0F9-48BE-ADFC-15D3824EF763')) or (BusinessCl_6.iwadisID
= '6DEE8CCB-FFCF-4676-9826-B53AE784A523')) or (BusinessCl_6.iwadisID
= 'AA01F3AD-9BA3-4311-9782-427DD88FCDB2')) or (BusinessCl_6.iwadisID
= 'EAAE48F9-E476-4B1F-BAF5-CF005F36EC8C')) or (BusinessCl_6.iwadisID
= '53C16C0A-8853-4EF3-8708-30DC973FBC51')) or (BusinessCl_6.iwadisID
= '2BE80EC4-C112-457E-9FA6-3FF2468949DF')) or (BusinessCl_6.iwadisID
= '0492C3CF-F678-4D44-880C-4ACFE403A219')) or (BusinessCl_6.iwadisID
= 'B39FB5E5-40CD-40C3-8003-ED6CEB85AA0C')) or (BusinessCl_6.iwadisID
= '819774B5-74B8-41C6-AA60-EC3B4BE2406A')) or (BusinessCl_6.iwadisID
= '6641A220-503E-4519-AB18-399651037FD7')) or (BusinessCl_6.iwadisID
= '9E81801515404AA6932B96612D2D63BA')) or (BusinessCl_6.iwadisID
= 'E821C777-368F-453E-841C-610AA01D5A8C')) or (BusinessCl_6.iwadisID
= '76EE9995-FCF6-417F-8001-C311A66C17D2')) or (BusinessCl_6.iwadisID
= '6A67CE80-E070-4C8A-8080-31DEFDDBC8FD')) or (BusinessCl_6.iwadisID
= 'E45E2991-9BEF-47DC-B2F6-E560522350FF')) or (BusinessCl_6.iwadisID
= '120860AC98A1499D8C3FF1F6788F9D94')) or (BusinessCl_6.iwadisID
= '50838DC8-9E59-4F71-A259-8A7368697128')) or (BusinessCl_6.iwadisID
= '2032B721-9196-4FCD-A7F6-2FA72B40B94E')) or (BusinessCl_6.iwadisID
= 'B10E3DA8-999D-4B44-AEA2-231BCAD71AD9')) or (BusinessCl_6.iwadisID
= '97CC84C6-1222-4193-BD63-A9B0968632D7')) or (BusinessCl_6.iwadisID
= '9ED8B0EE-7516-4CB1-A10E-EE9D6D8B9918')) or (BusinessCl_6.iwadisID
= 'EC8E3410-475E-4203-9CB4-F33E3AA216E2')) or (BusinessCl_6.iwadisID
= 'A3A3D81D-A947-4E97-BA46-EB65B1408361')) or (BusinessCl_6.iwadisID
= '93D1FD77-1E5C-411C-ACE3-E6B0E0CFE2DD')) or (BusinessCl_6.iwadisID
= '24347D49-C62D-4FB4-B3BE-5CC42897F139')) or (BusinessCl_6.iwadisID
= 'BF99E0F5-68AB-429E-A345-8DA0D4005A0D')) or (BusinessCl_6.iwadisID
= '00107FCC-B8B2-4DD9-8807-AD229C2D5B43')) or (BusinessCl_6.iwadisID
= 'FC41E093-7922-43F0-AC23-FD3E4952BDB8')) or (BusinessCl_6.iwadisID
= '20C6214F-633A-44DC-BCFE-E63E085DA1E9')) or (BusinessCl_6.iwadisID
= 'A417B40B-93D4-4BFF-900F-46CB8BD2EF4D')) or (BusinessCl_6.iwadisID
= '595819D9-469D-4534-ADD8-A5D498F6D017')) or (BusinessCl_6.iwadisID
= '1960BD48-4D41-4BC1-A233-4AEAB2A5FFF9')) or (BusinessCl_6.iwadisID
= '3F50889F-82C0-4B6B-B430-DB60038AE922')) or (BusinessCl_6.iwadisID
= 'E1DFBE2B-8478-4071-BB73-E212EA31F08B')) or (BusinessCl_6.iwadisID
= 'C7F507F760C44568A54D0C23D978B0BA')) or (BusinessCl_6.iwadisID
= '37EC3961D21E47218CA194737B3D1CDC')) or (BusinessCl_6.iwadisID
= '1C24958771BA4B6B9673EAF8C6FD2CE3')) or (BusinessCl_6.iwadisID
= '1CC3C5904C94496F8DA8E486DE441672')) or (BusinessCl_6.iwadisID
= '1C874E461F65434FA02B7ACA7C0B70BA')) or (BusinessCl_6.iwadisID
= '57F1B01619974857B8D3E8C6A46BE6B5'))
)))) and (not(EXISTS (SELECT Role__2.BOLD_ID
FROM Role_ Role__2 JOIN IwadisObject_Role IwadisObje_11 ON
(IwadisObje_11.coworkerRoles = Role__2.BOLD_ID), IwadisObject
IwadisObje_12, BusinessClassesRoot BusinessCl_7
WHERE IwadisObje_1.BOLD_ID = IwadisObje_12.BOLD_ID
AND IwadisObje_11.coworkerObjects = IwadisObje_12.BOLD_ID
AND Role__2.BOLD_ID = BusinessCl_7.BOLD_ID
AND
((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((BusinessCl_7.iwadisID
= '1D8FCCC3-52C3-4292-B21E-900BEBFDB61E') or (BusinessCl_7.iwadisID
= 'A6DA4CC6-4514-4313-89A5-78B5C6DC7594')) or (BusinessCl_7.iwadisID
= 'E7E017C9-C0ED-4F00-877E-87A73C7E82E2')) or (BusinessCl_7.iwadisID
= 'E7F945BE-0821-478B-8624-A67158C60E7F')) or (BusinessCl_7.iwadisID
= 'B10E4580-B162-4827-96C6-99F582CE0632')) or (BusinessCl_7.iwadisID
= 'EC68D385-42C3-4D6D-BFB6-63A74C0DB0E2')) or (BusinessCl_7.iwadisID
= 'BDA02AC6-D306-4900-A566-5C9CCC6D7CCA')) or (BusinessCl_7.iwadisID
= '607D9D60-5049-450A-BFB9-D5E3CB3BA10A')) or (BusinessCl_7.iwadisID
= 'E5EF26D2-1C49-41B6-A695-C93A5047CE88')) or (BusinessCl_7.iwadisID
= '00FB5425-5E14-4467-AEBE-D03ACC723ACC')) or (BusinessCl_7.iwadisID
= 'AB1C6F2D-01C4-42D4-87FC-081CD0B74863')) or (BusinessCl_7.iwadisID
= 'A879E175-CF15-4F8E-80EE-E1A14A4650BE')) or (BusinessCl_7.iwadisID
= '26F0266C-D566-44A6-8048-287F5DBC9AF2')) or (BusinessCl_7.iwadisID
= 'E21C6F0E-DB18-40E2-9891-A55D35FE4A70')) or (BusinessCl_7.iwadisID
= 'FF7080A2-3274-496F-A63F-367E0C22E29A')) or (BusinessCl_7.iwadisID
= '5939A987-0610-4310-ADDB-EC3D2220FE3D')) or (BusinessCl_7.iwadisID
= 'DD8F8E69-46E2-4D6B-9E6E-F1A9ABEAEC85')) or (BusinessCl_7.iwadisID
= '64BA4B03-AD83-4D0A-BDE1-CA04CD7357AB')) or (BusinessCl_7.iwadisID
= '6AF6A0F6-0D56-4B36-BA3B-265985436C67')) or (BusinessCl_7.iwadisID
= '9FEAF7CE-9D1C-4D85-A5DD-24C46FCBF0A1')) or (BusinessCl_7.iwadisID
= 'F29E8765-42F0-4C15-AECC-581FC7D83869')) or (BusinessCl_7.iwadisID
= 'C0DE8AD1-A0B5-4B6D-B493-94FB069074B5')) or (BusinessCl_7.iwadisID
= '0F4D6DE8-5DE9-4BF4-9E0C-A9DF0CD15E5C')) or (BusinessCl_7.iwadisID
= '201CA2F1-4F47-4528-9D53-F7CB5C848019')) or (BusinessCl_7.iwadisID
= '53C302DE-83FF-448F-9FFD-6A4A865B570D')) or (BusinessCl_7.iwadisID
= 'C7465A78-9C8D-44D1-B9A9-EF6A29DCDBDB')) or (BusinessCl_7.iwadisID
= '6693B56C-5B29-4EEC-BCC7-0975F4655BDC')) or (BusinessCl_7.iwadisID
= 'E66E7E6B-2B1E-4387-9CE1-CFAF9A86EA71')) or (BusinessCl_7.iwadisID
= '3AAB53ED-8649-4EDD-AE83-A0AF2EBAED80')) or (BusinessCl_7.iwadisID
= '6E5802E5-D4C7-47E3-9A2E-D7FD5057D25F')) or (BusinessCl_7.iwadisID
= 'BF5B75B7-C8B9-4966-B981-9B27990CFF2E')) or (BusinessCl_7.iwadisID
= '4578DD58-6960-4C3C-8FC0-E644FBE819E5')) or (BusinessCl_7.iwadisID
= 'E782A2CF-8915-47A7-B72E-3B40808584AE')) or (BusinessCl_7.iwadisID
= 'AF7677D3-02C3-44AD-A939-445DEC9C4214')) or (BusinessCl_7.iwadisID
= '17EFE29A-5457-4777-A89E-78B373EF56FB')) or (BusinessCl_7.iwadisID
= 'AE51FCE0-7907-4CCD-8667-E240CE2FC2BD')) or (BusinessCl_7.iwadisID
= 'ED57E4DF-4521-4926-8051-B1F9187C0E5A')) or (BusinessCl_7.iwadisID
= 'D87EC895-928C-41AE-B3ED-66E75C48B8A0')) or (BusinessCl_7.iwadisID
= 'AD3957DC-CAE0-4ABA-9A8A-0A47C496C4BB')) or (BusinessCl_7.iwadisID
= '40639C06-573B-45C8-8C3B-7DA5D5994982')) or (BusinessCl_7.iwadisID
= '4907B94B-4B60-4A46-82EF-E05B30C3AAF9')) or (BusinessCl_7.iwadisID
= '37D79ABE-3865-4B22-97DF-C2E17639C511')) or (BusinessCl_7.iwadisID
= 'C5C86613-F424-4742-A9C0-8C260C113FEF')) or (BusinessCl_7.iwadisID
= '87681A33-C766-4999-B677-52116D5831D5')) or (BusinessCl_7.iwadisID
= 'C7F8E9310F014095B893F46497371C37')) or (BusinessCl_7.iwadisID
= '86010DB3-9D2A-4DE6-ABBA-3B2C4B32801A')) or (BusinessCl_7.iwadisID
= '3AFD66B8-8E07-4FE8-9068-AB9A4D06FB74')) or (BusinessCl_7.iwadisID
= '10CB94F5-7977-4CE5-BF5F-7A8AB8183024')) or (BusinessCl_7.iwadisID
= '084A1633-5A6C-4561-98F4-426CCD62C677')) or (BusinessCl_7.iwadisID
= '6F3CA423-16C6-493C-8D2C-1D18CDEAE34E')) or (BusinessCl_7.iwadisID
= '90CFD874-56B3-4C7F-8968-469C612BC045')) or (BusinessCl_7.iwadisID
= '3222066A-16DF-4768-828E-BDB6C96C2CE4')) or (BusinessCl_7.iwadisID
= '4CD045BA-81E4-480B-BE90-C62D4FB51392')) or (BusinessCl_7.iwadisID
= '2B416D72-5839-4053-8BA4-DE543891B8F9')) or (BusinessCl_7.iwadisID
= '33D714D8-A777-4A27-B263-88488981F654')) or (BusinessCl_7.iwadisID
= '4C77CAE8-2ADA-46A1-9024-FCF2FE75FCF2')) or (BusinessCl_7.iwadisID
= 'B725665E-9C93-4AD4-8E51-C94A40CE929B')) or (BusinessCl_7.iwadisID
= '7FDF3D63-16FE-49F3-AFC2-1BCA1EB790E0')) or (BusinessCl_7.iwadisID
= 'F0CEA3D6-9B5A-4C9F-90F9-DA2F263FAF69')) or (BusinessCl_7.iwadisID
= 'C05AC12B-FAB0-4348-9AB4-4BD0C7F6F242')) or (BusinessCl_7.iwadisID
= '6261349D-7939-4AE2-87A4-4C4E280D6551')) or (BusinessCl_7.iwadisID
= '7B41C74F-27DA-4A97-AC88-17C7420D1512')) or (BusinessCl_7.iwadisID
= '00077ED1-63BB-493F-9705-EEE2D79DB638')) or (BusinessCl_7.iwadisID
= 'BB0432F1-55E8-4870-B53E-48FD2A7D335A')) or (BusinessCl_7.iwadisID
= 'D8D39BAA-AD5E-4DDB-906C-F815104CAB40')) or (BusinessCl_7.iwadisID
= 'FB287846-C77C-4F79-9095-9ABD8C196C7F')) or (BusinessCl_7.iwadisID
= '8C335F4A-ED01-49A8-8233-3CEC9CC7B2C3')) or (BusinessCl_7.iwadisID
= 'CB2DFB0F-A0F9-48BE-ADFC-15D3824EF763')) or (BusinessCl_7.iwadisID
= '6DEE8CCB-FFCF-4676-9826-B53AE784A523')) or (BusinessCl_7.iwadisID
= 'AA01F3AD-9BA3-4311-9782-427DD88FCDB2')) or (BusinessCl_7.iwadisID
= 'EAAE48F9-E476-4B1F-BAF5-CF005F36EC8C')) or (BusinessCl_7.iwadisID
= '53C16C0A-8853-4EF3-8708-30DC973FBC51')) or (BusinessCl_7.iwadisID
= '2BE80EC4-C112-457E-9FA6-3FF2468949DF')) or (BusinessCl_7.iwadisID
= '0492C3CF-F678-4D44-880C-4ACFE403A219')) or (BusinessCl_7.iwadisID
= 'B39FB5E5-40CD-40C3-8003-ED6CEB85AA0C')) or (BusinessCl_7.iwadisID
= '819774B5-74B8-41C6-AA60-EC3B4BE2406A')) or (BusinessCl_7.iwadisID
= '6641A220-503E-4519-AB18-399651037FD7')) or (BusinessCl_7.iwadisID
= '9E81801515404AA6932B96612D2D63BA')) or (BusinessCl_7.iwadisID
= 'E821C777-368F-453E-841C-610AA01D5A8C')) or (BusinessCl_7.iwadisID
= '76EE9995-FCF6-417F-8001-C311A66C17D2')) or (BusinessCl_7.iwadisID
= '6A67CE80-E070-4C8A-8080-31DEFDDBC8FD')) or (BusinessCl_7.iwadisID
= 'E45E2991-9BEF-47DC-B2F6-E560522350FF')) or (BusinessCl_7.iwadisID
= '120860AC98A1499D8C3FF1F6788F9D94')) or (BusinessCl_7.iwadisID
= '50838DC8-9E59-4F71-A259-8A7368697128')) or (BusinessCl_7.iwadisID
= '2032B721-9196-4FCD-A7F6-2FA72B40B94E')) or (BusinessCl_7.iwadisID
= 'B10E3DA8-999D-4B44-AEA2-231BCAD71AD9')) or (BusinessCl_7.iwadisID
= '97CC84C6-1222-4193-BD63-A9B0968632D7')) or (BusinessCl_7.iwadisID
= '9ED8B0EE-7516-4CB1-A10E-EE9D6D8B9918')) or (BusinessCl_7.iwadisID
= 'EC8E3410-475E-4203-9CB4-F33E3AA216E2')) or (BusinessCl_7.iwadisID
= 'A3A3D81D-A947-4E97-BA46-EB65B1408361')) or (BusinessCl_7.iwadisID
= '93D1FD77-1E5C-411C-ACE3-E6B0E0CFE2DD')) or (BusinessCl_7.iwadisID
= '24347D49-C62D-4FB4-B3BE-5CC42897F139')) or (BusinessCl_7.iwadisID
= 'BF99E0F5-68AB-429E-A345-8DA0D4005A0D')) or (BusinessCl_7.iwadisID
= '00107FCC-B8B2-4DD9-8807-AD229C2D5B43')) or (BusinessCl_7.iwadisID
= 'FC41E093-7922-43F0-AC23-FD3E4952BDB8')) or (BusinessCl_7.iwadisID
= '20C6214F-633A-44DC-BCFE-E63E085DA1E9')) or (BusinessCl_7.iwadisID
= 'A417B40B-93D4-4BFF-900F-46CB8BD2EF4D')) or (BusinessCl_7.iwadisID
= '595819D9-469D-4534-ADD8-A5D498F6D017')) or (BusinessCl_7.iwadisID
= '1960BD48-4D41-4BC1-A233-4AEAB2A5FFF9')) or (BusinessCl_7.iwadisID
= '3F50889F-82C0-4B6B-B430-DB60038AE922')) or (BusinessCl_7.iwadisID
= 'E1DFBE2B-8478-4071-BB73-E212EA31F08B')) or (BusinessCl_7.iwadisID
= 'C7F507F760C44568A54D0C23D978B0BA')) or (BusinessCl_7.iwadisID
= '37EC3961D21E47218CA194737B3D1CDC')) or (BusinessCl_7.iwadisID
= '1C24958771BA4B6B9673EAF8C6FD2CE3')) or (BusinessCl_7.iwadisID
= '1CC3C5904C94496F8DA8E486DE441672')) or (BusinessCl_7.iwadisID
= '1C874E461F65434FA02B7ACA7C0B70BA')) or (BusinessCl_7.iwadisID
= '57F1B01619974857B8D3E8C6A46BE6B5'))
))))))

I no that the SQL could be simplified but that's not possible because
the OR-Mapper generates it for us.

The SQL above is executed with the following plan:

PLAN JOIN (IWADISOBJE_2 INDEX (IX_IWADISOBJECT_BOLD_ID), GLOBALCONT_1
INDEX (IX_GLOBALCONTEXT_BOLD_ID))
PLAN JOIN (JOIN (IWADISOBJE_3 INDEX (IX_IWADISOBJECT_BOLD_ID),
BANNEDOBJE_1 INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN
(BUSINESSCL_1 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__1 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_4 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__2
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_5 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_2 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__3 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_6 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(CLIENT_1 INDEX (IX_CLIENT_BOLD_ID), BUSINESSCL_3 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (IWADISOBJE_7 INDEX (IX_IWADISOBJECT_BOLD_ID),
BANNEDOBJE_2 INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN
(BUSINESSCL_4 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__4 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_8 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__5
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_9 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_5 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__6 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (JOIN (IWADISOBJE_10 INDEX (IX_IWADISOBJECT_BOLD_ID),
ROLEOBJECT_1 INDEX (IX_ROLEOBJECTSHOLDER_BOLD_ID)), JOIN (ROLE__1
INDEX (IX_ROLE__BOLD_ID), BUSINESSCL_6 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (IWADISOBJE_12 INDEX (IX_IWADISOBJECT_BOLD_ID),
IWADISOBJE_11 INDEX (IX_IWADISOBJECT_ROLE_COWOBV2)), JOIN (ROLE__2
INDEX (IX_ROLE__BOLD_ID), BUSINESSCL_7 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (IWADISOBJE_1 INDEX (IDXIWADISOBJECT1, IDXIWADISOBJECT1),
WORD_1 INDEX (IDXWORD3))

The Query runs on a 40 GB-DB about 9 minutes. That's not acceptable
for us. Is there a way to optimize the DB-indexes to gain a better
performance? In prior posting we had a similar problen with another
SQL and could reduce the executing time from 5 minutes to 3 seconds
be creating other / additional indexes.

This is the schema of the affected tables:

CREATE TABLE IwadisObject ( BOLD_ID INTEGER NOT NULL, BOLD_TYPE
SMALLINT NOT NULL, archiveState INTEGER DEFAULT 0 , archiveValid
INTEGER DEFAULT 0 , creationDate DATE , creationUserAsString
VARCHAR(70) DEFAULT '' COLLATE DE_DE , description VARCHAR(2000)
DEFAULT '' COLLATE DE_DE , indexChangeNotifier VARCHAR(40)
DEFAULT '' COLLATE DE_DE , indexErrorCount INTEGER DEFAULT 0 ,
indexValid INTEGER DEFAULT 0 , iwadisObjectState INTEGER DEFAULT
0 , lastchangeDate DATE , lastchangeUserAsString VARCHAR(70)
DEFAULT '' COLLATE DE_DE , name VARCHAR(255) DEFAULT '' COLLATE
DE_DE , visibility INTEGER DEFAULT 0 , creatorClient INTEGER
DEFAULT -1 , folder INTEGER DEFAULT -1 , prototypeGlobalcontext
INTEGER DEFAULT -1 , privateUser INTEGER DEFAULT -1 , holder
INTEGER DEFAULT -1 , iwadissession INTEGER DEFAULT -1 ,
CONSTRAINT IX_IwadisObject_BOLD_ID PRIMARY KEY (BOLD_ID))
CREATE INDEX IX_IwadisObject_BOLD_TYPE ON IwadisObject (BOLD_TYPE)
CREATE INDEX IX_IwadisObject_creatorClKCD ON IwadisObject
(creatorClient)
CREATE INDEX IX_IwadisObject_folder ON IwadisObject (folder)
CREATE INDEX IX_IwadisObject_prototype7YQ ON IwadisObject
(prototypeGlobalcontext)
CREATE INDEX IX_IwadisObject_privateUser ON IwadisObject
(privateUser)
CREATE INDEX IX_IwadisObject_holder ON IwadisObject (holder)
CREATE INDEX IX_IwadisObject_iwadissesRY3 ON IwadisObject
(iwadissession)

CREATE TABLE Word ( BOLD_ID INTEGER NOT NULL, BOLD_TYPE SMALLINT
NOT NULL, attribute VARCHAR(50) DEFAULT '' COLLATE DE_DE , data
VARCHAR(40) DEFAULT '' COLLATE DE_DE , phoneticData VARCHAR(40)
DEFAULT '' COLLATE DE_DE , indexedObject INTEGER DEFAULT -1 ,
CONSTRAINT IX_Word_BOLD_ID PRIMARY KEY (BOLD_ID))
CREATE INDEX IX_Word_BOLD_TYPE ON Word (BOLD_TYPE)
CREATE INDEX IX_Word_indexedObject ON Word (indexedObject)

CREATE TABLE GlobalContext ( BOLD_ID INTEGER NOT NULL, BOLD_TYPE
SMALLINT NOT NULL, DBVersion INTEGER DEFAULT 0 ,
meetingaccounting BLOB , rules BLOB , rootCategory INTEGER
DEFAULT -1 , rootFolder INTEGER DEFAULT -1 , rootOrgunit INTEGER
DEFAULT -1 , agendaitemCounter INTEGER DEFAULT -1 ,
briefcaseCounter INTEGER DEFAULT -1 , documentCounter INTEGER
DEFAULT -1 , CONSTRAINT IX_GlobalContext_BOLD_ID PRIMARY KEY
(BOLD_ID))
CREATE INDEX IX_GlobalContext_BOLD_TYPE ON GlobalContext (BOLD_TYPE)
CREATE INDEX IX_GlobalContext_rootCateGQI ON GlobalContext
(rootCategory)
CREATE INDEX IX_GlobalContext_rootFolder ON GlobalContext
(rootFolder)
CREATE INDEX IX_GlobalContext_rootOrgunit ON GlobalContext
(rootOrgunit)
CREATE INDEX IX_GlobalContext_agendaitB8 ON GlobalContext
(agendaitemCounter)
CREATE INDEX IX_GlobalContext_briefcasYTG ON GlobalContext
(briefcaseCounter)
CREATE INDEX IX_GlobalContext_document3GJ ON GlobalContext
(documentCounter)

CREATE TABLE User_ ( BOLD_ID INTEGER NOT NULL, BOLD_TYPE
SMALLINT NOT NULL, Businesstask_User_Sender VARCHAR(255)
DEFAULT '' COLLATE DE_DE , isSysadmin INTEGER DEFAULT 0 , login
VARCHAR(30) DEFAULT '' COLLATE DE_DE , password_ VARCHAR(30)
DEFAULT '' COLLATE DE_DE , preferences BLOB , contact_ INTEGER
DEFAULT -1 , lruCreatorClient INTEGER DEFAULT -1 , lruCreatorRole
INTEGER DEFAULT -1 , CONSTRAINT IX_User__BOLD_ID PRIMARY KEY
(BOLD_ID))
CREATE INDEX IX_User__BOLD_TYPE ON User_ (BOLD_TYPE)
CREATE INDEX IX_User__contact_ ON User_ (contact_)
CREATE INDEX IX_User__lruCreatorClient ON User_ (lruCreatorClient)
CREATE INDEX IX_User__lruCreatorRole ON User_ (lruCreatorRole)

CREATE TABLE Client ( BOLD_ID INTEGER NOT NULL, BOLD_TYPE
SMALLINT NOT NULL, bmp BLOB , description VARCHAR(2000)
DEFAULT '' COLLATE DE_DE , emblemHeightPercentage INTEGER DEFAULT
0 , emblemWidthPercentage INTEGER DEFAULT 0 , freitext1 VARCHAR
(4000) DEFAULT '' COLLATE DE_DE , freitext10 VARCHAR(4000)
DEFAULT '' COLLATE DE_DE , freitext2 VARCHAR(4000) DEFAULT ''
COLLATE DE_DE , freitext3 VARCHAR(4000) DEFAULT '' COLLATE
DE_DE , freitext4 VARCHAR(4000) DEFAULT '' COLLATE DE_DE ,
freitext5 VARCHAR(4000) DEFAULT '' COLLATE DE_DE , freitext6
VARCHAR(4000) DEFAULT '' COLLATE DE_DE , freitext7 VARCHAR(4000)
DEFAULT '' COLLATE DE_DE , freitext8 VARCHAR(4000) DEFAULT ''
COLLATE DE_DE , freitext9 VARCHAR(4000) DEFAULT '' COLLATE
DE_DE , name VARCHAR(80) DEFAULT '' COLLATE DE_DE , number_
VARCHAR(255) DEFAULT '' COLLATE DE_DE , order_ INTEGER DEFAULT
0 , printname VARCHAR(80) DEFAULT '' COLLATE DE_DE , shortname
VARCHAR(255) DEFAULT '' COLLATE DE_DE , CONSTRAINT
IX_Client_BOLD_ID PRIMARY KEY (BOLD_ID))
CREATE INDEX IX_Client_BOLD_TYPE ON Client (BOLD_TYPE)

CREATE TABLE Role_ ( BOLD_ID INTEGER NOT NULL, BOLD_TYPE
SMALLINT NOT NULL, description VARCHAR(2000) DEFAULT '' COLLATE
DE_DE , isSysadmin INTEGER DEFAULT 0 , name VARCHAR(80)
DEFAULT '' COLLATE DE_DE , prime INTEGER DEFAULT 0 , orgUnit
INTEGER DEFAULT -1 , parent2 INTEGER DEFAULT -1 , CONSTRAINT
IX_Role__BOLD_ID PRIMARY KEY (BOLD_ID))
CREATE INDEX IX_Role__BOLD_TYPE ON Role_ (BOLD_TYPE)
CREATE INDEX IX_Role__orgUnit ON Role_ (orgUnit)
CREATE INDEX IX_Role__parent2 ON Role_ (parent2)

We created additional indexes already (which are used in other
queries):

create index IdxUser1 on User_ (login, password_)');
create index IdxIwadisObjectFBB on iwadisobject (folder, BOLD_ID,
BOLD_TYPE)');
create index IdxWord1 on Word (attribute, data)');
create index IdxWord2 on Word (attribute, phoneticdata)');
create index IdxWord3 on Word (indexedObject)');
create index IdxIwadisobject1 on iwadisobject (bold_type, bold_id)');
create index IdxIwadisObjectArchiveValid on iwadisobject
(archivevalid)');

The amount of record per table is:
iwadisobject 90744
word 5806405
globalcontext 1
user_ 34
client 20
role_ 107

Does anybody has some ideas?

Thanks

Stefan





[Non-text portions of this message have been removed]