Hi,
I use write medhod UPSERT with upsert mode Update and Insert,but performance is very bad.When i use Insert and Update mode,performance is very very good but data is doubled if the data is already loaded before.
How can i solve this?
Getting better upsert performance
Moderators: chulett, rschirm, roy
Why is the data doubling? That should not happen. Try trimming the keys and then sending it in. What about at the database end, are the keys a surrogate number or a character key?
Your seeing an increase in performance for 'insert then update' because there are more inserts then updates. The opposite will slow it down for the same reason. The best approach, split the inserts and updates.
Your seeing an increase in performance for 'insert then update' because there are more inserts then updates. The opposite will slow it down for the same reason. The best approach, split the inserts and updates.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Re: Getting better upsert performance
You need unique indexes of some sort, PK or otherwise, to make anything like this work. It only does the second action if the first one fails after all.dsisbank wrote:I use write medhod UPSERT with upsert mode Update and Insert,but performance is very bad.When i use Insert and Update mode,performance is very very good but data is doubled if the data is already loaded before.
If Update then Insert is slow, it's because it is doing full table scans for each record. If Insert then Update is fast but 'doubles' everything, that's because it is just inserting, which is allowed because you've got nothing to enforce uniqueness. I'll bet you if you run it again, it 'triples'... etc.
Add a unique index over the 'key' fields then pick the methodology that will get you the most 'hits' on the first try. Or as noted, split your actions.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
INSERTing is usually very fast, UPDATEs are notoriously slow. The best methods for loading are to partition align your data with the database, avoid global indexes on table structures, pre-determine inserts from updates, pure insert load your inserts, insert your updates into a runtime table and use parallel DML to mass update your data within the database environment.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle