September 24, 2013 at 6:15 am
Hi, I'm desesperated and I need some help.
I've a problem with a simple query. If I put a where clause to a particular field it takes a long but only depending the type of where.
Let's say if I put: where field is not null it runs fast
But If I put: where field > 1 it runs so slow.
This is the query (the most isolated version with the problem) with the > 1 clause.
---------------------------------------
-----------------------------
SELECT cabe.CodigoEpisodio
FROM dbo.CabecerasFacturas AS cabe RIGHT OUTER JOIN
dbo.Historias AS hist INNER JOIN
dbo.Episodios AS epis ON hist.CodigoHistoria = epis.CodigoHistoria INNER JOIN
dbo.Centros ON epis.CodigoCentro = dbo.Centros.CodigoCentro INNER JOIN
dbo.Empresas AS emp ON dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa AND hist.CodigoEmpresa = emp.CodigoEmpresa ON
cabe.CodigoCentro = dbo.Centros.CodigoCentro AND cabe.CodigoCentro = epis.CodigoCentro AND cabe.CodigoEpisodio = epis.CodigoEpisodio
WHERE (cabe.SwEstadoCabeceras > 1)
---------------------
------------------------
I include the execution plan for the slow and fast versions. Please, help me I really need to fix this as soon as possible.
A lot of thanks in advance.
September 24, 2013 at 6:37 am
This is just for information purposes. . .
"Slow" Query Definition
SELECT cabe.CodigoEpisodio
FROM dbo.CabecerasFacturas AS cabe
RIGHT JOIN dbo.Historias AS hist
INNER JOIN dbo.Episodios AS epis ON hist.CodigoHistoria = epis.CodigoHistoria
INNER JOIN dbo.Centros ON epis.CodigoCentro = dbo.Centros.CodigoCentro
INNER JOIN dbo.Empresas AS emp ON dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa
AND hist.CodigoEmpresa = emp.CodigoEmpresa ON cabe.CodigoCentro = dbo.Centros.CodigoCentro
AND cabe.CodigoCentro = epis.CodigoCentro
AND cabe.CodigoEpisodio = epis.CodigoEpisodio
WHERE (cabe.SwEstadoCabeceras > 1);
"Fast" Query Definition
SELECT cabe.CodigoEpisodio
FROM dbo.CabecerasFacturas AS cabe
RIGHT JOIN dbo.Historias AS hist
INNER JOIN dbo.Episodios AS epis ON hist.CodigoHistoria = epis.CodigoHistoria
INNER JOIN dbo.Centros ON epis.CodigoCentro = dbo.Centros.CodigoCentro
INNER JOIN dbo.Empresas AS emp ON dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa
AND hist.CodigoEmpresa = emp.CodigoEmpresa ON cabe.CodigoCentro = dbo.Centros.CodigoCentro
AND cabe.CodigoCentro = epis.CodigoCentro
AND cabe.CodigoEpisodio = epis.CodigoEpisodio
WHERE (cabe.SwEstadoCabeceras IS NOT NULL);
SQL Server suggests indexes for each of those queries, have you had a look at them?
From "slow" query: -
/*
Missing Index Details from
The Query Processor estimates that implementing the following index could improve the query cost by 62.8781%.
WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [GIO_SURGERY]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CabecerasFacturas] ([SwEstadoCabeceras])
INCLUDE ([CodigoCentro],[CodigoEpisodio])
GO
----------------
/*
Missing Index Details from
The Query Processor estimates that implementing the following index could improve the query cost by 61.528%.
WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [GIO_SURGERY]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CabecerasFacturas] ([CodigoCentro],[SwEstadoCabeceras])
INCLUDE ([CodigoEpisodio])
GO
From "fast" query: -
/*
Missing Index Details from
The Query Processor estimates that implementing the following index could improve the query cost by 28.0727%.
WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [GIO_SURGERY]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CabecerasFacturas] ([SwEstadoCabeceras])
INCLUDE ([CodigoCentro],[CodigoEpisodio])
GO
September 24, 2013 at 6:50 am
Hi,
Thank you very much for your response.
I've tried the index creation but I get same result:
fast near 0 seconds
slow about 15 seconds.
Here you'll find new execution plans.
http://www.comoflipas.com/newSlowPlan.xml
http://www.comoflipas.com/newFastPlan.xml
Thanks again.
September 24, 2013 at 7:26 am
I have removed "dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa" and now works good but I'm not really sure about the consequences
SELECT dbo.CabecerasFacturas.CodigoEpisodio
FROM dbo.CabecerasFacturas INNER JOIN
dbo.Episodios ON dbo.Episodios.CodigoCentro = dbo.CabecerasFacturas.CodigoCentro AND
dbo.Episodios.CodigoEpisodio = dbo.CabecerasFacturas.CodigoEpisodio INNER JOIN
dbo.Centros ON dbo.Centros.CodigoCentro = dbo.Episodios.CodigoCentro INNER JOIN
dbo.Historias ON dbo.Historias.CodigoHistoria = dbo.Episodios.CodigoHistoria INNER JOIN
dbo.Empresas ON dbo.Historias.CodigoEmpresa = dbo.Empresas.CodigoEmpresa
WHERE (dbo.CabecerasFacturas.SwEstadoCabeceras > 1)
...
September 24, 2013 at 8:07 am
mouthbow (9/24/2013)
I have removed "dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa" and now works good but I'm not really sure about the consequencesSELECT dbo.CabecerasFacturas.CodigoEpisodio
FROM dbo.CabecerasFacturas INNER JOIN
dbo.Episodios ON dbo.Episodios.CodigoCentro = dbo.CabecerasFacturas.CodigoCentro AND
dbo.Episodios.CodigoEpisodio = dbo.CabecerasFacturas.CodigoEpisodio INNER JOIN
dbo.Centros ON dbo.Centros.CodigoCentro = dbo.Episodios.CodigoCentro INNER JOIN
dbo.Historias ON dbo.Historias.CodigoHistoria = dbo.Episodios.CodigoHistoria INNER JOIN
dbo.Empresas ON dbo.Historias.CodigoEmpresa = dbo.Empresas.CodigoEmpresa
WHERE (dbo.CabecerasFacturas.SwEstadoCabeceras > 1)
...
Why not test the difference?
SELECT MIN(ResultSet) AS ResultSet, CodigoEpisodio
FROM
(
SELECT 'With Historias', cabe.CodigoEpisodio
FROM dbo.CabecerasFacturas AS cabe
RIGHT JOIN dbo.Historias AS hist
INNER JOIN dbo.Episodios AS epis ON hist.CodigoHistoria = epis.CodigoHistoria
INNER JOIN dbo.Centros ON epis.CodigoCentro = dbo.Centros.CodigoCentro
INNER JOIN dbo.Empresas AS emp ON dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa
AND hist.CodigoEmpresa = emp.CodigoEmpresa ON cabe.CodigoCentro = dbo.Centros.CodigoCentro
AND cabe.CodigoCentro = epis.CodigoCentro
AND cabe.CodigoEpisodio = epis.CodigoEpisodio
WHERE (cabe.SwEstadoCabeceras > 1)
UNION ALL
SELECT 'Without Historias', dbo.CabecerasFacturas.CodigoEpisodio
FROM dbo.CabecerasFacturas
INNER JOIN dbo.Episodios ON dbo.Episodios.CodigoCentro = dbo.CabecerasFacturas.CodigoCentro
AND dbo.Episodios.CodigoEpisodio = dbo.CabecerasFacturas.CodigoEpisodio
INNER JOIN dbo.Centros ON dbo.Centros.CodigoCentro = dbo.Episodios.CodigoCentro
INNER JOIN dbo.Historias ON dbo.Historias.CodigoHistoria = dbo.Episodios.CodigoHistoria
INNER JOIN dbo.Empresas ON dbo.Historias.CodigoEmpresa = dbo.Empresas.CodigoEmpresa
WHERE (dbo.CabecerasFacturas.SwEstadoCabeceras > 1)
) tmp(ResultSet,CodigoEpisodio)
GROUP BY CodigoEpisodio
HAVING COUNT(*) = 1
ORDER BY CodigoEpisodio;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply