Reputation: 1042
How to improve this SQL Server query with multiple 'CASE'?
I have this SQL query for SQL Server 2008 R2:
Declare @fechaDesde DateTime
Declare @fechaHasta DateTime
set @fechaDesde = '01/01/2014 00:00:00.000'
set @fechaHasta = '31/12/2014 23:59:59.999'
Select Cuenta, isnull(sum(SaldoDebe), 0) as SumaDebe,
isnull(sum(SaldoHaber), 0) as SumaHaber,
isnull(sum(SaldoDebe01), 0) as SumaDebe01,
isnull(sum(SaldoDebe02), 0) as SumaDebe02,
isnull(sum(SaldoDebe03), 0) as SumaDebe03,
isnull(sum(SaldoDebe04), 0) as SumaDebe04,
isnull(sum(SaldoDebe05), 0) as SumaDebe05,
isnull(sum(SaldoDebe06), 0) as SumaDebe06,
isnull(sum(SaldoDebe07), 0) as SumaDebe07,
isnull(sum(SaldoDebe08), 0) as SumaDebe08,
isnull(sum(SaldoDebe09), 0) as SumaDebe09,
isnull(sum(SaldoDebe10), 0) as SumaDebe10,
isnull(sum(SaldoDebe11), 0) as SumaDebe11,
isnull(sum(SaldoDebe12), 0) as SumaDebe12,
isnull(sum(SaldoHaber01), 0) as SumaHaber01,
isnull(sum(SaldoHaber02), 0) as SumaHaber02,
isnull(sum(SaldoHaber03), 0) as SumaHaber03,
isnull(sum(SaldoHaber04), 0) as SumaHaber04,
isnull(sum(SaldoHaber05), 0) as SumaHaber05,
isnull(sum(SaldoHaber06), 0) as SumaHaber06,
isnull(sum(SaldoHaber07), 0) as SumaHaber07,
isnull(sum(SaldoHaber08), 0) as SumaHaber08,
isnull(sum(SaldoHaber09), 0) as SumaHaber09,
isnull(sum(SaldoHaber10), 0) as SumaHaber10,
isnull(sum(SaldoHaber11), 0) as SumaHaber11,
isnull(sum(SaldoHaber12), 0) as SumaHaber12
From(
Select c.Código as Cuenta,
case When d.Debe_Haber = 'D' then d.Importe end as SaldoDebe,
case When d.Debe_Haber = 'H' then d.Importe end as SaldoHaber,
case When d.Debe_Haber = 'D' and Month(fecha) = 1
then d.Importe end as SaldoDebe01,
case When d.Debe_Haber = 'D' and Month(fecha) = 2
then d.Importe end as SaldoDebe02,
case When d.Debe_Haber = 'D' and Month(fecha) = 3
then d.Importe end as SaldoDebe03,
case When d.Debe_Haber = 'D' and Month(fecha) = 4
then d.Importe end as SaldoDebe04,
case When d.Debe_Haber = 'D' and Month(fecha) = 5
then d.Importe end as SaldoDebe05,
case When d.Debe_Haber = 'D' and Month(fecha) = 6
then d.Importe end as SaldoDebe06,
case When d.Debe_Haber = 'D' and Month(fecha) = 7
then d.Importe end as SaldoDebe07,
case When d.Debe_Haber = 'D' and Month(fecha) = 8
then d.Importe end as SaldoDebe08,
case When d.Debe_Haber = 'D' and Month(fecha) = 9
then d.Importe end as SaldoDebe09,
case When d.Debe_Haber = 'D' and Month(fecha) = 10
then d.Importe end as SaldoDebe10,
case When d.Debe_Haber = 'D' and Month(fecha) = 11
then d.Importe end as SaldoDebe11,
case When d.Debe_Haber = 'D' and Month(fecha) = 12
then d.Importe end as SaldoDebe12,
case When d.Debe_Haber = 'H' and Month(fecha) = 1
then d.Importe end as SaldoHaber01,
case When d.Debe_Haber = 'H' and Month(fecha) = 2
then d.Importe end as SaldoHaber02,
case When d.Debe_Haber = 'H' and Month(fecha) = 3
then d.Importe end as SaldoHaber03,
case When d.Debe_Haber = 'H' and Month(fecha) = 4
then d.Importe end as SaldoHaber04,
case When d.Debe_Haber = 'H' and Month(fecha) = 5
then d.Importe end as SaldoHaber05,
case When d.Debe_Haber = 'H' and Month(fecha) = 6
then d.Importe end as SaldoHaber06,
case When d.Debe_Haber = 'H' and Month(fecha) = 7
then d.Importe end as SaldoHaber07,
case When d.Debe_Haber = 'H' and Month(fecha) = 8
then d.Importe end as SaldoHaber08,
case When d.Debe_Haber = 'H' and Month(fecha) = 9
then d.Importe end as SaldoHaber09,
case When d.Debe_Haber = 'H' and Month(fecha) = 10
then d.Importe end as SaldoHaber10,
case When d.Debe_Haber = 'H' and Month(fecha) = 11
then d.Importe end as SaldoHaber11,
case When d.Debe_Haber = 'H' and Month(fecha) = 12
then d.Importe end as SaldoHaber12
From Cuentas as c inner join Diario as d on c.Código = d.Cuenta
Where d.Fecha >= @fechaDesde and d.Fecha <= @fechaHasta
) as table1
group by Cuenta
order by Cuenta
...
There is two tables: Cuentas and Diario. In table Diario I save movements of the accouns. And here are the tables:
Cuentas
It has two fields and 300000 rows: Código and Nombre. It contains the accounts used in the table Diario
Diario
Contains movements of money between accounts of 'Cuentas' table. His structure is
[Apunte] [int] NOT NULL, --Identity
[Fecha] [datetime] NOT NULL,
[Concepto] [nvarchar](255) NULL,
[Cuenta] [nvarchar](9) NULL,
[Importe] [float] NULL,
[Debe_Haber] [nvarchar](1) NULL,
CONSTRAINT [PK_Diario] PRIMARY KEY CLUSTERED
(
[Apunte] ASC
)
Cuenta Concepto Importe Debe_Haber Fecha
----------------------------------------------------------------------------
572000006 C/Ef.A2003313E01/01-572000006 123,52 H 01/02/14
433000077 C/Ef.A2003326E01/01-572000006 21,84 D 01/03/14
572000006 C/Ef.A2003326E01/01-572000006 21,84 H 01/03/14
430000754 C/Ef.A2003503E01/01-572000006 54,83 D 11/04/14
572000006 C/Ef.A2003503E01/01-572000006 54,83 H 12/05/14
430000807 C/Ef.F2030395E03/03-572000006 50,61 D 22/05/14
572000006 C/Ef.F2030395E03/03-572000006 50,61 H 23/08/14
430000497 C/Ef.F2034038E01/01-572000006 581,62 D 05/09/14
572000006 C/Ef.F2034038E01/01-572000006 581,62 H 06/09/14
Fecha is a DateTime field. I have included the index:
CREATE NONCLUSTERED INDEX [<IX_Diario_Fecha>]
ON [dbo].[Diario] ([Fecha])
INCLUDE ([Cuenta],[Importe],[Debe_Haber])
My query takes 3/4 secs, I need improve it to get results faster.
Upvotes: 1
Views: 475
Answers (5)
Reputation: 952
Try this updated query,I removed the multiple isnull and added else 0 in case to handle nulls.
DECLARE @fechaDesde DATETIME
DECLARE @fechaHasta DATETIME
SET @fechaDesde = '01/01/2014 00:00:00.000'
SET @fechaHasta = '31/12/2014 23:59:59.999'
Select Cuenta, sum(SaldoDebe)as SumaDebe, sum(SaldoHaber)as SumaHaber,
sum(SaldoDebe01)as SumaDebe01,
sum(SaldoDebe02)as SumaDebe02,
sum(SaldoDebe03)as SumaDebe03,
sum(SaldoDebe04)as SumaDebe04,
sum(SaldoDebe05)as SumaDebe05,
sum(SaldoDebe06)as SumaDebe06,
sum(SaldoDebe07)as SumaDebe07,
sum(SaldoDebe08)as SumaDebe08,
sum(SaldoDebe09)as SumaDebe09,
sum(SaldoDebe10)as SumaDebe10,
sum(SaldoDebe11)as SumaDebe11,
sum(SaldoDebe12)as SumaDebe12,
sum(SaldoHaber01)as SumaHaber01,
sum(SaldoHaber02)as SumaHaber02,
sum(SaldoHaber03)as SumaHaber03,
sum(SaldoHaber04)as SumaHaber04,
sum(SaldoHaber05)as SumaHaber05,
sum(SaldoHaber06)as SumaHaber06,
sum(SaldoHaber07)as SumaHaber07,
sum(SaldoHaber08)as SumaHaber08,
sum(SaldoHaber09)as SumaHaber09,
sum(SaldoHaber10)as SumaHaber10,
sum(SaldoHaber11)as SumaHaber11,
sum(SaldoHaber12)as SumaHaber12
From(
Select c.Código as Cuenta,
case When d.Debe_Haber = 'D' then d.Importe else 0 end as SaldoDebe,
case When d.Debe_Haber = 'H' then d.Importe else 0 end as SaldoHaber,
case When d.Debe_Haber = 'D' and Month(fecha) = 1 then d.Importe else 0 end as SaldoDebe01,
case When d.Debe_Haber = 'D' and Month(fecha) = 2 then d.Importe else 0 end as SaldoDebe02,
case When d.Debe_Haber = 'D' and Month(fecha) = 3 then d.Importe else 0 end as SaldoDebe03,
case When d.Debe_Haber = 'D' and Month(fecha) = 4 then d.Importe else 0 end as SaldoDebe04,
case When d.Debe_Haber = 'D' and Month(fecha) = 5 then d.Importe else 0 end as SaldoDebe05,
case When d.Debe_Haber = 'D' and Month(fecha) = 6 then d.Importe else 0 end as SaldoDebe06,
case When d.Debe_Haber = 'D' and Month(fecha) = 7 then d.Importe else 0 end as SaldoDebe07,
case When d.Debe_Haber = 'D' and Month(fecha) = 8 then d.Importe else 0 end as SaldoDebe08,
case When d.Debe_Haber = 'D' and Month(fecha) = 9 then d.Importe else 0 end as SaldoDebe09,
case When d.Debe_Haber = 'D' and Month(fecha) = 10 then d.Importe else 0 end as SaldoDebe10,
case When d.Debe_Haber = 'D' and Month(fecha) = 11 then d.Importe else 0 end as SaldoDebe11,
case When d.Debe_Haber = 'D' and Month(fecha) = 12 then d.Importe else 0 end as SaldoDebe12,
case When d.Debe_Haber = 'H' and Month(fecha) = 1 then d.Importe else 0 end as SaldoHaber01,
case When d.Debe_Haber = 'H' and Month(fecha) = 2 then d.Importe else 0 end as SaldoHaber02,
case When d.Debe_Haber = 'H' and Month(fecha) = 3 then d.Importe else 0 end as SaldoHaber03,
case When d.Debe_Haber = 'H' and Month(fecha) = 4 then d.Importe else 0 end as SaldoHaber04,
case When d.Debe_Haber = 'H' and Month(fecha) = 5 then d.Importe else 0 end as SaldoHaber05,
case When d.Debe_Haber = 'H' and Month(fecha) = 6 then d.Importe else 0 end as SaldoHaber06,
case When d.Debe_Haber = 'H' and Month(fecha) = 7 then d.Importe else 0 end as SaldoHaber07,
case When d.Debe_Haber = 'H' and Month(fecha) = 8 then d.Importe else 0 end as SaldoHaber08,
case When d.Debe_Haber = 'H' and Month(fecha) = 9 then d.Importe else 0 end as SaldoHaber09,
case When d.Debe_Haber = 'H' and Month(fecha) = 10 then d.Importe else 0 end as SaldoHaber10,
case When d.Debe_Haber = 'H' and Month(fecha) = 11 then d.Importe else 0 end as SaldoHaber11,
case When d.Debe_Haber = 'H' and Month(fecha) = 12 then d.Importe else 0 end as SaldoHaber12
From Cuentas as c
inner join
(select distinct [Fecha],
[Cuenta],
isnull([Importe],0) as [Importe],
[Debe_Haber] from Diario) as d
on c.Código = d.Cuenta
Where d.Fecha >= @fechaDesde and d.Fecha <= @fechaHasta
) as table1
group by Cuenta
order by Cuenta
CREATE NONCLUSTERED INDEX [<IX_Diario_Fecha>]
ON [dbo].[Diario] ([Fecha])
INCLUDE ([Cuenta],[Importe],[Debe_Haber])
Upvotes: 2
Reputation: 5243
The main cause for poor performance, I feel, is the constant call of the scalar valued functions for each record. To avoid it, you can store one dataset in a indexed temp table and remove redundant code. Try this:
select Month(fecha) FechaMon, *
into #FechaDiarioDebe
from Diario
where Debe_Haber = 'D'
select Month(fecha) FechaMon, *
into #FechaDiarioHaber
from Diario
where Debe_Haber = 'H'
create index IX_Fecha_Cuenta_Debe on #FechaDiarioDebe(fecha) include (FechaMon, Importe)
create index IX_Fecha_Cuenta_Debe on #FechaDiarioHaber(fecha) include (FechaMon, Importe)
Select Cuenta,
sum(SaldoDebe) as SumaDebe,
sum(SaldoHaber) as SumaHaber,
sum(SaldoDebe01) as SumaDebe01,
sum(SaldoDebe02) as SumaDebe02,
sum(SaldoDebe03) as SumaDebe03,
sum(SaldoDebe04) as SumaDebe04,
sum(SaldoDebe05) as SumaDebe05,
sum(SaldoDebe06) as SumaDebe06,
sum(SaldoDebe07) as SumaDebe07,
sum(SaldoDebe08) as SumaDebe08,
sum(SaldoDebe09) as SumaDebe09,
sum(SaldoDebe10) as SumaDebe10,
sum(SaldoDebe11) as SumaDebe11,
sum(SaldoDebe12) as SumaDebe12,
sum(SaldoHaber01) as SumaHaber01,
sum(SaldoHaber02) as SumaHaber02,
sum(SaldoHaber03) as SumaHaber03,
sum(SaldoHaber04) as SumaHaber04,
sum(SaldoHaber05) as SumaHaber05,
sum(SaldoHaber06) as SumaHaber06,
sum(SaldoHaber07) as SumaHaber07,
sum(SaldoHaber08) as SumaHaber08,
sum(SaldoHaber09) as SumaHaber09,
sum(SaldoHaber10) as SumaHaber10,
sum(SaldoHaber11) as SumaHaber11,
sum(SaldoHaber12) as SumaHaber12
from(
Select c.Código as Cuenta,
d.Importe SaldoDebe,
NULL SaldoHaber,
case When FechaMon = 1 then d.Importe end as SaldoDebe01,
case When FechaMon = 2 then d.Importe end as SaldoDebe02,
case When FechaMon = 3 then d.Importe end as SaldoDebe03,
case When FechaMon = 4 then d.Importe end as SaldoDebe04,
case When FechaMon = 5 then d.Importe end as SaldoDebe05,
case When FechaMon = 6 then d.Importe end as SaldoDebe06,
case When FechaMon = 7 then d.Importe end as SaldoDebe07,
case When FechaMon = 8 then d.Importe end as SaldoDebe08,
case When FechaMon = 9 then d.Importe end as SaldoDebe09,
case When FechaMon = 10 then d.Importe end as SaldoDebe10,
case When FechaMon = 11 then d.Importe end as SaldoDebe11,
case When FechaMon = 12 then d.Importe end as SaldoDebe12,
0 SaldoHaber01,
0 SaldoHaber02,
0 SaldoHaber03,
0 SaldoHaber04,
0 SaldoHaber05,
0 SaldoHaber06,
0 SaldoHaber07,
0 SaldoHaber08,
0 SaldoHaber09,
0 SaldoHaber10,
0 SaldoHaber11,
0 SaldoHaber12
From Cuentas as c inner join #FechaDiarioDebe as d on c.Código = d.Cuenta
Where d.Fecha >= @fechaDesde and d.Fecha <= @fechaHasta
union all
Select c.Código as Cuenta,
NULL SaldoDebe,
d.Importe SaldoHaber,
0 SaldoHaber01,
0 SaldoDebe02,
0 SaldoDebe03,
0 SaldoDebe04,
0 SaldoDebe05,
0 SaldoDebe06,
0 SaldoDebe07,
0 SaldoDebe08,
0 SaldoDebe09,
0 SaldoDebe10,
0 SaldoDebe11,
0 SaldoDebe12,
case When FechaMon = 1 then d.Importe end as SaldoHaber01,
case When FechaMon = 2 then d.Importe end as SaldoHaber02,
case When FechaMon = 3 then d.Importe end as SaldoHaber03,
case When FechaMon = 4 then d.Importe end as SaldoHaber04,
case When FechaMon = 5 then d.Importe end as SaldoHaber05,
case When FechaMon = 6 then d.Importe end as SaldoHaber06,
case When FechaMon = 7 then d.Importe end as SaldoHaber07,
case When FechaMon = 8 then d.Importe end as SaldoHaber08,
case When FechaMon = 9 then d.Importe end as SaldoHaber09,
case When FechaMon = 10 then d.Importe end as SaldoHaber10,
case When FechaMon = 11 then d.Importe end as SaldoHaber11,
case When FechaMon = 12 then d.Importe end as SaldoHaber12
From Cuentas as c inner join #FechaDiarioHaber as d on c.Código = d.Cuenta
Where d.Fecha >= @fechaDesde and d.Fecha <= @fechaHasta
)table1
group by Cuenta
order by Cuenta
Upvotes: 0
Reputation: 40319
I agree (as usual) with @Gordon regarding the case statements, they are almost certainly not the cause of your performance problems.
Pivot statements do what your extensive case statements do (quite literally, if you drill down far enough into the execution plans). They’d make you’re code a bit more legible [this is debatable, as pivot syntax is pretty arcane], but again shouldn’t affect performance all that much.
The likeliest source of your performance issue is going to be from the join between Diario and Cuentas.
Cuentas: Is there a primary key or an index on column Código? This might be an issue as you are reading all rows from this table that have any rows selected from Diario. Clustered vs. non-clustered (index) may also be an issue, but possibly not so relevant for this query.
Diario: Is there any index on column Código, or a compound index where Código is the first column? I suspect this is the real problem. Picture SQL performing the join: it takes the first Cuenta, finds all Diarios for that Cuenta (filtering by date), then does it again for every other Cuenta. If there is no index on Código, then for every Cuenta it as to read every single row in Diario in order to be sure it’s found them all. And then it has to do it again for the next Cuenta, and the next, and the next… 300,000 * however many Cuentas you have. Ow. With an index on Código—and this really should be the clustered index on the table—the looping process can retrieve only those rows in Diarios that have the desired Código, saving massive time on looping, I/O, and so forth.
I generally don’t mess with INCLUDE statements unless I’m dead certain they’d help the overall system, and not just the query I’m working on right now. Might help, might not, get the join working first.
Upvotes: 1
Reputation: 7692
I would try the following index: Diario (Cuenta, Pista, Fecha) include (Debe_Haber, Importe). The order of key columns is important, but you may play with it - it is possible that some other order will do the trick if the aforementioned one would not.
On the other hand, I can say from my experience that the best place to pivot the data is the client application.
Upvotes: 0
Reputation: 1269883
The driver of performance isn't the case statements. It is the join, where, and group by.
From Cuentas c inner join
Diario d
on c.Código = d.Cuenta
Where d.Pista in ('00') and d.Fecha >= @fechaDesde and d.Fecha <= @fechaHasta
I would recommend the following indexes: diario(Pista, Fecha, Cuenta) and Cuentas(Codigo).
You could also try reformulating the query using pivot. That may be marginally faster -- and the same indexes should work for that as well.
Upvotes: 1
Related Questions
- Attempting to use result of Case Expression in a join .. need to improve query
- Change CASE in WHERE?
- Speed up Query Run Time with multiple Case When Expressions
- Case Optimization
- SQL query with multiple CASE statements
- SQL Query with multiple CASE statements using the same field as THEN
- Improve this SQL Query for better performance
- Using multiple case statements in select query
- How can query be optimized using CASE in SQL
- Query with many CASE statements - optimization