504 Gateway Timeout: STRANGE!!! while server accepting the file into DB the response is not making it to the Client

naraomur

Member
Joined
Jun 26, 2023
Messages
7
Programming Experience
1-3
I have service that uploads and parses line by line into DataTable and records it via SQL bulk copy into the DB. Locally this service works fine with overall implementation in 20 secs and in LAN dev server takes a little longer in 46 secs. But when I run it on test server (the server for testing) the page is loading almost 1 minute and finally gives out '504 Gateway Time-out. The server didn't respond in time'. Although the SQL Table is being updated. If I upload less than half of the file then it works everywhere just fine. I am getting this error only on heavier (484613 lines) file.
Here is the code that holds the whole logic:
C#:
public int UploadCardBins(byte[] cardBins, out string rows, out List<string> mismatchedRows, out int fileLines)
    {
        mismatchedRows = new List<string>();
        fileLines = 0;           
        rows = null;
        int resultCode = (int)ResultCode.Ok;
        bool timeParsed = int.TryParse(ConfigurationManager.AppSettings["UploadCardBinSqlTimeOut"], out int timeOut);

        try
        {
            Stream ms = new MemoryStream(cardBins);
            StreamReader reader = new StreamReader(ms, System.Text.Encoding.UTF8);
            DataTable table = RetrieveCardBinFromTxtFile(reader.ReadToEnd(), out mismatchedRows, out fileLines);               
            
            rows = table.Rows.Count.ToString();             
          
            string sql = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;

            using (var connection = new SqlConnection(sql))
            {
                connection.Open();                   
                SqlTransaction transaction = connection.BeginTransaction();                   
                using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                {
                    bulkCopy.BatchSize = table.Rows.Count;
                    bulkCopy.DestinationTableName = "dbo.Dicts_CardBin";
                    try
                    {                                                     
                        var command = connection.CreateCommand();
                        if(timeParsed)
                            command.CommandTimeout = timeOut;                           
                        command.CommandText = "delete from dbo.Dicts_CardBin";
                        command.Transaction = transaction;
                        command.ExecuteNonQuery();                           
                        bulkCopy.WriteToServer(table);
                        Session.Flush();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();                           
                        logger.Error("[{0}] {1}", ex.GetType(), ex);
                        resultCode = (int)ResultCode.GenericError;
                    }
                    finally
                    {
                        transaction.Dispose();                           
                        connection.Close();
                    }
                }
            }               
        }
        catch (Exception ex)
        {
            logger.Error("[{0}] {1}", ex.GetType(), ex);
            resultCode = (int)ResultCode.GenericError;
        }           
        return resultCode;
    }

I tried(I thought maybe because of SQL):

I had set in app.settings the connection timeout to 120 seconds(2minutes), but still getting the same error.

What could be done from my side(source logic, db config, better tools for bulk copies). Threads operate and take the same amount of period by the way, tried it.
 
It's not a matter of the SQL connection that is timing out, but rather your your web host sending the time out. If you are using IIS as your web host, you can ask your IIS administrator to extend the response time out value for your app, or you can try playing games with the web.config to do the same. If your web host is Apache or some other web server, similar response timeouts can also be tweaked.

If you are not allowed to change those settings, then what you do is start another thread, and then respond back with a cookie. The cookie should be used by the caller to poll another controller method asking if the job (e.g the new thread that you started) is done yet. The new thread does what your current code does and doesn't tie up the web host's input request threads.
 
heavier (484613 lines) file

How many megabytes is that file? Recall that the web request time starts when the first byte of the HTTP request hits the server, not when the last byte of the request is accepted. If you have a slow network connection, that is working against you.
 
How many megabytes is that file? Recall that the web request time starts when the first byte of the HTTP request hits the server, not when the last byte of the request is accepted. If you have a slow network connection, that is working against you.

Thank you for your responses. File is 62.7mb txt file, internet is on the enterprise level and its fast. You think it might be also the volume of the file?
 
It's not a matter of the SQL connection that is timing out, but rather your your web host sending the time out. If you are using IIS as your web host, you can ask your IIS administrator to extend the response time out value for your app, or you can try playing games with the web.config to do the same. If your web host is Apache or some other web server, similar response timeouts can also be tweaked.

If you are not allowed to change those settings, then what you do is start another thread, and then respond back with a cookie. The cookie should be used by the caller to poll another controller method asking if the job (e.g the new thread that you started) is done yet. The new thread does what your current code does and doesn't tie up the web host's input request threads.

You mean at the front I create 2 threads one evoking the other? one being evoked will call the method UploadCardBins(byte[] cardBins, out string rows, out List<string> mismatchedRows, out int fileLines) and the other will be sending cookies to the web host?
 
File is 62.7mb txt file, internet is on the enterprise level and its fast

What is the bandwidth? Are you sure that you don't have any security software/devices between you and the server that adding extra overhead to the upload time?

Assuming that your local machine time is in synch with your server time, you could put a traces to log between lines 2-3, 13-14, and 37-38. You can then attempt an upload at particular time, and the look at the log file from the server to see when method was called, when the data was parsed, and when the SQL bulk copy completed. That would give you a rough idea of where the time is being used up.
 
What is the bandwidth? Are you sure that you don't have any security software/devices between you and the server that adding extra overhead to the upload time?

Assuming that your local machine time is in synch with your server time, you could put a traces to log between lines 2-3, 13-14, and 37-38. You can then attempt an upload at particular time, and the look at the log file from the server to see when method was called, when the data was parsed, and when the SQL bulk copy completed. That would give you a rough idea of where the time is being used up.

Thank you for your prompt response. I have yeah, that is adding overhead definitely but I've been told to just optimise my code as they tried to extend the timeout for response it didnt change anything still have the same problem. Now so far I optimized it and it works overall in 14 secs if before it worked in 22 secs. In order to check the way you're offering I need to get an approval for the testing purpose which is restricted for now. I could use the threads or async task but I don't know how to integrate it with tasks because they don't work with out parameters. Have you worked with tasks and threads with out parameters?
 
logs from my local server: RetrieveCardBinFromTxtFile takes 3 secs and SqlBulkCopy itself takes 3 secs
1690258871676.png
 
In order to check the way you're offering I need to get an approval for the testing purpose which is restricted for now.

What? Your current code has no logging? What kind of production quality code do you have that you have no logging? Adding traces is simply adding more logging.
 
logs from my local server: RetrieveCardBinFromTxtFile takes 3 secs and SqlBulkCopy itself takes 3 secs
View attachment 2898

Sounds like that is well under the typical 2 minute limit for IIS responses. Time to add more logging at the start and end of your controller method to see if you are responding in time. It sounds like you are when running locally in your dev environment.
 
What kind of production quality code do you have that you have no logging?

Surely

C#:
  static void Main()
  {
    try{
       App.Run();
    }
    catch
    {
      Console.Write("There was an error");
    }
  }

ought to be enough for anybody, right? :)

(Sure feels like it has been in some places I've joined)

I could use the threads or async task but I don't know how to integrate it with tasks because they don't work with out parameters. Have you worked with tasks and threads with out parameters?

It would only really help if the work is parallelizable. So the bulk copy to get the data into the db is completing in a few seconds, which is reasonable. Are you loading it into a temporary table and then running a process of inserting or updating the actual tables with it? It seems like some other process is responsible for the majority of the time
 
Surely

C#:
  static void Main()
  {
    try{
       App.Run();
    }
    catch
    {
      Console.Write("There was an error");
    }
  }

ought to be enough for anybody, right? :)

(Sure feels like it has been in some places I've joined)



It would only really help if the work is parallelizable. So the bulk copy to get the data into the db is completing in a few seconds, which is reasonable. Are you loading it into a temporary table and then running a process of inserting or updating the actual tables with it? It seems like some other process is responsible for the majority of the time

Thank you for response. There is no temp table before inserting there the table gets Truncated and then accept the bulk insert to it again. We found it faster rather than checking millions of lines for uniqueness. Some other process is RetrieveCardBinFromTxtFile method which takes 3 secs.
 
Back
Top Bottom