>Hi SET,
>
>I tried as below way by adding Group By clause:
>
>with TMP(PK_JOB_ITEMS) as
>(select distinct PK_JOB_ITEMS
>from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
>
>select
>Sum(cdi.QUANTITY) AS Delivered,
>cdi.FK_JOB_ITEM
>from tmp t
>join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
>join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
>where cdh.DOCUMENT_TYPE = 1
>group by cdi.FK_JOB_ITEM
>
>And sql is taking 1.893 seconds....
>
>Any other way to make this more faster ? Please.... If possible....

If you have indexes (or if they are keys) for these three fields:

CRM_JOB_ITEMS.FK_JOB
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER

then it will most likely be difficult to optimize this query any further (sometimes having trigger generated summary tables can help, but that does complicate things quite a bit and I've never seen it done when several tables are involved, so I'd only consider this if this particular query was executed frequently and was one of the most important queries in your system).

Note that while I may be considered a Firebird SELECT expert, I am a novice regarding other parts of Firebird (like configuration settings), so maybe someone else can help you speed up your query a bit more.

Set