大文件批量写入SQL数据库时出现504 Gateway Timeout问题求助

开发了一个文件上传解析服务，流程为：上传文件→逐行解析至DataTable→通过SQL Bulk Copy写入数据库。

• 本地环境全流程耗时20秒，局域网开发服务器耗时46秒
• 测试服务器处理大文件（484613行）时，页面加载近1分钟后返回504 Gateway Timeout. The server didn't respond in time错误，但SQL数据表已成功更新
• 文件行数不足一半时，所有环境均可正常运行，仅大文件触发该问题

核心逻辑代码 #

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;
}

转换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;
}

已尝试的解决方法 #

• 在app.settings中设置连接超时为120秒，问题依旧
• 尝试使用Stream但在Controller层触发读写超时异常，改为将Stream转换为string传递以减少内存分配，问题未解决
• 清理重复数据，将Delete语句改为Truncate table，合并DataTable转换方法中的循环，处理时间从22秒降至13秒，但测试服务器仍返回504错误

注：使用的.NET Framework版本为4.7。

寻求解决方案 #

需从代码逻辑、数据库配置、批量拷贝工具优化等方向获取解决思路。

内容的提问来源于stack exchange，提问作者Nara Omur