I'm seeing an issue where we are having a SystemOutOfMemoryException when trying to copy a stringBuilder.ToString() object to a string and then saving it to the database. This is only occurring on Azure though due to our app service running other apps. If
I run it local, it is fine due to me having 64gb of memory.
What would be an alternative to saving the StringBuilder into a SQL file and then saving that in the database instead of a nvarchar(max)?
You should use chunked updates, and not build a complete string. I’d probable use streams but string builder could be used, just push and clear once it’s over a certain size, say 16k.
You should use chunked updates, and not build a complete string. I’d probable use streams but string builder could be used, just push and clear once it’s over a certain size, say 16k.
What item can I store the final string in then? Or do you mean go back to the database each time it gets to 16k and keep appending to it?
Well we are generating a full database SQL script.
There will be 1 delete statement (possibly)
There will be 1 create statement (possibly)
Then there could be X Insert statements. In my case now, 100k or even 500k.
I don't get it. You're building a DML script and saving the script in a VARCHAR(MAX) column or field. Saving the script causes the exception? Or executing the script causes an exception?
Any way are you building a script in a web application??? What is the use case?
Edit: missed it is a script. What will you do next with that script? It could be saved maybe with a generated statement per row rather than as a single huge script? Depending on the purpose you may have better options such as using
https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15 or whatever???
Well we are generating a full database SQL script.
There will be 1 delete statement (possibly)
There will be 1 create statement (possibly)
Then there could be X Insert statements. In my case now, 100k or even 500k.
I don't get it. You're building a DML script and saving the script in a VARCHAR(MAX) column or field. Saving the script causes the exception? Or executing the script causes an exception?
Any way are you building a script in a web application??? What is the use case?
This is occurring after I build the script and call .ToString() when saving it to my string in my model to send it to the database which is varbinary.
This has nothing to do with the database right now.
Humm if I had to start from scratch rather than using a single string for a huge script I would likely split that into multiple rows. The big picture could perhaps help. It feels like you are trying to create your own db replication engine when you could
perhaps take advantage of existing features.
the SO link seems unrelated and it would be liklely best to deal which each problem one after the other or in its own thread. You are using EF to insert 103 000 entities ?
Humm if I had to start from scratch rather than using a single string for a huge script I would likely split that into multiple rows. The big picture could perhaps help. It feels like you are trying to create your own db replication engine when you could
perhaps take advantage of existing features.
the SO link seems unrelated and it would be liklely best to deal which each problem one after the other or in its own thread. You are using EF to insert 103 000 entities ?
So I updated the point of that link.
That is how I'm doing it.
I'm adding the insert statements NOT via EF Core. The error in the link was occurring after all of that finishes.
Here is the code that is causing the stringBuilder out of memory exception:
Well we are generating a full database SQL script.
There will be 1 delete statement (possibly)
There will be 1 create statement (possibly)
Then there could be X Insert statements. In my case now, 100k or even 500k.
I don't get it. You're building a DML script and saving the script in a VARCHAR(MAX) column or field. Saving the script causes the exception? Or executing the script causes an exception?
Any way are you building a script in a web application??? What is the use case?
This is occurring after I build the script and call .ToString() when saving it to my string in my model to send it to the database which is varbinary.
This has nothing to do with the database right now.
Does it make sense to StreamWriter it to a .SQL file on the file system? Read the .SQL file from the file system and convert to a byte array and store the file in the database instead of a string?
Does it make sense to StreamWriter it to a .SQL file on the file system? Read the .SQL file from the file system and convert to a byte array and store the file in the database instead of a string?
Files, strings, and streams are byte arrays. Given your response, I'm guessing the code is performing unnecessary processing. Share the source if you want a community code review.
Does it make sense to StreamWriter it to a .SQL file on the file system? Read the .SQL file from the file system and convert to a byte array and store the file in the database instead of a string?
Files, strings, and streams are byte arrays. Given your response, I'm guessing the code is performing unnecessary processing. Share the source if you want a community code review.
if (string.IsNullOrWhiteSpace(trackingInfo.InsertSqlScript.ToString()) == false)
{
trackingInfo.FileDetail = UpdateImportMetricStatus(trackingInfo.FileDetail, ImportMetricStatus.PopulatingInsertScripts);
foreach (var item in trackingInfo.InsertSqlScript)
{
errorSqlScript = item;
using (var cmd = new SqlCommand(item, sqlConnection, sqlTransaction))
{
cmd.CommandTimeout = 800;
cmd.CommandType = CommandType.Text;
await cmd.ExecuteScalarAsync().ConfigureAwait(false);
}
currentRowLine++;
// append insert statements to the main script...
trackingInfo.SqlScript.AppendLine(item).AppendLine();
}
}
So I'm taking a list of string (one insert statement each time) and adding it to a StringBuilder. Then after everything is done. I'm setting the SB.ToString() call, which is then blowing up because the Azure server does not have enough memory to work with
StringBuilder.
The value in the database is nvarchar(max). So the database can handle it. It is just getting that large of a string over to the database. I need to use something besides StringBuilder.
Now if you end up with a single huge script (ie this is a script that would include 500 000 insert SQL statements ?) you may have other issues later down the road.
Correct me if I'm wrong... you're saving a history of each DML scripts run. Since the DML scripts are run in a loop, it seems reasonable to insert each DML script (text) when the insert is successful. Use a standard foreign key to identify or group the
scripts that go with a processes.
Correct me if I'm wrong... you're saving a history of each DML scripts run. Since the DML scripts are run in a loop, it seems reasonable to insert each DML script (text) when the insert is successful. Use a standard foreign key to identify or group the
scripts that go with a process.
Yes, correct.
So the end-user can select to apply the script to the database OR just generate a .SQL file with all the insert statements.
We do this just in case the end-user doesn't have insert permissions on the SQL server.
So we need to generate EVERY insert statement into a file so we can allow the end-user to download it and send it to the database admin to run under their permissions.
Then, you can read all the text in the file as strings.
You can use the File.ReadAllText method to achieve this requirement, and you can
click this link to learn how to use it.
Best Regards,
YihuiSun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
So the end-user can select to apply the script to the database OR just generate a .SQL file with all the insert statements.
We do this just in case the end-user doesn't have insert permissions on the SQL server.
That's not how the code works. The code executes an insert script then appends the script to a string.
tvb2727
So we need to generate EVERY insert statement into a file so we can allow the end-user to download it and send it to the database admin to run under their permissions.
Again, your code does not meet this requirement it does not create a file.
Keep in mind, each time you post you share another bit of the overall requirement. Given the responses, I suspect design issues. The code shown above is clearly executing an insert. So the code that creates the file was not shared?
It's unclear if you are still trying to solve your StringBuilder issue (or it is solved and you have other issues or you finally choosed to try an alternate design ???). IMO the following should work even on Azure:
CREATE TABLE t(script VARBINARY(max))
DECLARE @p1 VARBINARY(max)
DECLARE @p2 VARBINARY(max)
SET @p1=0x0102
INSERT INTO t VALUES(@p1)
SET @p2=0x0304;UPDATE t SET script.write(@p2,NULL,NULL) -- append to existing data
SET @p2=0x0506;UPDATE t SET script.write(@p2,NULL,NULL) -- append to existing data, of course it would be sent from C#
SELECT * FROM t -- shows 0x010203040506
and so you'll send the script as multiple smaller chunks rather than as a single huge string( even "Read the .SQL file from the file system and convert to a byte array and store the file in the database instead of a string" would send the content to
the db as a single huge string even which might be still too big).
According to "Not this specific issue, but one that I was seeing prior to the memory out exception" you may try also to deal with multiple problems at the same time. It could be best to open a separate thread for each problem to make things easier
to follow and discuss.
Or if the current design just won't work, the big picture could help. For now it sounds like if you are trying to have a kind of service that would turn an Excel file into a kind of database initialization or data importation script ?????
So we need to generate EVERY insert statement into a file so we can allow the end-user to download it and send it to the database admin to run under their permissions.
Again, your code does not meet this requirement it does not create a file.
Keep in mind, each time you post you share another bit of the overall requirement. Given the responses, I suspect design issues. The code shown above is clearly executing an insert. So the code that creates the file was not shared?
The file is not created until after they try to download it. The SQL Script would be injected into the database and then when the user selected the Download Script button, at that point, it generates the .SQL file.
And you are right, it does appear to be a design issue and that is why I'm posting it is to find a valid answer.
It's unclear if you are still trying to solve your StringBuilder issue (or it is solved and you have other issues or you finally choosed to try an alternate design ???). IMO the following should work even on Azure:
CREATE TABLE t(script VARBINARY(max))
DECLARE @p1 VARBINARY(max)
DECLARE @p2 VARBINARY(max)
SET @p1=0x0102
INSERT INTO t VALUES(@p1)
SET @p2=0x0304;UPDATE t SET script.write(@p2,NULL,NULL) -- append to existing data
SET @p2=0x0506;UPDATE t SET script.write(@p2,NULL,NULL) -- append to existing data, of course it would be sent from C#
SELECT * FROM t -- shows 0x010203040506
and so you'll send the script as multiple smaller chunks rather than as a single huge string( even "Read the .SQL file from the file system and convert to a byte array and store the file in the database instead of a string" would send the content to
the DB as a single huge string even which might be still too big).
According to "Not this specific issue, but one that I was seeing prior to the memory out exception" you may try also to deal with multiple problems at the same time. It could be best to open a separate thread for each problem to make things easier
to follow and discuss.
Or if the current design just won't work, the big picture could help. For now, it sounds like if you are trying to have a kind of service that would turn an Excel file into a kind of database initialization or data importation script ?????
Yes, the string build issue will go away and we will go from a nvarchar(max) column in the DB to a varbinary (max) like you suggested. We will then upload it via EF Core
Participant
1038 Points
2816 Posts
StringBuilder Out Of Memory Exception
Oct 21, 2020 01:23 PM|tvb2727|LINK
Hello,
I'm seeing an issue where we are having a SystemOutOfMemoryException when trying to copy a stringBuilder.ToString() object to a string and then saving it to the database. This is only occurring on Azure though due to our app service running other apps. If I run it local, it is fine due to me having 64gb of memory.
What would be an alternative to saving the StringBuilder into a SQL file and then saving that in the database instead of a nvarchar(max)?
Thanks.
All-Star
58194 Points
15661 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 02:36 PM|bruce (sqlwork.com)|LINK
You should use chunked updates, and not build a complete string. I’d probable use streams but string builder could be used, just push and clear once it’s over a certain size, say 16k.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 03:09 PM|tvb2727|LINK
What item can I store the final string in then? Or do you mean go back to the database each time it gets to 16k and keep appending to it?
All-Star
58194 Points
15661 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 03:41 PM|bruce (sqlwork.com)|LINK
why do you need the full string? it appears to be too large for practical use.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 04:06 PM|tvb2727|LINK
Well we are generating a full database SQL script.
There will be 1 delete statement (possibly)
There will be 1 create statement (possibly)
Then there could be X Insert statements. In my case now, 100k or even 500k.
All-Star
53041 Points
23619 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 04:16 PM|mgebhard|LINK
I don't get it. You're building a DML script and saving the script in a VARCHAR(MAX) column or field. Saving the script causes the exception? Or executing the script causes an exception?
Any way are you building a script in a web application??? What is the use case?
All-Star
48530 Points
18073 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 04:24 PM|PatriceSc|LINK
Hi,
This is a [n]varchar(max) column or maybe a varbinary(max)? What is the expected size of this string?
Behind the scene you could perhaps use https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#updating-lobs which should allow to append new chunks efficiently (never tried).
Edit: missed it is a script. What will you do next with that script? It could be saved maybe with a generated statement per row rather than as a single huge script? Depending on the purpose you may have better options such as using https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15 or whatever???
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 04:28 PM|tvb2727|LINK
This is occurring after I build the script and call .ToString() when saving it to my string in my model to send it to the database which is varbinary.
This has nothing to do with the database right now.
Model.GenerateScript = sb.ToString(); errors here
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 04:30 PM|tvb2727|LINK
Check my response to mgebhard.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 04:34 PM|tvb2727|LINK
This shows how I'm doing it.
Not this specific issue, but one that I was seeing prior to the memory out exception:
https://stackoverflow.com/questions/64455373/execution-timeout-expired-after-inserting-into-database-and-then-using-ef-core-t
All-Star
48530 Points
18073 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 05:00 PM|PatriceSc|LINK
Humm if I had to start from scratch rather than using a single string for a huge script I would likely split that into multiple rows. The big picture could perhaps help. It feels like you are trying to create your own db replication engine when you could perhaps take advantage of existing features.
the SO link seems unrelated and it would be liklely best to deal which each problem one after the other or in its own thread. You are using EF to insert 103 000 entities ?
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 05:26 PM|tvb2727|LINK
Removed.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 05:32 PM|tvb2727|LINK
So I updated the point of that link.
That is how I'm doing it.
I'm adding the insert statements NOT via EF Core. The error in the link was occurring after all of that finishes.
Here is the code that is causing the stringBuilder out of memory exception:
try { await UpdateImportGenerateScript( new ImportGenerateScriptUpdateDTO { ImportDetailId = importDetailId, GenerateScript = trackingInfo.SqlScript.ToString() }, ImportService.GetEncryptDecryptKey()).ConfigureAwait(false); } catch (Exception ex) { await LogImporterError(trackingInfo.FileDetail, string.Format(CultureInfo.CurrentCulture, "Error: {0} | FileDetail: {1}", ex.ToString(), trackingInfo.FileDetail.ToJsonText())).ConfigureAwait(false); }(Sorry I was at lunch and couldn't copy the code).
Basically, the customer wants ONE script with the delete, create (if selected) and insert scripts brought in from an Excel file.
So I'm running into the issue where saving the GeneratedScript in my database as:
So the error occurs when I try to set the string property of my model to pass to the API I'm using for EF Core.
So the scripts are not doing by EF, but the update with the status of the script to save it in the database is.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 05:32 PM|tvb2727|LINK
mgebhard
Look at my previous response.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 05:43 PM|tvb2727|LINK
Does it make sense to StreamWriter it to a .SQL file on the file system? Read the .SQL file from the file system and convert to a byte array and store the file in the database instead of a string?
All-Star
53041 Points
23619 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 06:30 PM|mgebhard|LINK
Files, strings, and streams are byte arrays. Given your response, I'm guessing the code is performing unnecessary processing. Share the source if you want a community code review.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 06:57 PM|tvb2727|LINK
if (string.IsNullOrWhiteSpace(trackingInfo.InsertSqlScript.ToString()) == false) { trackingInfo.FileDetail = UpdateImportMetricStatus(trackingInfo.FileDetail, ImportMetricStatus.PopulatingInsertScripts); foreach (var item in trackingInfo.InsertSqlScript) { errorSqlScript = item; using (var cmd = new SqlCommand(item, sqlConnection, sqlTransaction)) { cmd.CommandTimeout = 800; cmd.CommandType = CommandType.Text; await cmd.ExecuteScalarAsync().ConfigureAwait(false); } currentRowLine++; // append insert statements to the main script... trackingInfo.SqlScript.AppendLine(item).AppendLine(); } }try { await UpdateImportGenerateScript( new ImportGenerateScriptUpdateDTO { ImportDetailId = importDetailId, GenerateScript = trackingInfo.SqlScript.ToString() }, ImportService.GetEncryptDecryptKey()).ConfigureAwait(false); } catch (Exception ex) { await LogImporterError(trackingInfo.FileDetail, string.Format(CultureInfo.CurrentCulture, "Error: {0} | FileDetail: {1}", ex.ToString(), trackingInfo.FileDetail.ToJsonText())).ConfigureAwait(false); }Here you go.
So I'm taking a list of string (one insert statement each time) and adding it to a StringBuilder. Then after everything is done. I'm setting the SB.ToString() call, which is then blowing up because the Azure server does not have enough memory to work with StringBuilder.
The value in the database is nvarchar(max). So the database can handle it. It is just getting that large of a string over to the database. I need to use something besides StringBuilder.
All-Star
48530 Points
18073 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 10:58 PM|PatriceSc|LINK
Then I would still likely try first https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/modify-large-value-max-data?view=sql-server-ver15#updating-data-using-update-write which should allow to append data to your SQL column without ever having to have the whole script in memory.
Now if you end up with a single huge script (ie this is a script that would include 500 000 insert SQL statements ?) you may have other issues later down the road.
In this later case some more context could help. Maybe something such as https://docs.microsoft.com/en-us/azure/data-factory/ could help ???
All-Star
53041 Points
23619 Posts
Re: StringBuilder Out Of Memory Exception
Oct 21, 2020 11:56 PM|mgebhard|LINK
Correct me if I'm wrong... you're saving a history of each DML scripts run. Since the DML scripts are run in a loop, it seems reasonable to insert each DML script (text) when the insert is successful. Use a standard foreign key to identify or group the scripts that go with a processes.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 12:18 AM|tvb2727|LINK
Yes, correct.
So the end-user can select to apply the script to the database OR just generate a .SQL file with all the insert statements.
We do this just in case the end-user doesn't have insert permissions on the SQL server.
So we need to generate EVERY insert statement into a file so we can allow the end-user to download it and send it to the database admin to run under their permissions.
Contributor
2750 Points
780 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 07:36 AM|YihuiSun|LINK
Hi tvb2727,
Model.GenerateScript = sb.ToString();
So the error occurs when I try to set the string property of my model
Because you don't have enough memory, the CLR cannot allocate an object of the required size.
According to your needs, you can try the following solution:
Best Regards,
YihuiSun
All-Star
53041 Points
23619 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 11:51 AM|mgebhard|LINK
That's not how the code works. The code executes an insert script then appends the script to a string.
Again, your code does not meet this requirement it does not create a file.
Keep in mind, each time you post you share another bit of the overall requirement. Given the responses, I suspect design issues. The code shown above is clearly executing an insert. So the code that creates the file was not shared?
All-Star
48530 Points
18073 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 01:10 PM|PatriceSc|LINK
It's unclear if you are still trying to solve your StringBuilder issue (or it is solved and you have other issues or you finally choosed to try an alternate design ???). IMO the following should work even on Azure:
and so you'll send the script as multiple smaller chunks rather than as a single huge string( even "Read the .SQL file from the file system and convert to a byte array and store the file in the database instead of a string" would send the content to the db as a single huge string even which might be still too big).
According to "Not this specific issue, but one that I was seeing prior to the memory out exception" you may try also to deal with multiple problems at the same time. It could be best to open a separate thread for each problem to make things easier to follow and discuss.
Or if the current design just won't work, the big picture could help. For now it sounds like if you are trying to have a kind of service that would turn an Excel file into a kind of database initialization or data importation script ?????
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 10:15 PM|tvb2727|LINK
It is how it works. I can't send the entire project or post the entire project.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 10:17 PM|tvb2727|LINK
The file is not created until after they try to download it. The SQL Script would be injected into the database and then when the user selected the Download Script button, at that point, it generates the .SQL file.
And you are right, it does appear to be a design issue and that is why I'm posting it is to find a valid answer.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 10:21 PM|tvb2727|LINK
After working with my co-worker, I think we are going this route:
I'll reply to this post after I implement it and everything is successful.
Participant
1038 Points
2816 Posts
Re: StringBuilder Out Of Memory Exception
Oct 22, 2020 10:22 PM|tvb2727|LINK
Yes, the string build issue will go away and we will go from a nvarchar(max) column in the DB to a varbinary (max) like you suggested. We will then upload it via EF Core