locked
Queries max-out CPU to 100% RRS feed

  • Question

  • I am running a small app that uses a combobox and a gridview. On page load the combobox is populated with values and the grid contains all records. Upon selecting a value in the combobox the grid re-filters and re-populates. Each and every time the page loads OR a PostBack is done the CPU maxes-out to 100% for 2-3 seconds. How can I eliminate this from happening? Is there some type of CPU throttling that can be done to limit the amount of CPU is used when running queries? Is there a way to cache or store my original query into a DataTable and then hit this DataTable or cache instead of continually hitting the db each time a query is performed?

    1. If throttling is an option - I don't know how

    2. If caching the query is an option - I also don't know how

    3. If creating a DataTable is the solution - I do know how, but once the DataTable is created and then filtered, I don't know how to re-use the same DataTable to request a new query against it.

    Sorry for the hand-holding, but I need baby steps. Thanks.

    Here is my code:

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    //RefreshControls();
                    RadComboBox2.DataSource = GetAttyData();
                    RadComboBox2.DataBind();
                }
            }        
    
            protected void Page_PreRender(object sender, EventArgs e)
            {
                if (RadGrid1.SelectedIndexes.Count == 0)
                    RadGrid1.SelectedIndexes.Add(0);
                if (RadGrid2.SelectedIndexes.Count == 0)
                {
                    RadGrid2.Rebind();
                    RadGrid2.SelectedIndexes.Add(0);
                }
            }
    
            protected void Page_Init(object sender, System.EventArgs e)
            {
                RadAjaxPanel1.AjaxRequest += new RadAjaxControl.AjaxRequestDelegate(RadAjaxPanel1_AjaxRequest);
                RadComboBox2.TextChanged += new EventHandler(RadComboBox2_TextChanged);
                RadGrid1.NeedDataSource += new GridNeedDataSourceEventHandler(RadGrid1_NeedDataSource);
                RadGrid2.NeedDataSource += new GridNeedDataSourceEventHandler(RadGrid2_NeedDataSource);
            }
    
            protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
            {
                RadGrid1.DataSource = GetData();
            }
    
            protected void RadGrid2_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
            {
                RadGrid2.DataSource = GetData2();
            }
    
            void RadAjaxPanel1_AjaxRequest(object sender, Telerik.Web.UI.AjaxRequestEventArgs e)
            {
                RefreshControls();
            }
    
            private void RefreshControls()
            {
                RadGrid1.Rebind();
                RadComboBox2.DataSource = GetAttyData();
                RadComboBox2.DataBind();
            }
    
            void RadComboBox2_TextChanged(object sender, EventArgs e)
            {
                RadGrid2.DataSource = GetData2();
                RadGrid2.Rebind();
            }
            
            private DataTable GetData()
            {
                String ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection conn = new SqlConnection(ConnString);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("SELECT top 1000 name, custID, status, lastname FROM vptimedata", conn);
    
                DataTable myDataTable = new DataTable();
                DataRow[] rows;
                
                try
                {
                    conn.Open();
                    try
                    {
                        adapter.Fill(myDataTable);
                    }
                    finally
                    {
                        conn.Close();
                    }
    
                    RadGrid1.DataSource = myDataTable;
                }
                catch
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('I am broken');", true);
                }
    
                if (!String.IsNullOrEmpty(RadTextBox1.Text))
                {
                    rows = myDataTable.Select("name LIKE '%" + RadTextBox1.Text + "%'");
                }
                
                else
                {
                    rows = myDataTable.Select();
                }
    
                return rows.CopyToDataTable();
            }
    
            private DataTable GetAttyData()
            {
                String ConnString3 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection conn3 = new SqlConnection(ConnString3);
                SqlDataAdapter adapter3 = new SqlDataAdapter();
                adapter3.SelectCommand = new SqlCommand("SELECT DISTINCT lastname FROM vptimedata", conn3);
    
                DataTable myDataTable3 = new DataTable();
                
                    conn3.Open();
                    try
                    {
                        adapter3.Fill(myDataTable3);
                    }
                    finally
                    {
                        conn3.Close();
                    }
    
                    RadComboBox2.DataSource = myDataTable3;
                    if (myDataTable3.Rows.Count <= 0)
                    {
                        return new DataTable();
                    }
                    return myDataTable3;
            }
    
            protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
            {
                
            }
    
            private DataTable GetData2()
            {
                String ConnString2 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection conn2 = new SqlConnection(ConnString2);
                SqlDataAdapter adapter2 = new SqlDataAdapter();
                adapter2.SelectCommand = new SqlCommand("SELECT top 1000 name, custID, status, employee FROM vptimedata WHERE tklast LIKE '%" + RadComboBox2.SelectedValue.ToString() + "%' ORDER BY name", conn2);
                string combotext = RadComboBox2.SelectedValue.ToString();
                
                DataTable myDataTable2 = new DataTable();
                
                try
                {
                    conn2.Open();
                    try
                    {
                        adapter2.Fill(myDataTable2);
                    }
                    finally
                    {
                        conn2.Close();
                    }
                    Label1.Text = combotext;
                    RadGrid2.DataSource = myDataTable2;
                }
                catch
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('I am broken');", true);
                }
    
                return myDataTable2;
            } 

    And my ASPX page

    <form id="form1" runat="server">
        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <AjaxSettings>
                <telerik:AjaxSetting AjaxControlID="custID">
                    <UpdatedControls>
                        <telerik:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="AjaxLoadingPanel1"></telerik:AjaxUpdatedControl>
                        <telerik:AjaxUpdatedControl ControlID="RadGrid2"></telerik:AjaxUpdatedControl>
                        <telerik:AjaxUpdatedControl ControlID="custID" />
                    </UpdatedControls>
                </telerik:AjaxSetting>
            </AjaxSettings>
        </telerik:RadAjaxManager>
        <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" />
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <telerik:RadCodeBlock ID="RadCodeBlock1" runat="server">
            <script type="text/javascript">
                function valueChanged(sender, args) {
                    $find('<%= RadAjaxPanel1.ClientID %>').ajaxRequest(args.get_newValue());
                }
            </script>
        </telerik:RadCodeBlock>
        <telerik:RadAjaxPanel ID="RadAjaxPanel1" runat="server" Width="900px">
            <telerik:RadTabStrip ID="RadTabStrip1" runat="server" 
                MultiPageID="RadMultiPage1">
                <Tabs>
                    <telerik:RadTab runat="server" Text="Client Name">
                    </telerik:RadTab>
                    <telerik:RadTab runat="server" Text="Attorney">
                    </telerik:RadTab>
                </Tabs>
            </telerik:RadTabStrip>
            <telerik:RadMultiPage ID="RadMultiPage1" runat="server">
                <telerik:RadPageView ID="RadPageView1" runat="server">
                    <br />
                    <h3>Original Grid:</h3>  
        <telerik:RadTextBox ID="RadTextBox1" runat="server">
                <ClientEvents OnValueChanged="valueChanged" />
            </telerik:RadTextBox>
        <telerik:RadGrid ID="RadGrid1" runat="server" CellSpacing="0" GridLines="None"
             OnNeedDataSource="RadGrid1_NeedDataSource" OnItemCommand="RadGrid1_ItemCommand">
                <ClientSettings AllowKeyboardNavigation="true" EnablePostBackOnRowClick="true">
                    <Selecting AllowRowSelect="true"></Selecting>
                </ClientSettings>
                <MasterTableView Width="100%" DataKeyNames="custID"></MasterTableView>
                <PagerStyle Mode="NextPrevAndNumeric" />
                <FilterMenu EnableTheming="true">
                    <CollapseAnimation Duration="200" Type="OutQuint" />
                </FilterMenu>
            </telerik:RadGrid> 
                </telerik:RadPageView>
                <telerik:RadPageView ID="RadPageView2" runat="server">
                    <br />
                    <telerik:RadComboBox ID="RadComboBox2" runat="server" DataTextField="lastname"
                DataValueField="lastname" AutoPostBack="true">
                <Items>
                    <telerik:RadComboBoxItem Text="No Filter" />
                </Items>
            </telerik:RadComboBox>
            <asp:Label ID="Label1" runat="server"
                Text=""></asp:Label>
        <telerik:RadGrid ID="RadGrid2" runat="server" CellSpacing="0" GridLines="None"
             OnNeedDataSource="RadGrid1_NeedDataSource" OnItemCommand="RadGrid1_ItemCommand">
                <ClientSettings AllowKeyboardNavigation="true" EnablePostBackOnRowClick="true">
                    <Selecting AllowRowSelect="true"></Selecting>
                </ClientSettings>
                <MasterTableView Width="100%" DataKeyNames="custID"></MasterTableView>
                <PagerStyle Mode="NextPrevAndNumeric" />
                <FilterMenu EnableTheming="true">
                    <CollapseAnimation Duration="200" Type="OutQuint" />
                </FilterMenu>
            </telerik:RadGrid>
                </telerik:RadPageView>
            </telerik:RadMultiPage>
        <h2>
            Advanced Data Binding Test
        </h2>
               
        </telerik:RadAjaxPanel>
           
        </form>

    • Moved by Jason Dot Wang Friday, April 5, 2013 5:50 AM This thread is about ASP.NET
    Wednesday, April 3, 2013 4:28 PM

