Database Performance

[jlibster]

Hi, I wonder if someone can answer a quesiton for me: I'm modifying a
database with the purpose of adding the new feature of address change
history. My model would consist of a table for keeping client
name/logon (for a public site) info in one table, and address info in
another table because the login info would likely be more frequently
accessed/changed than address updates. Now a group that does data
entry internally through a web interface always need to see the
address.

For the first stage I don't want to change the old table, just have a
new one for now. But moving forward, I thought it would be neat to
have all address update records in one table and have a Profile type
value to distinguish whether data entry or a public website user
created the update record.

However, a thought occured to me: If one table is responsible for
showing current address as well as adding records whenever there is an
address change, would it hurt performance? Would I get better
performance, splitting the record types into two tables, or does it
matter since the table I'm thinking of creating would have no
deletions: Only insersions and modifying an expiry date field so we
know which record to use. I'm not a specialist on database performance
so if any of you database gurus out that can advise me on that that
would be GREAT. Thanks a million guys.

Jonah A. Libster

[Erland Sommarskog]

Re: Database Performance

jlibster (jlibster@jalso lutions.com) writes:[color=blue]
> Hi, I wonder if someone can answer a quesiton for me: I'm modifying a
> database with the purpose of adding the new feature of address change
> history. My model would consist of a table for keeping client
> name/logon (for a public site) info in one table, and address info in
> another table because the login info would likely be more frequently
> accessed/changed than address updates. Now a group that does data
> entry internally through a web interface always need to see the
> address.
>
> For the first stage I don't want to change the old table, just have a
> new one for now. But moving forward, I thought it would be neat to
> have all address update records in one table and have a Profile type
> value to distinguish whether data entry or a public website user
> created the update record.
>
> However, a thought occured to me: If one table is responsible for
> showing current address as well as adding records whenever there is an
> address change, would it hurt performance? Would I get better
> performance, splitting the record types into two tables, or does it
> matter since the table I'm thinking of creating would have no
> deletions: Only insersions and modifying an expiry date field so we
> know which record to use. I'm not a specialist on database performance
> so if any of you database gurus out that can advise me on that that
> would be GREAT. Thanks a million guys.[/color]

With only a narrative and no table descriptions it is a little difficult
to follow.

However, while it is always good to keep performance in mind, first
focus on getting the functionality right. If one way gives you far
less programming than the other, go that road. If performance proves
to be bad, you can improve later. If you take a more complex method
directly, you run a greater risk for bugs.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Guinness Mann]

Re: Database Performance

In article <993e03ef.03100 61301.25705318@ posting.google. com>,
jlibster@jalsol utions.com says...[color=blue]
> My model would consist of a table for keeping client
> name/logon (for a public site) info in one table,
> and address info in another table because the login
> info would likely be more frequently accessed/changed
> than address updates.[/color]

That seems like an unnatural division to me. How does having two tables
(unless these are going to be *very* large tables) equate to better
performance than one table?

-- Rick