SQL很有技术含量的有关问题，达人请进。10万数据量查询居然要50秒
SQL很有技术含量的问题，达人请进。。10万数据量查询居然要50秒
大概情况是这样的。。数据库里数据量有10万条，要做很复杂的统计操作。。
所以我的做法如下：
1. 将这10万条数据按照日期筛选后放入临时表#A
2.建了一个返回结果的临时表#B
3.每次查询到一部分数据就对#B动态添加一列，然后更新数据
4。一共有16列，所以要查询#A 16次。。
因为查询比较复杂，所以没法用SQL搞出结果，所以只能一列一列这样添加，貌似性能很差。。
现在的情况是第一次查询要50秒。。。 以后每次只需要4秒。。。这个是为什么？ 还有有没有优化的方法。。
------解决方案--------------------
1.是否可以一次性将16列选出,做一次更新?
2.是否在日期列上建立索引?
按照道理说,10W数据,不至于这么差的.
可能的话,写出相应的表结构和脚本看看.
------解决方案--------------------
我觉得，最主要的还是要把业务处理的逻辑优化一下。
第一次是50S，因为要建立表，插入数据，修改表结构等，后面时间短可能是因为不需要这个DDL操作了，也有可能是缓存中有了相应的数据
------解决方案--------------------
大概情况是这样的。。数据库里数据量有10万条，要做很复杂的统计操作。。
所以我的做法如下：
1. 将这10万条数据按照日期筛选后放入临时表#A
2.建了一个返回结果的临时表#B
3.每次查询到一部分数据就对#B动态添加一列，然后更新数据
4。一共有16列，所以要查询#A 16次。。
因为查询比较复杂，所以没法用SQL搞出结果，所以只能一列一列这样添加，貌似性能很差。。
现在的情况是第一次查询要50秒。。。 以后每次只需要4秒。。。这个是为什么？ 还有有没有优化的方法。。
------解决方案--------------------
1.是否可以一次性将16列选出,做一次更新?
2.是否在日期列上建立索引?
按照道理说,10W数据,不至于这么差的.
可能的话,写出相应的表结构和脚本看看.
------解决方案--------------------
我觉得，最主要的还是要把业务处理的逻辑优化一下。
第一次是50S，因为要建立表，插入数据，修改表结构等，后面时间短可能是因为不需要这个DDL操作了，也有可能是缓存中有了相应的数据
------解决方案--------------------
- SQL code
--建议：
--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.去除在为临时表增加字段时的默认值设置，如果设置默认值，那么在创建这个字段的时候会向这个字段全部写一遍默认值，降低了效率
--先提这两点，楼主看着修改一下吧。
------解决方案--------------------
SQL2000?SQL 2005?
大概看了一下,
SQL2005的话,可以使用Top + 变量,
ROW_NUMBER等对你的数据进行分组处理,
另外,临时表使用的太多了,
看看是否可以减少一些。
------解决方案--------------------
和尚说的有道理呀。
楼主可以从下面几个地方着手。
1.尽量减少临时表。
2.减少动态SQL的使用
3.优化业务逻辑。
因为对你的业务不了解，所以只能说到这了。
------解决方案--------------------
- SQL code
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是另一个主要性能问题。
*/