0

I have searched endlessly for an answer I have ssms query that runs in about a second.

It returns a lot of data but they are all sums and so really it just returns one row. The query in SSMS executes in a little over a second. Does anyone have any ideas?

Don't get to caught up with property info portion it basically just abstracts the request model into stored procedure parameters and works fine but the SqlDataAdapter hangs for 8+ seconds. Again it's one row of data, 5 or 6 columns and executes in SSMS in less than 2 seconds.

public DataTable GetProcedureWithFilterDt(string procedure, ReportClientRequest request)
{
    DataTable dt = new DataTable();

    try
    {
        SqlConnection sqlConn = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
        SqlCommand sqlCmd = new SqlCommand(procedure, sqlConn);

        foreach (PropertyInfo property in request.GetType().GetProperties())
        {
            var str = property.GetValue(request)?.ToString() ?? null;
            string param = "@" + property.Name;
            string value = str;

            if (!String.IsNullOrWhiteSpace(str))
            {
                if (property.PropertyType == typeof(List<int>))
                {
                    List<int> list = property.GetValue(request) as List<int>;
                    if (list == null) continue;

                    DataTable TableDefined = new DataTable();
                    TableDefined.Columns.Add("id", typeof(Int32));

                    foreach (var id in list)
                    {
                        var row = TableDefined.NewRow();
                        row["id"] = id;
                        TableDefined.Rows.Add(row);
                    }
                    sqlCmd.Parameters.Add(new SqlParameter(param, SqlDbType.Structured)
                        {
                            TypeName = "dbo.idTableType",
                            Value = TableDefined
                        });
                }
                else if (property.PropertyType == typeof(List<long>))
                {
                    List<long> list = property.GetValue(request) as List<long>;
                    if (list == null) continue;

                    DataTable TableDefined = new DataTable();
                    TableDefined.Columns.Add("id", typeof(Int64));

                    foreach (var id in list)
                    {
                        var row = TableDefined.NewRow();
                        row["id"] = id;
                        TableDefined.Rows.Add(row);
                    }

                    sqlCmd.Parameters.Add(new SqlParameter(param, SqlDbType.Structured)
                        {
                            TypeName = "dbo.bigIdTableType",
                            Value = TableDefined
                        });
                }
                else
                {
                    value = property.GetValue(request).ToString();
                    sqlCmd.Parameters.Add(new SqlParameter(param, value));
                }
            }
        }

        sqlCmd.CommandTimeout = 60;
        sqlCmd.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCmd);
        dataAdapter.Fill(dt);
    }
    catch (Exception exp)
    {
    }

    return dt;
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    What parameter are you using to execute the stored proc in SSMS, taking 1 or 2 seconds? It may not be equivalent to the parameter generated by your code. Run SQL Profiler while your code runs, and copy the actual command from the profiler output so you can run that in SSMS. – Reg Edit May 17 '20 at 05:45
  • 1
    It's very likely parameter sniffing. SSMS emits SET OPTIONS that cause it to get a fresh plan for the params (SET ARITHABORT ON). Calling the SP with params does not; it simply uses a cached plan. Obligatory reading : http://www.sommarskog.se/query-plan-mysteries.html alos: https://stackoverflow.com/questions/801909/why-is-some-sql-query-much-slower-when-used-with-sqlcommand – Mitch Wheat May 17 '20 at 07:43
  • It's sounds like a parameter sniffing problem. Keep your statistics up to date an try to use a procedure that recompiles itseft every time is executed: `ALTER PROCEDURE YourProcedure ... WITH RECOMPILE AS ....` – Jesús López May 17 '20 at 09:21

0 Answers0