October 11, 2007 at 12:21 pm
Hello comunity
I have the following query , but they run very,very slower. I want to kno how can optimize this query:
select distinct ml.conta as 'Integração', ml.conta+ml.u_subconta AS CONTA,(select top 1 pc.descricao from pc (nolock) where pc.conta=ml.conta) as 'Tipo
Conta',
(select bl.conta from bl (nolock) where bl.banco=ml.u_subconta) as 'Banco', ml.u_subconta as 'Subconta',
(select sum(mll.edeb) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data >= '20070101'
and mll.data <= '20070131') AS DEBMES,
(select sum(mll.ecre) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data >= '20070101'
and mll.data <= '20070131') AS CRDMES,
(select sum(MLL.EDEB-mll.ecre) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data >= '20070101'
and mll.data <= '20070131') AS SLDMES,
isnull((select sum(mll.edeb) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data < '20070101'),0) AS DEBANT,
isnull((select sum(mll.ecre) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data < '20070101'),0) AS CRDANT,
isnull((select sum(MLL.EDEB-mll.ecre) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data < '20070101'),0)
AS SLDACCANT ,
isnull((select sum(mll.edeb) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data <= '20070131'),0) AS DEBACUM,
isnull((select sum(mll.ecre) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data <= '20070131'),0) AS CRDACUM,
isnull((select sum(MLL.EDEB-mll.ecre) from ml mll (NOLOCK) where mll.conta+mll.u_subconta = ml.conta+ml.u_subconta and mll.data <= '20070131'),0)
AS SLDACUM
from ml (NOLOCK) , pc (nolock), bl (nolock)
where ml.data >= '20070101' and ml.data <= '20070131' and
( ml.conta LIKE '11%' or ml.conta like '12%' or ml.conta like '23%')
and pc.conta=ml.conta and ml.u_subconta=bl.banco
union all
select distinct ml.conta,' ' as conta,(select top 1 pc.descricao from pc (nolock) where pc.conta=ml.conta) as 'Tipo Conta',' ' as 'Banco',
' ' as subconta, (select sum(mll.edeb) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data >= '20070101' and mll.data <= '20070131')as 'Debmes',
(select sum(mll.ecre) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data >= '20070101' and mll.data <= '20070131') AS CRDMES,
(select sum(MLL.EDEB-mll.ecre) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data >= '20070101' and mll.data <= '20070131') AS SLDMES,
isnull((select sum(mll.edeb) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data < '20070101'),0) AS DEBANT,
isnull((select sum(mll.ecre) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data < '20070101'),0) AS CRDANT,
isnull((select sum(MLL.EDEB-mll.ecre) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data < '20070101'),0) AS SLDACCANT,
isnull((select sum(mll.edeb) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data <= '20070131'),0) AS DEBACUM,
isnull((select sum(mll.ecre) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data <= '20070131'),0) AS CRDACUM,
isnull((select sum(MLL.EDEB-mll.ecre) from ml mll (NOLOCK) where mll.conta = ml.conta and mll.data <= '20070131'),0)
AS SLDACUM
from ml (NOLOCK) , pc (nolock), bl (nolock) where ml.data >= '20070101' and ml.data <= '20070131'
and ( ml.conta LIKE '11%' or ml.conta like '12%' or ml.conta like '23%')
and pc.conta=ml.conta order by ml.conta,ml.conta+ml.u_subconta asc
I hope someone could give me some helps
Many thanks
Luis Santos
October 11, 2007 at 12:59 pm
Wow - lots to work with. Most of the sub queries are using subtotals on the same table, and are set up as correlated sub-queries, so each row and each cell requires a new aggregate query to run.
We're going to need to break things up a bit, and optimize each of the main SELECT statements i.e before and after the UNION ALL, separately.
first half:
select distinct
ml.conta as 'Integração',
ml.conta+ml.u_subconta AS CONTA,
pc.descricao as 'Tipo Conta',
bl.conta as 'Banco',
ml.u_subconta as 'Subconta',
DEBMES,CRDMES,SLDMES,
DEBANT,CRDANT,SLDACCANT,
DEBACUM,CRDACUM,SLDACUM
from ml (NOLOCK) inner join
pc (nolock) on pc.conta=ml.conta inner join
bl (nolock) ml.u_subconta=bl.banco inner join
(select
conta,
u_subConta,
sum(case when data between '20070101' and '20070131' then edeb else 0 end) DEBMES,
sum(case when data between '20070101' and '20070131' then ecre else 0 end) crdMES,
sum(case when data between '20070101' and '20070131' then EDEB-ecre else 0 end) SLDMES,
sum(case when data < '20070101' then EDEB else 0 end) DEBANT,
sum(case when data < '20070101' then ecre else 0 end) CRDANT,
sum(case when data < '20070101' then edeb-ecre else 0 end) SLDACCANT ,
sum(case when data < '20080101' then EDEB else 0 end) DEBACUM,
sum(case when data < '20080101' then ecre else 0 end) CRDACUM,
sum(case when data < '20080101' then edeb-ecre else 0 end) SLDACUM ,
from ml
group by conta, u_subConta) MLL on mll.conta+mll.u_subconta = ml.conta+ml.u_subconta
group by
where ml.data >= '20070101' and ml.data <= '20070131' and
( ml.conta LIKE '11%' or ml.conta like '12%' or ml.conta like '23%')
This uses a derived table instead, so the totals are calculated through one run of the query, and the result are then matched up after the one run. Yours used a series of correlated sub-queries (the sub query relies on data in the outer query to run) so those are rerun once per column per row (in your case - that would be 9 queries PER ROW).
See if that doesn't run a LOT faster. I'll take a look at the second half in a little while.
EDIT - I notice something extra that should NOT be in there. remove the second GROUP BY (copy and paste problem from your old code). It's in bold up above now
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2007 at 1:51 pm
Hello again Matt
Sorry, if i send you this post , but i send to you a private message and i know if you will receive them, so i attache the XLS file to see what my slower tsql build and i know if you make change in your code to return the same result.
Sorry again and many thanks
Luis Santos
October 11, 2007 at 2:06 pm
one small syntax error - edited above. re-read my previous post now. Sorry!
I don't have any of your underlying data, so I no way to test - however - I'm fairly confident they're functionally equivalent.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2007 at 7:47 pm
luissantos (10/11/2007)
Hello again MattSorry, if i send you this post , but i send to you a private message and i know if you will receive them, so i attache the XLS file to see what my slower tsql build and i know if you make change in your code to return the same result.
Sorry again and many thanks
Luis Santos
There's a couple of problems with private messages of this nature... 1. Matt is pretty darned good with code, but if you send him private messages, no one else gets to see them and no one else can even try to help. 2. No one else benefits from anything in a private message. Whole idea of a forum is to share information and ideas... not have a free private consultant.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 12:33 pm
for better or worse - I haven't received a PM - so the whole communication is right here.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply