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

Question

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:

public int UploadCardBins(string 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 
        {                
            DataTable table = RetrieveCardBinFromTxtFile(cardBins, 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;
    }

private method to retrieve into DataTable:

private DataTable RetrieveCardBinFromTxtFile(string cardBins, out List<string> mismatchedLines, out int countedLines)
    {
        countedLines = 0;
        mismatchedLines = new List<string>();
        DataTable table = new DataTable();
        string pattern = @"^(?!.*[-\\/_+&!@#$%^&.,*={}();:?""])(\d.{8})\s\s\s(\d.{8})\s.{21}(\D\D)";
        MatchCollection matches = Regex.Matches(cardBins, pattern, RegexOptions.Multiline);

        table.Columns.Add("lk", typeof(string));
        table.Columns.Add("hk", typeof(string));
        table.Columns.Add("cb", typeof(string));

        // Remove empty lines at the end of the file
        string[] lines = cardBins.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
        int lastIndex = lines.Length - 1;
        while (lastIndex >= 0 && string.IsNullOrWhiteSpace(lines[lastIndex]))
        {
            lastIndex--;
        }
        Array.Resize(ref lines, lastIndex + 1);

        ////Check for lines that do not match the pattern
        for (int i = 0; i < lines.Length; i++)
        {
            string line = lines[i];
            if (!Regex.IsMatch(line, pattern))
            {
                mismatchedLines.Add($"Строка {i + 1} not matching: {line}");
            }
            countedLines++;
        }

        foreach (Match match in matches)
        {
            DataRow row = table.NewRow();

            row["lk"] = match.Groups[2].Value.Trim();
            row["hk"] = match.Groups[1].Value.Trim();
            row["cb"] = match.Groups[3].Value.Trim();

            table.Rows.Add(row);
        }

        return table;
    }

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. Note: .net framework is 4.7 Edited by advises: I have tried so far no using try catch and it gave Transportatiion Excception, tried the Stream but it kept throwing reading and writing timeout exception on a Controller layer. I've edited out the question by adding. So I just converted stream to string cause string can get through till the implementation layer. Instead of allocating several memory initializations now I have one reference cardBins without copies. Still I have the same issue

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.

So far I cleared out the duplicates and changed the Delete to Truncate table, also changed 2 loops with one in the method of retrieving data table. Now it waroks in 13 secs instead of 22 secs of before. But still have the issue though on the side of the Test server returning 504. Thank you for all of your interest and answers.

Answer 1

You can significantly increase the performance in the following ways:

• Change cardBins to be a Stream. You can get this using IFormFile if you want.
• Change RetrieveCardBinFromTxtFile (which I can't see) to both accept a Stream, and return a DbDataReader. You may want to use either the CsvHelpers or FastMember library.
• Retrieve the mismatchedRows etc values from this reader afterwards. You would need to make a custom property on your reader that could handle this.
• Instead of delete from, use TRUNCATE TABLE.
• Use async everywhere.
• You don't need all that error handling. Just make sure to put using on your transaction and connection objects, and the rollback will happen automatically.
• async functions cannot have out parameters. You need to change that to return a tuple instead.

public async Task<(int Result, List<string> mismatchedRows, int fileLines)> UploadCardBins(Stream cardBins)
{
    bool timeParsed = int.TryParse(ConfigurationManager.AppSettings["UploadCardBinSqlTimeOut"], out int timeOut);

    try 
    {
        using var reader = new StreamReader(cardBins, System.Text.Encoding.UTF8);
        using var table = RetrieveCardBinFromTxtFile(reader);
            
        string sql = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;

        await using var connection = new SqlConnection(sql);
        await connection.OpenAsync();
        await using var transaction = await connection.BeginTransactionAsync();

        using (var command = new SqlCommand("TRUNCATE TABLE dbo.Dicts_CardBin;", connection, transaction)
        {
            if(timeParsed)
                command.CommandTimeout = timeOut;                            
            await command.ExecuteNonQueryAsync();
        }

        using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
        bulkCopy.DestinationTableName = "dbo.Dicts_CardBin";
        await bulkCopy.WriteToServerAsync(table);
        Session.Flush();
        await transaction.CommitAsync();
        return ((int)ResultCode.Ok, table.MismatchedRows, table.FileLines);
    }
    catch (Exception ex)
    {
        logger.Error("[{0}] {1}", ex.GetType(), ex);
        return ((int)ResultCode.GenericError, -1, -1);
    }            
}

Consider using CancellationToken as well.