Stefan,

> The one with like:
>
> SELECT

Congratulations!

This is the single most complicated SQL statement that I have seen
through my 14 years of using/supporting IB/FB!!!

I am not surprised that FB is having a hard time optimizing it.

That said; I find that some of the SQL is needlessly complicated and in
some cases uses JOIN clause syntax which I don't recall seeing before.

For the needlessly complicated, let's try:

> WHERE IwadisObje_1.BOLD_ID = IwadisObje_5.BOLD_ID AND
> ((((((((BusinessCl_3.iwadisID = 'C065B968-4F39-4DF0-90C9-
> 6FDF7B91328E') or (BusinessCl_3.iwadisID = '177BADBB-3FF1-40FE-AD05-
> 3A41410F969F')) or (BusinessCl_3.iwadisID = 'DD76699F-CAA3-48E4-953C-
> B79FBF05401A')) or (BusinessCl_3.iwadisID = '38D902DB-C62D-4F12-A77E-
> F72EDDEF1092')) or (BusinessCl_3.iwadisID = 'AE0353BF-D7F0-4710-982B-
> B8110FC21103')) or (BusinessCl_3.iwadisID = '5F4809FC-FB16-48AF-A4FA-
> E49BF5DD728E')) or (BusinessCl_3.iwadisID = '12B9E4FE-0D5D-4C5D-82FB-
> A02CF566E06C')) or (BusinessCl_3.iwadisID = 'A4492F9C-7991-406E-8A1C-
> D08F1779F2B9'))

This can be written as:

WHERE
IwadisObje_1.BOLD_ID = IwadisObje_5.BOLD_ID
AND BusinessCl_3.iwadisID IN(
'C065B968-4F39-4DF0-90C9-6FDF7B91328E',
'177BADBB-3FF1-40FE-AD05-3A41410F969F',
'DD76699F-CAA3-48E4-953C-B79FBF05401A',
'38D902DB-C62D-4F12-A77E-F72EDDEF1092',
'AE0353BF-D7F0-4710-982B-B8110FC21103',
'5F4809FC-FB16-48AF-A4FA-E49BF5DD728E',
'12B9E4FE-0D5D-4C5D-82FB-A02CF566E06C',
'A4492F9C-7991-406E-8A1C-D08F1779F2B9'
)

This is significantly easier to read [you don't get lost in the ()
pairs]


For the next needlessly complicated, let's try:

> AND (NOT((EXISTS (SELECT User__5.BOLD_ID FROM User_ User__5 JOIN
> IwadisObject IwadisObje_6 ON (User__5.BOLD_ID =

IwadisObje_6.privateUser)

> WHERE IwadisObje_1.BOLD_ID = IwadisObje_6.BOLD_ID
> ) and EXISTS (SELECT User__6.BOLD_ID
> FROM User_ User__6 JOIN IwadisObject IwadisObje_7 ON (User__6.BOLD_ID

=

> IwadisObje_7.privateUser) JOIN BusinessClassesRoot BusinessCl_5 ON
> (User__6.BOLD_ID = BusinessCl_5.BOLD_ID) WHERE IwadisObje_1.BOLD_ID =
> IwadisObje_7.BOLD_ID AND (BusinessCl_5.iwadisID <>

'50C3B73C-095B-439C-

> BFAF-1BE6EC0BBD2E'))

This can be written as 2 NOT EXISTS clauses:

AND NOT EXISTS(
SELECT User__5.BOLD_ID
FROM User_ User__5
JOIN IwadisObject IwadisObje_6 ON (User__5.BOLD_ID =
IwadisObje_6.privateUser)
WHERE
IwadisObje_1.BOLD_ID = IwadisObje_6.BOLD_ID
)
AND NOT EXISTS(
SELECT User__6.BOLD_ID
FROM User_ User__6
JOIN IwadisObject IwadisObje_7 ON (User__6.BOLD_ID =
IwadisObje_7.privateUser)
JOIN BusinessClassesRoot BusinessCl_5 ON (User__6.BOLD_ID =
BusinessCl_5.BOLD_ID)
WHERE
IwadisObje_1.BOLD_ID = IwadisObje_7.BOLD_ID
AND (BusinessCl_5.iwadisID <>
'50C3B73C-095B-439C-BFAF-1BE6EC0BBD2E')
)

Finally, a needlessly complicated but also performance impacting
statement is:

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

Can be written as the much more efficient:

AND NOT EXISTS(
SELECT 1
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
)


The syntax which has me really confused is:

> FROM User_ User__1
> JOIN BannedObjects_User BannedObje_1 ON (BannedObje_1.bannedUsers =
> User__1.BOLD_ID), Document Document_1, BusinessClassesRoot

BusinessCl_1

What is the JOIN TableA on ..., Table B, Table C supposed to mean?


Sean