SQL很有技术含量的问题，达人请进。。10万数据量查询居然要50秒

Question

大概情况是这样的。。数据库里数据量有10万条，要做很复杂的统计操作。。
所以我的做法如下：
1. 将这10万条数据按照日期筛选后放入临时表#A2.建了一个返回结果的临时表#B3.每次查询到一部分数据就对#B动态添加一列，然后更新数据4。一共有16列，所以要查询#A 16次。。因为查询比较复杂，所以没法用SQL搞出结果，所以只能一列一列这样添加，貌似性能很差。。现在的情况是第一次查询要50秒 以后每次只需要4秒这个是为什么？ 还有有没有优化的方法。。

Answer 1

    Declare @MinTime datetime  --数据库中最小的日期
    Declare @MaxTime datetime  --数据库中最大的日期
    SELECT @MinTime=Min(LcCreateTime) from tbl_lcinfo
    SELECT @MaxTime=Max(LcCreateTime) from tbl_lcinfo
    
    --验证输入参数有效性
    IF @BeginTime IS NULL
       SET @BeginTime=@MinTime
    IF @EndTime IS NULL
      SET @EndTime=@MaxTime    DECLARE @SecondSortNum int --取二级分类数量
    SET @SecondSortNum=4    DECLARE @ThirdSortNum int  --取三级分类数量
    SET @ThirdSortNum=3    DECLARE @StreetNum int     --街道数量
    SET @StreetNum=13    --创建临时信息表
    Create table #TempLcInfo
    (
LcID  varchar(20) not null primary key,
        LcSort1 int,
        LcSort2 int,
        LcSort3 int,
        DealDept varchar(40),
        LcCreateTime datetime
    )
    
   --插入数据
    INSERT INTO #TempLcInfo 
      SELECT LcID,LcSort1,LcSort2,LcSort3,DealDept,LcCreateTime
      From tbl_LcInfo where LcCreateTime Between @BeginTime and @EndTime
     --创建临时表，保存数量最多的二级分类ID
Create table  #TempSecontSortTable
(
   ID        int identity(1,1),
   SortID    int unique,
       SortName  varchar(64),
   SortCount int
)  --创建临时表，保存数量最多的三级分类ID
Create table #TemPThirdSortTable
(
   ID        int identity(1,1),
   SortID    int unique,
   SortCount int,
   ParentSortID int,
       SortName  varchar(64)
)    --创建临时表，用来保存返回给用户的分类的名称
    Create table #TempReturnThirdSortTable
    (
       ID        int identity(1,1),
   SortName  varchar(64)
    ) --插入数据
