OK, these plans seems OK, except that you don't seem to have an index for MOC_ITEMCOMMENTS.MOCITEMID. Create it and see whether this solves your problem.

HTH,
Set

d_dude_2003 wrote:

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

[Non-text portions of this message have been removed]