Lag on page when populating datatable with SP results that runs instantly in SSMS

Asked Jan 26 '16 at 09:06

Active Jan 27 '16 at 09:37

Viewed 49 times

I have a web forms page built in C# that takes user input to search an SQL Server 2012 database. The page then exports the results to excel using EPPlus.

I have just optimised all my SQL and the stored procedure the page uses now runs in under a second in SSMS but takes around 30s on the page.

When I have debugged this the hold up is when the datatable is being populated on the myDataAdaptor.Fill(myDataTable); in the following code, all other code runs instantly.

var myDataAdaptor = new SqlDataAdapter(myCommand);

using (myConnection)
using (myCommand)
using (myDataAdaptor)
{
    myDataAdaptor.Fill(myDataTable); //Hold up happens here
}

return myDataTable;

The datatable is returned and then passed to the EPPlus code and the spreadsheet is generated, I have used a datatable as all EPPlus examples i have found use datatables.

Any help on speeding this up would be greatly appreciated.

edited Jan 27 '16 at 09:37

asked Jan 26 '16 at 09:06

Simon

1,293
5
21
39

But is the hold up specifically the Fill call or that whole code section including opening the database connection? – Alan Macdonald Jan 26 '16 at 21:58
The hold up happens when the fill is called. I've edited the original question to show this. – Simon Jan 27 '16 at 09:35
Would be worth looking at the suggestions here http://stackoverflow.com/questions/250713/sqldataadapter-fill-method-slow – Alan Macdonald Jan 27 '16 at 10:20

Lag on page when populating datatable with SP results that runs instantly in SSMS

0 Answers0