SQL Server 7.0上的索引 [英] Indexes on SQL Server 7.0
问题描述
我的任务是在SQL Server 7.0上维护一个大型数据库。
性能是一个问题并不奇怪，我已经将两个相当于
的大表定位到看看我能用索引做什么。
这两个表格描述如下：
MatterConflicts：
字段：MatterConflicts varchar（16），Matters varchar（16），HitMatters
varchar（16），IsInclude varchar（1）
索引：MatterConflicts
MatterConflictHits：
字段：MatterConflictHits varchar（16），MatterConflicts varchar（16），
ColumnLabel varchar（40），Hit varchar（100）
索引：MatterConflictHits
现在MatterConflicts行数接近500K并且
MatterConflictHits接近100万行。每个表上只有一个
索引，每个索引都是表的主键。 MatterConflicts表格中的事项
字段与用户
直接访问的表格连接起来。
问题是，它会是有益于添加或修改这些表的现有
索引，以包括主键和外键，
以及另外一个字段？
看起来只有
包含主键的索引似乎不是非常有益。例如，我正在考虑为MatterConflicts创建一个
索引，其中包括以下字段：MatterConflicts，
Matters和HitMatters。
想法？建议？谢谢...
I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I''ve targeted two rather
large tables to see what I can do with the indexes.
The 2 tables are described as follows:
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table''s primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
Doesn''t seem to be to be very beneficial to have an index that only
includes the primary key. So for example I''m thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
Thoughts? Suggestions? Thanks...
推荐答案
3月23日下午12:08，Zamdrist < zamdr ... @ gmail.comwrote：
On Mar 23, 12:08 pm, "Zamdrist" <zamdr...@gmail.comwrote:
我的任务是在SQL Server 7.0上维护一个大型数据库。
性能是一个问题并不奇怪，我已经针对两个相当
的大表来看看我能用索引做什么。
描述了2个表如下：
MatterConflicts：
字段：MatterConflicts varchar（16），Matters varchar（16），HitMatters
varchar（ 16），IsInclude varchar（1）
索引：MatterConflicts
MatterConflictHits：
字段：MatterConflictHits varchar（16），MatterConflicts varchar（16），
ColumnLabel varchar（40），命中varchar（100）
索引：MatterConflictHits
现在MatterConflicts行计数接近500K和
MatterConflictHits接近100万行。每个表上只有一个
索引，每个索引都是表的主键。 MatterConflicts表格中的事项
字段与用户
直接访问的表格连接起来。
问题是，它会是有益于添加或修改这些表的现有
索引，以包括主键和外键，
以及另外一个字段？
看起来只有
包含主键的索引似乎不是非常有益。例如，我正在考虑为MatterConflicts创建一个
索引，其中包括以下字段：MatterConflicts，
Matters和HitMatters。
想法？建议？谢谢...
I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I''ve targeted two rather
large tables to see what I can do with the indexes.
The 2 tables are described as follows:
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table''s primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
Doesn''t seem to be to be very beneficial to have an index that only
includes the primary key. So for example I''m thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
Thoughts? Suggestions? Thanks...
这是一个报告和分析系统还是一个交易系统？
Is this a reporting and analytical system or a transactional system?
3月23日下午12:20，布拉德 < Brad.Marsh ... @ Teksouth.comwrote：
On Mar 23, 12:20 pm, "Brad" <Brad.Marsh...@Teksouth.comwrote:
3月23日下午12:08，Zamdrist < zamdr ... @ gmail.comwrote：
On Mar 23, 12:08 pm, "Zamdrist" <zamdr...@gmail.comwrote:
我的任务是在SQL Server 7.0上维护一个大型数据库。
性能这是一个不足为奇的问题，我已经针对两个相当大的b $ b大表来看看我能用索引做些什么。
I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I''ve targeted two rather
large tables to see what I can do with the indexes.
这两个表描述如下：
The 2 tables are described as follows:
MatterConflicts：
字段：MatterConflicts varchar（16），Matters varchar（16），HitMatters
varchar（16），IsInclude varchar（1）
索引：MatterConflicts
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
MatterConflictHits：
字段：MatterConflictHits varchar（16），MatterConflicts varchar（16），
ColumnLabel varchar（40），Hit varchar（100）
索引：MatterConflictHits
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
现在MatterConflicts行数接近500K并且
MatterConflictHits接近100万行。每个表上只有一个
索引，每个索引都是表的主键。 MatterConflicts表中的事项
字段与用户
直接访问的表连接在一起。
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table''s primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
问题是，添加或修改这些表的现有
索引是否有益于包括主键和外键，
以及另外一个字段？
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
看起来只有
包含主键的索引似乎不是非常有益。例如，我正在考虑为MatterConflicts创建一个
索引，其中包括以下字段：MatterConflicts，
Matters和HitMatters。
Doesn''t seem to be to be very beneficial to have an index that only
includes the primary key. So for example I''m thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
想法？建议？谢谢...
Thoughts? Suggestions? Thanks...
这是一个报告和分析系统还是交易系统？
Is this a reporting and analytical system or a transactional system?
交易
Transactional
" Zamdrist" < za ****** @ gmail.com写信息
新闻：11 ********************** @ n59g2000hsh .googlegr oups.com ...
"Zamdrist" <za******@gmail.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.com...
>我的任务是在SQL Server 7.0上维护一个大型数据库。
性能是一个问题并不奇怪，我已经针对两个相当
的大表来看看我能用索引做些什么。
这两个表被描述为如下：
MatterConflicts：
字段：MatterConflicts varchar（16），Matters varchar（16），HitMatters
varchar（16 ），IsInclude varchar（1）
索引：MatterConflicts
MatterConflictHits：
字段：MatterConflictHits varchar（16），MatterConflicts varchar （16），
ColumnLabel varchar（40），命中varchar（100）
索引：MatterConflictHits
现在MatterConflicts行数接近500K并且
MatterConflictHits接近100万行。
>I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I''ve targeted two rather
large tables to see what I can do with the indexes.
The 2 tables are described as follows:
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows.
你知道吗，这些都不是很大的桌子。
但是，我肯定会同意你可能想要一些索引。
但是，你问的问题有点过于通用。您可能需要
来查看您正在进行的查询并针对这些查询进行优化。
通常您希望找到运行时间最长的查询，
但是那些被称为最多的人。如果你有一个查询，每天10次，每次运行10分钟，优惠10％，你每天可以节省10分钟。
如果你有一个查询每天10,000次，一分钟并优化它，那么
10％，你将节省1000分钟。
Just so you know, these aren''t very large tables.
However, I''d definitely agree you probably want some indexes.
However, the question you''re asking is a bit too generic. You probably need
to look at what queries you''re doing and optimize for those specifically.
And generally you want to find not necessarily the longest running queries,
but the ones called the most. If you have one query called 10 times a day
that runs for 10 minutes and optimize it 10%, you''ll save 10 minutes a day.
If you have one query called 10,000 times a day for a minute and optimize it
10%, you''ll save 1000 minutes.
>每张表上只有一个
索引，每个索引都是表格的主键。 MatterConflicts表格中的事项
字段与用户
直接访问的表格连接起来。
问题是，它会是有益于添加或修改这些表的现有
索引，以包括主键和外键，
以及另外一个字段？
看起来只有
包含主键的索引似乎不是非常有益。例如，我正在考虑为MatterConflicts创建一个
索引，其中包括以下字段：MatterConflicts，
Matters和HitMatters。
想法？建议？谢谢......
>There are only one
index on each table, each for the table''s primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
Doesn''t seem to be to be very beneficial to have an index that only
includes the primary key. So for example I''m thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
Thoughts? Suggestions? Thanks...
-
Greg Moore
SQL Server DBA Consulting
电子邮件：sql（at）greenms.com http://www.greenms.com
这篇关于SQL Server 7.0上的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
