| Subject | Re: [firebird-support] Re: Need Speed Optimization for SQL |
|---|---|
| Author | Vishal Tiwari |
| Post date | 2015-04-16T06:45:48Z |
@Rob: Ya, this field is already a primary key in the same table and there a separate index created on this single column.
On Thursday, 16 April 2015 9:58 AM, "Virgo Pärna virgo.parna@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:
On Wed, 15 Apr 2015 21:54:32 +0000 (UTC), Vishal Tiwari vishualsoft@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
>
>
> 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
I would start by rewriting IN as a JOIN - something like:
SELECT Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered, CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM CRM_DOCUMENT_ITEMS JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER = CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
JOIN CRM_JOB_ITEMS ON CRM_DOCUMENT_ITEMS.FK_JOB_ITEM = CRM_JOB_ITEMS.PK_JOB_ITEMS
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and
CRM_JOB_ITEMS.FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}'
--
Virgo Pärna
virgo.parna@...