有效利用索引和SQL构造，成功解决性能问题的经验

有效降低以下SQL语句在统计大数据量时的效率，将使系统的性能体验更上一个层次,试验证明通在大数据量表中创建Trddt、updateid、UPDATESTATE的非聚焦索引以及改进SQL语句的构造，对于检索数据统计功能的SQL语句有质的提高，试验证明一次查询可以在4s内完成。

/*

    测试环境：将数据拷贝到本地，进行调试测试(测试环境不是在100.206上面，我把1.01个G的数据全拷贝到本地了)

    数据库：CSMAR_WebData

    表名：TRD_Dalyr ,在csmar前台系统上是日个股回报率文件的一部分

    本地测试表的数据量：1.01G，共计条记录3992396条

   

    测试方法：1.先执行清除数据库相关缓存的操作

             2.执行原始方法，耗费时间s左右

3.对字段Trddt(字符型)、updateid(字符型)、UPDATESTATE(int)建立非聚焦索引

            4.执行清除数据库相关缓存的操作

            5.执行新创建的Sql查询，耗费时间在s左右

            

    结论：在大数据表中创建Trddt、updateid、UPDATESTATE相对于的非聚焦索引，对于检索数据统计功能的SQL语句有质的提高

   

*/

 

 

/* 清除数据库相关的缓存，以保证用户处于第一次访问的状态，在执行SQL前都应该执行这个操作*/

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

 

--原始方法：共耗费时间s

select 'TRD_Dalyr'as TableName,count(1)as TotalCount from TRD_Dalyrwhere1=1 and[Trddt]

BETWEEN '2002-03-01'AND'2022-03-24' and(TRD_Dalyr.updatestate<>2)

 

--方法二：

--步骤一：对字段Trddt、updateid、UPDATESTATE建立非聚焦索引

create index index_Trddton TRD_Dalyr(Trddt)

create index index_updateidon TRD_Dalyr(updateid)

create index index_Stkcdon TRD_Dalyr(UPDATESTATE)

--步骤二：使用以下方法进行查询，耗费时间在4s左右

SELECT     'TRD_Dalyr' ASTableName,COUNT(updateid)AS TotalCount

FROM   dbo.TRD_DalyrWHERE (UPDATESTATE<2or UPDATESTATE>2)and [Trddt]BETWEEN'2002-03-01'AND '2022-03-24'

索引注意在查询字段上面进行更改和添加。

SELECT     'TRD_Dalyr' AS TableName, COUNT(1) AS TotalCount
FROM   dbo.TRD_Dalyr  WITH(INDEX=index_Stkcd) WHERE  (UPDATESTATE<2 or UPDATESTATE>2) and [Trddt] BETWEEN '2002-03-01' AND '2022-03-24'  --3518556 --3992396 这样也可以，感受好像要快一些。