数据库设计---之统计

     要过年了，今天我做的活动马上出现了大量数据的发问，出现连接池过大的情况，找到原因是查询时间过长，都已经接近1800毫秒，分原因是这个条sql语句的问题，经过优化过后查询速度接近300毫秒，语句优化过后如下：SELECT CASE WHEN  M.voteCount is NULL THEN 0 ELSE voteCount END AS vote_Count ,M.id,
 M.Mark,M.workBigImgUrl,M.last_uid,M.title,M.upLoadTime,M.isPass,M.workIntroduction FROM
 (
      SELECT ROW_NUMBER() OVER ( ORDER BY upLoadTime DESC)AS Mark,* FROM
            (
                SELECT a.id,a.workBigImgUrl,a.uid AS last_uid,a.workIntroduction,a.title,a.isPass,
                voteCount=(CASE   WHEN d.UId is null THEN SUM(b.actualVote) ELSE isnull(SUM(b.actualVote),0) + 20 END),d.Uid,
                MIN(upLoadTime) AS upLoadTime
                FROM dbo.DDB_Activity_MyWorks a
                LEFT JOIN
                (
                   SELECT id as preHotId,UId FROM dbo.DDB_Activity_MyWorksPreHot               
                )  d
                ON a.uid=d.UId
                LEFT JOIN
                (
                     SELECT workId,actualVote = (CASE WHEN uid=0 THEN 1 ELSE 7 END)
                     from dbo.DDB_Activity_VoteInfo
                 )b
                 ON a.id=b.workId
                 WHERE a.isPass=1
                GROUP BY a.id,a.workBigImgUrl,a.workBigImgUrl,a.workIntroduction,a.title,a.isPass,a.uid,d.UId
           ) c
  )AS  M
  WHERE M.Mark>@pageStart AND M.Mark<@pageEnd，其中就是voteInfo这个表出现了问题，这个表数据量已经达到40万的数据量！导致程序一直处于查询的状态，综合以上情况就是出现了，应该在设计的表的时候一定设计 一张统计表，这是我在以前没有注意到的， 以后一定注意到！