April 1, 2008 at 12:59 pm
I wrote a SQL Query which simply sums a field from a table, and there are some conditions for it. The situation is that when I add some specific combinations to where clausule it just hang up and never show results...
The code:
Set @Result = (Select Sum(CobrosDetalles.Monto)
From CobrosDetalles
Inner Join Cobros on CobrosDetalles.CobroId = Cobros.CobroId
Inner Join Contratos on Contratos.DocumentoCXCId = CobrosDetalles.DocumentoCXCId
Inner Join GruposCobranzas on GruposCobranzas.GrupoCobranzaId = Contratos.GrupoCobranzaId
Inner Join Bancos on GruposCobranzas.Bancoid = Bancos.Bancoid
Where (Contratos.GrupoCobranzaId = (Case @GrupoCobranzaId When 0 Then Contratos.GrupoCobranzaId Else @GrupoCobranzaId End)) And
(Contratos.EntidadId = (Case @EntidadId When 0 Then Contratos.EntidadId Else @EntidadId End)) And
(GruposCobranzas.BancoId = (Case @BancoId When 0 Then GruposCobranzas.Bancoid Else @BancoId End )) And
(Contratos.DistribuidorId = (Case @DistribuidorId When 0 Then Contratos.DistribuidorId Else @DistribuidorId End )) And
(Contratos.VendedorId = (Case @VendedorId When 0 Then Contratos.VendedorId Else @Vendedorid end )) And
((Cobros.Fecha >=@Desde) And (Cobros.Fecha<=@Hasta)) And
(Cobros.Procesado=1) And
(CobrosDetalles.CuotaId>0))
As you could see, there are 5 contional statements in the where clausule, when parameter's value is zero then compare the same field, otherwise, it compares Field and parameter.
So far so good...
This query "as is" it's not working... it takes so much time to execute that I just stop query execution... But, when I change the conditional statements to some specific combinations it works fine.
For Example:
Removing (Case GruposCobranza.BancoId... Fails!
Removing (Case Contratos.EntidadId... Works! (with and without BancoId Case sentence)
Removing (Case Contratos.GrupoCobranzaId... Works! (with and without BancoId Case sentence)
Removing (Case Contratos.VendedorId... Works (with and without BancoId Case sentence)
Removing (Case Contratos.DistribuidorId... Works (with and without BancoId Case sentence)
The Table "Contratos" Contains all these fields as foreign keys:
-DistribuidorId
-VendedorId
-GrupoCobranzaId
-EntidadId
The Table GruposCobranzas Contains BancoId As Foreign Key
The Table Cobros Detalles contains DocumentoCXCId And CobroId(to master table) As Foreign Keys
What could be happening here?
April 1, 2008 at 1:10 pm
Without structure & data, it'll be really hard to say.
Do you have the execution plan? Can you post the estimated plan for the full query that takes too long to complete? That'll be the one to attack. Any of the other query plans showing the queries that work as contrast would also be helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2008 at 1:40 pm
Without looking at the execution plan, it is hard to say, but I would guess that the query optimizer is unhappy with the case statements and you are getting table scans as it tries to resolve all of the case logic.
You could try turning it into some OR logic:
make:
Contratos.GrupoCobranzaId = (Case @GrupoCobranzaId When 0 Then Contratos.GrupoCobranzaId Else @GrupoCobranzaId End)
into:
(Contratos.GrupoCobranzaId = @GrupoCobranzaId OR @GrupoCobranzaId = 0)
carefully use parenthesis.
April 1, 2008 at 2:58 pm
Thank you guys!,
Michael, Using "or" clausule looks much smarter than case, thanks for your advice.
I've tested and it's working fine now. THANK YOU!!!
Select ...
Where ((Contratos.GrupoCobranzaId = @GrupoCobranzaId) Or (@GrupoCobranzaId = 0) ) And
((Contratos.EntidadId = @EntidadId ) Or (@EntidadId = 0 ) ) And
((GruposCobranzas.BancoId = @BancoId) Or (@BancoId=0) ) And
((Contratos.DistribuidorId = @DistribuidorId) Or (@DistribuidorId = 0)) And
((Contratos.VendedorId = @VendedorId) or (@VendedorId = 0)) And
...
Anyway, attached you'll find the execution plan for this query. To be honest, this is the first time I use this tool :blush:... very helpfull.
April 2, 2008 at 5:33 am
Excellent. Now you just need to look into those two clustered index scans to see if you can improve the performance even more. Look at your table and the predicates being used in the scan and you determine if you need a different index or need a bit more tweaking on the TSQL to elminate the scan. From the looks of the predicate, I'd think you'd be able to get an index seek out of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2008 at 5:36 am
So if you have not done much performance tuning, an interesting thing for you to do is look at the estimated or actual execution plans with the CASE statements and then with the OR conditions to see what SQL is doing differently.
Good luck.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply