SQL2005 索引问题

Question

版主好：

问 一下，关于数据库索引的问题

1、表的结构如下

     DataID varchar(16) not null

     Date  datetime not null

     Column1 float

     Column2 float

     .....................

     Column150 float

     大约有 150 多列

2、表的索引 如下：

     唯一 非聚集索引      索引字段  DataID   Date, 都是 升序

3、表的数据如下：

     000001        1990-1-1

     000001        1990-1-2

   .........

     000001         2012-12-29

     000002        1990-1-1

     000002        1990-1-2

   .........

     000002         2012-12-29

.............

     600999        1990-1-1

     600999        1990-1-2

   .........

     600999         2012-12-29

      整个表 大约 有 几百万行数据，DataID 有 2000 多个，时间 差不多 是 20 年的

问题： 我上面建立的索引，是否合理，对 查询(我的查询 基本都是 where DataID='XXXXXX' and Date='XXXX-XX-XX') ，插入，更新 的操作合理吗？

现象：往这个表 插数据 ，以000开头的挺快，随着 DataID的增大，插入数据变的越来越慢，原因是什么？我的索引 建立的是不是不合理？ 如何 建这个索引，才能使得插入数据

都是一般的快呢？

请帮我分析一下

Answer 1

唯一性高的字段， 通常应该放在索引的前面， 所以你的这个索引以 Date, DataID 来建应该会更好一些

Answer 2

Does table have clustered pkey? On which column?

Answer 3

唯一性高的字段， 通常应该放在索引的前面， 所以你的这个索引以 Date, DataID 来建应该会更好一些

他的条件用的是“where DataID='XXXXXX' and Date='XXXX-XX-XX'” ，所以无论DataID在前面，还是Date在前面，都一样的效果，不影响查询性能, 就算所有的DataID 都一样，所有的Date都不同，DataID在前面也没有关系。假如条件变成“where Date='XXXX-XX-XX'”，那顺序才有关系。

---

Please click the Mark as Answer button if a post solves your problem!

Answer 4

建议在 DataID  Date 上建立聚集索引会比较好

---

给我写信： QQ我：

Answer 5

查询，性能还说的过去，关键是 Insert ,Update 的时候，随着 DataID的增大，越来越慢，按理说，跟DataID具体值的大小应该没关系，但是现在看到的现象就是 000001 这样的以000开头的做Insert,Update 快，反而 600000，这样的以600 开头的 ，700，800，900 这样的开头的，做 Insert,Update 慢，3-6秒 才能插入一条，什么原因呢？

表中的字段  有 200左右的列吧，到底 是 我的索引 建的有问题 还是 跟 表的 列的数量 大 有关系呢？

Answer 6

查询，性能还说的过去，关键是 Insert ,Update 的时候，随着 DataID的增大，越来越慢，按理说，跟DataID具体值的大小应该没关系，但是现在看到的现象就是 000001 这样的以000开头的做Insert,Update 快，反而 600000，这样的以600 开头的 ，700，800，900 这样的开头的，做 Insert,Update 慢，3-6秒 才能插入一条，什么原因呢？

表中的字段  有 200左右的列吧，到底 是 我的索引 建的有问题 还是 跟 表的 列的数量 大 有关系呢？

把下面的语句运行一下，然后把Rows Executes StmtText 。。。。等为列头的结果表（不要漏了表头，直接拷贝好了（包含表头），格式乱没有关系）跟消息那个TAB下来的内容发上来。
 1,set statistics profile on
   go
   set statistics io on
   go
set statistics time on
   go

     --  insert into  快的时候的T-SQL

  set statistics time off
   go
set statistics io off
   go
set statistics profile off
go

-----------------------------------------------

 2,set statistics profile on
   go
   set statistics io on
   go
set statistics time on
   go

     --      --  insert into  慢的时候的T-SQL

  set statistics time off
   go
set statistics io off
   go
set statistics profile off
go

---

Please click the Mark as Answer button if a post solves your problem!

Answer 7

个人觉得LZ的这个问题是由于阻塞引起的  解决的办法：优化语句，加合适索引， 注意一下事务的提交

LZ提到的：

000001 这样的以000开头的做Insert,Update 快，应该是你的表里的000001 这样的以000开头的记录比较少，而且排序是从000开始的

所以做update 跟insert的时候不会申请很多锁资源

而 600000，这样的以600 开头的 ，700，800，900 这样的开头的记录很多，SQL从000开始按顺序查找加上记录多的关系，所以申请的

锁资源会比较多

不知道LZ使用的事务隔离级别还有LZ有没有使用BEGIN TRANSACTION 和COMMIT TRANSACTION来指定事务的开始跟提交

LZ可以在测试环境试一下下面的做法：

在SSMS里新建一个查询写上下面的SQL语句 数据库名跟表名还有update语句就换成LZ你的，

然后按F4 弹出属性视图看一下这个查询的SPID是多少 where里的DataID就随便选一个600开头的 ：

USE [AdventureWorks]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [Title]='changeheap'
WHERE [EmployeeID] =60

再在SSMS里新建一个查询，写上下面的SQL语句，数据库名，表名 insert语句换成LZ你的，然后按F4 弹出属性视图看一下这个查询的SPID是多少

USE [AdventureWorks]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
INSERT INTO [dbo].[Employee_Demo_Heap]
        ( [EmployeeID] ,
          [NationalIDNumber] ,
          [ContactID] ,
          [LoginID] ,
          [ManagerID] ,
          [Title] ,
          [BirthDate] ,
          [MaritalStatus] ,
          [Gender] ,
          [HireDate] ,
          [ModifiedDate]
        )
SELECT 
501,
480168528,
1009,
'adventure-works\thierry0',
263,
'Tool Desinger',
'1949-08-29 00:00:00.000',
'M',
'M',
'1998-01-11 00:00:00.000',
'2004-07-31 00:00:00.000'

再在SSMS里新建一个查询，写上下面的SQL语句数据库名也是换成LZ你的，根据SPID就知道LZ你的insert语句跟update语句究竟申请了多少锁资源

如果很多的话，那么LZ你就需要加上合适的索引，例如：聚集索引  ，还有事务也要及时提交 使用了begin transaction就需要有commit trasaction

尽量使用默认的事务隔离级别

--------根据DMV查看有多少个锁被连接持有------------------
USE [AdventureWorks] --要查询申请锁的数据库
GO
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname, 
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id] 
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID('AdventureWorks')  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

---

给我写信： QQ我：

Answer 8

通过那个DMV可以看到下面的信息，如果锁数量比较多的话就要注意了，下面是的图示是我运行上面的那个update语句之后，再运行那个

DMV得到的结果

---

给我写信： QQ我：

Answer 9

---

给我写信： QQ我：

Answer 10

当然还有sp_lock跟sysprocesses 等可以查看是否有阻塞如果阻塞的时间比较长的话

---

给我写信： QQ我：

Answer 11

程序 执行的过程中，我用下列语句，没有检测到 阻塞的 SQL语句

SELECT SESSION_ID,TEXT
FROM
 SYS.DM_EXEC_CONNECTIONS
CROSS APPLY
 SYS.DM_EXEC_SQL_TEXT(MOST_RECENT_SQL_HANDLE) AS ST
WHERE SESSION_ID in(select request_session_id from SYS.DM_TRAN_LOCKS where request_status = 'WAIT')

Answer 12

Does table have pkey?

Answer 13

表 没有 设置主键，非要设置主键吗？ 唯一非聚集索引，不是一样可以达到效果吗？

Answer 14

In that case, shouldn't slow down insert because new row always goes to end of the table. That table may fragmented, sql can't defrag it without clustered index. By the way, how often do you rebuild index/update stats?

Answer 15

按你的说法，我将 DataID  和 Date 做成 联合主键 ，可以解决我的问题吗？

Answer 16

Not necessary converting them to pkey, but that table should have clustered index. 

Answer 17

查询，性能还说的过去，关键是 Insert ,Update 的时候，随着 DataID的增大，越来越慢，按理说，跟DataID具体值的大小应该没关系，但是现在看到的现象就是 000001 这样的以000开头的做Insert,Update 快，反而 600000，这样的以600 开头的 ，700，800，900 这样的开头的，做 Insert,Update 慢，3-6秒 才能插入一条，什么原因呢？

表中的字段  有 200左右的列吧，到底 是 我的索引 建的有问题 还是 跟 表的 列的数量 大 有关系呢？

把下面的语句运行一下，然后把Rows Executes StmtText 。。。。等为列头的结果表（不要漏了表头，直接拷贝好了（包含表头），格式乱没有关系）跟消息那个TAB下来的内容发上来。
 1,set statistics profile on
   go
   set statistics io on
   go
set statistics time on
   go

     --  insert into  快的时候的T-SQL

  set statistics time off
   go
set statistics io off
   go
set statistics profile off
go

-----------------------------------------------

 2,set statistics profile on
   go
   set statistics io on
   go
set statistics time on
   go

     --      --  insert into  慢的时候的T-SQL

  set statistics time off
   go
set statistics io off
   go
set statistics profile off
go

---

Please click the Mark as Answer button if a post solves your probl

LZ为什么不试下我给的方法呢，把你2个查询的IO,TIME跟PLAN都贴出来，问题不是清楚很多了嘛

---

 

Answer 18

慢，3-6秒 才能插入一条，什么原因呢？ 想问一下LZ 3~6秒 是如何计算出来的？如果阻塞时间短的话你用SQL语句也查询不出来

---

给我写信： QQ我：

Answer 19

Not necessary converting them to pkey, but that table should have clustered index. 

