Hi

Try replacing your IN statement with an EXISTS clause. Something like

CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 AND EXISTS(select PK_JOB_ITEMS
from CRM_JOB_ITEMS where PK_JOB_ITEMS = CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
and FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}' ROWS 1)

Cheers
Rob


On 16/04/2015 9:54 a.m., Vishal Tiwari vishualsoft@...
[firebird-support] wrote:

> Hi All,
>
> I have below SQL and DDL of respective columns used in this SQL.
>
> Below sql takes 6-7 seconds to give the result.
>
> Would it be possible to bring the output time to 1 seconds, coz there
> are some other operartion I need to perform based on this sql output
> withing short period of time.
>
> SQL:
>
>
> SELECT
> Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
> CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
> FROM
> CRM_DOCUMENT_ITEMS
> INNER JOIN CRM_DOCUMENT_HEADER ON
> CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER =
> CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
> WHERE
> CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and
> CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in (select PK_JOB_ITEMS from
> CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
> GROUP BY
> CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
>
> -----------------------------------------------------------------------
> DDL:
>
>
> There are some other columns also in below three tables:
>
> CRM_DOCUMENT_ITEMS Table (Total Rows in this table : 803151)
> QUANTITY : Numeric(15,2)
> FK_JOB_ITEM : Char(38) (GUID)
> FK_DOCUMENT_HEADER : Char(38) (GUID)
>
>
> CRM_DOCUMENT_HEADER Table (Total Rows in this table : 110238)
> PK_DOCUMENT_HEADER : Char(38) (GUID)
> DOCUMENT_TYPE : Integer
>
> CRM_JOB_ITEMS Table (Total Rows in this table : 503743)
> PK_JOB_ITEMS : Char(38) (GUID)
> FK_JOB : Char(38) (GUID)
>
>
>
> Thanks In Advance.
>
>
> With Best Regards.
>
> Vishal
>
>