| Author |
 Topic  |
|
dineshrajan_it
Posting Yak Master
217 Posts |
 Posted - 04/21/2009 : 09:17:44
|
yes whitefang,
i just modified ur thoughts, by going for single table single row concept. |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/21/2009 : 09:18:11
|
quote:
Originally posted by dineshrajan_it
Thanks spirit,
that's the spirit man. i will look into documented. i think ur solution can solve my problem.
Keeping a separate db for audit is indeed a good concept.
thanks man again.
i know 
also look into the 2nd part of the article where it shows how you can do this for N servers.
___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 09:20:48
|
quote:
Originally posted by Peso
Parses incoming SQL? How is that relevant to BULK INSERT?
E 12°55'05.63"
N 56°04'39.26"
No, the threaded DB utility combines all incoming SQL statements (always inserts) and then does a bulk insert. It parses the sql statements 1 by 1 to get the IDs needed for backups. In case of sudden failure, it has written the inserts into a physical backup. Also, it can check to see if the database engine is under load to do the inserts. |
Edited by - whitefang on 04/21/2009 09:21:22 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/21/2009 : 09:26:25
|
What SQL statements?
I am talking about the daily 50+ million record ATM transaction file, that needs to be BULK INSERTed into database.
Or the 250k+ record file containing measure values from the production line, which needs to be inserted hourly.
Or the financial application which need to BULK INSERT about 8k-10k files daily ranging from 800 bytes to 34kb.
E 12°55'05.63"
N 56°04'39.26" |
Edited by - SwePeso on 04/21/2009 09:30:19 |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 09:40:33
|
quote:
Originally posted by Peso
What SQL statements?
I am talking about the daily 50+ million record ATM transaction file, that needs to be BULK INSERTed into database.
Or the 250k+ record file containing measure values from the production line, which needs to be inserted hourly.
Or the financial application which need to BULK INSERT about 8k-10k files daily ranging from 800 bytes to 34kb.
E 12°55'05.63"
N 56°04'39.26"
So?? The DB Utility will handle this. 250K inserts takes about 5 mins tops on a medium server. Did you forget that the DB Utility is threaded and that it does performance analysis of the DB server?
|
Edited by - whitefang on 04/21/2009 09:40:54 |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 09:41:38
|
quote:
Originally posted by dineshrajan_it
yes whitefang,
i just modified ur thoughts, by going for single table single row concept.
You don't want to go with a single row concept, that makes no sense and it makes restoring and querying for data next to impossible. |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/21/2009 : 09:44:29
|
whitefang, you haven't answered my question: what do you use for logging? log4net?
___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/21/2009 : 09:46:18
|
Let me get this straight.
1. You will open the file into a "db utility".
2. Then read all files into memory
3. Send all data to database.
My question is now, how do you deal with the three different file formats?
ATM file is fixed size, production line file is tab separated and financial application get both fixed, separated and XML files.
These are real examples from one of my clients.
Their "enterprise" (if whitefang allows) application have 4000 concurrent users (of total 70000 users) at any given time, 300 production units in 80 countries all over the world and about 12000 stores.
You create three different "read objects"?
And you create three different write objects?
And what about network traffic, sending 50+ million records one by one?
E 12°55'05.63"
N 56°04'39.26" |
Edited by - SwePeso on 04/21/2009 09:55:06 |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 09:49:29
|
quote:
Originally posted by spirit1
whitefang, you haven't answered my question: what do you use for logging? log4net?
___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
For application side, yes but for the backend, we use custom logging. Each of our business entities has a Log() method that is invoked in the Save() method. Internally, it calls a centralized method that logs to a single table. The entity is serialized to XML along with a status message. We can restore it easily and provide important analysis. |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 09:54:04
|
quote:
Originally posted by Peso
Let me get this straight.
1. You will open the file into a "db utility".
2. Then read all files into memory
3. Send all data to database.
My question is now, how do you deal with the three different file formats?
ATN file is fixed size, production line file is tab separated and financial application get fixed, separated and XML files.
You create three different "read objects"?
And you create three different write objects?
And what about network traffic, sending 50+ million records one by one?
E 12°55'05.63"
N 56°04'39.26"
We would use 3 separate load methods, 1 single write method in a object. This would be part of the application design. Since our app servers and db is in the same datacenter, we would use the SqlBulkCopy feature in .NET on top of the DB utility. No problem. |
Edited by - whitefang on 04/21/2009 09:55:24 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/21/2009 : 09:56:14
|
Have you ever measured the overhead of invoking .Net into this?
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 09:59:31
|
| During testing phase for one of our larger applications, I randomly initiated entities and invoked the Save method 20 million times. I think it took like 20 mins to complete (this was along time ago, so it probably took a little bit more). The app server was HIGHLY responsive, the DB server had 50% load. Remember, our application architecture is "professional enterprise". |
Edited by - whitefang on 04/21/2009 10:01:29 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 11:32:25
|
| So you are doing 50 million records inserts with FULL logging AND recovery model in 12 minutes? Your database must be running on the IBM Roadrunner supercomputer. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/21/2009 : 11:37:18
|
Why would they use full logging? They use a staging area to cleans and rinse imported data.
No need for logging. Read about format file.
When staging area has completed, data is later seamlessly inserted/updated into real production tables.
Where do you cleans and rinse data? Where do you store records not fulfilling some of the business rules?
Where do you do your UPSERTS?
E 12°55'05.63"
N 56°04'39.26" |
Edited by - SwePeso on 04/21/2009 11:41:15 |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 11:41:02
|
Oh, I had full logging and recovery model enabled on mine AND I was using the regular data access utility CRUD methods (not the bulk designed methods). Now, if I put my recovery model to simple and disable logging, and use the bulk methods of the DB Utility, I could too easily do 50 million records in 12 mins.
I also put it in a stage DB where data analysis is run to verify the integrity and import reports are generated. Then it gets moved to a production DB. Same process as you. This is all part of the application design and req process. You're not going to decide one day to randomly import 50 million records....no, you're going to plan for that in app reqs.
So it's pretty much even. |
Edited by - whitefang on 04/21/2009 11:43:46 |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 04/21/2009 : 11:52:24
|
Whitefang - I can't work out whether its that you can't concede that you may be wrong sometimes or whether you're just trolling for the hell of it.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/21/2009 : 11:53:15
|
And your data analysis is still being done with your application object?
And the proper UPSERT is still being done with your application object?
Oh man, those network roundtrips.
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 04/21/2009 : 11:53:43
|
| I'm not wrong. I have a different way of doings things, the better intelligent way. You just refuse to accept it because you're delusional that everything must be done on the DB side. |
Edited by - whitefang on 04/21/2009 11:53:57 |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 04/21/2009 : 11:57:14
|
| Obviously just trolling then, well done. |
|
|
|
Topic |
|
Tracking the Inserts,Updates to Table
