一个查询语句:SELECT a.Id ,
a.Bid ,
a.RequestCode,
l.RequestTime,
l.CreateTime
INTO #tmpQDBFlow
FROM lending.QDBFlow a WITH ( NOLOCK )
LEFT JOIN lending.Lending l WITH(NOLOCK) ON l.Bid=a.Bid
WHERE a.Step = 3
AND a.IsDisable = 1;
SELECT a.Bid
FROM #tmpQDBFlow a WITH ( NOLOCK )
LEFT JOIN lending.QDBRequestLog r WITH ( NOLOCK ) ON a.Id = r.FlowId
AND a.RequestCode = r.RequestCode
WHERE CAST(r.ResponseData AS VARCHAR(100)) = ''
ORDER BY a.Bid;
DROP TABLE #tmpQDBFlow;
ResponseData 是 Text 数据类型,要取出来判断是否为空,还得转成 varchar,消耗性能:
在程序中使用报超时错误:
可以通过设置 SqlCommand 的TimeOut 值大点来防止超时:
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 180;
但是并不能从根本上解决问题,还得从 sql 语句本身去优化。
改成如下,速度瞬间变快:SELECT a.Id ,
a.Bid ,
a.RequestCode,
l.RequestTime,
l.CreateTime
INTO #tmpQDBFlow
FROM lending.QDBFlow a WITH ( NOLOCK )
LEFT JOIN lending.Lending l WITH(NOLOCK) ON l.Bid=a.Bid
WHERE a.Step = 3
AND a.IsDisable = 1;
SELECT a.Bid,r.ResponseData
INTO #tmpSltBids
FROM #tmpQDBFlow a WITH ( NOLOCK )
LEFT JOIN lending.QDBRequestLog r WITH ( NOLOCK ) ON a.Id = r.FlowId
AND a.RequestCode = r.RequestCode
SELECT Bid FROM #tmpSltBids
WHERE CAST(ResponseData AS VARCHAR(100)) = ''
ORDER BY Bid;
DROP TABLE #tmpQDBFlow;
DROP TABLE #tmpSltBids;
具体不多解释。
将查询结果放到一个临时表中,再去连接查询这个临时表是一个很不错的优化方式。
refer:https://www.cnblogs.com/nx520zj/articles/6089935.html