SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Tracking the Inserts,Updates to Table
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 6

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  09:17:44  Show Profile  Reply with Quote
yes whitefang,

i just modified ur thoughts, by going for single table single row concept.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/21/2009 :  09:18:11  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:20:48  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/21/2009 :  09:26:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:40:33  Show Profile  Reply with Quote
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
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:41:38  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/21/2009 :  09:44:29  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/21/2009 :  09:46:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:49:29  Show Profile  Reply with Quote
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.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:54:04  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/21/2009 :  09:56:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Have you ever measured the overhead of invoking .Net into this?



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:59:31  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/21/2009 :  11:25:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Ok. I guess my client's "non-professional" application which uses native BULK INSERT with 50 million records (and a proper BATCH_SIZE) which takes about 12 minutes to import is nothing to consider for you? Especially since the CPU never raises over 30%.

Dmitry is a application developer friend of yours?
http://www.sqlmag.com/Articles/ArticleID/98568/pg/2/2.html

And also bear in mind the table is queried against during import
http://sqlcat.com/technicalnotes/archive/2009/04/06/bulk-loading-data-into-a-table-with-concurrent-queries.aspx


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 04/21/2009 11:31:09
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  11:32:25  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/21/2009 :  11:37:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  11:41:02  Show Profile  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 04/21/2009 :  11:52:24  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/21/2009 :  11:53:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  11:53:43  Show Profile  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 04/21/2009 :  11:57:14  Show Profile  Reply with Quote
Obviously just trolling then, well done.
Go to Top of Page
Page: of 6 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000