Thank you Svein.

here is the plan for original SQL

PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN
(JOIN (MOC_ITEMS NATURAL,PARTIES INDEX (PK_PARTIES)),PARTYEMPLOYEES
INDEX (PK_PARTYEMPLOYEES)),PARTIES_1 INDEX
(PK_PARTIES)),MOC_OFFICEEMPLOYEES_1 INDEX
(PK_PARTYEMPLOYEES)),MOC_CATEGORIES INDEX
(PK_MOC_CATEGORIES)),MOC_SUBCATEGORIES INDEX
(PK_MOC_SUBCATEGORIES)),STATUSES INDEX (PK_STATUSES)),PRIORITIES
INDEX (PK_PRIORITIES)),STATUSES_1 INDEX
(PK_STATUSES)),MOC_ITEMCOMMENTS NATURAL),MOC_COPIEDITEMS INDEX
(PK_MOC_COPIEDITEMS))

and here is the one for yours

PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN
(JOIN (MI NATURAL,PS INDEX (PK_PARTIES)),PR INDEX (PK_PARTIES)),PE
INDEX (PK_PARTYEMPLOYEES)),PEMO INDEX (PK_PARTYEMPLOYEES)),SA INDEX
(PK_STATUSES)),MC INDEX (PK_MOC_CATEGORIES)),MSC INDEX
(PK_MOC_SUBCATEGORIES)),SS INDEX (PK_STATUSES)),PP INDEX
(PK_PRIORITIES)),MIC NATURAL),MCI INDEX (PK_MOC_COPIEDITEMS))



--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Hi!
>
> Let's clean your sql so that it is easier for me to read (removing
> parenthesis, using alias everywhere and use only LEFT JOIN):
>
> SELECT MI.*,
> PS.NAME AS SENDERPARTY,
> PE.NAME AS SENDERPERSON,
> PR.NAME AS RECIPIENTPARTY,
> PEMO.NAME AS RECIPIENTPERSON,
> MC.CATEGORY,
> MSC.SUBCATEGORY,
> SS.NAME AS STATUS,
> PP.NAME AS PRIORITY,
> SA.NAME AS ACCESSSTATUS,
> MIC.COMMENTLINE,
> MCI.USERID AS COPIEDUSERID
> FROM MOC_ITEMS MI
> LEFT JOIN PARTIES PS ON PS.PARTYID = MI.SENDERPARTYID
> LEFT JOIN PARTIES PR ON PR.PARTYID = MI.RECIPIENTPARTYID
> LEFT JOIN PARTYEMPLOYEES PE ON PE.OFFICEEMPLOYEEID =

MI.SENDERPERSONID

> LEFT JOIN PARTYEMPLOYEES PEMO ON PEMO.OFFICEEMPLOYEEID =
> MI.RECIPIENTPERSONID
> LEFT JOIN STATUSES SA ON SA.STATUSID = MI.ACCESSSTATUSID
> LEFT JOIN MOC_CATEGORIES MC ON MC.MOCCATEGORYID =

MI.MOCCATEGORYID

> LEFT JOIN MOC_SUBCATEGORIES MSC ON MSC.MOCSUBCATEGORYID =
> MI.MOCSUBCATEGORYID
> LEFT JOIN STATUSES SS ON SS.STATUSID = MI.STATUSID
> LEFT JOIN PRIORITIES PP ON PP.PRIORITYID = MI.PRIORITYID
> LEFT JOIN MOC_ITEMCOMMENTS MIC ON MIC.MOCITEMID = MI.MOCITEMID
> LEFT JOIN MOC_COPIEDITEMS MCI ON MCI.ID = MI.MOCITEMID
> WHERE upper(PS.NAME) = 'ADK'
>
> MOC_ITEMS will always have to be accessed first (since you only

use

> OUTER joins), and since it is not referred to in your WHERE

clause, this

> means it has to go NATURAL, but this isn't a big deal unless the

table

