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.
