| Subject | Re: [firebird-support] Need Speed Optimization for SQL |
|---|---|
| Author | Vishal Tiwari |
| Post date | 2015-04-16T07:30Z |
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
(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....
Woooowwwwwww SET !!!!!! You again saved dear friend......
Hats Off to You as Usual My Dear.....
Any other way to make this more faster ? Please.... If possible....
On Thursday, 16 April 2015 12:54 PM, "Vishal Tiwari vishualsoft@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:
Hi SET,
Nice to see you here, but I am getting below error after executing your sql, i think group by clause is needed, would you please help?
Error:
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
On Thursday, 16 April 2015 12:48 PM, "Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:
>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