Hello Sean,

as written in my post I can't change anything of the SQL-Statement.
I'm aware that some things could be better but the whole SQL is
generated from our framework Bold for Delphi from an OCL2SQL-Engine.

BTW, SQL-Server 2005 and Oracle 9/10/11 doesn't have any problems
with that statement...

Stefan

--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...>
wrote:

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