基于VB.NET与jQuery的Datatables加载6万条数据出现超时崩溃问题

• 服务端分页未生效：前端已经开启serverSide: true，但后端接口没有处理Datatables自动传递的start（分页起始下标）、length（每页条数）参数，每次请求都返回全量符合条件的数据，数据量超过1000后返回包过大、前端渲染压力陡增，直接导致浏览器崩溃或接口超时。
• SQL查询效率低下：使用了多个关联子查询，数据量上升后查询耗时会指数级增长，是接口超时的主要原因之一。
• 无效计算增加负担：进度条、状态标签的HTML拼接逻辑全部放在后端数据遍历环节，既增大了返回JSON的体积，也提升了后端的计算耗时。

前端调整 #

只需要新增加载状态配置，其他现有配置无需改动：

var exampleTable = $('#example').DataTable({
        "serverSide": true,
        "processing": true, // 新增：加载时显示加载提示，避免用户误认为页面卡死
        "responsive": true,
        "ajax": {
            "url": "EmailService.asmx/exampleScript",
            "type": "GET",
            "contentType": "application/json",
            "dataType": "json",
            "dataSrc": function (json) {
                var output = JSON.parse(json.d);
                json.draw = output.Draw;
                json.recordsTotal = output.recordsTotal;
                json.recordsFiltered = output.recordsFiltered;
                return output.data;
            },
        },
        scrollY: 500,
        deferRender: true,
        scroller: true,
        "pageLength": 50,
        // 建议把进度条、状态标签的渲染移到前端columns.render中实现，后端仅返回状态标识，大幅降低返回包体积
        "columns": [
            { 
                "data": "progressStatus", 
                "render": function(status) {
                    // 在这里根据状态值拼接进度条HTML，替代后端返回的HTML字符串
                }
            },
            { "data": "Status" },
            { "data": "Mail" },
            { "data": "Date_Delivery" },
            { "data": "Date_Send" },
            { "data": "Date_Reject" },
            { "data": "Date_Open" },
            { "data": "Date_First_Click" },
            { "data": "Date_Last_Click" },
            { "data": "Date_Bounce" },
            { "data": "Date_Complaint" },
            { "data": "Date_Delivery_Delay" },
            { "data": "Date_Bounce_Type" },
            { "data": "Date_Bounce_Sub_Type" },
            { "data": "ErrorDescription" }
        ]
    });

后端核心修改 #

1. 新增接口入参，接收Datatables的分页参数

<WebMethod>
<ScriptMethod(ResponseFormat:=ResponseFormat.Json, UseHttpGet:=True)>
Public Function exampleScript(draw As Integer, start As Integer, length As Integer)
    Dim defaultDatabaseEngine = System.Configuration.ConfigurationManager.AppSettings("DATABASE_ENGINE_DEFAULT")
    Dim CS As String = ""
    If defaultDatabaseEngine.Equals("MySQL") Then
        CS = System.Configuration.ConfigurationManager.ConnectionStrings("MySQLConnString").ConnectionString.ToString()
    ElseIf defaultDatabaseEngine.Equals("MSSQL") Then
        CS = System.Configuration.ConfigurationManager.ConnectionStrings("MSSQLConnString").ConnectionString.ToString()
    End If

2. 新增总记录数查询，无需遍历全量数据统计总数

Dim totalCount As Integer = 0
    ' 先查询符合条件的总记录数
    Dim countQuery = "SELECT COUNT(*) FROM OCP_MAILS WHERE OCP_MAILS.HDR = '2021001109'"
    Using countCon As SqlConnection = New SqlConnection(CS)
        Dim countCmd As SqlCommand = New SqlCommand(countQuery, countCon)
        countCon.Open()
        totalCount = CInt(countCmd.ExecuteScalar())
    End Using

3. 优化SQL查询，新增分页逻辑，仅查询当前页需要的数据
   以MSSQL为例，修改查询语句使用OFFSET FETCH实现分页，同时把关联子查询改成JOIN模式提升查询效率：

Dim stringQuery = "SELECT OCP_MAILS.DESTINATION as Mail , OCP_MAILS.MESSAGE_ID as MessageID , OCP_MAILS.EVENT_DELIVERY as Date_Delivery , OCP_MAILS.EVENT_SEND as Date_Send, OCP_MAILS.EVENT_REJECT as Date_Reject,
        ISNULL(openData.firstOpen, '') As Date_Open,
        ISNULL(clickData.firstClick, '') as Date_First_Click,
        ISNULL(clickData.lastClick, '') as Date_Last_Click,
        OCP_MAILS.EVENT_BOUNCE as Date_Bounce , OCP_MAILS.EVENT_COMPLAINT as Date_Complaint , OCP_MAILS.EVENT_DELIVERYDELAY as Date_Delivery_Delay , OCP_MAILS.EVENT_BOUNCE_TYPE as Date_Bounce_Type , 
        OCP_MAILS.EVENT_BOUNCE_SUBTYPE as Date_Bounce_SubType
        FROM OCP_MAILS 
        LEFT JOIN (SELECT MESSAGE_ID, MIN(TIMESTAMP) as firstOpen FROM OCP_MAIL_OPENS WHERE HDR = '2021001109' GROUP BY MESSAGE_ID) openData ON openData.MESSAGE_ID = OCP_MAILS.MESSAGE_ID
        LEFT JOIN (SELECT MESSAGE_ID, MIN(TIMESTAMP) as firstClick, MAX(TIMESTAMP) as lastClick FROM OCP_MAIL_LINKS_CLICKED WHERE HDR = '2021001109' GROUP BY MESSAGE_ID) clickData ON clickData.MESSAGE_ID = OCP_MAILS.MESSAGE_ID
        WHERE OCP_MAILS.HDR = '2021001109'
        ORDER BY OCP_MAILS.MESSAGE_ID
        OFFSET @start ROWS FETCH NEXT @length ROWS ONLY"

    Dim Informacoes As New List(Of info)
    Using con As SqlConnection = New SqlConnection(CS)
        Dim cmd As SqlCommand = New SqlCommand(stringQuery, con)
        cmd.CommandType = CommandType.Text
        ' 给SQL语句参数化赋值，避免注入风险同时提升查询效率
        cmd.Parameters.AddWithValue("@start", start)
        cmd.Parameters.AddWithValue("@length", length)
        con.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader()
        While rdr.Read()
            ' 原有状态判断逻辑保持不变，建议把HTML拼接逻辑移除，仅返回状态值给前端渲染
            ' ......
        End While
    End Using

    Dim JsonGrande As New JobEmailSelected
    JsonGrande.draw = draw
    JsonGrande.recordsTotal = totalCount
    JsonGrande.recordsFiltered = totalCount
    JsonGrande.data = Informacoes

    Dim json As JavaScriptSerializer = New JavaScriptSerializer()
    json.MaxJsonLength = Int32.MaxValue
    Return json.Serialize(JsonGrande)
End Function

• 给查询涉及的OCP_MAILS.HDR、OCP_MAIL_OPENS.MESSAGE_ID、OCP_MAIL_LINKS_CLICKED.MESSAGE_ID字段添加数据库索引，查询速度可提升数倍。
• 开启接口响应的gzip压缩，可将返回包体积缩小70%以上。
• 单页条数不要设置过大，保持默认的50条即可，兼顾加载速度和浏览体验。

内容的提问来源于stack exchange，提问作者André