linq翻译出来的最终sql为什么这么怪异?
一个web项目,近日由于访问量急增,速度很慢,怀疑是数据库压力过大,用sql2005的Sql Server Profier监测下来,发现执行的sql把很多表的数据全部取出来了,并不是按我期望的仅取需要分页的当前页数据,后来测试了一下,发现是linq最终提交到数据库的sql有问题,代码如下:
using (CntvsWebProductDataContext db = new CntvsWebProductDataContext(Website.CntvsWebProductConnectionString()))
{
if (db.Connection.State != System.Data.ConnectionState.Open) { db.Connection.Open(); }
try {
Expression<Func<V_Product, bool>> _Where = PredicateBuilder.True<V_Product>();
_Where = _Where.And(v => v.F_IsShow == 1);
var s1 = db.V_Products.Where<V_Product>(_Where.Compile()).Skip(100).Take(30).Select(v=>v.F_ID).ToList();
//最终提交到数据库的sql语句居然是
// SELECT [t0].[F_ID], [t0].[F_No], [t0].[F_ShopID], [t0].[F_BrandID], [t0].[F_OtherBrand], [t0].[F_ClsID], [t0].[F_ShopClsID], [t0].[F_Tags], [t0].[F_PinYingTags], [t0].[F_Name], [t0].[F_Summary], [t0].[F_LogoPic],
//[t0].[F_HotPic], [t0].[F_PlaceID], [t0].[F_MarketPrice], [t0].[F_SalePrice], [t0].[F_SubjectID], [t0].[F_Hits], [t0].[F_Orders], [t0].[F_IsHtml], [t0].[F_HtmlUrl], [t0].[F_IsShow], [t0].[F_Status], [t0].[F_ShopOrders],
//[t0].[F_ShopIsShow], [t0].[F_ShopIsRecommend], [t0].[F_HasInvoice], [t0].[F_HasGuarantee], [t0].[F_AutoID], [t0].[F_ClassName], [t0].[F_ClsAutoID], [t0].[F_ParentIDStr], [t0].[F_ParentNameStr], [t0].[F_InputDate],
//[t0].[F_IsSellerPayExpress]
//FROM [dbo].[V_Product] AS [t0]
var s2 = db.V_Products.Where(v=>v.F_IsShow==1).Skip(100).Take(30).Select(v=>v.F_ID).ToList();
//最终提交到数据库的sql语句是
// exec sp_executesql N'SELECT [t2].[F_ID]
//FROM (
// SELECT [t1].[F_ID], [t1].[ROW_NUMBER]
// FROM (
// SELECT ROW_NUMBER() OVER (ORDER BY [t0].[F_ID], [t0].[F_No], [t0].[F_ShopID], [t0].[F_BrandID], [t0].[F_OtherBrand], [t0].[F_ClsID], [t0].[F_ShopClsID], [t0].[F_Tags], [t0].[F_PinYingTags], [t0].[F_Name],
//[t0].[F_Summary], [t0].[F_LogoPic], [t0].[F_HotPic], [t0].[F_PlaceID], [t0].[F_MarketPrice], [t0].[F_SalePrice], [t0].[F_SubjectID], [t0].[F_Hits], [t0].[F_Orders], [t0].[F_IsHtml], [t0].[F_HtmlUrl], [t0].[F_IsShow],
//[t0].[F_Status], [t0].[F_ShopOrders], [t0].[F_ShopIsShow], [t0].[F_ShopIsRecommend], [t0].[F_HasInvoice], [t0].[F_HasGuarantee], [t0].[F_AutoID], [t0].[F_ClassName], [t0].[F_ClsAutoID], [t0].[F_ParentIDStr],
//[t0].[F_ParentNameStr], [t0].[F_InputDate], [t0].[F_IsSellerPayExpress]) AS [ROW_NUMBER], [t0].[F_ID]
// FROM [dbo].[V_Product] AS [t0]
// WHERE [t0].[F_IsShow] = @p0
// ) AS [t1]
// WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
// ) AS [t2]
//ORDER BY [t2].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=100,@p2=30
}
finally
{
db.Connection.Close();
}
}
上面的sql语句是我在事件跟踪器里记录下来的,生成这样的语句,性能低下也就不奇怪了?为何linq to sql翻译出来的最终语句这么怪??????
我只不过要一个类似
Select * FROM (select ROW_NUMBER() Over(order by F_ID) as rowId,F_ID from V_Product where F_isShow=1) as V_Product where rowId between 1 and 30
的语句,为什么这么难?