Try to modify:



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')...



In this one



WHERE IwadisObje_1.BOLD_ID = IwadisObje_6.BOLD_ID
AND ((((((((((((((((((((BusinessCl_3.iwadisID IN ( '68D3C5EE-8EDC-4E5C-
BA90-88D495596910', '9B58C591-3FF4-4E14-
A9E0-F2EE49F4844E', '8374F54C-A890-4537-
B343-A7F55C5DC060', 'C8A7D593-B28B-4A31-
94A6-E6C7367CA8CF', '6B590600-C19C-4678-
B073-5BE22776C744' , '549E4270-5532-48EF-
9311-61D3EA92F22E').......



Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of swestner
Sent: Sunday, August 03, 2008 2:58 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Query runs 9 minutes - how to set indexes to
optimize it



Hello Helen,

I stripped down the select and remove most of NOT INs but it still
runs 9 minutes.

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(((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((((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'))
))))))

If I remove the whole ORs and reduce the select to:

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(((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((((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 = '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 = '57F1B01619974857B8D3E8C6A46BE6B5'))
))))))

then it runs 7 seconds.

The problems are the ORs.

What could be done?

Stefan

--- In firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> , Helen Borrie <helebor@...>
wrote:

>
> At 11:36 PM 2/08/2008, you wrote:
> >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:
>
> ...Lots and lots of NOTs...
>
> It's intrinsically slow because of all of those NOTs. It's slower

than it needs to be because, in places, you are using NOT(SELECT
COUNT * > 0) as an existence test!! and NOT IN (SELECT...)
structures: replace both of these with with NOT EXISTS (SELECT
1....). Another source of slowdown is what appears to be indexes on
fields design to be Boolean switches. Remove these, or recreate
those indexes to include the primary key value as the right-hand key
of a composite index....I do see also some potential for slowdown in
your use of COLLATEd index keys - try to do without these if they are
not needed.

>
> Can't help thinking that a relational database engine is not a

great choice as the data provider for Bold...but then, I do have a
bias against object-relational schemes in general - like using a
shark as bait for catching goldfish, except that you have to kill the
shark and make it into fishburger to get it working in practice...

>
> ./heLen
>

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