>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 t ime.
>
>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
Hopefully, Rob or Virgos solutions worked, if not try this:
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
HTH,
Set
