|
I would implement a tmp table with a twist: diff
I would create a table, lets call it "ImportData" with 3 columns:
ID: i assume there is a field on the CSV that is unique
Data: the complete CVS line
Status: a importa status depending on the operation
I like char instead of int with "magical numbers" for these kinds os status
A single char will not hurt performance
Then:
1: Set the status to '?' before importing
2: Import to this table row by row
3: Have a SQL trigger that compares the value of column 'Data'
If it changed, set Status to 'U' (Updated) if did not change set status to 'N' (NOP, No operation)
4: At the end, set all rows with '?' to 'D' (Deleted), these are the rows not present on the CSV
5: Process all rows with status different from 'N' (the changed ones)
If 'U', insert or update on main table, if 'D' delete from main table
This will give you a differential update with only the changed rows being updated in your Main data table.
Will also give you a 'snapshot' of all data received in the last CSV file. Very useful for debug
I have several imports done this way, usually imports are very fast since (in my case) most CSV rows do not change.
If the import data is XML, o JSON you can do the same by saving the XML node or JSON node on the 'Data' column.
|
|
|
|
|
This is a huge area with all sorts of possible ways of solving it.
I currently work on a rather huge codebase, part of which performs automated imports and exports in a batching system.
That would probably be overkill for what you are wanting.
There isn't a one solution for everything in this sort of case - however a staging table is always a good place to start - import the data into a staging table then you can decide on a strategy from there.
The strategy might involve selectively picking records that are then fed into your main table's or even allowing the user to choose the data.
The advantage of a staging table is that you can isolate any issues within the staging table as well as be able to query it and analyse data from there without affecting your main tables.
As for timings - for something like an import there should really be no guarantees of how many seconds it takes because there are so many variables out of your control(connection speed, number of records, record sizes etc.)
What you can do is provide some sort of progress bar to let the user know what stage things are at(I actually deliberately slow the progress bar in some applications so that users get a sense of something happening rather than something flashing on and off the screen).
βThat which can be asserted without evidence, can be dismissed without evidence.β
β Christopher Hitchens
|
|
|
|
|
This falls under the 'checking to make sure I'm not leaving my drink on the roof of my car before driving off' category of suggestions.
You may want to make sure the database connection is not closing and reconnecting between each record. This is something I've run into back in my old Access days. 
|
|
|
|
|
I had a similar issue where I needed to import 10's of thousands of records every day, most of them duplicates. We only inserted if the record didn't already exist. It was a serious performance bottleneck.
I generated hashes for all of the existing rows and added an index for the new hash column. Now I calculate the hash of the values in each new row to be inserted and compare the hash to existing rows. I skip the insert if an existing identical row already exists. Using the index solved the insert performance problem for me.
I don't recommend using MD5 or similar simple hashes because it's too easy to create duplicate hashes.
|
|
|
|
|
- Figure out the bottle neck.
Since it does not happen in Dev, but does in production, I suspect it is the duplicate checking. If you size dev the same as production will it reproduce in dev?
Also, do you have more fault tolerance/active nodes in production? If you are doing a few thousand small transactions in a high availability setup, it will take longer.
Do you perform all of your duplicate checks in a single query? Or one import row at a time?
If you setup good, unique indexes, then you can skip the duplicate checking and let the DB do it for you. This would point you toward a commit per import row.
We had an import feature that ended up with an unnecessary exponential complexity. It was fine during testing but started getting really slow at only 150 rows.
|
|
|
|
|
#Worldle #267 1/6 (100%)
π©π©π©π©π©π
https://worldle.teuteuf.fr
easy one
"A little time, a little trouble, your better day"
Badfinger
|
|
|
|
|
Wordle 484 6/6
π¨β¬β¬π¨π©
β¬π¨π¨β¬π©
π©β¬π©β¬π©
π©β¬π©β¬π©
π©β¬π©π¨π©
π©π©π©π©π©
|
|
|
|
|
Wordle 484 2/6
🟨🟨β¬🟩🟩
🟩🟩🟩🟩🟩
My first 2!
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Wordle 484 3/6
β¬π¨β¬β¬β¬
β¬π¨π©β¬π©
π©π©π©π©π©
Not my first 3 but still proud
"A little time, a little trouble, your better day"
Badfinger
|
|
|
|
|
Wordle 484 4/6
π©β¬π©β¬π©
π©β¬π©β¬π©
π©π©π©β¬π©
π©π©π©π©π©
No comment! 
|
|
|
|
|
Wordle 484 5/6
π¨π¨β¬π¨β¬
π¨π¨π©β¬β¬
β¬β¬π©π©π©
β¬β¬π©π©π©
π©π©π©π©π©
|
|
|
|
|
Wordle 484 5/6
β¬π¨β¬β¬β¬
β¬β¬β¬π¨π©
π©β¬π©β¬π©
π©π©π©β¬π©
π©π©π©π©π©
The order of my last two guesses was a coinflip
"If we don't change direction, we'll end up where we're going"
|
|
|
|
|
Wordle 484 4/6
π¨β¬β¬π¨π©
π©π©π©β¬π©
π©π©π©β¬π©
π©π©π©π©π©
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Wordle 484 4/6
β¬β¬π©β¬π©
π©π©π©β¬π©
π©π©π©β¬π©
π©π©π©π©π©
|
|
|
|
|
Wordle 484 5/6
β¬β¬β¬β¬π¨
π¨β¬β¬β¬π©
π©π¨β¬β¬π©
π©β¬π©π¨π©
π©π©π©π©π©
|
|
|
|
|
Wordle 484 6/6
β¬β¬π©β¬π©
π©β¬π©β¬π©
π©β¬π©β¬π©
π©β¬π©β¬π©
π©β¬π©β¬π©
π©π©π©π©π©
Phew
Life should not be a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming βWow! What a Ride!" - Hunter S Thompson - RIP
|
|
|
|
|
|
But not so for iron, which is ironic.
|
|
|
|
|
|
I thought the adjective for iron was "feral".
|
|
|
|
|
So should the adjective for copper be capric?
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows.
-- 6079 Smith W.
|
|
|
|
|
I think this thread is very, very thin.
"A little time, a little trouble, your better day"
Badfinger
|
|
|
|
|
Ok! Just very thin.
"A little time, a little trouble, your better day"
Badfinger
|
|
|
|
|
|
Now do AI code generation. 
To err is human. Fortune favors the monsters.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
