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.