question

tdinpsp avatar image
tdinpsp asked

Query Efficiency where a column is a select statement

SELECT dbo.TCorporations.TCorporationsId , dbo.TCorporations.Name AS AccountName , dbo.TFacilities.TFacilitiesId , dbo.TFacilities.Name AS FacilityName , ( SELECT COUNT(LEquipmentTypesId) AS Expr1 FROM dbo.TEquipment WHERE ( LEquipmentTypesId = 1 ) AND ( TFacilitiesId = dbo.TFacilities.TFacilitiesId ) ) AS MRICount FROM dbo.TFacilities INNER JOIN dbo.TCorporations ON dbo.TCorporations.TCorporationsId = dbo.TFacilities.TCorporationsId Although this query works I was wondering if this is the best/most efficient way to do this. The TCorporations table is a parent table to the TFacilities table with a 1:N relationship. The LEquipmentTypes table is a lookup table with about 8 rows in it and the TEquipment table is a table that links facilities to equipment. Thanks.
queryselect
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
I always pull these down to the FROM clause and join them in... SELECT dbo.TCorporations.TCorporationsId , dbo.TCorporations.Name AS AccountName , dbo.TFacilities.TFacilitiesId , dbo.TFacilities.Name AS FacilityName , -- -- -- column from the joined in subquery EquipmentTypes.MRICount FROM dbo.TFacilities INNER JOIN dbo.TCorporations ON dbo.TCorporations.TCorporationsId = dbo.TFacilities.TCorporationsId -- -- -- pulled this aggregation out of the select statement so it will not execute one time for every row in the resultset LEFT OUTER JOIN ( SELECT TFacilitiesId, COUNT(LEquipmentTypesId) AS MRICount FROM dbo.TEquipment WHERE ( LEquipmentTypesId = 1 ) GROUP BY TFacilitiesId ) as EquipmentTypes ON EquipmentTypes.TFacilitiesId = dbo.TFacilities.TFacilitiesId
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks KenJ, but what is the reasoning for doing so?
0 Likes 0 ·
Thanks KenJ, but what is the reasoning for doing so?
0 Likes 0 ·
I only want the count query running 1 time. When you run the count as a column, the query gets run 1 time for every row in your result set - potentially millions or billions of times.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.