Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
John Sourcer
Yak Posting Veteran
91 Posts |
 Posted - 2006-09-19 : 07:18:57
|
| I have searched for a good answer to this but have no joy. I have a SQL2005 web-based database which stores 100's of clients data in a relational manner across > 20 tables for editing, etc. I receive an update to a set clients data daily. Naturally, I want to update existing records, add new ones and flag deleted records as deleted. I am being told by a senior developer and manager that this is slow and inefficient and that I must delete all the data pertaining to this client across the tables and re-insert it, bearing in mind that my indexes, et al will need to rebuilt and relations to external tables will be poked. They are under the impression that this will improve database performance etc.They insist this should happen whether one record changes or all do, out of a > 1000.Any thoughts? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-19 : 07:27:19
|
| It very much depends on primary keys.You can't delete a record that has a foreign key attached, to insert later. Your database will scream.Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-19 : 08:36:22
|
| "They insist this should happen whether one record changes or all do, out of a > 1000"What a load of bollocks!Sure, if 100% of the data changes every time that probably makes sense.But I would have thought it was much more likely that a very small percentage changes.So ... you have a database that has been reindexed, defragged, the cache is full of useful statistics, and they want you to throw all that away to get the new, almost-identical, data in?And this is going to be faster than just importing the few changes selectively?Unless most of the the data has changed, or there are a pitiful number of rows of data, that isn't going to the the case IME.However, for a bumbling amateur it would be pretty surefire - for example, of you do an update where there is a difference in one of the columns what about if the difference is only Upper/Lower case? Most SQL databases are case insensitive, so that's likely to cause NO update, and you getting bollocked by your manager!I'm struggling to believe that your don't have some FKeys on the database, if you don;t have any then its a pretty poor implementation, or there is a good reason for not having them, and they are going to be a pain in a "delete all data and reimport everything" scenario.That said, a strategy based on truncating tables and then BCP'ing in the data, particularly if the import data files are presorted in clustered-index order, would be pretty swift.Kristen |
|
|
|
John Sourcer
Yak Posting Veteran
91 Posts |
Posted - 2006-09-19 : 09:29:42
|
| Kristen, thanks for the reply!I am quite angry about the intervention. On refusing they suggested getting a 2nd opinion! Of course there are foreign key constraints and the related data spread across multiple tables. Worse, is that this is a semi-financial system with critical data and multiple clients spread across multiple companies with sensitive data locked to companies and even users. I have concurrency issues all over to deal with and would like to be able to audit and track happenings over the entire life-cycle! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-19 : 09:32:52
|
| Can you fire the senior developer and manager?Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-19 : 09:43:38
|
"Of course there are foreign key constraints ..."Excellent ... so its a proper system then, in which case a slash-and-burn policy makes even less sense. There's some "UpSert" code samples I've posted somewhere around here which I can find for you if you get to go down that route."... they suggested getting a 2nd opinion! "Give them the URL then. Create a login for them if they won't have time to make one themselves, then they can ask some questions if they have additional views, or Q&A clarification etc."Can you fire the senior developer and manager?"Not allowed in my organisation - nor is "delegating upwards"  Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-26 : 13:53:27
|
| Related discussion: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72567 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-26 : 16:39:17
|
quote:
Originally posted by John Sourcer...I am being told by a senior developer and manager that this is slow and inefficient and that I must delete all the data pertaining to this client across the tables and re-insert it, bearing in mind that my indexes, et al will need to rebuilt and relations to external tables will be poked. They are under the impression that this will improve database performance etc.They insist this should happen whether one record changes or all do, out of a > 1000...
Words of wisdom to live by:"There is always an easy solution to every human problem — neat, plausible and wrong."H. L. Mencken, "The Divine Afflatus", New York Evening Mail, November 16, 1917CODO ERGO SUM |
|
|
|
|
|
|
|
|
Delete/Insert vs Update