exec('INSERT INTO  #TempSecontSortTable
          SELECT A.LCSORT2,B.SortName,A.SORTCOUNT
          From 
          (select TOP '+ @SecondSortNum +' LCSORT2,COUNT(LCSORT2) AS SORTCOUNT 
        FROM #TempLcInfo 
        GROUP BY LCSORT2 ORDER BY sortcount DESC
           ) A,
          dic_LcSort2 B where A.LCSORT2=B.ID ORDER BY A.sortcount DESC') --创建返回结果临时表
Create table #returnTable
(
   SerialNumber int identity(1,1) primary key,  --主键
   StreetName varchar(500),   --街道名称
   StreetID  varchar(50),     --街道ID
   TotalCount  int --总计列
) --添加街道
    INSERT INTO #returnTable (StreetID,StreetName)
SELECT DeptID,DeptName
FROM dic_DeptSort
ORDER BY DeptID ASC
   
    DECLARE @NUM INT
    SET @NUM=1
 
--最大的四列
WHILE @NUM<@SecondSortNum+1
  BEGIN
truncate table #TemPThirdSortTable
declare @CurrentSortID INT
SELECT  @CurrentSortID=SortID from #TempSecontSortTable where ID=@NUM
exec('INSERT INTO #TemPThirdSortTable
              Select  A.LcSort3,A.SortCount,A.Lcsort2,
               CASE B.SortName 
               WHEN ''其他'' THEN ''[其它]''
               WHEN ''其它'' THEN ''[其它]'' 
               ELSE B.SortName END as SrotName 
               from
               (Select top '+ @ThirdSortNum +' LcSort3,count(LCSort3) as SortCount,Lcsort2
     FROM tbl_lcinfo
     Group by LcSort3,LcSort2
     HAVING LcSort2='+@CurrentSortID+'
     ORDER BY SortCount DESC) A, 
               dic_LcSort3 B
               where A.lcSort3=B.ID order by A.SortCount DESC')
INSERT INTO #TempReturnThirdSortTable
      SELECT SortName
          FROM #TemPThirdSortTable
        
        INSERT INTO #TempReturnThirdSortTable (SortName) VALUES ('其它')
INSERT INTO #TempReturnThirdSortTable (SortName) VALUES ('小计')

        Declare @SonNum int
Set @SonNum=1
    
--前三列
WHILE @SonNum<@ThirdSortNum+1
  BEGIN
declare @CurrentSonSortID INT
SELECT  @CurrentSonSortID=SortID from #TemPThirdSortTable where ID=@SonNum
EXEC('ALter table #returnTable ADD Sort2Top'+ @NUM +'Sort3Top'+ @SonNum +' int default(0) not null')
EXEC('UPDATE  #returnTable Set Sort2Top'+ @NUM +'Sort3Top'+ @SonNum +'=C.SortCount
  From #returnTable D,
  (Select TOP '+ @StreetNum +' Count(A.LcID) as SortCount,B.StreetID
  FROM #TempLcInfo A
  INNER JOIN dic_DeptMapStreet B  ON A.DEALDEPT=B.DeptID
  GROUP BY B.StreetID,A.LCSORT3
  HAVING A.LCSORT3='+@CurrentSonSortID+'
  Order by B.StreetID ASC) C Where D.StreetID=C.StreetID')
SET @SonNum=@SonNum+1
 END
     
 --其它列
 EXEC('ALter table #returnTable ADD Sort2Top'+ @NUM +'Other int default(0) not null')
 EXEC('UPDATE  #returnTable SET Sort2Top'+ @NUM +'Other=C.SortCount
  FROM #returnTable D,
  (Select TOP '+ @StreetNum +' Count(A.LcID) as SortCount,B.StreetID
  FROM 
 (select DEALDEPT,LcID from #TempLcInfo 
  where LCSORT3 NOT IN (SELECT SortID FROM #TemPThirdSortTable)
  AND lcsort2='+ @CurrentSortID +')
  AS A
  INNER JOIN dic_DeptMapStreet B ON A.DEALDEPT=B.DeptID
  GROUP BY B.StreetID
  Order by B.StreetID ASC) C where D.StreetID=C.StreetID')
     
 --小计列
 EXEC('ALter table #returnTable ADD Sort2Top'+ @NUM +'Count int')
 EXEC('UPDATE #returnTable SET Sort2Top'+ @NUM +'Count=
  (Sort2Top' + @NUM +'Sort3Top1+Sort2Top' + @NUM +'Sort3Top2
  +Sort2Top' + @NUM +'Sort3Top3+Sort2Top'+ @NUM +'Other)')
 Set @NUM=@NUM+1
  END
 
--总计列
    Print '计算总列数'
exec('UpDate #returnTable Set TotalCount=Sort2Top1Count+Sort2Top2Count+Sort2Top3Count+Sort2Top4Count')
--返回结果集
select * from #returnTable
    select * from #TempSecontSortTable
    select * from #TempReturnThirdSortTable
    --清除操作
Drop table #TempSecontSortTable
Drop table #TemPThirdSortTable
    Drop table #TempReturnThirdSortTable
Drop table #returnTable
    Drop table #TempLcInfo
这个是我的SQL。。麻烦hery2002大哥帮我看看，我知道我这样写性能很差，可是不知道如何优化

Answer 2

我觉得，最主要的还是要把业务处理的逻辑优化一下。
第一次是50S，因为要建立表，插入数据，修改表结构等，后面时间短可能是因为不需要这个DDL操作了，也有可能是缓存中有了相应的数据

Answer 3

看来只能再优化下sql了，不知道如何下手。。唉！

Answer 4

不熟悉你的业务，很难做到完全优化，，
这个SQL貌似没有可优化的了，，

Answer 5

是啊，兄弟，我以前很少接触sql这些。。现在还真的不知道如何优化呢，呵呵。。 一次性把所有的都查出来貌似很难。。

Answer 6

sqlserver的sql语句可以很像过程语言的，支持if/while。

Answer 7

首先减少io，做临时表就是IO了

Answer 8

--建议：
--1.sql server 2005支持 select top (@变量) * from tb 的形式，可以将动态语句变为静态，例如：
    INSERT INTO  #TempSecontSortTable
          SELECT A.LCSORT2,B.SortName,A.SORTCOUNT
          From 
          (select TOP (@SecondSortNum) LCSORT2,COUNT(LCSORT2) AS SORTCOUNT 
            FROM #TempLcInfo 
            GROUP BY LCSORT2 ORDER BY sortcount DESC
           ) A,
          dic_LcSort2 B where A.LCSORT2=B.ID ORDER BY A.sortcount DESC--2.去除在为临时表增加字段时的默认值设置，如果设置默认值，那么在创建这个字段的时候会向这个字段全部写一遍默认值，降低了效率
--先提这两点，楼主看着修改一下吧。

Answer 9

SQL2000?SQL 2005?
大概看了一下,
SQL2005的话,可以使用Top + 变量,
ROW_NUMBER等对你的数据进行分组处理,
另外,临时表使用的太多了,
看看是否可以减少一些。

Answer 10

和尚说的有道理呀。楼主可以从下面几个地方着手。
1.尽量减少临时表。
2.减少动态SQL的使用
3.优化业务逻辑。因为对你的业务不了解，所以只能说到这了。

Answer 11

第1次要50s，以后就只要4s，这是因为在cache中有了，你可以查看master..syscacheobjects。
至于没有cache为什么会相差这么远，我也不太清楚，目前也正为此事苦恼，有个client的机器就是这样，我怀疑是mssql出了问题。

Answer 12

 Declare @MinTime datetime  --数据库中最小的日期
    Declare @MaxTime datetime  --数据库中最大的日期
    SELECT @MinTime=Min(LcCreateTime) from tbl_lcinfo
    SELECT @MaxTime=Max(LcCreateTime) from tbl_lcinfo/*
ashzs:1、@MinTime和@MaxTime的取值过早，如果@BeginTime和@EndTime都不为null，岂不是白白浪费了两次查询？
2、tbl_lcinfo是不是有针对LcCreateTime的索引？索引问题不再重复提起。*/    
    --验证输入参数有效性
    IF @BeginTime IS NULL
       SET @BeginTime=@MinTime
    IF @EndTime IS NULL
      SET @EndTime=@MaxTime    DECLARE @SecondSortNum int --取二级分类数量
    SET @SecondSortNum=4    DECLARE @ThirdSortNum int  --取三级分类数量
    SET @ThirdSortNum=3    DECLARE @StreetNum int     --街道数量
    SET @StreetNum=13/*
ashzs:
1、LcSort1和LcCreateTime字段从来没有用过，为什么要创建？
2、LcID为什么需要设置为主键？在INSERT INTO #TempLcInfo的时候会出现主键重复的情况？tbl_LcInfo中的LcID不是主键吗？
如果是，这里根本不需要设置主键。如果说是为了提高效率，后面的查询也没看到能够使用LcID提高效率的地方。
3、不判断临时表是否存在就直接创建？你后面的drop table有可能因为中途异常不能运行啊。
*/
    --创建临时信息表
    Create table #TempLcInfo
    (
        LcID  varchar(20) not null primary key,
        LcSort1 int,
        LcSort2 int,
        LcSort3 int,
        DealDept varchar(40),
        LcCreateTime datetime
    )
/*
ashzs:
tbl_LcInfo上有针对LcCreateTime的索引吗？tbl_LcInfo上字段多吗？如果字段很多，请考虑索引覆盖。*/    
   --插入数据
    INSERT INTO #TempLcInfo 
      SELECT LcID,LcSort1,LcSort2,LcSort3,DealDept,LcCreateTime
      From tbl_LcInfo where LcCreateTime Between @BeginTime and @EndTime
    
/*
ashzs:1、SortName和SortCount在算法中也没有用到啊，为了最后的返回结果集？
2、SortID为什么是unique？保证唯一？但是如果不唯一的话你有错误处理吗？如果是为了提高查询，SortID根本不需要加唯一索引。
   只是一个unqiue，你的SortID值就要存储两份。
   
后面的临时表不再为你检查是否有不需要的字段\unique。依例改之。   
*/
    --创建临时表，保存数量最多的二级分类ID
    Create table  #TempSecontSortTable
    (
       ID        int identity(1,1),
       SortID    int unique,
       SortName  varchar(64),
       SortCount int
    )     --创建临时表，保存数量最多的三级分类ID
    Create table #TemPThirdSortTable
    (
       ID        int identity(1,1),
       SortID    int unique,
       SortCount int,
       ParentSortID int,
       SortName  varchar(64)
    )    --创建临时表，用来保存返回给用户的分类的名称
    Create table #TempReturnThirdSortTable
    (
       ID        int identity(1,1),
       SortName  varchar(64)
    )/*
ashzs:
1、很不喜欢你这种不写明对应字段的做法，字段又不多，为什么不为#TempSecontSortTable写出插入的字段名？
2、如果是sql2005（包括2005）以后版本，这种动态top值已经不需要动态sql了。
3、为什么要ORDER BY A.sortcount DESC？你这里order by会保证最后显示的结果是按照这个循序排序的吗？
请记住你的临时表是个堆，只有在需要排序展现的地方才需要order by。每次插入的时候，你的order by 是浪费的。
这也是你存储过程中sql写法上最大的性能问题。后面的地方依例改之。
*/    --插入数据
    exec('INSERT INTO  #TempSecontSortTable
          SELECT A.LCSORT2,B.SortName,A.SORTCOUNT
          From 
          (select TOP '+ @SecondSortNum +' LCSORT2,COUNT(LCSORT2) AS SORTCOUNT 
            FROM #TempLcInfo 
            GROUP BY LCSORT2 ORDER BY sortcount DESC
           ) A,
          dic_LcSort2 B where A.LCSORT2=B.ID ORDER BY A.sortcount DESC')    --创建返回结果临时表
    Create table #returnTable
    (
       SerialNumber int identity(1,1) primary key,  --主键
       StreetName varchar(500),   --街道名称
       StreetID  varchar(50),     --街道ID
       TotalCount  int --总计列
    )    --添加街道
    INSERT INTO #returnTable (StreetID,StreetName)
    SELECT DeptID,DeptName
    FROM dic_DeptSort
    ORDER BY DeptID ASC
   
    DECLARE @NUM INT    
    SET @NUM=1
 
    --最大的四列
    WHILE @NUM<@SecondSortNum+1
      BEGIN
        truncate table #TemPThirdSortTable
        declare @CurrentSortID INT
/*
ashzs:
1、@ThirdSortNum是固定的（常量）还是传入的？看你的定义方式很像固定的，如果是固定的为什么还要动态sql？惯性动作？
2、select和insert是可以使用一条sql完成的，为什么要分开做？
3、case when最好不在这里做，等到最后展现时一次作。*/        
        SELECT  @CurrentSortID=SortID from #TempSecontSortTable where ID=@NUM
        exec('INSERT INTO #TemPThirdSortTable
              Select  A.LcSort3,A.SortCount,A.Lcsort2,
               CASE B.SortName 
               WHEN ''其他'' THEN ''[其它]''
               WHEN ''其它'' THEN ''[其它]'' 
               ELSE B.SortName END as SrotName 
               from
               (Select top '+ @ThirdSortNum +' LcSort3,count(LCSort3) as SortCount,Lcsort2
                 FROM tbl_lcinfo
                 Group by LcSort3,LcSort2
                 HAVING LcSort2='+@CurrentSortID+'
                 ORDER BY SortCount DESC) A, 
               dic_LcSort3 B
               where A.lcSort3=B.ID order by A.SortCount DESC')
        INSERT INTO #TempReturnThirdSortTable
          SELECT SortName
          FROM #TemPThirdSortTable
        
        INSERT INTO #TempReturnThirdSortTable (SortName) VALUES ('其它')
        INSERT INTO #TempReturnThirdSortTable (SortName) VALUES ('小计')
        
        Declare @SonNum int
        Set @SonNum=1
 
 /*
 ashzs:
 
 这个“前三列”循环需要吗？1条或2条sql是不是可以完成！？好好想想（提示：group+join）！！^O^
 
 如果这个循环可以节省，最外面的循环需要吗？或者说需要这么细粒度的处理吗？你的这个存储过程最大的结构问题就在这两个循环上。
 
 请记住：只有在下条数据是根据上条数据的结果产生时（如金额递增累加），循环或者游标才有意义，你的数据之间根本就不需要循环处理。
 请使用集合操作！
 
 */
        
        --前三列
        WHILE @SonNum<@ThirdSortNum+1
          BEGIN
            declare @CurrentSonSortID INT
            SELECT  @CurrentSonSortID=SortID from #TemPThirdSortTable where ID=@SonNum
            EXEC('ALter table #returnTable ADD Sort2Top'+ @NUM +'Sort3Top'+ @SonNum +' int default(0) not null')
            EXEC('UPDATE  #returnTable Set Sort2Top'+ @NUM +'Sort3Top'+ @SonNum +'=C.SortCount
              From #returnTable D,
              (Select TOP '+ @StreetNum +' Count(A.LcID) as SortCount,B.StreetID
              FROM #TempLcInfo A
              INNER JOIN dic_DeptMapStreet B  ON A.DEALDEPT=B.DeptID
              GROUP BY B.StreetID,A.LCSORT3
              HAVING A.LCSORT3='+@CurrentSonSortID+'
              Order by B.StreetID ASC) C Where D.StreetID=C.StreetID')
            SET @SonNum=@SonNum+1
         END
         
         --其它列
         EXEC('ALter table #returnTable ADD Sort2Top'+ @NUM +'Other int default(0) not null')
         EXEC('UPDATE  #returnTable SET Sort2Top'+ @NUM +'Other=C.SortCount
              FROM #returnTable D,
              (Select TOP '+ @StreetNum +' Count(A.LcID) as SortCount,B.StreetID
              FROM 
             (select DEALDEPT,LcID from #TempLcInfo 
              where LCSORT3 NOT IN (SELECT SortID FROM #TemPThirdSortTable)
              AND lcsort2='+ @CurrentSortID +')
              AS A
              INNER JOIN dic_DeptMapStreet B ON A.DEALDEPT=B.DeptID
              GROUP BY B.StreetID
              Order by B.StreetID ASC) C where D.StreetID=C.StreetID')
         
         --小计列
         EXEC('ALter table #returnTable ADD Sort2Top'+ @NUM +'Count int')
         EXEC('UPDATE #returnTable SET Sort2Top'+ @NUM +'Count=
              (Sort2Top' + @NUM +'Sort3Top1+Sort2Top' + @NUM +'Sort3Top2
              +Sort2Top' + @NUM +'Sort3Top3+Sort2Top'+ @NUM +'Other)')
         Set @NUM=@NUM+1
      END
     
    --总计列
    Print '计算总列数'
    exec('UpDate #returnTable Set TotalCount=Sort2Top1Count+Sort2Top2Count+Sort2Top3Count+Sort2Top4Count')
/*ashzs:
“返回结果集”才是order by和case when应该登场的地方。*/  
  
    --返回结果集
    select * from #returnTable
    select * from #TempSecontSortTable
    select * from #TempReturnThirdSortTable
    --清除操作
    Drop table #TempSecontSortTable
    Drop table #TemPThirdSortTable
    Drop table #TempReturnThirdSortTable
    Drop table #returnTable
    Drop table #TempLcInfo/*ashzs:总评：
1、细粒度的循环操作是最大的结构性性能问题。
2、insert时的order by是另一个主要性能问题。*/

Answer 13

现在的情况是第一次查询要50秒 以后每次只需要4秒这个是为什么？ 还有有没有优化的方法。。---------------------------------因为buffer pool中有缓存啊。第一次是硬解析，以后是软解析。
另外：建议楼主看看rollup和cude的使用方法，可能有意外的惊喜。

Answer 14

动态SQL每次执行，SQLServer都会重新编译执行的SQL串。因为你的脚本中含有大量的动态SQL。建议，你可以使用SQL Profile来跟踪下，看看那个地方最消耗性能，然后重点优化性能差的地方，比较有针对性。

Answer 15

可否详细的介绍一下 SQL语句

Answer 16

谢谢这么多朋友的关心，这两天一直有事没上CSDN,看到这么多的朋友回帖给予帮助我很开心。。我讲下我这个问题的基本情况吧。。数据库表结构情况：
一张主表：TBL_LCINFO ..这个表的字段非常多，我在查询中用到的就是Sort1（分类1 ID）、Sort2（分类2 ID）、Sort3（分类3 ID）、DealDept（部门ID）。。也就是说这里的每条记录有3个级别的分类。一张部分到街道的映射表：DeptMapStreet  就是把DealDept映射到对应的街道一张街道表：Dic_DeptSort 提供街道ID和街道Name，大家在这里不要误解了，其实Dic_DeptSort就是接到表，名字可能比较怪，不过没办法，老系统了。
当然还有三张对应分类的表，就是分类ID，分类Name的。。现在的需求是，要统计主表里面的记录，Sort2数量最大的前4个，然后在这4个中在统计每个里面数量最大的前3个，另外的数量用其它标示，另外还有个小计，还有总计。
举个例子，按Sort2的数量统计后结果如下：
Sort2ID   Count
313      100
314      80
315      70
316      50
319      30
310      10那么Sort2的前4个就是313，314，315，316然后求个Sort2分类中的Sort3的数量SOrt3ID  COUNT  SORT2ID
31310    40      313
31311    30      313
31312    10      313
31314     5      313
31315     5      313
31316     5      313
31317     5      313那么313的三级分类数量就是31310，31311，31312。这个是基本需求，另外就是要对这些数量按照街道进行分组。。最后需要的表的格式如下：街道名称            313（100）                          314（80）               315（70）                  316（60）             总计
          
            31310  31311  31312   其它   小计    XX   XX   XX  其它   小计南京街道    10      10     5       4     29北京街道    10      10     5       6     31江西街道    10      10     0       3     23西安街道    10       0     0       7     17 合 计     40      30    10       20    100                                                                          XXX因为需求是必须用一个存储过程返回，所以必须一次性返回。
因为再返回分类名称到一个select语句里太复杂，我就用了另外2个表专门存Sort2Name和Sort3Name。。另外，索引本来已经建过了的。。所以不是没有建索引的问题。因为要求要按时间段查询，主表TBL_LCINFO字段太多，信息量比较大，所以我第一次就建了张临时表，先根据时间把需要的记录插到临时表了，上面存储过程我也写了。。不知道这样好不好

Answer 17

临时表上貌似没建索引哦。
另外，大量的动态SQL，似乎也不高效。