Stored procedure causing connection time execution out error when fetch large data

Question

I am calling a SQL Server stored procedure to get huge amount of records from the database, but it's causing timeout execution errors when run. How can we make it fast in this way?

I am using stored procedure call in Entity Framework. The stored procedure takes input parameters and out parameter is total record in table for pagination in the fronted. I want to get all data with 10 record per page. Database table name is Player_account_flow and store and stored procedure name is also given. I am mapping my db model when the data load.

using (SqlConnection conn = dbContext.Database.GetDbConnection() as SqlConnection)
{
    conn.Open();                    

    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "Pro_GetPageData"; 
        cmd.CommandType = CommandType.StoredProcedure; 

        // input parameters to procedure
        cmd.Parameters.AddWithValue("@TableName", "Player_Account_Flow"); 
        cmd.Parameters.AddWithValue("@OrderString", "Create_Time Desc"); 
        cmd.Parameters.AddWithValue("@ReFieldsStr", "*");
        cmd.Parameters.AddWithValue("@PageIndex", 1); 
        cmd.Parameters.AddWithValue("@PageSize", 10); 
        cmd.Parameters.AddWithValue("@WhereString", query);                                        

        cmd.Parameters.AddWithValue("@TotalRecord", SqlDbType.Int); 
        cmd.Parameters["@TotalRecord"].Direction = ParameterDirection.Output; 

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var map = new PlayerAccountFlow();
                map.Id = (Int32)reader["Id"];
                map.AccountId = (Int32)reader["AccountId"];
                map.Type = (byte)reader["Type"];
                map.BeforeAmout = (decimal)reader["BeforeAmout"];
                map.AfterAmout = (decimal)reader["AfterAmout"];
                map.Amout = (decimal)reader["Amout"];
                map.Source = (Int32)reader["Source"];
                map.Memo = reader["Memo"].ToString();                            
                map.CreateTime = (DateTime)reader["Create_Time"];
                playerAccountFlowsList.Add(map);
            }
        }

        obj = cmd.Parameters["@TotalRecord"].Value;
    }
}

PagingData<PlayerAccountFlow> pagingData = new PagingData<PlayerAccountFlow>();
pagingData.Countnum = (Int32)obj;
pagingData.Data = playerAccountFlowsList;
return pagingData;

anyone here who have face this problem, i need efficient suggestion that can help for fetching the record fast from database using this store procedure or some other alternative solution is appreciated — Hassan Muhammad Saad, Jan 03 '20 at 02:53
After the line `cmd.CommandType = CommandType.StoredProcedure;`, can you please add `cmd.CommandTimeout = 0;` and give a try. — Arulkumar, Jan 03 '20 at 02:56
its work, i test this one somedays before but the speed of fetching record is too slow that is the problem. my record is large like 12438515. It will increase in the future as well. So the speed is not fast. — Hassan Muhammad Saad, Jan 03 '20 at 03:03
In this case, you need to fine tune your stored procedure `Pro_GetPageData`. The fix I gave to solve the timeout issue as per your initial question. — Arulkumar, Jan 03 '20 at 03:05
yes you have given a solution, i appreciate. My store procedure is very fast when i manually run it in sql. So i think something wrong here not in SQL — Hassan Muhammad Saad, Jan 03 '20 at 03:08
This could be a parameter sniffing issue. To confirm you need to monitor the stored procedure call using SQL Profiler or server side traces and confirm that it's a database performance issue when called from the app (and not when run directly) — Nick.Mc, Jan 03 '20 at 05:24
@HassanMuhammadSaad Please take a look at this question: https://stackoverflow.com/questions/6585417/stored-procedure-slow-when-called-from-web-fast-from-management-studio — Rahul Sharma, Jan 03 '20 at 06:33
nick-mcdermaid, i have test the stored procedure in SQL studio but it is very fast there but in entity framework calling it does not fast — Hassan Muhammad Saad, Jan 03 '20 at 07:37
Rahul's link is interesting - definitely worth trying what is suggested there. Where do you see the slow down? Is it at the point of `using (var reader = cmd.ExecuteReader())` before the `while` loop? Also, have you thought about paging on the SQL Server side? This would mean you're not passing huge datasets back to the front end just to cut them down to a page of 10 results. (you mention you page on the front end - forgive my mistake if you are doing the work in the procedure already). — scgough, Jan 03 '20 at 09:43
Yes you already mentioned that it's slow in the app but fast in SSMS. This is a good indicator that it's parameter sniffing. I recommend you pursue that route, but there are so many sugestions now in this thread I'm going to leave it there. — Nick.Mc, Jan 04 '20 at 03:37
scgough, stored procedure is doing everything at server side including total count and pagination in SQL. I am just getting number of total record and 10 record in every hit. Yes here using (var reader = cmd.ExecuteReader()) is some problem, i think. — Hassan Muhammad Saad, Jan 04 '20 at 03:45

score 0 · Answer 1 · answered Jan 03 '20 at 14:29

There's multiple things to look into here. You could set a longer command timeout than the default. You could look into the stored procedure to see why is it running so slow.

For the CommandTimeout just give it a value in seconds. In the example below that would be 2 minutes. Although it would be a bad user experience to make them wait minutes to display their result. I would advise on optimizing the stored procedure first(look at indexes, multiple joins, sub queries, and so on). Also, are you actually displaying that huge dataset on the user interface, or are you splitting it in pages? You could change the stored procedure to just return the current page, and call it again when the user goes to the next page. A well written stored procedure should be a lot faster when you only return a small subset of the whole data.

using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandTimeout = 120;
        cmd.CommandText = "Pro_GetPageData"; 
        cmd.CommandType = CommandType.StoredProcedure;

I've seen something like this before. Look at this [link](https://www.mssqltips.com/sqlservertip/4318/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application/). — Alex, Jan 05 '20 at 14:31
There's also this https://stackoverflow.com/questions/2248112/query-times-out-when-executed-from-web-but-super-fast-when-executed-from-ssms, with some good ideas to try — Alex, Jan 05 '20 at 14:40

score -1 · Answer 2 · answered Jan 03 '20 at 11:47

In this case you should revisit your stored procedure for example multiple joins. You should also ask yourself if all data should be loaded immediately or not. I would suggest to display initial data and load further data on user request. That means that you can use simple count on database and calculate pages based on result, then fetch data only from selected range.

Stored procedure causing connection time execution out error when fetch large data

2 Answers2