Linq to sql 查询十分慢

Linq to sql 查询非常慢.
用过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

[解决办法]
表设计问题，检查索引
if多没有关系
[解决办法]
你的（3）中查询语句设计不好，这样的话会使用数据库中数据多层调用。从而减慢查询速度。
[解决办法]

探讨

在unid列上建立索引后再试试。

[解决办法]

sp1234
 
(2015年恐怖分子将得诺贝尔奖) 

等　级：

[解决办法]
应该加上和平做定语
[解决办法]
有没试过先把Query ToList()再做后续操作？
[解决办法]
Linq 其实性能不行的，特别大量数据查询时候，性能很差的，主要分页时 他还是最初始查出所有的数据，然后用take方法，取部分数据，不像用存储过程，每次可以查特定页码的数据，效率高多了。

查看更多 下一篇