有几个性能问题需要解决…

如果可能，不要多次访问同一张表

不要将子查询用于无需参考同一表的其他副本即可完成的条件。如果由于使用聚合函数（MAX，MIN等）而需要表副本中的数据，这是可以接受的，尽管分析函数（ROW_NUMBER，RANK等）可能更便于容纳（假设受支持）。

不要比较你不需要的东西

如果您的参数为NULL，则意味着您想要与之比较的列具有任何值，请不要包括过滤条件。像这样的语句：

WHERE a.Name LIKE '%' + ISNULL(@Name, N'') + '%'

…保证优化器必须比较该name列的值（是否使用通配符）。更糟糕的LIKE是，如果对要搜索的列上存在索引，则在评估的左侧使用通配符可确保无法使用索引。

更好的方法是：

IF @Name IS NOT NULL 
BEGIN
   SELECT ...
     FROM ...
    WHERE a.name LIKE '%' + @Name + '%'
END
ELSE 
BEGIN
   SELECT ...
     FROM ...
END

表现良好的SQL就是根据您的需求量身定制的。这就是为什么当您使用两个或多个独立条件进行查询时，应该考虑使用动态SQL的原因。

使用正确的工具

LIKE当您检查文本数据中是否存在字符串时，该运算符在搜索文本时效率不是很高。
全文搜索（FTS）技术旨在解决以下缺点：

IF @Name IS NOT NULL
BEGIN
   SELECT ...
     FROM ...
    WHERE CONTAINS(a.name, @Name) 
END
ELSE
BEGIN
   SELECT ...
     FROM ...
END

始终测试和比较

我同意LittleBobbyTables的观点-
该解决方案最终依赖于查询查询/执行计划中的所有替代方案，因为表设计和数据可能会影响优化器的决策和性能。在SQL
Server中，子树成本最低的服务器效率最高，但是如果不维护表统计信息和索引，它会随着时间的推移而变化。