Posted by DH on 04/16/07 14:16

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

What are you using for hardware? Something from the year 1999? Do you have
at least 512MB of RAM in it? With 512MB of RAM, unless there's a lot more
tolerably large tables involved, your entire database should fit into main
memory, which is a good place to have it. If you don't already have enough
memory on the box to comfortably fit SQL Server, the OS and any other
ancillary processes into main memory, I'd recommend you buy some. Buy a
gigabyte. In fact, if it's a decent quality server, buy a gigabyte in 4 X
256MB cards, which will usually allow maximum memory interleaving on good
hardware. Memory, nowadays, costs very little - compared to your time.

In terms of contemporary WIntel servers, your database really isn't all that
large. Guessing at like 75% use of the varchar() fields in volved, you've
got something less than 200MB in those two tables. Can you sneak a new
server in under the database? Would the app run against a SQL Server 2000
back end?

I've got a two or three year old, 4 X 700mhz CPU server here, with 16GB of
memory, and if I run a select count(*) from <table> where <check something
that's not indexed> on a 9 million row, 5GB table, it takes 27 seconds the
first time I execute that command and the second time I run that it takes 4
seconds (the second time, the table is in memory). You don't need that much
server to get similar performance.



--
Posted via a free Usenet account from http://www.teranews.com

[Back to original message]