Query Execution Slow and Fast find the difference

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Execution Slow and Fast find the difference

Author

Topic

mouthbow
Starting Member

3 Posts

Posted - 2013-09-24 : 08:14:53

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.

http://www.comoflipas.com/slowPlan.xml

http://www.comoflipas.com/fastPlan.xml

VasiAnu
Starting Member

9 Posts

Posted - 2013-09-24 : 08:46:45

I think there is no index on cabe.SwEstadoCabeceras column. Please create an index and verify the execution plan once again.

Thanks,
VA.

mouthbow
Starting Member

3 Posts

Posted - 2013-09-24 : 08:56:21

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.

mouthbow
Starting Member

3 Posts

Posted - 2013-09-24 : 09:26:07

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)

...

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-24 : 12:24:36

Need to see the table definitions, including indexes (if possible).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources