out of memory exception
-
Colin Angus Mackay wrote:
Your strategy is to dump the entire contents of the files in to memory then write them to the database. Why?
Because I am Passing That List Datables to a function whose purpose is to write data to table using sqlbulkcopy
Colin Angus Mackay wrote:
Why not just read the files one-by-one and write them to the database?
So you want me to change strategy. 1. for( int i=0; i<totalnumberoffilestoscan;> { Step1: // Create A New DataTable Step2: // Call a Function That accepts the ith File and newly created DataTable as parameter and do the work of adding required number of Columns with their DataType and size Step3: // Call a function that will read the text file row by row and add them to datatable Step 4: // Pass this DataTable to another function whose purpose is to use SQlBulkCopy to write Records from the datatable. } Is This Strategy Feasible, will not be again give me out of memory Exception. in each loop i am creating a new Datatable ??????:confused::confused::confused:
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
Mogaambo wrote:
Is This Strategy Feasible, will not be again give me out of memory Exception. in each loop i am creating a new Datatable ??????
I don't know if it will work for you because I don't know enough about your data. I do know that it will use a lot less memory than you are using now. If an individual file is large enough then you will still get an out of memory exception. It has been suggested to you else where that you read each file in chunks and process the chunks. That seems the most sensible strategy to me.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
-
There's this really neat thing in databases called "transactions". Start a transaction, perform your operations, if all goes well you commit the transaction, if not then you rollback the transaction.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
SqlTransaction will only be used for current 10000 records not for next 10000 records.. Like this step 1: //DataTable dt= ReadTenThousandLines(); Step 2: // Write_to_database (dt); In Write_to_database() function, I will use sqlBulkCopy nad their i Use SqlTransaction Class, but suppose if i again passing 10000 Record containing datatable and if it fails then ??????????????
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
-
SqlTransaction will only be used for current 10000 records not for next 10000 records.. Like this step 1: //DataTable dt= ReadTenThousandLines(); Step 2: // Write_to_database (dt); In Write_to_database() function, I will use sqlBulkCopy nad their i Use SqlTransaction Class, but suppose if i again passing 10000 Record containing datatable and if it fails then ??????????????
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
Mogaambo wrote:
SqlTransaction will only be used for current 10000 records not for next 10000 records..
That's a bit pointless.
Mogaambo wrote:
In Write_to_database() function, I will use sqlBulkCopy nad their i Use SqlTransaction Class, but suppose if i again passing 10000 Record containing datatable and if it fails then ??????????????
My advice has not changed. You use a transaction. You start it, you do all your bulk copying then you commit the transaction. You do NOT create one transaction for each batch of data. You create ONE transaction to cover everything.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
-
Mogaambo wrote:
SqlTransaction will only be used for current 10000 records not for next 10000 records..
That's a bit pointless.
Mogaambo wrote:
In Write_to_database() function, I will use sqlBulkCopy nad their i Use SqlTransaction Class, but suppose if i again passing 10000 Record containing datatable and if it fails then ??????????????
My advice has not changed. You use a transaction. You start it, you do all your bulk copying then you commit the transaction. You do NOT create one transaction for each batch of data. You create ONE transaction to cover everything.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
With your suggestion reading from file and writing to database should be done in 1 shot. For eg. WriteToDataBase(string filename) { //Begin SqlTransaction Begin loop to read 10000 records at a time (While all the records are not read) //write in a datatable //End loop //End SqlTransaction } // Some thing like this you want me to do
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
-
With your suggestion reading from file and writing to database should be done in 1 shot. For eg. WriteToDataBase(string filename) { //Begin SqlTransaction Begin loop to read 10000 records at a time (While all the records are not read) //write in a datatable //End loop //End SqlTransaction } // Some thing like this you want me to do
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
You are not paying attention. Or you are not bothering to understand transactions.
Begin Transaction
Start File Loop
Start Data Chunk Loop
Read upto 10,000 records from file
Write those records to the database
End Data Chunk Loop
End File Loop
End TransactionEach iteration around the Data Chunk Loop has one communication with the database (One "shot" per iteration) If you have a million records you have 100 "shots" at writing to the database.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
-
You are not paying attention. Or you are not bothering to understand transactions.
Begin Transaction
Start File Loop
Start Data Chunk Loop
Read upto 10,000 records from file
Write those records to the database
End Data Chunk Loop
End File Loop
End TransactionEach iteration around the Data Chunk Loop has one communication with the database (One "shot" per iteration) If you have a million records you have 100 "shots" at writing to the database.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
Thanks for your quick reply, But,
Colin Angus Mackay wrote:
If you have a million records you have 100 "shots" at writing to the database.
Is this strategy fast enough , because to write 25 files it would take approx. 25 * 100= 2500 shots Means 2500 iteration will be performed for writing 25 files.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
-
Thanks for your quick reply, But,
Colin Angus Mackay wrote:
If you have a million records you have 100 "shots" at writing to the database.
Is this strategy fast enough , because to write 25 files it would take approx. 25 * 100= 2500 shots Means 2500 iteration will be performed for writing 25 files.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
Well, your choice is going slightly slower, use SSIS (as someone else suggested) or running out of memory - You choose! I give up.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
-
Well, your choice is going slightly slower, use SSIS (as someone else suggested) or running out of memory - You choose! I give up.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
Well first of all thanks for your valuable suggestions but as you are MVP and you say
Colin Angus Mackay wrote:
I give up.
But Something for you. Sam Ewing: It's not the hours you put in your work that count, it's work you put in the hours. F. Scott Fitzgerald: You don't write because you want to say something; you write because you've got something to say.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
-
Well first of all thanks for your valuable suggestions but as you are MVP and you say
Colin Angus Mackay wrote:
I give up.
But Something for you. Sam Ewing: It's not the hours you put in your work that count, it's work you put in the hours. F. Scott Fitzgerald: You don't write because you want to say something; you write because you've got something to say.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
He gave up because you're an asshat. Be a programmer for god's sake, and implement a solution.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001 -
He gave up because you're an asshat. Be a programmer for god's sake, and implement a solution.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001John Simmons / outlaw programmer wrote:
asshat
Thank you very very much AssHole
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
-
John Simmons / outlaw programmer wrote:
asshat
Thank you very very much AssHole
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
You're arguing with the guy that gave you a solution. Who here is the asshole?
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001 -
Paul Conrad wrote:
Didn't you already ask this question once?
Yes I did , but check yourself what answers i received, are they fruitful no no no no So this time I asked my question in a different manner. If, you can provide a good path then take thanks in Advance.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
Mogaambo wrote:
Yes I did , but check yourself what answers i received, are they fruitful no no no no
Uhhh, yes they are. And you were told exactly why your approach wouldn't work. You simply ARE running the machine out of memory trying to read and retain all that data at once. You have no choice but to either read and process the data in small chunks, or farm the entire job out to SQL Server to do it for you.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
Well first of all thanks for your valuable suggestions but as you are MVP and you say
Colin Angus Mackay wrote:
I give up.
But Something for you. Sam Ewing: It's not the hours you put in your work that count, it's work you put in the hours. F. Scott Fitzgerald: You don't write because you want to say something; you write because you've got something to say.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
Mogaambo wrote:
Well first of all thanks for your valuable suggestions but as you are MVP and you say Colin Angus Mackay wrote: I give up. But Something for you.
How about this. He's good at what he does because he recognizes that to keep working on a futile solution is a pointless.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
You're arguing with the guy that gave you a solution. Who here is the asshole?
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001Tell me where do i argued, i say thanks to his valuable suggestions and is my questions are pointless , no not they are. if he doesn't say anything to me then y your azz is itching.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
-
Colin Angus Mackay wrote:
Your strategy is to dump the entire contents of the files in to memory then write them to the database. Why?
Because I am Passing That List Datables to a function whose purpose is to write data to table using sqlbulkcopy
Colin Angus Mackay wrote:
Why not just read the files one-by-one and write them to the database?
So you want me to change strategy. 1. for( int i=0; i<totalnumberoffilestoscan;> { Step1: // Create A New DataTable Step2: // Call a Function That accepts the ith File and newly created DataTable as parameter and do the work of adding required number of Columns with their DataType and size Step3: // Call a function that will read the text file row by row and add them to datatable Step 4: // Pass this DataTable to another function whose purpose is to use SQlBulkCopy to write Records from the datatable. } Is This Strategy Feasible, will not be again give me out of memory Exception. in each loop i am creating a new Datatable ??????:confused::confused::confused:
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
25 files x 1 Million rows = 25 Million If you say each row is 1024 bytes. Its totally 25 GB of memory required. As you might already know, when RAM is not sufficent the system writes to harddisk & its slow. Unless you have free space of 40GB (to accomodate calculation issues), you cannot run your application & still it will be slow. So your approach is to break it up into pieces. You got few approaches: 1. Dont use datatables and use memory streams, use SqlCommand - this is more code, i guess 2. If you want to use datatables. You have to run the BulkCopy, for each datatable & call DataTable.Dispose() method (and may be call to Garbage collector, as Dispose will not free memory immediatly) after each use. 3. Use SQL Server, to import the files directly. DTS / SISS supports importing of various file formats, excel, csv, access. It will need a SQL command, which will take the file location & it will import it. You can google up for the sql syntax or use DTS Export / Import wizard & in last step, save the command.
Abi ( Abishek Bellamkonda ) My Blog: http://abibaby.blogspot.com =(:*
-
You are not paying attention. Or you are not bothering to understand transactions.
Begin Transaction
Start File Loop
Start Data Chunk Loop
Read upto 10,000 records from file
Write those records to the database
End Data Chunk Loop
End File Loop
End TransactionEach iteration around the Data Chunk Loop has one communication with the database (One "shot" per iteration) If you have a million records you have 100 "shots" at writing to the database.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
Thanks alot. I have implemented your algorithm and its working fine and more fast then previous one. Millions of bows to you :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D and sorry if you hurt from any of mine statements.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
modified on Tuesday, October 14, 2008 11:41 AM
-
Thanks alot. I have implemented your algorithm and its working fine and more fast then previous one. Millions of bows to you :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D and sorry if you hurt from any of mine statements.
“You will never be a leader unless you first learn to follow and be led.” –Tiorio "Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
modified on Tuesday, October 14, 2008 11:41 AM
Mogaambo wrote:
more fast then previous one
Probably because you are not thrashing the virtual memory's page file.
Recent blog posts: *Method hiding Vs. overriding *Microsoft Surface *SQL Server / Visual Studio install order My Blog
