| Subject | [firebird-support] Re: Query runs 9 minutes - how to set indexes to optimize it |
|---|---|
| Author | Svein Erling Tysvaer |
| Post date | 2008-08-05T10:39:25Z |
Hi Stefan!
I love SQL puzzles, so I thought this could be a nice challenge. First,
a few general remarks:
1) Don't mix implicit and explicit JOINs
I noticed parts of your query were like this:
(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
2) You have several hundred parenthesis that you do not need
Some of them do matter, though I actually wonder whether some may
actually give you a result you do not want.
I'm not certain whether or these things confuse the optimizer, but it
certainly confuse me (and that probably means others get confused as
well)...
So for a short description of what I've changed:
In general, I abbreviated the aliases, removed a lot of parenthesis,
used explicit JOIN everywhere and replaced the field selection within
your NOT EXISTS to make the statement easier for me to read.
Then, I noticed that you had:
AND (not(IwadisObje_1.visibility <> 2 and not EXISTS (SELECT ...
I find statements like:
'The value must not be different from 2 when there does not exist'
more complicated than:
'The value must equal 2 or there must exist'
so I changed this part. However, further down in your statement, I found
AND (not(((IwadisObje_1.visibility = 0 and not EXISTS (SELECT ... )
and not EXISTS (SELECT ... ))...
(both of these NOT EXISTS contain over 100 parenthesis)
Notice that the two NOT EXISTS are within the outer 'not'. I guess I
could have changed this to:
AND (IwadisObje_1.visibility <> 0 OR EXISTS(...) OR EXISTS(...)
but wasn't certain whether this is what you want.
Below is my suggested statement, tell us whether it helps anything and
show us the plan if it doesn't. I might have forgotten one parenthesis
or so when trying to remove them, but think the below statement should
be simpler to modify. Also, I've ignored considering the NULL state,
e.g. when there neither EXISTS nor NOT EXISTS records. I assume the
mixing of implicit and explicit JOINs can be considered a flaw in your
OR mapper.
HTH,
Set
SELECT I1.BOLD_ID, I1.BOLD_TYPE
FROM IwadisObject I1
JOIN Word W1 ON I1.BOLD_ID = W1.indexedObject
WHERE W1.data LIKE 'SCHAAF%'
AND I1.BOLD_TYPE = 132 or I1.BOLD_TYPE = 131
AND (I1.visibility = 2
or EXISTS (SELECT * FROM Client C1
JOIN IwadisObject I6 ON C1.BOLD_ID = I6.creatorClient
JOIN BusinessClassesRoot B3 ON C1.BOLD_ID = B3.BOLD_ID
WHERE I1.BOLD_ID = I6.BOLD_ID
AND (B3.iwadisID = '68D3C5EE-8EDC-4E5C-BA90-88D495596910'
or B3.iwadisID = '9B58C591-3FF4-4E14-A9E0-F2EE49F4844E'
or B3.iwadisID = '8374F54C-A890-4537-B343-A7F55C5DC060'
or B3.iwadisID = 'C8A7D593-B28B-4A31-94A6-E6C7367CA8CF'
or B3.iwadisID = '6B590600-C19C-4678-B073-5BE22776C744'
or B3.iwadisID = '549E4270-5532-48EF-9311-61D3EA92F22E'
or B3.iwadisID = '345F364D-0B9B-4A51-B5B7-376F467A168D'
or B3.iwadisID = 'CD4A6A54-C0D1-4344-B2B8-243AF7173A7A'
or B3.iwadisID = 'F8C410C0-DE0D-4536-A72C-37AA8D6972DB'
or B3.iwadisID = '23B7773C-1049-4761-BE46-8734B359E309'
or B3.iwadisID = 'BE3D0C31-E882-4EF5-9B4A-7E9EE2159E9C'
or B3.iwadisID = 'B12387B7-9B46-4EAB-B2CD-EA6B99465611'
or B3.iwadisID = '158E33BE-9270-408A-AE9F-D986661C780F'
or B3.iwadisID = '35E449B2-C973-478D-A31C-F4FE6572B60E'
or B3.iwadisID = '3C3162B7-B8E1-4A17-B975-EE0C5DBABA63'
or B3.iwadisID = 'D57BAD0A-821B-4155-9B38-1061DDA5A67B'
or B3.iwadisID = 'E0B9D7F7-48CA-4DB9-A8B8-DD5AC9D9F9AE'
or B3.iwadisID = '93A29955-FC9D-4A36-9AB1-6BA572B44B82'
or B3.iwadisID = '9BF80633-0541-4B4D-A925-89881546C5AB'
or B3.iwadisID = '21DAA5BB-BBCB-4885-9CD4-230C004D8F82')))
AND (not(I1.visibility = 0
and not EXISTS (SELECT * FROM IwadisObject I10
JOIN RoleObjectsHolder RO1 ON RO1.BOLD_ID = I10.holder
JOIN Role_ R1 ON R1.BOLD_ID = RO1.role_
JOIN BusinessClassesRoot B6 ON R1.BOLD_ID = B6.BOLD_ID
WHERE I1.BOLD_ID = I10.BOLD_ID
AND (B6.iwadisID = '1D8FCCC3-52C3-4292-B21E-900BEBFDB61E'
or B6.iwadisID = 'A6DA4CC6-4514-4313-89A5-78B5C6DC7594'
or B6.iwadisID = 'E7E017C9-C0ED-4F00-877E-87A73C7E82E2'
or B6.iwadisID = 'E7F945BE-0821-478B-8624-A67158C60E7F'
or B6.iwadisID = 'B10E4580-B162-4827-96C6-99F582CE0632'
or B6.iwadisID = 'EC68D385-42C3-4D6D-BFB6-63A74C0DB0E2'
or B6.iwadisID = 'BDA02AC6-D306-4900-A566-5C9CCC6D7CCA'
or B6.iwadisID = '607D9D60-5049-450A-BFB9-D5E3CB3BA10A'
or B6.iwadisID = 'E5EF26D2-1C49-41B6-A695-C93A5047CE88'
or B6.iwadisID = '00FB5425-5E14-4467-AEBE-D03ACC723ACC'
or B6.iwadisID = 'AB1C6F2D-01C4-42D4-87FC-081CD0B74863'
or B6.iwadisID = 'A879E175-CF15-4F8E-80EE-E1A14A4650BE'
or B6.iwadisID = '26F0266C-D566-44A6-8048-287F5DBC9AF2'
or B6.iwadisID = 'E21C6F0E-DB18-40E2-9891-A55D35FE4A70'
or B6.iwadisID = 'FF7080A2-3274-496F-A63F-367E0C22E29A'
or B6.iwadisID = '5939A987-0610-4310-ADDB-EC3D2220FE3D'
or B6.iwadisID = 'DD8F8E69-46E2-4D6B-9E6E-F1A9ABEAEC85'
or B6.iwadisID = '64BA4B03-AD83-4D0A-BDE1-CA04CD7357AB'
or B6.iwadisID = '6AF6A0F6-0D56-4B36-BA3B-265985436C67'
or B6.iwadisID = '9FEAF7CE-9D1C-4D85-A5DD-24C46FCBF0A1'
or B6.iwadisID = 'F29E8765-42F0-4C15-AECC-581FC7D83869'
or B6.iwadisID = 'C0DE8AD1-A0B5-4B6D-B493-94FB069074B5'
or B6.iwadisID = '0F4D6DE8-5DE9-4BF4-9E0C-A9DF0CD15E5C'
or B6.iwadisID = '201CA2F1-4F47-4528-9D53-F7CB5C848019'
or B6.iwadisID = '53C302DE-83FF-448F-9FFD-6A4A865B570D'
or B6.iwadisID = 'C7465A78-9C8D-44D1-B9A9-EF6A29DCDBDB'
or B6.iwadisID = '6693B56C-5B29-4EEC-BCC7-0975F4655BDC'
or B6.iwadisID = 'E66E7E6B-2B1E-4387-9CE1-CFAF9A86EA71'
or B6.iwadisID = '3AAB53ED-8649-4EDD-AE83-A0AF2EBAED80'
or B6.iwadisID = '6E5802E5-D4C7-47E3-9A2E-D7FD5057D25F'
or B6.iwadisID = 'BF5B75B7-C8B9-4966-B981-9B27990CFF2E'
or B6.iwadisID = '4578DD58-6960-4C3C-8FC0-E644FBE819E5'
or B6.iwadisID = 'E782A2CF-8915-47A7-B72E-3B40808584AE'
or B6.iwadisID = 'AF7677D3-02C3-44AD-A939-445DEC9C4214'
or B6.iwadisID = '17EFE29A-5457-4777-A89E-78B373EF56FB'
or B6.iwadisID = 'AE51FCE0-7907-4CCD-8667-E240CE2FC2BD'
or B6.iwadisID = 'ED57E4DF-4521-4926-8051-B1F9187C0E5A'
or B6.iwadisID = 'D87EC895-928C-41AE-B3ED-66E75C48B8A0'
or B6.iwadisID = 'AD3957DC-CAE0-4ABA-9A8A-0A47C496C4BB'
or B6.iwadisID = '40639C06-573B-45C8-8C3B-7DA5D5994982'
or B6.iwadisID = '4907B94B-4B60-4A46-82EF-E05B30C3AAF9'
or B6.iwadisID = '37D79ABE-3865-4B22-97DF-C2E17639C511'
or B6.iwadisID = 'C5C86613-F424-4742-A9C0-8C260C113FEF'
or B6.iwadisID = '87681A33-C766-4999-B677-52116D5831D5'
or B6.iwadisID = 'C7F8E9310F014095B893F46497371C37'
or B6.iwadisID = '86010DB3-9D2A-4DE6-ABBA-3B2C4B32801A'
or B6.iwadisID = '3AFD66B8-8E07-4FE8-9068-AB9A4D06FB74'
or B6.iwadisID = '10CB94F5-7977-4CE5-BF5F-7A8AB8183024'
or B6.iwadisID = '084A1633-5A6C-4561-98F4-426CCD62C677'
or B6.iwadisID = '6F3CA423-16C6-493C-8D2C-1D18CDEAE34E'
or B6.iwadisID = '90CFD874-56B3-4C7F-8968-469C612BC045'
or B6.iwadisID = '3222066A-16DF-4768-828E-BDB6C96C2CE4'
or B6.iwadisID = '4CD045BA-81E4-480B-BE90-C62D4FB51392'
or B6.iwadisID = '2B416D72-5839-4053-8BA4-DE543891B8F9'
or B6.iwadisID = '33D714D8-A777-4A27-B263-88488981F654'
or B6.iwadisID = '4C77CAE8-2ADA-46A1-9024-FCF2FE75FCF2'
or B6.iwadisID = 'B725665E-9C93-4AD4-8E51-C94A40CE929B'
or B6.iwadisID = '7FDF3D63-16FE-49F3-AFC2-1BCA1EB790E0'
or B6.iwadisID = 'F0CEA3D6-9B5A-4C9F-90F9-DA2F263FAF69'
or B6.iwadisID = 'C05AC12B-FAB0-4348-9AB4-4BD0C7F6F242'
or B6.iwadisID = '6261349D-7939-4AE2-87A4-4C4E280D6551'
or B6.iwadisID = '7B41C74F-27DA-4A97-AC88-17C7420D1512'
or B6.iwadisID = '00077ED1-63BB-493F-9705-EEE2D79DB638'
or B6.iwadisID = 'BB0432F1-55E8-4870-B53E-48FD2A7D335A'
or B6.iwadisID = 'D8D39BAA-AD5E-4DDB-906C-F815104CAB40'
or B6.iwadisID = 'FB287846-C77C-4F79-9095-9ABD8C196C7F'
or B6.iwadisID = '8C335F4A-ED01-49A8-8233-3CEC9CC7B2C3'
or B6.iwadisID = 'CB2DFB0F-A0F9-48BE-ADFC-15D3824EF763'
or B6.iwadisID = '6DEE8CCB-FFCF-4676-9826-B53AE784A523'
or B6.iwadisID = 'AA01F3AD-9BA3-4311-9782-427DD88FCDB2'
or B6.iwadisID = 'EAAE48F9-E476-4B1F-BAF5-CF005F36EC8C'
or B6.iwadisID = '53C16C0A-8853-4EF3-8708-30DC973FBC51'
or B6.iwadisID = '2BE80EC4-C112-457E-9FA6-3FF2468949DF'
or B6.iwadisID = '0492C3CF-F678-4D44-880C-4ACFE403A219'
or B6.iwadisID = 'B39FB5E5-40CD-40C3-8003-ED6CEB85AA0C'
or B6.iwadisID = '819774B5-74B8-41C6-AA60-EC3B4BE2406A'
or B6.iwadisID = '6641A220-503E-4519-AB18-399651037FD7'
or B6.iwadisID = '9E81801515404AA6932B96612D2D63BA'
or B6.iwadisID = 'E821C777-368F-453E-841C-610AA01D5A8C'
or B6.iwadisID = '76EE9995-FCF6-417F-8001-C311A66C17D2'
or B6.iwadisID = '6A67CE80-E070-4C8A-8080-31DEFDDBC8FD'
or B6.iwadisID = 'E45E2991-9BEF-47DC-B2F6-E560522350FF'
or B6.iwadisID = '120860AC98A1499D8C3FF1F6788F9D94'
or B6.iwadisID = '50838DC8-9E59-4F71-A259-8A7368697128'
or B6.iwadisID = '2032B721-9196-4FCD-A7F6-2FA72B40B94E'
or B6.iwadisID = 'B10E3DA8-999D-4B44-AEA2-231BCAD71AD9'
or B6.iwadisID = '97CC84C6-1222-4193-BD63-A9B0968632D7'
or B6.iwadisID = '9ED8B0EE-7516-4CB1-A10E-EE9D6D8B9918'
or B6.iwadisID = 'EC8E3410-475E-4203-9CB4-F33E3AA216E2'
or B6.iwadisID = 'A3A3D81D-A947-4E97-BA46-EB65B1408361'
or B6.iwadisID = '93D1FD77-1E5C-411C-ACE3-E6B0E0CFE2DD'
or B6.iwadisID = '24347D49-C62D-4FB4-B3BE-5CC42897F139'
or B6.iwadisID = 'BF99E0F5-68AB-429E-A345-8DA0D4005A0D'
or B6.iwadisID = '00107FCC-B8B2-4DD9-8807-AD229C2D5B43'
or B6.iwadisID = 'FC41E093-7922-43F0-AC23-FD3E4952BDB8'
or B6.iwadisID = '20C6214F-633A-44DC-BCFE-E63E085DA1E9'
or B6.iwadisID = 'A417B40B-93D4-4BFF-900F-46CB8BD2EF4D'
or B6.iwadisID = '595819D9-469D-4534-ADD8-A5D498F6D017'
or B6.iwadisID = '1960BD48-4D41-4BC1-A233-4AEAB2A5FFF9'
or B6.iwadisID = '3F50889F-82C0-4B6B-B430-DB60038AE922'
or B6.iwadisID = 'E1DFBE2B-8478-4071-BB73-E212EA31F08B'
or B6.iwadisID = 'C7F507F760C44568A54D0C23D978B0BA'
or B6.iwadisID = '37EC3961D21E47218CA194737B3D1CDC'
or B6.iwadisID = '1C24958771BA4B6B9673EAF8C6FD2CE3'
or B6.iwadisID = '1CC3C5904C94496F8DA8E486DE441672'
or B6.iwadisID = '1C874E461F65434FA02B7ACA7C0B70BA'
or B6.iwadisID = '57F1B01619974857B8D3E8C6A46BE6B5'))
and not EXISTS (
SELECT * FROM IwadisObject I12
JOIN IwadisObject_Role I11 ON I11.coworkerObjects = I12.BOLD_ID
JOIN Role_ R2 ON I11.coworkerRoles = R2.BOLD_ID
JOIN BusinessClassesRoot B7 ON R2.BOLD_ID = B7.BOLD_ID
WHERE I1.BOLD_ID = I12.BOLD_ID
AND (B7.iwadisID = '1D8FCCC3-52C3-4292-B21E-900BEBFDB61E'
or B7.iwadisID = 'A6DA4CC6-4514-4313-89A5-78B5C6DC7594'
or B7.iwadisID = 'E7E017C9-C0ED-4F00-877E-87A73C7E82E2'
or B7.iwadisID = 'E7F945BE-0821-478B-8624-A67158C60E7F'
or B7.iwadisID = 'B10E4580-B162-4827-96C6-99F582CE0632'
or B7.iwadisID = 'EC68D385-42C3-4D6D-BFB6-63A74C0DB0E2'
or B7.iwadisID = 'BDA02AC6-D306-4900-A566-5C9CCC6D7CCA'
or B7.iwadisID = '607D9D60-5049-450A-BFB9-D5E3CB3BA10A'
or B7.iwadisID = 'E5EF26D2-1C49-41B6-A695-C93A5047CE88'
or B7.iwadisID = '00FB5425-5E14-4467-AEBE-D03ACC723ACC'
or B7.iwadisID = 'AB1C6F2D-01C4-42D4-87FC-081CD0B74863'
or B7.iwadisID = 'A879E175-CF15-4F8E-80EE-E1A14A4650BE'
or B7.iwadisID = '26F0266C-D566-44A6-8048-287F5DBC9AF2'
or B7.iwadisID = 'E21C6F0E-DB18-40E2-9891-A55D35FE4A70'
or B7.iwadisID = 'FF7080A2-3274-496F-A63F-367E0C22E29A'
or B7.iwadisID = '5939A987-0610-4310-ADDB-EC3D2220FE3D'
or B7.iwadisID = 'DD8F8E69-46E2-4D6B-9E6E-F1A9ABEAEC85'
or B7.iwadisID = '64BA4B03-AD83-4D0A-BDE1-CA04CD7357AB'
or B7.iwadisID = '6AF6A0F6-0D56-4B36-BA3B-265985436C67'
or B7.iwadisID = '9FEAF7CE-9D1C-4D85-A5DD-24C46FCBF0A1'
or B7.iwadisID = 'F29E8765-42F0-4C15-AECC-581FC7D83869'
or B7.iwadisID = 'C0DE8AD1-A0B5-4B6D-B493-94FB069074B5'
or B7.iwadisID = '0F4D6DE8-5DE9-4BF4-9E0C-A9DF0CD15E5C'
or B7.iwadisID = '201CA2F1-4F47-4528-9D53-F7CB5C848019'
or B7.iwadisID = '53C302DE-83FF-448F-9FFD-6A4A865B570D'
or B7.iwadisID = 'C7465A78-9C8D-44D1-B9A9-EF6A29DCDBDB'
or B7.iwadisID = '6693B56C-5B29-4EEC-BCC7-0975F4655BDC'
or B7.iwadisID = 'E66E7E6B-2B1E-4387-9CE1-CFAF9A86EA71'
or B7.iwadisID = '3AAB53ED-8649-4EDD-AE83-A0AF2EBAED80'
or B7.iwadisID = '6E5802E5-D4C7-47E3-9A2E-D7FD5057D25F'
or B7.iwadisID = 'BF5B75B7-C8B9-4966-B981-9B27990CFF2E'
or B7.iwadisID = '4578DD58-6960-4C3C-8FC0-E644FBE819E5'
or B7.iwadisID = 'E782A2CF-8915-47A7-B72E-3B40808584AE'
or B7.iwadisID = 'AF7677D3-02C3-44AD-A939-445DEC9C4214'
or B7.iwadisID = '17EFE29A-5457-4777-A89E-78B373EF56FB'
or B7.iwadisID = 'AE51FCE0-7907-4CCD-8667-E240CE2FC2BD'
or B7.iwadisID = 'ED57E4DF-4521-4926-8051-B1F9187C0E5A'
or B7.iwadisID = 'D87EC895-928C-41AE-B3ED-66E75C48B8A0'
or B7.iwadisID = 'AD3957DC-CAE0-4ABA-9A8A-0A47C496C4BB'
or B7.iwadisID = '40639C06-573B-45C8-8C3B-7DA5D5994982'
or B7.iwadisID = '4907B94B-4B60-4A46-82EF-E05B30C3AAF9'
or B7.iwadisID = '37D79ABE-3865-4B22-97DF-C2E17639C511'
or B7.iwadisID = 'C5C86613-F424-4742-A9C0-8C260C113FEF'
or B7.iwadisID = '87681A33-C766-4999-B677-52116D5831D5'
or B7.iwadisID = 'C7F8E9310F014095B893F46497371C37'
or B7.iwadisID = '86010DB3-9D2A-4DE6-ABBA-3B2C4B32801A'
or B7.iwadisID = '3AFD66B8-8E07-4FE8-9068-AB9A4D06FB74'
or B7.iwadisID = '10CB94F5-7977-4CE5-BF5F-7A8AB8183024'
or B7.iwadisID = '084A1633-5A6C-4561-98F4-426CCD62C677'
or B7.iwadisID = '6F3CA423-16C6-493C-8D2C-1D18CDEAE34E'
or B7.iwadisID = '90CFD874-56B3-4C7F-8968-469C612BC045'
or B7.iwadisID = '3222066A-16DF-4768-828E-BDB6C96C2CE4'
or B7.iwadisID = '4CD045BA-81E4-480B-BE90-C62D4FB51392'
or B7.iwadisID = '2B416D72-5839-4053-8BA4-DE543891B8F9'
or B7.iwadisID = '33D714D8-A777-4A27-B263-88488981F654'
or B7.iwadisID = '4C77CAE8-2ADA-46A1-9024-FCF2FE75FCF2'
or B7.iwadisID = 'B725665E-9C93-4AD4-8E51-C94A40CE929B'
or B7.iwadisID = '7FDF3D63-16FE-49F3-AFC2-1BCA1EB790E0'
or B7.iwadisID = 'F0CEA3D6-9B5A-4C9F-90F9-DA2F263FAF69'
or B7.iwadisID = 'C05AC12B-FAB0-4348-9AB4-4BD0C7F6F242'
or B7.iwadisID = '6261349D-7939-4AE2-87A4-4C4E280D6551'
or B7.iwadisID = '7B41C74F-27DA-4A97-AC88-17C7420D1512'
or B7.iwadisID = '00077ED1-63BB-493F-9705-EEE2D79DB638'
or B7.iwadisID = 'BB0432F1-55E8-4870-B53E-48FD2A7D335A'
or B7.iwadisID = 'D8D39BAA-AD5E-4DDB-906C-F815104CAB40'
or B7.iwadisID = 'FB287846-C77C-4F79-9095-9ABD8C196C7F'
or B7.iwadisID = '8C335F4A-ED01-49A8-8233-3CEC9CC7B2C3'
or B7.iwadisID = 'CB2DFB0F-A0F9-48BE-ADFC-15D3824EF763'
or B7.iwadisID = '6DEE8CCB-FFCF-4676-9826-B53AE784A523'
or B7.iwadisID = 'AA01F3AD-9BA3-4311-9782-427DD88FCDB2'
or B7.iwadisID = 'EAAE48F9-E476-4B1F-BAF5-CF005F36EC8C'
or B7.iwadisID = '53C16C0A-8853-4EF3-8708-30DC973FBC51'
or B7.iwadisID = '2BE80EC4-C112-457E-9FA6-3FF2468949DF'
or B7.iwadisID = '0492C3CF-F678-4D44-880C-4ACFE403A219'
or B7.iwadisID = 'B39FB5E5-40CD-40C3-8003-ED6CEB85AA0C'
or B7.iwadisID = '819774B5-74B8-41C6-AA60-EC3B4BE2406A'
or B7.iwadisID = '6641A220-503E-4519-AB18-399651037FD7'
or B7.iwadisID = '9E81801515404AA6932B96612D2D63BA'
or B7.iwadisID = 'E821C777-368F-453E-841C-610AA01D5A8C'
or B7.iwadisID = '76EE9995-FCF6-417F-8001-C311A66C17D2'
or B7.iwadisID = '6A67CE80-E070-4C8A-8080-31DEFDDBC8FD'
or B7.iwadisID = 'E45E2991-9BEF-47DC-B2F6-E560522350FF'
or B7.iwadisID = '120860AC98A1499D8C3FF1F6788F9D94'
or B7.iwadisID = '50838DC8-9E59-4F71-A259-8A7368697128'
or B7.iwadisID = '2032B721-9196-4FCD-A7F6-2FA72B40B94E'
or B7.iwadisID = 'B10E3DA8-999D-4B44-AEA2-231BCAD71AD9'
or B7.iwadisID = '97CC84C6-1222-4193-BD63-A9B0968632D7'
or B7.iwadisID = '9ED8B0EE-7516-4CB1-A10E-EE9D6D8B9918'
or B7.iwadisID = 'EC8E3410-475E-4203-9CB4-F33E3AA216E2'
or B7.iwadisID = 'A3A3D81D-A947-4E97-BA46-EB65B1408361'
or B7.iwadisID = '93D1FD77-1E5C-411C-ACE3-E6B0E0CFE2DD'
or B7.iwadisID = '24347D49-C62D-4FB4-B3BE-5CC42897F139'
or B7.iwadisID = 'BF99E0F5-68AB-429E-A345-8DA0D4005A0D'
or B7.iwadisID = '00107FCC-B8B2-4DD9-8807-AD229C2D5B43'
or B7.iwadisID = 'FC41E093-7922-43F0-AC23-FD3E4952BDB8'
or B7.iwadisID = '20C6214F-633A-44DC-BCFE-E63E085DA1E9'
or B7.iwadisID = 'A417B40B-93D4-4BFF-900F-46CB8BD2EF4D'
or B7.iwadisID = '595819D9-469D-4534-ADD8-A5D498F6D017'
or B7.iwadisID = '1960BD48-4D41-4BC1-A233-4AEAB2A5FFF9'
or B7.iwadisID = '3F50889F-82C0-4B6B-B430-DB60038AE922'
or B7.iwadisID = 'E1DFBE2B-8478-4071-BB73-E212EA31F08B'
or B7.iwadisID = 'C7F507F760C44568A54D0C23D978B0BA'
or B7.iwadisID = '37EC3961D21E47218CA194737B3D1CDC'
or B7.iwadisID = '1C24958771BA4B6B9673EAF8C6FD2CE3'
or B7.iwadisID = '1CC3C5904C94496F8DA8E486DE441672'
or B7.iwadisID = '1C874E461F65434FA02B7ACA7C0B70BA'
or B7.iwadisID = '57F1B01619974857B8D3E8C6A46BE6B5'))
))
swestner wrote:
I love SQL puzzles, so I thought this could be a nice challenge. First,
a few general remarks:
1) Don't mix implicit and explicit JOINs
I noticed parts of your query were like this:
(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
2) You have several hundred parenthesis that you do not need
Some of them do matter, though I actually wonder whether some may
actually give you a result you do not want.
I'm not certain whether or these things confuse the optimizer, but it
certainly confuse me (and that probably means others get confused as
well)...
So for a short description of what I've changed:
In general, I abbreviated the aliases, removed a lot of parenthesis,
used explicit JOIN everywhere and replaced the field selection within
your NOT EXISTS to make the statement easier for me to read.
Then, I noticed that you had:
AND (not(IwadisObje_1.visibility <> 2 and not EXISTS (SELECT ...
I find statements like:
'The value must not be different from 2 when there does not exist'
more complicated than:
'The value must equal 2 or there must exist'
so I changed this part. However, further down in your statement, I found
AND (not(((IwadisObje_1.visibility = 0 and not EXISTS (SELECT ... )
and not EXISTS (SELECT ... ))...
(both of these NOT EXISTS contain over 100 parenthesis)
Notice that the two NOT EXISTS are within the outer 'not'. I guess I
could have changed this to:
AND (IwadisObje_1.visibility <> 0 OR EXISTS(...) OR EXISTS(...)
but wasn't certain whether this is what you want.
Below is my suggested statement, tell us whether it helps anything and
show us the plan if it doesn't. I might have forgotten one parenthesis
or so when trying to remove them, but think the below statement should
be simpler to modify. Also, I've ignored considering the NULL state,
e.g. when there neither EXISTS nor NOT EXISTS records. I assume the
mixing of implicit and explicit JOINs can be considered a flaw in your
OR mapper.
HTH,
Set
SELECT I1.BOLD_ID, I1.BOLD_TYPE
FROM IwadisObject I1
JOIN Word W1 ON I1.BOLD_ID = W1.indexedObject
WHERE W1.data LIKE 'SCHAAF%'
AND I1.BOLD_TYPE = 132 or I1.BOLD_TYPE = 131
AND (I1.visibility = 2
or EXISTS (SELECT * FROM Client C1
JOIN IwadisObject I6 ON C1.BOLD_ID = I6.creatorClient
JOIN BusinessClassesRoot B3 ON C1.BOLD_ID = B3.BOLD_ID
WHERE I1.BOLD_ID = I6.BOLD_ID
AND (B3.iwadisID = '68D3C5EE-8EDC-4E5C-BA90-88D495596910'
or B3.iwadisID = '9B58C591-3FF4-4E14-A9E0-F2EE49F4844E'
or B3.iwadisID = '8374F54C-A890-4537-B343-A7F55C5DC060'
or B3.iwadisID = 'C8A7D593-B28B-4A31-94A6-E6C7367CA8CF'
or B3.iwadisID = '6B590600-C19C-4678-B073-5BE22776C744'
or B3.iwadisID = '549E4270-5532-48EF-9311-61D3EA92F22E'
or B3.iwadisID = '345F364D-0B9B-4A51-B5B7-376F467A168D'
or B3.iwadisID = 'CD4A6A54-C0D1-4344-B2B8-243AF7173A7A'
or B3.iwadisID = 'F8C410C0-DE0D-4536-A72C-37AA8D6972DB'
or B3.iwadisID = '23B7773C-1049-4761-BE46-8734B359E309'
or B3.iwadisID = 'BE3D0C31-E882-4EF5-9B4A-7E9EE2159E9C'
or B3.iwadisID = 'B12387B7-9B46-4EAB-B2CD-EA6B99465611'
or B3.iwadisID = '158E33BE-9270-408A-AE9F-D986661C780F'
or B3.iwadisID = '35E449B2-C973-478D-A31C-F4FE6572B60E'
or B3.iwadisID = '3C3162B7-B8E1-4A17-B975-EE0C5DBABA63'
or B3.iwadisID = 'D57BAD0A-821B-4155-9B38-1061DDA5A67B'
or B3.iwadisID = 'E0B9D7F7-48CA-4DB9-A8B8-DD5AC9D9F9AE'
or B3.iwadisID = '93A29955-FC9D-4A36-9AB1-6BA572B44B82'
or B3.iwadisID = '9BF80633-0541-4B4D-A925-89881546C5AB'
or B3.iwadisID = '21DAA5BB-BBCB-4885-9CD4-230C004D8F82')))
AND (not(I1.visibility = 0
and not EXISTS (SELECT * FROM IwadisObject I10
JOIN RoleObjectsHolder RO1 ON RO1.BOLD_ID = I10.holder
JOIN Role_ R1 ON R1.BOLD_ID = RO1.role_
JOIN BusinessClassesRoot B6 ON R1.BOLD_ID = B6.BOLD_ID
WHERE I1.BOLD_ID = I10.BOLD_ID
AND (B6.iwadisID = '1D8FCCC3-52C3-4292-B21E-900BEBFDB61E'
or B6.iwadisID = 'A6DA4CC6-4514-4313-89A5-78B5C6DC7594'
or B6.iwadisID = 'E7E017C9-C0ED-4F00-877E-87A73C7E82E2'
or B6.iwadisID = 'E7F945BE-0821-478B-8624-A67158C60E7F'
or B6.iwadisID = 'B10E4580-B162-4827-96C6-99F582CE0632'
or B6.iwadisID = 'EC68D385-42C3-4D6D-BFB6-63A74C0DB0E2'
or B6.iwadisID = 'BDA02AC6-D306-4900-A566-5C9CCC6D7CCA'
or B6.iwadisID = '607D9D60-5049-450A-BFB9-D5E3CB3BA10A'
or B6.iwadisID = 'E5EF26D2-1C49-41B6-A695-C93A5047CE88'
or B6.iwadisID = '00FB5425-5E14-4467-AEBE-D03ACC723ACC'
or B6.iwadisID = 'AB1C6F2D-01C4-42D4-87FC-081CD0B74863'
or B6.iwadisID = 'A879E175-CF15-4F8E-80EE-E1A14A4650BE'
or B6.iwadisID = '26F0266C-D566-44A6-8048-287F5DBC9AF2'
or B6.iwadisID = 'E21C6F0E-DB18-40E2-9891-A55D35FE4A70'
or B6.iwadisID = 'FF7080A2-3274-496F-A63F-367E0C22E29A'
or B6.iwadisID = '5939A987-0610-4310-ADDB-EC3D2220FE3D'
or B6.iwadisID = 'DD8F8E69-46E2-4D6B-9E6E-F1A9ABEAEC85'
or B6.iwadisID = '64BA4B03-AD83-4D0A-BDE1-CA04CD7357AB'
or B6.iwadisID = '6AF6A0F6-0D56-4B36-BA3B-265985436C67'
or B6.iwadisID = '9FEAF7CE-9D1C-4D85-A5DD-24C46FCBF0A1'
or B6.iwadisID = 'F29E8765-42F0-4C15-AECC-581FC7D83869'
or B6.iwadisID = 'C0DE8AD1-A0B5-4B6D-B493-94FB069074B5'
or B6.iwadisID = '0F4D6DE8-5DE9-4BF4-9E0C-A9DF0CD15E5C'
or B6.iwadisID = '201CA2F1-4F47-4528-9D53-F7CB5C848019'
or B6.iwadisID = '53C302DE-83FF-448F-9FFD-6A4A865B570D'
or B6.iwadisID = 'C7465A78-9C8D-44D1-B9A9-EF6A29DCDBDB'
or B6.iwadisID = '6693B56C-5B29-4EEC-BCC7-0975F4655BDC'
or B6.iwadisID = 'E66E7E6B-2B1E-4387-9CE1-CFAF9A86EA71'
or B6.iwadisID = '3AAB53ED-8649-4EDD-AE83-A0AF2EBAED80'
or B6.iwadisID = '6E5802E5-D4C7-47E3-9A2E-D7FD5057D25F'
or B6.iwadisID = 'BF5B75B7-C8B9-4966-B981-9B27990CFF2E'
or B6.iwadisID = '4578DD58-6960-4C3C-8FC0-E644FBE819E5'
or B6.iwadisID = 'E782A2CF-8915-47A7-B72E-3B40808584AE'
or B6.iwadisID = 'AF7677D3-02C3-44AD-A939-445DEC9C4214'
or B6.iwadisID = '17EFE29A-5457-4777-A89E-78B373EF56FB'
or B6.iwadisID = 'AE51FCE0-7907-4CCD-8667-E240CE2FC2BD'
or B6.iwadisID = 'ED57E4DF-4521-4926-8051-B1F9187C0E5A'
or B6.iwadisID = 'D87EC895-928C-41AE-B3ED-66E75C48B8A0'
or B6.iwadisID = 'AD3957DC-CAE0-4ABA-9A8A-0A47C496C4BB'
or B6.iwadisID = '40639C06-573B-45C8-8C3B-7DA5D5994982'
or B6.iwadisID = '4907B94B-4B60-4A46-82EF-E05B30C3AAF9'
or B6.iwadisID = '37D79ABE-3865-4B22-97DF-C2E17639C511'
or B6.iwadisID = 'C5C86613-F424-4742-A9C0-8C260C113FEF'
or B6.iwadisID = '87681A33-C766-4999-B677-52116D5831D5'
or B6.iwadisID = 'C7F8E9310F014095B893F46497371C37'
or B6.iwadisID = '86010DB3-9D2A-4DE6-ABBA-3B2C4B32801A'
or B6.iwadisID = '3AFD66B8-8E07-4FE8-9068-AB9A4D06FB74'
or B6.iwadisID = '10CB94F5-7977-4CE5-BF5F-7A8AB8183024'
or B6.iwadisID = '084A1633-5A6C-4561-98F4-426CCD62C677'
or B6.iwadisID = '6F3CA423-16C6-493C-8D2C-1D18CDEAE34E'
or B6.iwadisID = '90CFD874-56B3-4C7F-8968-469C612BC045'
or B6.iwadisID = '3222066A-16DF-4768-828E-BDB6C96C2CE4'
or B6.iwadisID = '4CD045BA-81E4-480B-BE90-C62D4FB51392'
or B6.iwadisID = '2B416D72-5839-4053-8BA4-DE543891B8F9'
or B6.iwadisID = '33D714D8-A777-4A27-B263-88488981F654'
or B6.iwadisID = '4C77CAE8-2ADA-46A1-9024-FCF2FE75FCF2'
or B6.iwadisID = 'B725665E-9C93-4AD4-8E51-C94A40CE929B'
or B6.iwadisID = '7FDF3D63-16FE-49F3-AFC2-1BCA1EB790E0'
or B6.iwadisID = 'F0CEA3D6-9B5A-4C9F-90F9-DA2F263FAF69'
or B6.iwadisID = 'C05AC12B-FAB0-4348-9AB4-4BD0C7F6F242'
or B6.iwadisID = '6261349D-7939-4AE2-87A4-4C4E280D6551'
or B6.iwadisID = '7B41C74F-27DA-4A97-AC88-17C7420D1512'
or B6.iwadisID = '00077ED1-63BB-493F-9705-EEE2D79DB638'
or B6.iwadisID = 'BB0432F1-55E8-4870-B53E-48FD2A7D335A'
or B6.iwadisID = 'D8D39BAA-AD5E-4DDB-906C-F815104CAB40'
or B6.iwadisID = 'FB287846-C77C-4F79-9095-9ABD8C196C7F'
or B6.iwadisID = '8C335F4A-ED01-49A8-8233-3CEC9CC7B2C3'
or B6.iwadisID = 'CB2DFB0F-A0F9-48BE-ADFC-15D3824EF763'
or B6.iwadisID = '6DEE8CCB-FFCF-4676-9826-B53AE784A523'
or B6.iwadisID = 'AA01F3AD-9BA3-4311-9782-427DD88FCDB2'
or B6.iwadisID = 'EAAE48F9-E476-4B1F-BAF5-CF005F36EC8C'
or B6.iwadisID = '53C16C0A-8853-4EF3-8708-30DC973FBC51'
or B6.iwadisID = '2BE80EC4-C112-457E-9FA6-3FF2468949DF'
or B6.iwadisID = '0492C3CF-F678-4D44-880C-4ACFE403A219'
or B6.iwadisID = 'B39FB5E5-40CD-40C3-8003-ED6CEB85AA0C'
or B6.iwadisID = '819774B5-74B8-41C6-AA60-EC3B4BE2406A'
or B6.iwadisID = '6641A220-503E-4519-AB18-399651037FD7'
or B6.iwadisID = '9E81801515404AA6932B96612D2D63BA'
or B6.iwadisID = 'E821C777-368F-453E-841C-610AA01D5A8C'
or B6.iwadisID = '76EE9995-FCF6-417F-8001-C311A66C17D2'
or B6.iwadisID = '6A67CE80-E070-4C8A-8080-31DEFDDBC8FD'
or B6.iwadisID = 'E45E2991-9BEF-47DC-B2F6-E560522350FF'
or B6.iwadisID = '120860AC98A1499D8C3FF1F6788F9D94'
or B6.iwadisID = '50838DC8-9E59-4F71-A259-8A7368697128'
or B6.iwadisID = '2032B721-9196-4FCD-A7F6-2FA72B40B94E'
or B6.iwadisID = 'B10E3DA8-999D-4B44-AEA2-231BCAD71AD9'
or B6.iwadisID = '97CC84C6-1222-4193-BD63-A9B0968632D7'
or B6.iwadisID = '9ED8B0EE-7516-4CB1-A10E-EE9D6D8B9918'
or B6.iwadisID = 'EC8E3410-475E-4203-9CB4-F33E3AA216E2'
or B6.iwadisID = 'A3A3D81D-A947-4E97-BA46-EB65B1408361'
or B6.iwadisID = '93D1FD77-1E5C-411C-ACE3-E6B0E0CFE2DD'
or B6.iwadisID = '24347D49-C62D-4FB4-B3BE-5CC42897F139'
or B6.iwadisID = 'BF99E0F5-68AB-429E-A345-8DA0D4005A0D'
or B6.iwadisID = '00107FCC-B8B2-4DD9-8807-AD229C2D5B43'
or B6.iwadisID = 'FC41E093-7922-43F0-AC23-FD3E4952BDB8'
or B6.iwadisID = '20C6214F-633A-44DC-BCFE-E63E085DA1E9'
or B6.iwadisID = 'A417B40B-93D4-4BFF-900F-46CB8BD2EF4D'
or B6.iwadisID = '595819D9-469D-4534-ADD8-A5D498F6D017'
or B6.iwadisID = '1960BD48-4D41-4BC1-A233-4AEAB2A5FFF9'
or B6.iwadisID = '3F50889F-82C0-4B6B-B430-DB60038AE922'
or B6.iwadisID = 'E1DFBE2B-8478-4071-BB73-E212EA31F08B'
or B6.iwadisID = 'C7F507F760C44568A54D0C23D978B0BA'
or B6.iwadisID = '37EC3961D21E47218CA194737B3D1CDC'
or B6.iwadisID = '1C24958771BA4B6B9673EAF8C6FD2CE3'
or B6.iwadisID = '1CC3C5904C94496F8DA8E486DE441672'
or B6.iwadisID = '1C874E461F65434FA02B7ACA7C0B70BA'
or B6.iwadisID = '57F1B01619974857B8D3E8C6A46BE6B5'))
and not EXISTS (
SELECT * FROM IwadisObject I12
JOIN IwadisObject_Role I11 ON I11.coworkerObjects = I12.BOLD_ID
JOIN Role_ R2 ON I11.coworkerRoles = R2.BOLD_ID
JOIN BusinessClassesRoot B7 ON R2.BOLD_ID = B7.BOLD_ID
WHERE I1.BOLD_ID = I12.BOLD_ID
AND (B7.iwadisID = '1D8FCCC3-52C3-4292-B21E-900BEBFDB61E'
or B7.iwadisID = 'A6DA4CC6-4514-4313-89A5-78B5C6DC7594'
or B7.iwadisID = 'E7E017C9-C0ED-4F00-877E-87A73C7E82E2'
or B7.iwadisID = 'E7F945BE-0821-478B-8624-A67158C60E7F'
or B7.iwadisID = 'B10E4580-B162-4827-96C6-99F582CE0632'
or B7.iwadisID = 'EC68D385-42C3-4D6D-BFB6-63A74C0DB0E2'
or B7.iwadisID = 'BDA02AC6-D306-4900-A566-5C9CCC6D7CCA'
or B7.iwadisID = '607D9D60-5049-450A-BFB9-D5E3CB3BA10A'
or B7.iwadisID = 'E5EF26D2-1C49-41B6-A695-C93A5047CE88'
or B7.iwadisID = '00FB5425-5E14-4467-AEBE-D03ACC723ACC'
or B7.iwadisID = 'AB1C6F2D-01C4-42D4-87FC-081CD0B74863'
or B7.iwadisID = 'A879E175-CF15-4F8E-80EE-E1A14A4650BE'
or B7.iwadisID = '26F0266C-D566-44A6-8048-287F5DBC9AF2'
or B7.iwadisID = 'E21C6F0E-DB18-40E2-9891-A55D35FE4A70'
or B7.iwadisID = 'FF7080A2-3274-496F-A63F-367E0C22E29A'
or B7.iwadisID = '5939A987-0610-4310-ADDB-EC3D2220FE3D'
or B7.iwadisID = 'DD8F8E69-46E2-4D6B-9E6E-F1A9ABEAEC85'
or B7.iwadisID = '64BA4B03-AD83-4D0A-BDE1-CA04CD7357AB'
or B7.iwadisID = '6AF6A0F6-0D56-4B36-BA3B-265985436C67'
or B7.iwadisID = '9FEAF7CE-9D1C-4D85-A5DD-24C46FCBF0A1'
or B7.iwadisID = 'F29E8765-42F0-4C15-AECC-581FC7D83869'
or B7.iwadisID = 'C0DE8AD1-A0B5-4B6D-B493-94FB069074B5'
or B7.iwadisID = '0F4D6DE8-5DE9-4BF4-9E0C-A9DF0CD15E5C'
or B7.iwadisID = '201CA2F1-4F47-4528-9D53-F7CB5C848019'
or B7.iwadisID = '53C302DE-83FF-448F-9FFD-6A4A865B570D'
or B7.iwadisID = 'C7465A78-9C8D-44D1-B9A9-EF6A29DCDBDB'
or B7.iwadisID = '6693B56C-5B29-4EEC-BCC7-0975F4655BDC'
or B7.iwadisID = 'E66E7E6B-2B1E-4387-9CE1-CFAF9A86EA71'
or B7.iwadisID = '3AAB53ED-8649-4EDD-AE83-A0AF2EBAED80'
or B7.iwadisID = '6E5802E5-D4C7-47E3-9A2E-D7FD5057D25F'
or B7.iwadisID = 'BF5B75B7-C8B9-4966-B981-9B27990CFF2E'
or B7.iwadisID = '4578DD58-6960-4C3C-8FC0-E644FBE819E5'
or B7.iwadisID = 'E782A2CF-8915-47A7-B72E-3B40808584AE'
or B7.iwadisID = 'AF7677D3-02C3-44AD-A939-445DEC9C4214'
or B7.iwadisID = '17EFE29A-5457-4777-A89E-78B373EF56FB'
or B7.iwadisID = 'AE51FCE0-7907-4CCD-8667-E240CE2FC2BD'
or B7.iwadisID = 'ED57E4DF-4521-4926-8051-B1F9187C0E5A'
or B7.iwadisID = 'D87EC895-928C-41AE-B3ED-66E75C48B8A0'
or B7.iwadisID = 'AD3957DC-CAE0-4ABA-9A8A-0A47C496C4BB'
or B7.iwadisID = '40639C06-573B-45C8-8C3B-7DA5D5994982'
or B7.iwadisID = '4907B94B-4B60-4A46-82EF-E05B30C3AAF9'
or B7.iwadisID = '37D79ABE-3865-4B22-97DF-C2E17639C511'
or B7.iwadisID = 'C5C86613-F424-4742-A9C0-8C260C113FEF'
or B7.iwadisID = '87681A33-C766-4999-B677-52116D5831D5'
or B7.iwadisID = 'C7F8E9310F014095B893F46497371C37'
or B7.iwadisID = '86010DB3-9D2A-4DE6-ABBA-3B2C4B32801A'
or B7.iwadisID = '3AFD66B8-8E07-4FE8-9068-AB9A4D06FB74'
or B7.iwadisID = '10CB94F5-7977-4CE5-BF5F-7A8AB8183024'
or B7.iwadisID = '084A1633-5A6C-4561-98F4-426CCD62C677'
or B7.iwadisID = '6F3CA423-16C6-493C-8D2C-1D18CDEAE34E'
or B7.iwadisID = '90CFD874-56B3-4C7F-8968-469C612BC045'
or B7.iwadisID = '3222066A-16DF-4768-828E-BDB6C96C2CE4'
or B7.iwadisID = '4CD045BA-81E4-480B-BE90-C62D4FB51392'
or B7.iwadisID = '2B416D72-5839-4053-8BA4-DE543891B8F9'
or B7.iwadisID = '33D714D8-A777-4A27-B263-88488981F654'
or B7.iwadisID = '4C77CAE8-2ADA-46A1-9024-FCF2FE75FCF2'
or B7.iwadisID = 'B725665E-9C93-4AD4-8E51-C94A40CE929B'
or B7.iwadisID = '7FDF3D63-16FE-49F3-AFC2-1BCA1EB790E0'
or B7.iwadisID = 'F0CEA3D6-9B5A-4C9F-90F9-DA2F263FAF69'
or B7.iwadisID = 'C05AC12B-FAB0-4348-9AB4-4BD0C7F6F242'
or B7.iwadisID = '6261349D-7939-4AE2-87A4-4C4E280D6551'
or B7.iwadisID = '7B41C74F-27DA-4A97-AC88-17C7420D1512'
or B7.iwadisID = '00077ED1-63BB-493F-9705-EEE2D79DB638'
or B7.iwadisID = 'BB0432F1-55E8-4870-B53E-48FD2A7D335A'
or B7.iwadisID = 'D8D39BAA-AD5E-4DDB-906C-F815104CAB40'
or B7.iwadisID = 'FB287846-C77C-4F79-9095-9ABD8C196C7F'
or B7.iwadisID = '8C335F4A-ED01-49A8-8233-3CEC9CC7B2C3'
or B7.iwadisID = 'CB2DFB0F-A0F9-48BE-ADFC-15D3824EF763'
or B7.iwadisID = '6DEE8CCB-FFCF-4676-9826-B53AE784A523'
or B7.iwadisID = 'AA01F3AD-9BA3-4311-9782-427DD88FCDB2'
or B7.iwadisID = 'EAAE48F9-E476-4B1F-BAF5-CF005F36EC8C'
or B7.iwadisID = '53C16C0A-8853-4EF3-8708-30DC973FBC51'
or B7.iwadisID = '2BE80EC4-C112-457E-9FA6-3FF2468949DF'
or B7.iwadisID = '0492C3CF-F678-4D44-880C-4ACFE403A219'
or B7.iwadisID = 'B39FB5E5-40CD-40C3-8003-ED6CEB85AA0C'
or B7.iwadisID = '819774B5-74B8-41C6-AA60-EC3B4BE2406A'
or B7.iwadisID = '6641A220-503E-4519-AB18-399651037FD7'
or B7.iwadisID = '9E81801515404AA6932B96612D2D63BA'
or B7.iwadisID = 'E821C777-368F-453E-841C-610AA01D5A8C'
or B7.iwadisID = '76EE9995-FCF6-417F-8001-C311A66C17D2'
or B7.iwadisID = '6A67CE80-E070-4C8A-8080-31DEFDDBC8FD'
or B7.iwadisID = 'E45E2991-9BEF-47DC-B2F6-E560522350FF'
or B7.iwadisID = '120860AC98A1499D8C3FF1F6788F9D94'
or B7.iwadisID = '50838DC8-9E59-4F71-A259-8A7368697128'
or B7.iwadisID = '2032B721-9196-4FCD-A7F6-2FA72B40B94E'
or B7.iwadisID = 'B10E3DA8-999D-4B44-AEA2-231BCAD71AD9'
or B7.iwadisID = '97CC84C6-1222-4193-BD63-A9B0968632D7'
or B7.iwadisID = '9ED8B0EE-7516-4CB1-A10E-EE9D6D8B9918'
or B7.iwadisID = 'EC8E3410-475E-4203-9CB4-F33E3AA216E2'
or B7.iwadisID = 'A3A3D81D-A947-4E97-BA46-EB65B1408361'
or B7.iwadisID = '93D1FD77-1E5C-411C-ACE3-E6B0E0CFE2DD'
or B7.iwadisID = '24347D49-C62D-4FB4-B3BE-5CC42897F139'
or B7.iwadisID = 'BF99E0F5-68AB-429E-A345-8DA0D4005A0D'
or B7.iwadisID = '00107FCC-B8B2-4DD9-8807-AD229C2D5B43'
or B7.iwadisID = 'FC41E093-7922-43F0-AC23-FD3E4952BDB8'
or B7.iwadisID = '20C6214F-633A-44DC-BCFE-E63E085DA1E9'
or B7.iwadisID = 'A417B40B-93D4-4BFF-900F-46CB8BD2EF4D'
or B7.iwadisID = '595819D9-469D-4534-ADD8-A5D498F6D017'
or B7.iwadisID = '1960BD48-4D41-4BC1-A233-4AEAB2A5FFF9'
or B7.iwadisID = '3F50889F-82C0-4B6B-B430-DB60038AE922'
or B7.iwadisID = 'E1DFBE2B-8478-4071-BB73-E212EA31F08B'
or B7.iwadisID = 'C7F507F760C44568A54D0C23D978B0BA'
or B7.iwadisID = '37EC3961D21E47218CA194737B3D1CDC'
or B7.iwadisID = '1C24958771BA4B6B9673EAF8C6FD2CE3'
or B7.iwadisID = '1CC3C5904C94496F8DA8E486DE441672'
or B7.iwadisID = '1C874E461F65434FA02B7ACA7C0B70BA'
or B7.iwadisID = '57F1B01619974857B8D3E8C6A46BE6B5'))
))
swestner wrote:
> 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