> contains many rows (18000 is nothing).
>
> Am I right in assuming that all other tables are accessed using

their

> primary key or some other almost unique field? If so, I guess the

plan

> should be decent and that to improve performance you would have to
> change some of the joins to inner joins (in your case, this could

be

> very simple, just move whatever alias that is referenced in your

WHERE

> clause up below MOC_ITEMS and remove the word LEFT). I don't quite
> understand why having to change only the where clause is simpler

than

> changing both the WHERE and JOIN clauses.
>
> Try to prepare (you don't have to run a query to get the plan, DB
> Workbench has a (square?) button for preparing, and you get the
> resulting plan in the lower part of the window) your original

query and

> the query I wrote above, compare the plans and report back to this

list.

>
> HTH,
> Set
>
> d_dude_2003 wrote:
> > here is the actual SQL
> >
> > SELECT MOC_ITEMS.*,
> > PARTIES.NAME AS SENDERPARTY,
> > PARTYEMPLOYEES.NAME AS SENDERPERSON,
> > PARTIES_1.NAME AS RECIPIENTPARTY,
> > MOC_OFFICEEMPLOYEES_1.NAME AS RECIPIENTPERSON,
> > MOC_CATEGORIES.CATEGORY,
> > MOC_SUBCATEGORIES.SUBCATEGORY,
> > STATUSES.NAME AS STATUS,
> > PRIORITIES.NAME AS PRIORITY,
> > STATUSES_1.NAME AS ACCESSSTATUS,
> > MOC_ITEMCOMMENTS.COMMENTLINE,
> > MOC_COPIEDITEMS.USERID AS COPIEDUSERID
> > FROM ((STATUSES STATUSES_1 RIGHT JOIN
> > (((((PARTYEMPLOYEES MOC_OFFICEEMPLOYEES_1
> > RIGHT JOIN (PARTIES PARTIES_1
> > RIGHT JOIN (PARTYEMPLOYEES
> > RIGHT JOIN (PARTIES
> > RIGHT JOIN MOC_ITEMS ON PARTIES.PARTYID =

MOC_ITEMS.SENDERPARTYID)

> > ON PARTYEMPLOYEES.OFFICEEMPLOYEEID = MOC_ITEMS.SENDERPERSONID)
> > ON PARTIES_1.PARTYID = MOC_ITEMS.RECIPIENTPARTYID)
> > ON MOC_OFFICEEMPLOYEES_1.OFFICEEMPLOYEEID =
> > MOC_ITEMS.RECIPIENTPERSONID)
> > LEFT JOIN MOC_CATEGORIES ON MOC_ITEMS.MOCCATEGORYID =
> > MOC_CATEGORIES.MOCCATEGORYID)
> > LEFT JOIN MOC_SUBCATEGORIES ON MOC_ITEMS.MOCSUBCATEGORYID =
> > MOC_SUBCATEGORIES.MOCSUBCATEGORYID)
> > LEFT JOIN STATUSES ON MOC_ITEMS.STATUSID = STATUSES.STATUSID)
> > LEFT JOIN PRIORITIES ON MOC_ITEMS.PRIORITYID =

PRIORITIES.PRIORITYID)

> > ON STATUSES_1.STATUSID = MOC_ITEMS.ACCESSSTATUSID)
> > LEFT JOIN MOC_ITEMCOMMENTS ON MOC_ITEMS.MOCITEMID =
> > MOC_ITEMCOMMENTS.MOCITEMID)
> > LEFT JOIN MOC_COPIEDITEMS ON MOC_ITEMS.MOCITEMID =

MOC_COPIEDITEMS.ID

> > WHERE (upper(PARTIES.NAME) = 'ADK')
> >
> > When i run this in Database Workbench, i even cant go to Plan
> > Analysis, CPU usage gets to 100% on firebird process...
> >
> > I understand that the query is not optimal. But it allows to

search by

> > all the fields with varying WHERE statement...
> >
> > Is there a way to improve the speed on large dataset?
> >
> > Thanks.
>