sqlsql-server-2008indexingquery-performancesqlperformance

SQL Server query poor performance

We are working with an app, .NET, in which when you press a button a DevExpress form is opened and a SQL Server query is executed, so it can fill with data some comboboxes. Application is working fine in lots of customers, but in a particular one it´s taking more than a minute in loading the form. I can see in the performance monitor that SQL Server is taking a lot of CPU when I want to load the form.

I executed the query directly in SQL Server Management Studio, taking no more than a second, however I tried having a look at SQL Activity Monitor and what I can see here (not happening to other customers, same IO, same SQL, same everything) is this:

enter image description here

So the thing I can see here, that I don´t understand, is why is this query having so much executions? Why is it taking so long to retrieve data? Here it´s the execution plan of this query:

Select * 
From cuinac_pos  
Where [group] in (Select [group] 
                  From proc_groups  
                  Where Code = 13100271)

enter image description here

Thank you for any help you can give me, and please if I can give any more info do not hesitate to ask.

Once again, thanks!

AFTER ADDING THE EXECUTION PLAN SUGGESTED INDEX

enter image description here

enter image description here

EXECUTION PLAN FOR QUERY 

Select count(*) 
From proc_groups 
Where Code = 13100271

enter image description here

enter image description here

Definition of the index in proc_groups:

enter image description here

Example of the code:

private static void LoadDTPurchaseHerdRelation(Int32 status, Int32 herdNumber)
        {
            try
            {
                StringBuilder sb = new StringBuilder();


                sb.Append(" Select gr.[group] as HerdId, gr.code as HerdNumber, bo.code as PurchaseCode");
                sb.Append(" From cuinac_pos bo ");
                sb.Append(" inner join proc_groups gr on bo.code=gr.code ");

                if (herdNumber == 0)
                {
                    string s1 = " Where (gr.created between '2015-12-09' And '2016-01-08') ";
                    sb.Append(s1);

                    if (status != 4)
                    {
                        string s2 = string.Format(" AND bo.purchasestatus = {0} ", status);
                        sb.Append(s2);
                    }

                    sb.Append(" order by bo.code ");
                }
                else
                {
                    string s3 = string.Format(" Where gr.code = '{0}' ", herdNumber);
                    sb.Append(s3);
                }

                DTPurchaseHerdRelation.Clear();
                using (ConnectionScope cs = new ConnectionScope())
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sb.ToString(), (SqlConnection)cs.Connection);
                    adapter.Fill(DTPurchaseHerdRelation);
                }
            }
            catch (Exception ex)
            {

            }
        }

    }
}

Execution plan for query

Select * From cuinac_pos Where [group] in (Select [group] From proc_groups Where Code = N'13100271')

enter image description here

Solved:

I finally got it by adding indexes suggested in the answer marked as correct, and adding in the code, in the queries which searched by nvarchar value "Code", an N before rhe value as suggested in comments by shriop. Thank you all for your effort!

Solution

  • For this query:

    Select *
From cuinac_pos
Where [group] in (Select [group] From proc_groups  Where Code = 13100271 );

    The optimal indexes are proc_groups(code, group) and cuinac_pos(group). Having those indexes might help.

    EDIT:

    For performance, this might be better:

    Select *
From cuinac_pos cp
Where exists (Select 1
              From proc_groups pg
              Where pg.Code = 13100271 and pg.[group] = cp.[group]
             );

    with an index on `proc_groups(group, code)