I have a view with the following SELECT statement. I wanted to know how to improve the performance of this ( specifically the count part) if possible as the table grows and contains more data. There are less than 200 rows in each one of these tables right now.
SELECT sr.SRIID, su.SRNAME su2.SRNAME2, ( SELECT COUNT(*) FROM SomeTable WHERE ThisID = sr.SRIID ) AS SomeName, sr.DATETIME, . . . . FROM SizeRatio AS sr LEFT JOIN SizeUnit AS su ON sr.SRIID= su.SRIID LEFT JOIN SizeUnit2 AS su2 ON sr.SUID = su2.SU2ID
When the tables grow would a CTE or a temp table be more ideal for the Count part or a function ?