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.

Interesting to see that you are able to solve most/all of the query
performance problems reported here by working around using CTEs.
Shouldn't that ring a bell for Firebird developers in the optimizer
area? ;-)

Regarding configuration settings for the thread creator:

* What is the result of gstat -h for the database?
* What exact version and Firebird architecture are you using?
* Number of concurrent connections?
* Available RAM?
* What's the TempCacheLimit entry in firebird.conf?
* What's the FileSystemCacheThreshold entry in firebird.conf?



--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.