RadAutoCompleteBox and very large SQL table

28 posts, 0 answers
  1. Darryl
    Darryl avatar
    6 posts
    Member since:
    Jun 2011

    Posted 05 Dec 2012 Link to this post

    I have a SQL Server table with over 2 million rows.  It's well optimized and I have a stored procedure that, given the start of a word, can return 10 matching records very quickly.  However, I'm trying to see if this can be used via the RadAutoCompleteBox. 

    My test page is not loading (no error but it's taking forever to load) and I am assuming this is because it's trying to look at ALL 2 million records?

    <telerik:RadAutoCompleteBox runat="server" ID="RadAutoCompleteBox1" InputType="Text"
                    DataSourceID="SqlDataSource1" DataTextField="Title" Filter="StartsWith">
    </telerik:RadAutoCompleteBox>
     
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
       ConnectionString="<%$ ConnectionStrings:MyConnectionString%>"
       SelectCommand="SELECT Title FROM dbo.AllKeywords ORDER BY Title">
    </asp:SqlDataSource>

    Am I doing something wrong?  Does anyone have experience in using RadAutoCompleteBox on very large data sets?
  2. Kalina
    Admin
    Kalina avatar
    918 posts

    Posted 06 Dec 2012 Link to this post

    Hello Darryl,

    RadAutoCompleteBox performs a query for data only if the user has typed in it some text.
    At initial page loading the control dropdown is empty - there is no data in it and it is hidden.

    There has to be something else that slows down the loading of your page.
    Are there any other controls on it?  
    Can you reproduce the issue with a plain test page that contains only one RadAutoCompleteBox?

    All the best,
    Kalina
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Darryl
    Darryl avatar
    6 posts
    Member since:
    Jun 2011

    Posted 10 Dec 2012 Link to this post

    Hi Kalina,

    The issue I have is not on the page load - that loads quickly.  It's when I start typing - that's where the unacceptable delay happens.  I tried reducing the number of rows in the table from 29,000 to 100 and it's lightning fast.  This would seem to suggest it's the control that is being directly affected by the number of rows.

    The database/table is well optimized and a stored procedure I have written returns the results (using SQL Server LIKE) very quickly indeed.  So that leads me to suspect that the control is not doing its job very efficiently?  Is it possible to use my stored procedure instead of just providing a SELECT * FROM table approach?

    Can you provide more detailed information on how this control is fetching the results? 
  5. Kalina
    Admin
    Kalina avatar
    918 posts

    Posted 13 Dec 2012 Link to this post

    Hello Darryl,

    RadAutoCompleteBox performs a query for data after the user has typed some text in it.
    Only the rows that match the text entered are returned and rendered on client-side.
    Whole data retrieval logic uses Load On Demand implemented internally.

    Regarding your question about the stored procedure - since the RadAutoCompleteBox can be databound to SQLDataSource - you can use your stored procedure as select command.

    All the best,
    Kalina
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  6. Darryl
    Darryl avatar
    6 posts
    Member since:
    Jun 2011

    Posted 13 Dec 2012 Link to this post

    I realise I can use the Stored Procedure as a Select Command but I'm assuming I can't use parameters, right? 

    The bit I'm interested in finding out is the "Only the rows that match the text entered" statement of your response - how is the match performed?

    Does Telerilk have an example of RadAutoCompleteBox working against a dataset of over e.g. 10,000 or 20,000 rows?

    Thanks
  7. Kalina
    Admin
    Kalina avatar
    918 posts

    Posted 19 Dec 2012 Link to this post

    Hello Darryl,


    Please accept my apologies for the delay.

    The RadAutoCompleteBox is a control that has been created to filter data using the text typed in it and you do not need to add any custom logic to achieve this.
    The matching logic is implemented internally - this is the part of the RadAutoCompleteBox implementation.
    So the control uses Load On Demand via callback internally.

    I managed to create a large database table and I am currently testing how the RadAutoCompleteBox works with large amounts of data.
    I will try to prepare a working sample for you as soon as I can.
    Thank you for your patience and excuse me for any inconvenience caused.




    Kind regards,
    Kalina
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  8. Darryl
    Darryl avatar
    6 posts
    Member since:
    Jun 2011

    Posted 20 Dec 2012 Link to this post

    Thanks Kalina - I look forward to seeing what you can come up with.
  9. Kalina
    Admin
    Kalina avatar
    918 posts

    Posted 24 Dec 2012 Link to this post

    Hello Darryl,

    We decided that it will be useful to add a new functionality in RadAutoCompleteBox that allows the programmers to change the select query that control performs to retrieve data.
    I am afraid that I am not able to give an exact time frame for this, but I will update this forum thread once we are ready.


    Greetings,
    Kalina
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  10. Alex Dybenko
    Alex Dybenko avatar
    119 posts
    Member since:
    Jan 2005

    Posted 28 Jan 2013 Link to this post

    Hi Kalina,
    Looking forward  for that functionality too!
    It is impossible to use control with large table

    Was too fast, made it working with Web service binding...

    Alex
  11. Tom
    Tom avatar
    7 posts
    Member since:
    Apr 2006

    Posted 01 Mar 2013 Link to this post

    For me the same. In demo mode everbody was happy. Now with real data it is not to use in many cases. A "MaxRowReturn" would be enough in my opinion.
  12. Bozhidar
    Admin
    Bozhidar avatar
    1102 posts

    Posted 04 Mar 2013 Link to this post

    Hello Thomas,

    With the 2013 Q1 release of RadControls for ASP.NET AJAX come two great enhancements for RadAutoCompleteBox.

    First of all we inspected and revamped the server filtering mechanism and increased the performance in server binding scenarios. So now your AutoCompleteBox can handle thousands of records easily out of the box, without you writing any additional logic.

    We also added the OnDataSourceSelect event for scenarios with extremely heavy loads, so that you can filter the items directly on the SQL Server. The event is raised just before the select query is sent to the datasource, and exposes the following parameters:
    - an instance of the DataSource object, from which you can get and modify the SELECT command that goes to the SQL Server
    - the FilterString that's currently typed in the AutoCompleteBox, so that you can use it in the SELECT command.

    I've attached a small sample page demonstrating how you can use the OnDataSourceSelect event to modify the SELECT command of an SqlDataSource so that the filtering is done on the SQL Server, and also the results returned are limited to 100 ( the archive does not include the database, due to it's size )

    Greetings,
    Bozhidar
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  13. Tom
    Tom avatar
    7 posts
    Member since:
    Apr 2006

    Posted 04 Mar 2013 Link to this post

    Hello Bozhidar,

    thank you very much so far, that seems to be, what I was looking for.

    When I try your example in my VB solution I get this error (ReadOnlyDataSource):

                Dim oDS As SqlDataSource
                oDS = CType(e.DataSource, SqlDataSource)


    http://snap.ashampoo.com/QsQm2pBv

    What can be the problem there?

    Greetings!

    Thomas
  14. Bozhidar
    Admin
    Bozhidar avatar
    1102 posts

    Posted 04 Mar 2013 Link to this post

    Hi Thomas,

    The sample I provided was using an ASP.NET SqlDataSource component, but it seems you are using another way to bind RadAutoCompleteBox. Could you elaborate on how exactly are you doing that.
     

    Greetings,
    Bozhidar
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  15. Tom
    Tom avatar
    7 posts
    Member since:
    Apr 2006

    Posted 04 Mar 2013 Link to this post

    Hello Bozhidar,

    thank you very much again! I did not think about it, that I am binding the AutoCompleteBox with SqlDataAdapter and a DataTable initialy. Now everything works perfect!

    Greetings and thank you!

    Thomas
  16. BB
    BB avatar
    3 posts
    Member since:
    Jan 2007

    Posted 07 Mar 2013 Link to this post

    Is there something similar to this for the WPF version of the autocompletebox ?
  17. Ivo
    Admin
    Ivo avatar
    390 posts

    Posted 11 Mar 2013 Link to this post

    Hello Brian,

    In the WPF version of RadAutoCompleteBox huge amounts of data can be handled easily. I would suggest you to use the use the SearchTextChanged event and into the event handler to write your filtering logic. Here is sample code:
    private FilterServiceClient client;
     
    public MainWindow()
    {
        InitializeComponent();
        this.client = new ServiceReference1.FilterServiceClient();
        this.client.GetDataCompleted += this.OnClientGetDataCompleted;
    }
     
    private void OnClientGetDataCompleted(object sender, GetDataCompletedEventArgs e)
    {
        this.AutoCompleteBox.ItemsSource = e.Result;
        this.AutoCompleteBox.Populate(this.AutoCompleteBox.SearchText);
    }
     
    private void OnRadAutoCompleteBoxSearchTextChanged(object sender, EventArgs e)
    {
        this.client.GetDataAsync(this.AutoCompleteBox.SearchText);
    }

    Greetings,
    Ivo
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  18. Muthukumar
    Muthukumar avatar
    6 posts
    Member since:
    May 2013

    Posted 02 May 2013 Link to this post

    Hi,

    I am using objectDatasource to bind the RAdAutoCompleteTextbox, but the getmethod will return huge number of records as it is getting the information from Active directory of the organisation.Can you please help me to use the RadAutocompletetextbox effectively.

    Please find the code as below.

    <telerik:RadScriptManager runat="server" ID="RadScriptManager1" />
        <div>
            <telerik:RadAutoCompleteBox ID="RadAutoCompleteBox1" runat="server" Width="200" DropDownWidth="200"
             DataSourceID="ObjectDataSource1" DataValueField="EmailAddress" 
                DataTextField="Name" ondatasourceselect="RadAutoCompleteBox1_DataSourceSelect" >
            </telerik:RadAutoCompleteBox>
            <asp:Button runat="server" ID="btnGetList" Text="GetList" 
                onclick="btnGetList_Click" />
                <telerik:RadListView ID="RadListView1" runat="server">
                </telerik:RadListView>
        </div>
         <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="AutoComplete.ADSHelper" SelectMethod="GetEmails">
         </asp:ObjectDataSource>
        </form>


    And the Getmethod

    PrincipalContext context = new PrincipalContext(ContextType.Domain);
                UserPrincipal user = new UserPrincipal(context);
                user.EmailAddress = string.Concat(filterString, "*");
                PrincipalSearcher searcher = new PrincipalSearcher(user);
                DataTable table = new DataTable();

                table.Columns.Add(new DataColumn("Name"));
                table.Columns.Add(new DataColumn("EmailAddress"));

                if (!string.IsNullOrEmpty(filterString))
                {
                    foreach (UserPrincipal result in searcher.FindAll())
                    {
                        DataRow row = table.NewRow();
                        row["Name"] = result.Name;
                        row["EmailAddress"] = result.EmailAddress;
                        table.Rows.Add(row);
                    }
                }

                return table;

    I would like to know is there a way to pass the filterstring value as a selectmethod parameter to ObjectDataSource.?

    Thank you,
    Muthukumar V
  19. Bozhidar
    Admin
    Bozhidar avatar
    1102 posts

    Posted 03 May 2013 Link to this post

    Hello,

    I've attached a small sample web site that demonstrates how you can achieve the desired funtionality.
     

    All the best,
    Bozhidar
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  20. Muthukumar
    Muthukumar avatar
    6 posts
    Member since:
    May 2013

    Posted 03 May 2013 Link to this post

    Thank you so much.!

    Regards,
    Muthukumar V
  21. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 10 Jul 2013 Link to this post

    This is exactly what I was looking for BUT it seems it's not implemented in the GridAutoCompleteColumn, can you confirm?
    So hot to use GridAutoCompleteColumn for "google like" column filtering against a huge dataset being able to filter and return only "TOP n" records?
  22. Bozhidar
    Admin
    Bozhidar avatar
    1102 posts

    Posted 11 Jul 2013 Link to this post

    Hello Massimiliano,

    You can handle the Grid's ItemCreated event, where you can find the AutoCompleteBox and attach the OnDataSourceSelect event to it programmatically.
     

    Regards,
    Bozhidar
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  23. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 11 Jul 2013 Link to this post

    Thank you Bozhidar this could be the solution I was looking for... but I'm a complete newbie both to OOP and to Telerik controls could you just provide us with the 2 lines of code in the ItemCreated event to attach to the AutoCompleteBox the DataSourceSelect event?
    Also this should work for multiple GridAutoCompleteColumn, will this work for all of them or should we give unique names to the column and also check for that?
    I guess is also possible to cancel the filtering for example if characters in the AutoCompleteBox are less than... x
    One last question, since you've been so kind... is it possible to provide a simple array/list/Iqueryable of strings as return data type from the objectdatasource or are we forced to use a structured data type? I'm asking because it seem the GridAutoCompleteColumn with ObjectDataSource expects DataTextField="" DataValueField="" wich would make no sense for example with a list of strings.

    I think this is a very important implementation for many people, otherwise the new GridAutoCompleteColumn would be almost useless in real life scenarios (you just press a key and have 10.000 results suggested)

    Thanks in advance for your kind answer
  24. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 15 Jul 2013 Link to this post

    Hello Massimiliano,

    In this help topic it is described how to access controls in RadGrid during the ItemCreated event.
    http://www.telerik.com/help/aspnet-ajax/grid-accessing-cells-and-rows.html

    You can handle the OnClientRequesting event of the RadAutoCompleteBox to cancel the filtering if less than specified values chars are entered.

    function OnClientRequesting(sender, args) {
        if (args.get_text().length < 4) {
            args.set_cancel(true);  
        }
    }


    Regards,
    Vasil
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  25. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 15 Jul 2013 Link to this post

    Hallo Vasil thanks much appreciated (even the js function). Too bad this is not applicable in a

    GridAutoCompleteColumn

    scenario as well since it doesn't have the OnClientRequesting event.
    Is the "...and attach the OnDataSourceSelect event to it programmatically" that I will struggle to obtain but I will try and report back.
  26. Bernard
    Bernard avatar
    21 posts
    Member since:
    Nov 2012

    Posted 17 Jul 2013 Link to this post

    Any of you here has actually tried SqlDataReader Instead if you only meant to fill up the combobox with Sql Result Set??

    here's some facts on SqlDataAdapter and SqlDataReader:

    SqlDataReader:

    • Holds the connection open until you are finished (don't forget to close it!).
    • Can typically only be iterated over once
    • Is not as useful for updating back to the database

    On the other hand, it:

    • Only has one record in memory at a time rather than an entire result set (this can be huge)
    • Is about as fast as it you can get for that one iteration
    • Allows you start processing results sooner

    SqlDataAdapter/DataSet

    • Lets you close the connection as soon it's done loading data, and may even close it for you automatically
    • All of the results are available in memory
    • You can iterate over it as many times as you need, or even look up a specific record by index
    • Has some built-in functionality for updating back to the database

    At the cost of:

    • Much higher memory use
    • You wait until all the data is loaded before using any of it
    and here's a sample of my combobox using SqlDataReader:

    ASPX
    <telerik:GridTemplateColumn HeaderText="Consignee">
                                <ItemTemplate>
                                     <%#DataBinder.Eval(Container.DataItem, "Cons")%>
                                </ItemTemplate>
                                <EditItemTemplate>
                                     <telerik:RadComboBox runat="server" ID="RadComboBox6" DataTextField="Cons" OnItemsRequested="RadComboBox6_ItemsRequested"
                       
                                         EnableLoadOnDemand="true"
                                          DataValueField="ConsId" OnSelectedIndexChanged="RadComboBox6_SelectedIndexChanged"
                                          SelectedValue='<%#Bind("ConsId") %>'>
                                     </telerik:RadComboBox>
                                </EditItemTemplate>
                           </telerik:GridTemplateColumn>

    C#

    protected void RadComboBox6_ItemsRequested(object sender, RadComboBoxItemsRequestedEventArgs e)
            {
                string sql = "SELECT [CustId], [CustName] FROM [Customers] WHERE CustName LIKE @CustName + '%'";
                var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ZEUS"].ConnectionString);
                var cmd = new SqlCommand(sql, con);
                var reader = default(SqlDataReader);
     
                cmd.Parameters.AddWithValue("@CustName", e.Text);
     
                try
                {
                    con.Open();
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    var obj = default(object);
                    RadComboBox comboBox = (RadComboBox)sender;
                    while (reader.Read())
                    {
                        obj = reader["CustId"];
                        var id = obj == DBNull.Value ? 0 : Convert.ToInt64(obj);
     
                        obj = reader["CustName"];
                        var name = obj == DBNull.Value ? string.Empty : Convert.ToString(obj);
     
                        RadComboBoxItem item = new RadComboBoxItem();
                        item.Text = name;
                        item.Value = id.ToString();
                        comboBox.Items.Add(item);
                    }
                }
                finally
                {
                    if (reader != null && !reader.IsClosed) reader.Close();
                    if (con != null && con.State == ConnectionState.Open) con.Close();
                }
            }

    just my $0.02 hope it made a difference.
  27. Mohammed BENKHDAR
    Mohammed BENKHDAR avatar
    7 posts
    Member since:
    May 2010

    Posted 14 Aug 2014 Link to this post

    Hello All,

    I will get you back to the first topic of this post.
    I have about just 20 000 record in may table (indexed) and it takes 10 second to find the result if it is located fery far in the list !!

    <telerik:RadAutoCompleteBox ID="ddlOwner" runat="server" EnableClientFiltering="true" Width="250" DropDownHeight="150"
                                                            DropDownWidth="250" Filter="Contains" ToolTip="IT person reporting the incident \n Or person in charge of resolving the incident"
                                                            DropDownPosition="Automatic" MaxResultCount="10"  MinFilterLength="1" InputType="Text" ShowLoadingIcon="true" TextSettings-SelectionMode="Single"
                                                            DataSourceID="SqlDataSource1" DataTextField="display" DataValueField="login" >
                                                        </telerik:RadAutoCompleteBox>
     <asp:SqlDataSource 
        ID="SqlDataSource1" 
        ConnectionString="<%$ ConnectionStrings:DBConnectionString %>" runat="server" 
        SelectCommand="p_GetUser SelectCommandType="StoredProcedure"></asp:SqlDataSource>
     
     can you help me please to resume the delay of response?
  28. Nencho
    Admin
    Nencho avatar
    1457 posts

    Posted 19 Aug 2014 Link to this post

    Hello Mohammed,

    In such heavy data scenario, we recommend the usage of the OnDataSourceSelect server-side event of the RadAutoCompleteBox, which is triggered just before the query to the underlying database is fired. You could use the event, in order to modify the parameters of the request via the SelectCommand. Thus, for example you could set the amount of items returned for the datasource, only for the specific request. Please refer to the following documentation article, describing in details the suggested event :

    http://www.telerik.com/help/aspnet-ajax/autocompletebox-ondatasourceselect.html

    Regards,
    Nencho
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  29. Mohammed BENKHDAR
    Mohammed BENKHDAR avatar
    7 posts
    Member since:
    May 2010

    Posted 10 Feb 2015 in reply to Nencho Link to this post

    Hello,

    Thank you it's works fine
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017