|
|
Posted by Greg D. Moore \(Strider\) on 03/23/07 18:38
"Zamdrist" <zamdrist@gmail.com> wrote in message
news:1174669705.000774.151130@n59g2000hsh.googlegroups.com...
>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.
>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
Navigation:
[Reply to this message]
|