Indexes on SQL Server 7.0

#1

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...

#2

On Mar 23, 12:08 pm, "Zamdrist" <zamdr... (AT) gmail (DOT) com> wrote:

Quote:

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

On Mar 23, 12:20 pm, "Brad" <Brad.Marsh... (AT) Teksouth (DOT) com> wrote:

Quote:

On Mar 23, 12:08 pm, "Zamdrist" <zamdr... (AT) gmail (DOT) com> wrote:

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?

Transactional

#4

"Zamdrist" <zamdrist (AT) gmail (DOT) com> wrote

Quote:

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.

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.

Quote:

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
Email: sql (at) greenms.com http://www.greenms.com

#5

On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:

Quote:

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.

A million records isn't large? Ok.

An application I didn't write and have NO support for is accessing the
data, reading & writing to these tables. There *are* indexes but only
one each and only on the primary key field.

I have no access to the queries as the application is reading from the
tables, probably using in-line string & code queries. There are no
views or procedures used by the application (well very few and not in
this instance). For all I know it could be using "Select * From
TableName...".

There are only 4 fields in each table, so I suppose the query(ies)
could only so complex as four fields would allow for. I know what one
of the fields ties back into a more widely used table that does have
more indexes, ones that appear to be useful.

Thanks

#6

See inline

Zamdrist wrote:

Quote:

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.

Don't expect SQL Server 2000 or 2005 to be any faster if you keep the
current table and index structures... The fact you are using SQL Server
7.0 is no reason to have poor performance.

Quote:

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.

Those queries would most likely benefit from an index on this column.
But it depends on the selectivity of the column. If it is highly
selective (many different values), then an index is probably very
useful.

Quote:

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?

It would surely be worth a try. Just keep in mind that there is a cost
associated with creating an additional index, because it will require
disk space, memory (for caching) and can affect concurrency because of
added blocking. Blocking could really hurt an OLTP system.

HTH,
Gert-Jan

Quote:

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...

#7

"Zamdrist" <zamdrist (AT) gmail (DOT) com> wrote

Quote:

On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
mooregr_deletet... (AT) greenms (DOT) com> wrote:

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.

A million records isn't large? Ok.

Nah, rather trivial these days. ;-)

Quote:

An application I didn't write and have NO support for is accessing the
data, reading & writing to these tables. There *are* indexes but only
one each and only on the primary key field.

If you have no access to these tables, you can't put indexes on it. So
perhaps I misunderstand.

Quote:

I have no access to the queries as the application is reading from the
tables, probably using in-line string & code queries. There are no
views or procedures used by the application (well very few and not in
this instance). For all I know it could be using "Select * From
TableName...".

Ack.

In any case, you probably CAN get this information via profiler and looking
at the queries as they come through the machine.

Also, there's some tools (only source I know of is via the SQL Server
Magazine website so they're copyrighted I believe) to follow wait
statistics, which can be VERY powerful to find out where your application is
doing a lot of querying.

You can also try (though generally I don't find it useful) the Index Wizard
in EM.

Quote:

There are only 4 fields in each table, so I suppose the query(ies)
could only so complex as four fields would allow for.

And any joins.

Quote:

I know what one
of the fields ties back into a more widely used table that does have
more indexes, ones that appear to be useful.

Thanks

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

#8

On Mar 23, 1:59 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:

Quote:

If you have no access to these tables, you can't put indexes on it. So
perhaps I misunderstand.

No I have access to all the objects via Enterprise Manager, I just
don't have access to the application code to see what it is doing,
there are however only a handful of queries/procedures, and over 70+
tables, so I fear the worst.

Quote:

In any case, you probably CAN get this information via profiler and looking
at the queries as they come through the machine.

Also, there's some tools (only source I know of is via the SQL Server
Magazine website so they're copyrighted I believe) to follow wait
statistics, which can be VERY powerful to find out where your application is
doing a lot of querying.

You can also try (though generally I don't find it useful) the Index Wizard
in EM.

Thanks Greg.

#9

"Greg D. Moore (Strider)" <mooregr_deleteth1s (AT) greenms (DOT) com> wrote

Quote:

"Zamdrist" <zamdrist (AT) gmail (DOT) com> wrote in message
news:1174675879.584481.208100 (AT) l75g2000hse (DOT) googlegroups.com...
On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
mooregr_deletet... (AT) greenms (DOT) com> wrote:

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.

A million records isn't large? Ok.

Nah, rather trivial these days. ;-)

Does "trivial" mean easy or unimportant?

#10

"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote

Quote:

A million records isn't large? Ok.

Nah, rather trivial these days. ;-)

Does "trivial" mean easy or unimportant?

No, in this case it means rather small which impacts how you approach
maintainence issues. And to some extent how you solve problems.

For example, for some databases, it may be "simpler" to simply through more
memory at the problem. For a database 10x the size, more memory might not
even make a dent.

Quote:

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com