CODEWITHSUNDEEP
sqlsql-servert-sql
Aditya Sawant
Aditya Sawant

Reputation: 51

Different Approach for COUNT(1)

I am having below sub query which get the total count of cargodetails on the selected row that has that reference or booking.

Select Column_Names
WHEN department = 'e' THEN
(SELECT COUNT(1) FROM AMAMDBECUGROUPAGE.dbo.cargodetail WITH (nolock)
WHERE AMAMDBECUGROUPAGE.dbo.cargodetail.inactive = 0 
AND CAST(AMAMDBECUGROUPAGE.dbo.cargodetail.cargonrbooking AS varchar) = CAST(dbo.StockCargo.reference AS varchar)) 
ELSE '' END AS [#Cargos]
FROM dbo.StockCargo WITH (nolock)

But the above query takes more than 10 min to execute. I have created appropriate indexes still no improvement.

I then tried to convert the WHERE clause to INNER JOIN for dbo.StockCargo as per below.

(SELECT COUNT(1) FROM AMAMDBECUGROUPAGE.dbo.cargodetail WITH (nolock) 
INNER JOIN dbo.StockCargo WITH (nolock) ON 
CAST(AMAMDBECUGROUPAGE.dbo.cargodetail.cargonrbooking AS varchar) = CAST(dbo.StockCargo.reference AS varchar)
WHERE AMAMDBECUGROUPAGE.dbo.cargodetail.inactive = 0 )

This executed in seconds but the result set was not as expected.

Can anyone suggest me a different approach for this?

Thank you in advance.

Upvotes: 1

Views: 74

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Having to cast() for joins is a performance killer. If the columns are not of the same type, you should fix your data model so common keys have the same type -- and foreign keys are properly declared. Your efforts would be better spent fixing the data model rather than trying to make queries run better.

Building on Thorsten's query, I would suggest writing this query as a correlated subquery:

select sc.column_names,
       (select count(*)
        from amamdbecugroupage.dbo.cargodetail cd
        where cd.cargonrbooking = sc.reference and
              cd.inactive = 0
       ) as [#Cargos]
from dbo.stockcargo sc
where sc.department = 'e';

If the columns are not of the same type, then convert sc.reference to the tpe of cd.cagonrbooking. It is unclear which is appropriate:

        where cd.cargonrbooking = try_convert(int, sc.reference) and
        where cd.cargonrbooking = try_convert(varchar(255), sc.reference) and

For performance you want an index on cargodetail(reference, inactive). It is very important which column is converted; and this should allow the use of the index.

You might also want an index on stockcargo(department, reference).

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

First, COUNT(1) is just an obfuscated way to count rows, for which COUNT(*) is made for. Only count expressions, when these expressions can be null. 1 is never null, so there is no sense in telling the DBMS to count all rows for which this value is not null.

I gather that a StockCargo.reference can reference very different things of which cargodetail.cargonrbooking is just one of several. This is probably the reason why the column is called reference and not cargonrbooking and why the data types don't match. However, I assume that you don't have to cast both to VARCHAR. I suggest that you use TRY_CAST on the reference to match cargonrbooking's type, as cargodetail is the table you want to look up.

Your query looks okay apart from that. (Though I think I remember that WITH (nolock) should only be used in very special situations if at all.)

You can write the same with an outer join:

select sc.column_names, count(cd.cargonrbooking) as [#Cargos]
from dbo.stockcargo sc
left join amamdbecugroupage.dbo.cargodetail cd
  on cd.cargonrbooking = try_cast(sc.reference as int)
  and cd.inactive = 0
  and sc.department = 'e';

You read all rows of StockCargo, so the DBMS will probably just read the table sequentially. For cargodetail, however, you want an index to look up data quickly.

create index idx on cargodetail (cargonrbooking, inactive);

Upvotes: 0

gotqn
gotqn

Reputation: 43636

Maybe something like this:

SELECT Column_Names
      ,CASE 
            WHEN department = 'e'
            THEN counts
            ELSE ''
       END AS [#Cargos]
FROM dbo.StockCargo ST WITH (nolock)
LEFT JOIN
(
    SELECT cargonrbooking
          ,COUNT(1)
    FROM AMAMDBECUGROUPAGE.dbo.cargodetail
    WHERE inactive = 0 
    GROUP BY cargonrbooking
) DS (cargonrbooking, counts)
    ON CAST(DS.cargonrbooking AS varchar) = CAST(ST.reference AS varchar))

Also, to simply more, you can try insert the LEFT JOIN sub-query in temporary table.

Upvotes: 1

Related Questions