SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Execution Slow and Fast find the difference
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mouthbow
Starting Member

3 Posts

Posted - 09/24/2013 :  08:14:53  Show Profile  Reply with Quote
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

India
9 Posts

Posted - 09/24/2013 :  08:46:45  Show Profile  Reply with Quote
I think there is no index on cabe.SwEstadoCabeceras column. Please create an index and verify the execution plan once again.

Thanks,
VA.
Go to Top of Page

mouthbow
Starting Member

3 Posts

Posted - 09/24/2013 :  08:56:21  Show Profile  Reply with Quote
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.
Go to Top of Page

mouthbow
Starting Member

3 Posts

Posted - 09/24/2013 :  09:26:07  Show Profile  Reply with Quote
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)


...
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
269 Posts

Posted - 09/24/2013 :  12:24:36  Show Profile  Reply with Quote
Need to see the table definitions, including indexes (if possible).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000