LZ可以根据rmiao大侠说的在表上加聚集索引

---

给我写信： QQ我：

Answer 20

原来Jackie Shen大侠有两个马甲的

---

给我写信： QQ我：

Answer 21

3-6秒 ，是我的计算程序，将结果计算出来后，插入到数据库后，计算程序 是连续的，我看一下 两个连续数据的插入间隔 就得到了 插一条数据是 3-6秒啊

我的表中有个 字段是 LastUpdate ，是插入时间的，

Answer 22

同样的计算程序， 计算 000 开头的，6-7条/秒， 计算 600 开头 的，6-7秒/条

Answer 23

哎~LZ，不知道该说你什么好，最后一次回帖，参考一下吧。

1，LZ一直在误导我们。一开始问为什么前面的快，后面的慢，现在突然来了个什么“计算程序”。那你怎么知道是真的数据库的INSERT 慢，还是你的那个“计算程序”造成的？你又没有直接在数据库的层面上进行测试。

2，上面提了好几次，让你给出IO,TIME,跟PLAN等相关数据，因为这样可以看出是不是真的数据库造成的快慢，还是被阻塞等情况引起的，或者是其他的因素，可是一直被你忽略。。。。

3，根据关系理论，个人认为RMIAO表达的其实是每个表都应该有个主键，而默认情况下主键也是聚集索引，所以说每个表都应该有个聚集索引。

4，从插入性能上来说，没有聚集索引的HEAP更加快，至少不会有分页的产生。

5，个人认为，你的问题不是数据库的问题，纯粹从数据库层面上来说，不太可能出现你的问题，去查查的你的程序吧，特别是那个计算执行时间的计算方式。

就是直接在SSMS里插入0000开头的跟60000开头的，然后再比较。

---

Please click the Mark as Answer button if a post solves your problem!

Answer 24

其实测试最好还是在SSMS里测试比较公证

还有网络的问题，网速慢这些都有可能的

SQL语句在SQLSERVER内执行总时间不仅包含SQL的执行时间，还包含把结果集发给客户端的时间
SQL执行完毕之后都会发送执行结果给客户端，等待客户端确认。只有确认之后，SQL才认为语句执行完毕，

释放执行申请的资源（包括锁资源）

所以个人觉得网络延时也有可能是因素，当然还有你的程序逻辑

---

给我写信： QQ我：

Answer 25

老大们：

不好意思，，让你们久等了，我用程序 监视到了每个SQL语句的执行时间，说明如下：

1、我的表中 有 200多列，目前为止，数据量已经到了300W的级别

2、我的计算程序，将计算结果插入到这个表中，但是我插入时，不是插一次，就结束了，

     对于某一天

     我的操作过程是：

     Insert('600000'，'2002-5-6',columnValue1，......，columnValue20)

     update 表名 Set column21=3.5，......................，column30= 5.6 where DataID='600000' and Date='2002-5-6'

     update 表名 Set column31=3.5，......................，column40= 5.6 where DataID='600000' and Date='2002-5-6'

     update 表名 Set column41=3.5，......................，column50= 5.6 where DataID='600000' and Date='2002-5-6'

     update 表名 Set column51=3.5，......................，column60= 5.6 where DataID='600000' and Date='2002-5-6'

     update 表名 Set column61=3.5，......................，column70= 5.6 where DataID='600000' and Date='2002-5-6'

     update 表名 Set column71=3.5，......................，column80= 5.6 where DataID='600000' and Date='2002-5-6'

     update 表名 Set column81=3.5，......................，column90= 5.6 where DataID='600000' and Date='2002-5-6'

     update 表名 Set column91=3.5，......................，column100= 5.6 where DataID='600000' and Date='2002-5-6'

.......................

   Update 我做要 24 次，才能处理完一天的数据

现在监视到的情况是 每一个SQL 语句，在现在300W数据量的基础上 ，执行花费时间是  130 毫秒左右，所以处理完一天的数据 需要 150*24=3.600秒左右

所以整体觉得性能下降了不少。

注：处理一天的数据情况下，需要做一次 Select，判断是否存在，存在的话，剩下的操作全是Update(25次)，不存在的话， 先做一次Insert，再做24 次 Update。

所以，我的问题就是：大数据量下，如何 提高 UpDate 的速度，因为之前，数据量 几十万的情况下，每秒能处理6 天的数据， 现在 到了  近 4 秒 才 处理完一天的数据。

请帮忙，由于某些原因，有些东西不便不公开，所以 请老大们，帮忙，看如何在大数据量的情况下，提高Update 的速度。

  

Answer 26

Why update 24 times for same rows in the table?

Answer 27

这是我的计算程序决定的，目前只能这么实现，

是否把 update 的SQL语句整体作为存储过程的一个参数，使用存储过程中实现，会更快一些呢？

Answer 28

Keep in mind that sql is really good on batch processing.