动态SQL笔记

动态SQL，就是将查询条件中的判断语句，提前在代码中判断完成，而放到数据库（如SQL Server）中执行时就是简单的、可利用索引的SQL语句了，在这个例子中，判断@userId和@menuId是否为null的代码，可能会长这个样子（如果是Dapper）：

var sql = new StringBuilder();
sql.Append("SELECT * FROM FoodOrder WHERE 1=1 ");

if (userId != null) 
{
    sql.AppendLine("AND OrderUserId = @userId");
}

if (menuId != null)
{
    sql.AppendLine("AND FoodMenuId = @menuId");
}
// ...

如果是EF，代码可能是这个样子：

IQueryable<FoodOrder> query = db.FoodOrders;

if (userId != null)
{
    query = query.Where(x => x.OrderUserId == userId);
}

if (menuId != null)
{
    query = query.Where(x => x.FoodMenuId = menuId);
}
// ...

这样一来，最终在数据中执行的SQL语句就比较简单了，如果客户确实传了userId和menuId两个参数，SQL就应该长这个样子：

select * from FoodOrder where 
	OrderUserId = @userId AND 
	FoodMenuId = @menuId

运行的set statistics io on结果如下：

(3 行受影响)
Table 'FoodOrder'. Scan count 2, logical reads 11, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

显然仅进行了11次逻辑读（相比静态SQL的337次），然后执行计划如下：

显示进行了两次Index Seek，显然是走了索引，显示查询开销只占5%，而之前的开销占95%，性能区别高达20倍以上。