Can you help me with this query please?

M. Santos 2014-03-10 16:31:12

SELECT AcumuladosVendas.Entidade
, Artigo.Artigo
, Artigo.Descricao
, CabLiq.TipoDoc
, CabLiq.NumDoc
, CabLiq.DataDoc
, LinhasDoc.Quantidade
FROM
dbo.AcumuladosVendas
INNER JOIN dbo.Artigo
ON AcumuladosVendas.Artigo = Artigo.Artigo
INNER JOIN dbo.CabLiq
ON AcumuladosVendas.Entidade = CabLiq.Entidade
INNER JOIN dbo.LinhasDoc
ON LinhasDoc.Artigo = Artigo.Artigo

Aaron Bertrand 2014-03-10 16:38:14
First thing I noticed are the two implicit conversions. Can you change the tables such that the data types (and length!) of Artigo and Entidade in all tables match? Also is there any opportunity for indexes to support the joining columns? Those hash matches really add up.
John M Couch 2014-03-10 16:40:42
Nice catches Aaron. That's why you are one of the greats in the SQL world. I didn't even dig that far in. I will attempt to pay more attention to the inner details when attempting to provide feedback from now on. Learn by example, and you are a good one to learn from.
John M Couch 2014-03-10 16:37:33
You have no where clause on the statement at all. You are essentially just returning everything which will result in table and/or Clustered Index scans as shown. You are missing a clustered index on AcumuladosVendas (not that it will help in this case). You are returning 9.6 billion records (estimated) not sure how much help can be provided without a filter of some sort, a.k.a. a where clause.