Answers

  • Use the debugger to step through the code.  Which line takes all the time?

    Paul Linton

    • Marked as answer by Alex Dove Tuesday, May 7, 2013 8:40 PM
    Thursday, April 4, 2013 4:05 AM

All replies

  • On 4/3/2013 12:28 PM, Alex Dove wrote:

    <snipped>

    You should post to the ASP.NET forums.

    <http://forums.asp.net/>

    Thursday, April 4, 2013 3:41 AM
  • Use the debugger to step through the code.  Which line takes all the time?

    Paul Linton

    • Marked as answer by Alex Dove Tuesday, May 7, 2013 8:40 PM
    Thursday, April 4, 2013 4:05 AM
  • Hi Alex Dove,

      Welcome MSDN Forum Support.

      You are more likely to get more efficient responses to ASP.NET issues at http://forums.asp.net where you can contact ASP.NET experts.

      Sincerely,

      Jason Wang


    Jason Wang [MSFT]
    MSDN Community Support | Feedback to us

    Friday, April 5, 2013 5:50 AM
  • We stopped using the Telerik controls and wrote the code in pure C#. I optimized the data performance by using the following items:

    • Pagination on GridView
    • Ajax Control Toolkit - specifically the AutoComplete for textboxes to provide "Google-like" filtering on content
    • Bound all 3 textbox controls to allow sub-querying of the results
    • Re-wrote queries to use views instead of querying on-the-fly DISTINCT queries. These new view only held the needed data.
    • Added indexes to the database tables
    • Re-wrote some database tables to contain all the needed data instead of writing nested queries to pull data from multiple tables and databases

    I used the debugger and watched the SQL Server Performance Analytics when my app hit the queries. There was a huge slowdown when my app tried to query the database because it was originally pull all 35K records during Page_Load and each PostBack. Also since my drop-down were not optimized it was doing a DISTINCT query on the 35K records which was an additional burden.

    Once I was able to optimize my controls and how the queries were written my performance increased significantly.

    Thank you for your valued help.

    Tuesday, May 7, 2013 8:40 PM