locked
StringBuilder Out Of Memory Exception RRS feed

  • Question

  • User-1188570427 posted

    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. 

    Wednesday, October 21, 2020 1:23 PM

All replies

  • User-474980206 posted

    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.

    Wednesday, October 21, 2020 2:36 PM
  • User-1188570427 posted

    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?

    Wednesday, October 21, 2020 3:09 PM
  • User-474980206 posted

    why do you need the full string? it appears to be too large for practical use. 

    Wednesday, October 21, 2020 3:41 PM
  • User-1188570427 posted

    why do you need the full string? it appears to be too large for practical use. 

    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.

    Wednesday, October 21, 2020 4:06 PM
  • User475983607 posted

    tvb2727

    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?

    Wednesday, October 21, 2020 4:16 PM
  • User753101303 posted

    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???

    Wednesday, October 21, 2020 4:24 PM
  • User-1188570427 posted

    tvb2727

    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.

    Model.GenerateScript = sb.ToString();  errors here

    Wednesday, October 21, 2020 4:28 PM
  • User-1188570427 posted

    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).

    Check my response to mgebhard.

    Wednesday, October 21, 2020 4:30 PM
  • User-1188570427 posted

    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

    Wednesday, October 21, 2020 4:34 PM
  • User753101303 posted

    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 ?

    Wednesday, October 21, 2020 5:00 PM
  • User-1188570427 posted

    Removed.

    Wednesday, October 21, 2020 5:26 PM
  • User-1188570427 posted

    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:

                    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: 

    	[GeneratedScript] [nvarchar](max) NULL,

    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.

    Wednesday, October 21, 2020 5:32 PM
  • User-1188570427 posted

    mgebhard

    tvb2727

    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.

    Model.GenerateScript = sb.ToString();  errors here

    mgebhard

    Look at my previous response.

    Wednesday, October 21, 2020 5:32 PM
  • User-1188570427 posted

    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?

    Wednesday, October 21, 2020 5:43 PM
  • User475983607 posted

    tvb2727

    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.  

    Wednesday, October 21, 2020 6:30 PM
  • User-1188570427 posted

    tvb2727

    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();
                            }
                        }
                    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. 

    Wednesday, October 21, 2020 6:57 PM
  • User753101303 posted

    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 ???

    Wednesday, October 21, 2020 10:58 PM
  • User475983607 posted

    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. 

    Wednesday, October 21, 2020 11:56 PM
  • User-1188570427 posted

    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. 

    Thursday, October 22, 2020 12:18 AM
  • User1686398519 posted

    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:

    1. When the data is too large, you can write the data to the file first, because there is more disk space than memory, so such errors will not occur.
    2. 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

    Thursday, October 22, 2020 7:36 AM
  • User475983607 posted

    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.  

    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?

    Thursday, October 22, 2020 11:51 AM
  • User753101303 posted

    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 ?????

    Thursday, October 22, 2020 1:10 PM
  • User-1188570427 posted

    tvb2727

    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.  

    It is how it works. I can't send the entire project or post the entire project. 

    Thursday, October 22, 2020 10:15 PM
  • User-1188570427 posted

    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?

    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. 

    Thursday, October 22, 2020 10:17 PM
  • User-1188570427 posted

    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:

    1. When the data is too large, you can write the data to the file first, because there is more disk space than memory, so such errors will not occur.
    2. 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

    After working with my co-worker, I think we are going this route:

    1. Use File Stream instead of String Builder
    2. Write the SQL Statements to the File Stream
    3. Take the file off the file system
    4. Save it into a new column (not string) var binary max with the actual .Sql file from the system. 
    5. When the user selects the download script button, go to the database, obtain the file, then save it to the computer so the user can download it
    6. At each time in place, the actual physical file will be deleted after it has been there for 45 minutes. 

    I'll reply to this post after I implement it and everything is successful. 

    Thursday, October 22, 2020 10:21 PM
  • User-1188570427 posted

    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 

    Thursday, October 22, 2020 10:22 PM