SQL Server query poor performance

Question

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:

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)

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

EXECUTION PLAN FOR QUERY

Select count(*) 
From proc_groups 
Where Code = 13100271

Definition of the index in proc_groups:

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')

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!

In the 2nd screenshot, isn't it asking you to create an index on the cuinac_pos.group column? — nullforce
– nullforce, Commented Jan 18, 2016 at 19:13
right click on the execution plan and pick missing index. this will give you index script. then try it again — Hiten004
– Hiten004, Commented Jan 18, 2016 at 19:13
I need several pieces of info to help. Select count() From proc_groups Where Code = 13100271. Select count() From proc_groups. What data type is Code? What is the execution plan for Select count(*) From proc_groups Where Code = 13100271? Fixing the plan for that simpler query is the next problem to get it to not "index scan". — Bruce Dunwiddie
– Bruce Dunwiddie, Commented Jan 18, 2016 at 19:34
And are you sure you don't have a loop in your code that calls this multiple times for one page? — Bruce Dunwiddie
– Bruce Dunwiddie, Commented Jan 18, 2016 at 19:38
I changed the query, so no, I do not see an execution plan above nor the changed query. "Select * From cuinac_pos Where [group] in (Select [group] From proc_groups Where Code = N'13100271')". The key piece is running it inside single quotes, and prefixing the single quotes with the letter N. This defines it as an NVARCHAR value. If you look at the screenshot of the hover that you provided, it shows a CONVERT_IMPLICIT that is preventing the index from being used. Making the change in the query and the code is an attempt to remove that. — Bruce Dunwiddie
– Bruce Dunwiddie, Commented Jan 19, 2016 at 16:10

Gordon Linoff · Accepted Answer · 2016-01-19 02:39:07Z

3

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)

edited Jan 19, 2016 at 2:39

answered Jan 18, 2016 at 19:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guillermo Zooby Over a year ago

Thank you for your help, I have added the index suggested in the execution plan, but still it´s very very slow.

Guillermo Zooby Over a year ago

Modifying the code is our last option, cause it´s working in all the clients except this one, by the moment we are going to try making changes in SQL. However, thanks for the advice, maybe we will have to make some modifications in the code at the end!

Sunderam Dubey · Accepted Answer · 2023-03-16 20:53:17Z

0

Whenever I read something like "fast in SSMS but slow in application" I have to think about this:

http://www.sommarskog.se/query-plan-mysteries.html

This applies especially to older DBs, which exist from SQL Server version to SQL Server version and are upgraded via scripts, and where the data reading is done through Stored Procedures.

Most of the time this behaviour is solveable with a SET ARITHABORT ON as first line of your SQL code.

You can put this into you SPs directly, or set it in your application through the Connection as default.

edited Mar 16, 2023 at 20:53

Sunderam Dubey

8,84812 gold badges26 silver badges43 bronze badges

answered Jan 18, 2016 at 19:32

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

SQL Server query poor performance

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related