SQL Server - Multiple Counts from multiple tables with the same WHERE clause

Question

I need multiple counts from multiple tables, but all tables join with the same tables and have the same where clause. I get the correct result, but, in a very large and slow query, and, I'd like to learn how to improve this.

I have to get a count from tables:

Ocorrencias
Followups
PropostasComerciais
PropostasDeLocacao
PedidosDeVendas

And 3 SUM, all from PedidosDeVendas table.

all this querys joins with: - Empresas - PedidosDeVendaXItens

and all querys have the same WHERE clause: - WHERE SolicitanteID = {User ID} AND Data >= '{Initial Date}' AND Data <= '{Final Date}'

This is the query that return all results I need:

SELECT U.[ID] AS UniqKey, U.NomeCompleto, R.Regiao,    

(SELECT Count(*)    
FROM Ocorrencias O    
INNER JOIN Nomes N ON (O.ClientesID = N.Codigo AND O.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
WHERE O.UsuariosID = U.[ID]    
AND MR.ID_Regiao = R.uniqKey    
AND O.Data >= '2012-12-01 00:00:00' AND O.Data <= '2012-12-31 23:59:59') AS Ocorrencias,    

(SELECT Count(*)    
FROM FollowUp F    
INNER JOIN Nomes N ON (F.ClienteID = N.Codigo AND F.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
WHERE SolicitanteID = U.[ID]    
AND MR.ID_Regiao = R.uniqKey    
AND Data >= '2012-12-01 00:00:00' AND Data <= '2012-12-31 23:59:59') AS FollowUps,    

(SELECT Count(*)    
FROM v_PropostaComercial PC    
INNER JOIN Nomes N ON (PC.ClienteID = N.Codigo AND PC.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
WHERE PC.SolicitanteID = U.[ID]    
AND MR.ID_Regiao = R.uniqKey    
AND PC.Data >= '2012-12-01 00:00:00' AND PC.Data <= '2012-12-31 23:59:59') AS PropostasComerciais,                  

(SELECT Count(*)    
FROM PropostaDeLocacao PL    
INNER JOIN Nomes N ON (PL.ClienteID = N.Codigo AND PL.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
WHERE PL.ResponsavelID = U.[ID]    
AND MR.ID_Regiao = R.uniqKey    
AND PL.Data >= '2012-12-01 00:00:00' AND PL.Data <= '2012-12-31 23:59:59') AS PropostasDeLocacao,    

(SELECT Count(*)    
FROM PedidosDeVenda PV    
INNER JOIN Nomes N ON (PV.ClienteID = N.Codigo AND PV.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
WHERE PV.SolicitanteID = U.[ID]    
AND MR.ID_Regiao = R.uniqKey    
AND PV.Data >= '2012-12-01 00:00:00' AND PV.Data <= '2012-12-31 23:59:59') AS PedidosDeVenda,                   

(SELECT SUM(PVI.Valor)    
FROM PedidosDeVenda PV    
INNER JOIN Empresas N ON (PV.ClienteID = N.Codigo AND PV.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
INNER JOIN PedidosDeVendaXItens PVI ON PVI.PedidoDeVendaID = PV.[ID]    
INNER JOIN Modelos M ON PVI.ProdutoID = M.[ID]    
WHERE PV.SolicitanteID = U.[ID]    
AND MR.ID_Regiao = R.uniqKey
AND PV.Data >= '2012-12-01 00:00:00' AND PV.Data <= '2012-12-31 23:59:59') As TotalPedidosDeVenda,    

(SELECT SUM(PVI.Valor)    
FROM PedidosDeVenda PV    
INNER JOIN Nomes N ON (PV.ClienteID = N.Codigo AND PV.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
INNER JOIN PedidosDeVendaXItens PVI ON PVI.PedidoDeVendaID = PV.[ID]    
INNER JOIN Modelos M ON PVI.ProdutoID = M.[ID]    
WHERE PV.SolicitanteID = U.[ID]    
AND N.TipoEmpresa = 'PU'    
AND PV.ClienteID <> U.ClienteID    
AND MR.ID_Regiao = R.uniqKey    
AND PV.Data >= '2012-12-01 00:00:00' AND PV.Data <= '2012-12-31 23:59:59') As TotalVendasPublicas,    


(SELECT SUM(PVI.Valor)    
FROM PedidosDeVenda PV    
INNER JOIN Nomes N ON (PV.ClienteID = N.Codigo AND PV.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
INNER JOIN PedidosDeVendaXItens PVI ON PVI.PedidoDeVendaID = PV.[ID]    
INNER JOIN Modelos M ON PVI.ProdutoID = M.[ID]    
WHERE PV.SolicitanteID = U.[ID]    
AND N.TipoEmpresa = 'PR'    
AND PV.ClienteID <> U.ClienteID    
AND MR.ID_Regiao = R.uniqKey    
AND PV.Data >= '2012-12-01 00:00:00' AND PV.Data <= '2012-12-31 23:59:59') As TotalVendasPrivadas,    

(SELECT SUM(PVI.Valor)    
FROM PedidosDeVenda PV    
INNER JOIN Nomes N ON (PV.ClienteID = N.Codigo AND PV.SubCadastro = N.SubCadastro)    
INNER JOIN Municipios_Regiao MR ON N.ID_Municipio = MR.ID_Municipio    
INNER JOIN PedidosDeVendaXItens PVI ON PVI.PedidoDeVendaID = PV.[ID]    
INNER JOIN Modelos M ON PVI.ProdutoID = M.[ID]    
WHERE PV.SolicitanteID = U.[ID]    
AND PV.ClienteID = U.ClienteID    
AND MR.ID_Regiao = R.uniqKey    
AND PV.Data >= '2012-12-01 00:00:00' AND PV.Data <= '2012-12-31 23:59:59') As TotalVendasProprias    

FROM Usuarios U    
INNER JOIN Regioes_Usuario RU ON U.[ID] = RU.ID_Usuario    
INNER JOIN Regioes R ON RU.ID_Regiao = R.uniqKey    
WHERE U.Representante = 64        
ORDER BY R.Regiao, U.NomeCompleto;

is there any trick to turn my query faster and shorter?

Look at the query plan and see where the slowness is. It's going to be very hard -- if not impossible -- for someone to improve on your query. They might restructure things and possibly cut down on the total number of queries, but my hunch is the generated query plan will be close to the same. You might need some indexes to get it running faster, but without looking at your plan it's hard to say. — Eli Gassert, Jan 16 '13 at 17:47
The date comparisons look a little fishy to me, what is the type of the PV.Data? Also, you can run this query in the index wizard to let SQL Server come up with suggestions for improvement through the placement of indexes. — flup, Jan 16 '13 at 17:51
As an aside, I *strongly* recommend you stop using `BETWEEN` (well, the range equivalent). What is the data type of `PL.Data`? If it's `SMALLDATETIME`, your end range rounds up; if it's `DATETIME` or `DATETIME2` or `DATETIMEOFFSET` you could be missing data in the last minute of the data. Much, much, much safer to use `>= '20121201' AND < '20130101'`. More details here: https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx — Aaron Bertrand, Jan 16 '13 at 17:53
Hi Eli, I'm reading about query plan, I never heard about that, thanks for the advice. Flup, Data is "Date" in brazilian language, this field is a DateTime field. — Andy Schmitt, Jan 16 '13 at 17:53

score 1 · Accepted Answer · answered Jan 16 '13 at 18:05

1

You should not be doing intensive nested queries inside of a select inside of a main query as each nested I believe will execute each time a join on the outer occurs.

Instead think of declaring variables and assigning these first.

Like

declare @outsidepredicate int, @Count1 int, @Count 2 int;

select @outsidepredicate = value from sharedtable for expressions

select @Count1 = count(*) 
from table1 t (nolock) 
 join table2 tt (nolock) on t.idshared = tt.idshared
where outsidepredicatecolumn = @outsidepredicate

select @Count2 = count(*) 
from table3 t (nolock) 
 join table4 tt (nolock) on t.idshared = tt.idshared
where outsidepredicatecolumn = @outsidepredicate

Then you could do your regular test like this:

select 
    @Count1
,   @Count2
,   valuefrommain
from tableMain m (nolock) 
 join tableSide s (nolock) on m.idshared = s.idshared

answered Jan 16 '13 at 18:05

djangojazz

14,131
10
56
94

I think this is a great idea. I'll try!! :D – Andy Schmitt Jan 16 '13 at 18:18
5

Please review and understand the [consequences](http://stackoverflow.com/questions/1452996/is-the-nolock-sql-server-hint-bad-practice) of [NOLOCK](http://dba.stackexchange.com/questions/10655/is-nolock-always-bad) before recommending or using it as part of any solution. – Mike Fal Jan 16 '13 at 19:03
NoLock does not hurt anything, but you are correct I will amend my statement here of what it does. NoLock is a 'hint' you can give a table that sets it's isolation level at 'uncommitted' the default is set at 'committed'. This in essence returns rows faster at times by avoiding locks but get's 'uncommitted' results. It's common name is 'dirty reads' meaning you get stuff hot off the presses of the SQL engine not caring if they are committed(finalized) or not. – djangojazz Jan 16 '13 at 20:16

score 0 · Answer 2 · answered May 30 '17 at 12:05

So, having looked at the query in depth, I think the optimal thing is to reduce requerying the same tables.

Forgive the typo's if any.. interesting to not understand the names...

Try this:

SELECT 
    U.[ID] AS UniqKey, 
    U.NomeCompleto, 
    R.Regiao,    
    COUNT(O.ClientesID) Ocorrencias,
    COUNT(F.ClienteID) FollowUps,
    COUNT(P.SolicitanteID) PropostasComerciais,
    COUNT(PL.ResponsavelID) PropostasDeLocacao,
    COUNT(PV.SolicitanteID) PedidosDeVenda,
    COUNT(PVTotal.SolicitanteID) TotalPedidosDeVenda,
    SUM(PVTotalVendasPublicas.Valor) TotalVendasPublicas,
    SUM(PVTotalVendasPrivadas.Valor) TotalVendasPrivadas,
    SUM(PVTotalVendasProprias.Valor) TotalVendasProprias 
FROM Usuarios U    
    JOIN Regioes_Usuario RU 
        ON U.[ID] = RU.ID_Usuario    
    JOIN Regioes R 
        ON RU.ID_Regiao = R.uniqKey     
    JOIN Municipios_Regiao MR 
        ON MR.ID_Regiao = R.uniqKey    
    LEFT JOIN Nomes N 
        ON N.ID_Municipio = MR.ID_Municipio
    LEFT JOIN Ocorrencias O 
        ON O.ClientesID = N.Codigo 
        AND O.SubCadastro = N.SubCadastro 
        AND O.UsuariosID = U.[ID]
    LEFT JOIN FollowUp F
        ON F.SolicitanteID = U.[ID]
        AND F.ClienteID = N.Codigo    
        AND F.SubCadastro = N.SubCadastro
    LEFT JOIN v_PropostaComercial PC
        ON PC.SolicitanteID = U.[ID] 
        AND PC.ClienteID = N.Codigo 
        AND PC.SubCadastro = N.SubCadastro
    LEFT JOIN PropostaDeLocacao PL
        ON PL.ResponsavelID = U.[ID]
        AND PL.ClienteID = N.Codigo 
        AND PL.SubCadastro = N.SubCadastro
    LEFT JOIN PedidosDeVenda PV
        ON PV.SolicitanteID = U.[ID]
        AND PV.ClienteID = N.Codigo 
        AND PV.SubCadastro = N.SubCadastro
    LEFT JOIN PedidosDeVenda PVTotal
            ON PVTotal.SolicitanteID = U.[ID]
        JOIN PedidosDeVendaXItens PVI 
            ON PVI.PedidoDeVendaID = PVTotal.[ID]    
        JOIN Modelos M 
            ON PVI.ProdutoID = M.[ID]         
        JOIN Empresas N1 
            ON PVTotal.ClienteID = N1.Codigo 
            AND PVTotal.SubCadastro = N1.SubCadastro
        LEFT JOIN Municipios_Regiao MRTotal 
            ON R.uniqKey = MRTotal.ID_Regiao
            AND N1.ID_Municipio = MRTotal.ID_Municipio
    LEFT JOIN PedidosDeVenda PVTotalVendasPublicas    
            ON PVTotalVendasPublicas.SolicitanteID = U.[ID]    
            AND PVPVTotalVendasPublicas.ClienteID <> U.ClienteID  
            AND PVTotalVendasPublicas.ClienteID = N.Codigo 
            AND PVTotalVendasPublicas.SubCadastro = N.SubCadastro    
            AND N.ID_Municipio = MR.ID_Municipio 
            AND N.TipoEmpresa = 'PU' 
        JOIN PedidosDeVendaXItens PVI 
            ON PVI.PedidoDeVendaID = PVTotalVendasPublicas.[ID]    
        JOIN Modelos M 
            ON PVI.ProdutoID = M.[ID]    
  LEFT JOIN PedidosDeVenda PVTotalVendasPrivadas
        ON PVTotalVendasPrivadas.SolicitanteID = U.[ID]
        AND PVTotalVendasPrivadas.ClienteID <> U.ClienteID 
        AND PVTotalVendasPrivadas.ClienteID = N.Codigo 
        AND PVTotalVendasPrivadas.SubCadastro = N.SubCadastro
        AND N.TipoEmpresa = 'PR'
        JOIN PedidosDeVendaXItens PVI 
            ON PVI.PedidoDeVendaID = PVTotalVendasPrivadas.[ID]    
        JOIN Modelos M 
            ON PVI.ProdutoID = M.[ID]    
    LEFT JOIN PedidosDeVenda PVTotalVendasProprias    
            ON PVTotalVendasProprias.SolicitanteID = U.[ID]    
            AND PVTotalVendasProprias.ClienteID = U.ClienteID
            AND PVTotalVendasProprias.ClienteID = N.Codigo 
            AND PVTotalVendasProprias.SubCadastro = N.SubCadastro
        JOIN PedidosDeVendaXItens PVI 
            ON PVI.PedidoDeVendaID = PVTotalVendasProprias.[ID]    
        JOIN Modelos M 
            ON PVI.ProdutoID = M.[ID]  
WHERE U.Representante = 64
    AND O.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND F.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND PC.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND PL.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND PV.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND PVTotal.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND PVTotalVendasPublicas.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND PVTotalVendasPrivadas.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
    AND PVTotalVendasProprias.Data BETWEEN '2012-12-01 00:00:00' AND '2012-12-31 23:59:59'
GROUP BY U.ID, U.NameCompleto, R.Regiao
ORDER BY R.Regiao, U.NomeCompleto;

Hope someone can verify this is an improvement... 4 years old question! :( — Alocyte, May 30 '17 at 12:09

SQL Server - Multiple Counts from multiple tables with the same WHERE clause

2 Answers2