Subject Re: [firebird-support] Need Speed Optimization for SQL
Author Vishal Tiwari
Post date 2015-04-15T22:03:04Z
I tried with below SQL, but it is also taking 5 seconds,

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   
    exists (select PK_JOB_ITEMS from CRM_JOB_ITEMS where CRM_JOB_ITEMS.PK_JOB_ITEMS = CRM_DOCUMENT_ITEMS.FK_JOB_ITEM  And FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
    and CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1
GROUP BY
  CRM_DOCUMENT_ITEMS.FK_JOB_ITEM



On Thursday, 16 April 2015 3:24 AM, "Vishal Tiwari vishualsoft@... [firebird-support]" <firebird-support@yahoogroups.com> 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