Linq to sql 查询十分慢
Linq to sql 查询非常慢.
用过Vs2008的哥哥姐姐们帮个忙。
问题如下:
(1)源代码的一个获取结果集的方法。
(2)如果查询的是第1页的数据
跟踪得到Sql语句是:
这样是没有问题的,速度很快。
(3)如果查询到100页之后。跟踪得到SQL语句是:
这样的话,查询就超级慢。。。非常慢,我把这条语句拿到查询分析器里面执行,也等了半天才出来。
测试数据是10000条。
小弟对linq to sql 不是很了解。。不知道为什么会这样。。哪个大哥大姐有碰到过,帮忙帮忙啊 ,,谢谢~~~感激。。
------解决方案--------------------
在unid列上建立索引后再试试。
------解决方案--------------------
不是LINK TO SQL的问题,
请从SQL语句找找原因..
------解决方案--------------------
尽量少在IF里面嵌套IF
用过Vs2008的哥哥姐姐们帮个忙。
问题如下:
(1)源代码的一个获取结果集的方法。
- C# code
public static IQueryable GetAllcb_project(int pageIndex, int pageSize, string sort, string dir,out int count) { var query = from q in DataContext.cb_project select new { ProjectID = q.unid, ProjectName = q.prjname, CreatPerson = q.prjauthor, CreatTime = q.createdate }; #region 排序字段和排序方向 if (sort != "" && dir != "") { if (sort == "UNID") { if (dir == "desc") { query = query.OrderByDescending(q => q.ProjectID); } if (dir == "asc") { query = query.OrderBy(q => q.ProjectID); } } } count = query.Count(); #endregion return query.Skip(pageIndex).Take(pageSize); }
(2)如果查询的是第1页的数据
跟踪得到Sql语句是:
- SQL code
SELECT TOP 25 [t0].[unid] AS [ProjectID], [t0].[prjname] AS [ProjectName], [t0].[prjauthor] AS [CreatPerson], [t0].[createdate] AS [CreatTime] FROM [dbo].[cb_project] AS [t0]
这样是没有问题的,速度很快。
(3)如果查询到100页之后。跟踪得到SQL语句是:
- SQL code
SELECT TOP 25 [t0].[unid] AS [ProjectID], [t0].[prjname] AS [ProjectName], [t0].[prjauthor] AS [CreatPerson], [t0].[createdate] AS [CreatTime]
FROM [dbo].[cb_project] AS [t0]
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP 2475 [t1].[unid], [t1].[prjname], [t1].[prjauthor], [t1].[createdate]
FROM [dbo].[cb_project] AS [t1]
) AS [t2]
WHERE ((([t0].[unid] IS NULL) AND ([t2].[unid] IS NULL)) OR (([t0].[unid] IS NOT NULL) AND ([t2].[unid] IS NOT NULL) AND ((([t0].[unid] IS NULL) AND ([t2].[unid] IS NULL)) OR (([t0].[unid] IS NOT NULL) AND ([t2].[unid] IS NOT NULL) AND ([t0].[unid] = [t2].[unid]))))) AND ((([t0].[prjname] IS NULL) AND ([t2].[prjname] IS NULL)) OR (([t0].[prjname] IS NOT NULL) AND ([t2].[prjname] IS NOT NULL) AND ((([t0].[prjname] IS NULL) AND ([t2].[prjname] IS NULL)) OR (([t0].[prjname] IS NOT NULL) AND ([t2].[prjname] IS NOT NULL) AND ([t0].[prjname] = [t2].[prjname]))))) AND ((([t0].[prjauthor] IS NULL) AND ([t2].[prjauthor] IS NULL)) OR (([t0].[prjauthor] IS NOT NULL) AND ([t2].[prjauthor] IS NOT NULL) AND ((([t0].[prjauthor] IS NULL) AND ([t2].[prjauthor] IS NULL)) OR (([t0].[prjauthor] IS NOT NULL) AND ([t2].[prjauthor] IS NOT NULL) AND ([t0].[prjauthor] = [t2].[prjauthor]))))) AND ((([t0].[createdate] IS NULL) AND ([t2].[createdate] IS NULL)) OR (([t0].[createdate] IS NOT NULL) AND ([t2].[createdate] IS NOT NULL) AND ((([t0].[createdate] IS NULL) AND ([t2].[createdate] IS NULL)) OR (([t0].[createdate] IS NOT NULL) AND ([t2].[createdate] IS NOT NULL) AND ([t0].[createdate] = [t2].[createdate])))))
))
这样的话,查询就超级慢。。。非常慢,我把这条语句拿到查询分析器里面执行,也等了半天才出来。
测试数据是10000条。
小弟对linq to sql 不是很了解。。不知道为什么会这样。。哪个大哥大姐有碰到过,帮忙帮忙啊 ,,谢谢~~~感激。。
------解决方案--------------------
在unid列上建立索引后再试试。
------解决方案--------------------
不是LINK TO SQL的问题,
请从SQL语句找找原因..
------解决方案--------------------
尽量少在IF里面嵌套IF