How can I improve performance of dataadapter.fill?

Question

I'm storing data in a DataTable using SqlDataAdapter.fill. Then returning that table as JSon.

When getting that JSon in client application(Excel), it gets very slow.

Any idea how can I improve the performance.?

API code:

 [Authorize]
        [Route("GetRecord")]
        public DataTable Records(DSModel model)
        {
            var table = new DataTable();
            using (var da = new SqlDataAdapter(model.qstring, model.ds))
            {
                da.Fill(table);
                return table;
            }
        }

Client Code:

    DataTable dt = (DataTable)JsonConvert.DeserializeObject(records, (typeof(DataTable)));
    foreach (DataRow dr in dt.Rows)
    {
        rowcount += 1;
        for (int i = 1; i <= dt.Columns.Count; i++)
        {
            // on the first iteration we add the column headers
            if (rowcount == 3)
            {
                osheet.Cells[2, i] = dt.Columns[i - 1].ColumnName;
            }
            // Filling the excel file 
            osheet.Cells[rowcount, i] = dr[i - 1].ToString();
        }
    }

For comparison, If I query the same data from Excel using SQL Connection, it takes 2-3 seconds. With the code above, its almost 11-14 seconds for a particular table.

The Excel Interop is very slow and can't be improved. You can use oledb to write to excel or use Entity which is faster. — jdweng, Jan 11 '20 at 17:08
@jdweng so does it mean that the way I'm getting the data is optimum.? I mean the method..like sql > datatable > json in api code and then from json > datatable and then populate excel.? Do you seen any improvement here.? — Hesoti, Jan 11 '20 at 17:17
The Interop uses a Proprietary Scripting language to communicate between c# and excel. The script gets generate when the c# is complied, but excel and to parse the results and return data in same format to c#. Interop is slow either when reading or writing. The Interop is the only way of formating the excel workbook from c#. So if you do not need formatting the it is better to use other methods. — jdweng, Jan 11 '20 at 17:39
you should try to set a complete row at once and not every cell — Charles, Jan 11 '20 at 17:57
What part is slow? `da.Fill(table);` on the server side? Calling the server and downloading the result as a string on the client side (code not shown)? `JsonConvert.DeserializeObject(records, (typeof(DataTable)))` on the client side? Or the `foreach (DataRow dr in dt.Rows) { ...` Excel interop loop on the client side? Your question title implies that `da.Fill(table);` is what is slow, but have you checked? — dbc, Jan 11 '20 at 19:01
If it's the Excel Interop which is slow -- not the `da.Fill(table);` -- then see [Microsoft.Office.Interop.Excel really slow](https://stackoverflow.com/q/3989122/3744182) or [Excel Interop - Efficiency and performance](https://stackoverflow.com/q/356371/3744182). — dbc, Jan 11 '20 at 19:04
[this specific answer](https://stackoverflow.com/a/21079709) to [How to export DataTable to Excel](https://stackoverflow.com/q/8207869), and also [this answer](https://stackoverflow.com/a/536699) to [Write Array to Excel Range](https://stackoverflow.com/q/536636) might also help with Excel Interop performance. — dbc, Jan 11 '20 at 19:07

score 0 · Answer 1 · answered Jan 11 '20 at 22:50

Can the client create a connection to the sql server? If so you could write the results of the query to a table and return the DataBase and Tablename to the client. The client could then load the table into Excel.

For more information see post: Fastest way to load data from SQL Server to Excel

How can I improve performance of dataadapter.fill?

1 Answers1