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]

jlibster (jl******@jalsolutions.com) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Guinness Mann]

In article <99**************************@posting.google.com >,
jl******@jalsolutions.com says...

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.

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