案发现场
我们先到事故现场视察一下,看看会找到什么线索。这是半个月前新上线WMS网站系统,最近两天频频发生sql查询超时的报错。先看看项目的基本面信息:
- Web应用:ASP.NET项目网站,使用PetaPoco ORM
- 数据库:大微软的SQL Server2012
- 硬件:阿里云数据库RDS,4核心16G
- 查询数据:查询订单明细数据,数据总量小于10W行;查询视图 UNOIN出库入库订单数据( InStock/OutStock),并且JOIN一下几个表:Customer、Supplier、Product、Saler、OrderDetail
- 系统界面查询出现30秒查询超时的情况,但,SQL查询分析器里面查询视图却是非常正常200ms?
OMG!堂堂的SQL Server + 阿里云数据库,的表现竟然不如Sqlite?!这完全不科学,需要好好分析一下。
索引的问题?
根据过往的经验,就算没有索引裸跑,SQL Server 也不至于那么不“耐操”吧。算了,不就加索引么?早晚是要弄的。
(30 minutes later...)
orderNo、productNo、customerNo、salerNo、orderTime的索引全部搞定!
but,超时,依旧还是超时。:(
ORM的问题?
到底ORM把我的SQL脚本弄成啥样了?立马打开Profiler,截获超时的脚本。嗯,有发现!
exec sp_executesql N'SELECT ROW_NUMBER() OVER (ORDER BY orderDate) peta_rn, * FROM VW_Order_Detail
WHERE (1=1)
AND createDate> @0
AND createDate< @1 ORDER BY orderDate
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY',N'@0 nvarchar(4000),@1 nvarchar(4000)',@0=N'20210718',@1=N'20210808'
跟我在查询分析器上测试的SQL脚本,多了一个sp_executesql
的存储过程。
SELECT ROW_NUMBER() OVER (ORDER BY orderDate) peta_rn, * FROM VW_Order_Detail
WHERE (1=1)
AND createDate> '20210704'
AND createDate< '20210808' ORDER BY orderDate
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
在查询分析起跑了一下,带sp_executesql
的脚本,果然妥妥的超时。看来,真相,很快就要浮出水面了!
真相大白
用“sp_executesql over time”的关键词搜索了一下,果然stackoverflow上找到了类似的案例。
原来问题出在ORM把传入string 的SqlDbType弄成nvarchar
了。然而项目数据库字段类型清一色全都是varchar
,sp_executesql
执行查询前还要做类型转换,消耗了大量的CPU时间。好比开车挂着手踩油门,车子轰隆隆地响就是跑不快。
解决问题
能够重现问题,并且知道引起的原因。接下来就好办了。不是改数据库字段类型,就是直接对ORM代码开刀!我选择了代价最小的后者。
PetaPoco
然而,PetaPoco却说:“这不是我的锅!SqlDbType的类型映射是System.Data.SqlClient做的!我只是个传话的。”
确实,我在某度查了一下,C#里面string类型的参数,都被映射成nvarchar了,后面我会补充一下mapping的表格。然后,调试了一下PetaPoco的代码,发现还是可以在里面找到解决的办法。以下这是我的解决办法:
- 找到
AddParam
的方法 - 对string类型的参数动一下手脚,让它强行映射成varchar
else if (t == typeof(string))
{
if (p.GetType().Name == \"SqlParameter\") // parameter SqlDbType: nvarchar => varchar
p.GetType().GetProperty(\"SqlDbType\").SetValue(p, SqlDbType.VarChar, null);
if ((value as string).Length + 1 > 4000 && p.GetType().Name == \"SqlCeParameter\")
p.GetType().GetProperty(\"SqlDbType\").SetValue(p, SqlDbType.NText, null);
p.Size = Math.Max((value as string).Length + 1, 4000); // Help query plan caching by using common size
p.Value = value;
}
简单粗暴,但确实解决了问题,围观的大牛们不喜勿喷。到时候用你们自己的办法去解决就好了~
SqlSugar
噢,差点忘了!还有个MQ队列的消费者应用。用的不是PetaPoco而是SqlSugar。
查SqlSugar的文档无果。直接从源码入手,找到了一个配置参数 DisableNvarchar = true
。(看来SqlSugar的使用者,曾经有人踩过此坑)
new ConnectionConfig()
{
ConnectionString = conn,
DbType = dbType,
MoreSettings = new ConnMoreSettings { DisableNvarchar = true }
};
加一行代码,搞定~~爽!
总结
原来这么多年来,我用的SQL Server竟然是一个被ORM封印了的版本!不起眼的查询超时,竟然挖出这么个巨坑,而且潜伏多来。这算是今年以来一个非常大的收获了。
附录:SqlDbType与DbType的映射关系
SqlDbType => DbType
SqlDbType | DbType ---|--- SqlDbType.BigInt|DbType.Int64 SqlDbType.Binary|DbType.Binary SqlDbType.Bit| DbType.Boolean SqlDbType.Char| DbType.AnsiStringFixedLength SqlDbType.DateTime|DbType.DateTime SqlDbType.Decimal|DbType.Decimal SqlDbType.Float| DbType.Double SqlDbType.Image| DbType.Binary SqlDbType.Int| DbType.Int32 SqlDbType.Money| DbType.Currency SqlDbType.NChar| DbType.StringFixedLength SqlDbType.NText| DbType.String SqlDbType.NVarChar|DbType.String SqlDbType.Real| DbType.Single SqlDbType.UniqueIdentifier |DbType.Guid SqlDbType.SmallDateTime |DbType.DateTime SqlDbType.SmallInt|DbType.Int16 SqlDbType.SmallMoney|DbType.Currency SqlDbType.Text| DbType.AnsiString SqlDbType.Timestamp|DbType.Binary SqlDbType.TinyInt|DbType.Byte SqlDbType.VarBinary|DbType.Binary SqlDbType.VarChar|DbType.AnsiString SqlDbType.Variant|DbType.Object SqlDbType.Xml| DbType.Xml SqlDbType.Udt| DbType.Object SqlDbType.Structured|DbType.Object SqlDbType.Date| DbType.Date SqlDbType.Time| DbType.Time SqlDbType.DateTime2|DbType.DateTime2 SqlDbType.DateTimeOffset|DbType.DateTimeOffset
DbType => SqlDbType
DbType | SqlDbType ---|--- DbType.AnsiString|SqlDbType.VarChar DbType.Binary| SqlDbType.VarBinary DbType.Byte| SqlDbType.TinyInt DbType.Boolean| SqlDbType.Bit DbType.Currency| SqlDbType.Money DbType.Date| SqlDbType.DateTime DbType.DateTime| SqlDbType.DateTime DbType.Decimal| SqlDbType.Decimal DbType.Double | SqlDbType.Float DbType.Guid| SqlDbType.UniqueIdentifier DbType.Int16 | SqlDbType.SmallInt DbType.Int32| SqlDbType.Int DbType.Int64| SqlDbType.BigInt DbType.Object| SqlDbType.Variant DbType.SByte| (抛异常) DbType.Single| SqlDbType.Real DbType.String| SqlDbType.NVarChar DbType.Time| SqlDbType.DateTime DbType.UInt16| (抛异常) DbType.UInt32| (抛异常) DbType.UInt64| (抛异常) DbType.VarNumeric|(抛异常) DbType.AnsiStringFixedLength|SqlDbType.Char DbType.StringFixedLength|SqlDbType.NChar DbType.Xml| SqlDbType.Xml DbType.DateTime2|SqlDbType.DateTime2 DbType.DateTimeOffset|SqlDbType.DateTimeOffset