SQL Server Performance optimizations: subquery VS joins

Create a table in the database as follows, and count the number of Tasknote for each task.

The first of these solutions:

SelectT.taskid, (Select Count(n.id) fromTasknote NwhereN.taskid=T.taskid)'Notes',--exec every row(Select Count(n.comment) fromTasknote NwhereN.taskid=T.taskid)'Notes1',--exec every row(Select Count(N.createdtime) fromTasknote NwhereN.taskid=T.taskid)'Notes2'  --exec every row fromTask T

The sub-query above is just as an example, the actual use may need to count each time each task corresponding comment number, here steal a lazy (ˇ?ˇ) like this to love lazy cat ~

The execution plan is as follows: (very complex, each subquery needs to be executed once, the visible performance is also not very good)

This way, easy to understand, readability is high, but when the sub-query when the amount of data is large, because each row will execute embedded subquery, can cause slow performance.

Second Solution:

--using GROUP BY and table joinsSelectT.taskid,Count(n.id)'Notes',       Count(n.comment)'Notes1',       Count(N.createdtime)'Notes2' fromTask T Left Jointasknote N on     IsNull(T.taskid,'__')= IsNull(N.taskid,'__')Group  byT.taskid

The execution plan is as follows: (this looks much simpler than the above, imagine that performance will improve)

When the amount of data is large, the performance aspect of using the left join will be greatly improved, note that when on, the null value is converted using IsNull.

The two methods can be very different when the data volume performance is not very large, but when the data is large and the sub-query nesting complex may need to carefully consider.

At work, there is a big difference in the performance gap when dealing with big data and complex statistical data. Take this log as a record, warning!

Fighting for me,my Dear Darling,my family, ~o (∩_∩) o~.

SQL Server Performance optimizations: subquery VS joins