This is a migrated thread and some comments may be shown as answers.
RadAutoCompleteBox and very large SQL table
27 Answers 396 Views
This is a migrated thread and some comments may be shown as answers.
Darryl
Top achievements
Rank 1
Darryl asked on 05 Dec 2012, 10:26 AM
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?

27 Answers, 1 is accepted

Sort by
0
Kalina
Telerik team
answered on 06 Dec 2012, 03:36 PM
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.
0
Darryl
Top achievements
Rank 1
answered on 10 Dec 2012, 08:01 AM
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? 
0
Kalina
Telerik team
answered on 13 Dec 2012, 11:26 AM
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.
0
Darryl
Top achievements
Rank 1
answered on 13 Dec 2012, 11:32 AM
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
0
Kalina
Telerik team
answered on 19 Dec 2012, 07:50 AM
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.
0
Darryl
Top achievements
Rank 1
answered on 20 Dec 2012, 07:41 AM
Thanks Kalina - I look forward to seeing what you can come up with.
0
Kalina
Telerik team
answered on 24 Dec 2012, 09:23 AM
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.
0
Alex Dybenko
Top achievements
Rank 2
answered on 28 Jan 2013, 10:29 AM
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
0
Tom
Top achievements
Rank 2
answered on 01 Mar 2013, 03:44 PM
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.
0
Bozhidar
Telerik team
answered on 04 Mar 2013, 08:16 AM
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.
0
Tom
Top achievements
Rank 2
answered on 04 Mar 2013, 12:10 PM
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
0
Bozhidar
Telerik team
answered on 04 Mar 2013, 12:27 PM
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.
0
Tom
Top achievements
Rank 2
answered on 04 Mar 2013, 03:13 PM
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
0
BB
Top achievements
Rank 1
answered on 07 Mar 2013, 10:33 PM
Is there something similar to this for the WPF version of the autocompletebox ?
0
Ivo
Telerik team
answered on 11 Mar 2013, 07:58 AM
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.
0
Muthukumar
Top achievements
Rank 2
answered on 02 May 2013, 08:48 PM
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
0
Bozhidar
Telerik team
answered on 03 May 2013, 04:02 PM
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.
0
Muthukumar
Top achievements
Rank 2
answered on 03 May 2013, 04:49 PM
Thank you so much.!

Regards,
Muthukumar V
0
Massimiliano
Top achievements
Rank 1
answered on 10 Jul 2013, 07:18 PM
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?
0
Bozhidar
Telerik team
answered on 11 Jul 2013, 08:29 AM
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.
0
Massimiliano
Top achievements
Rank 1
answered on 11 Jul 2013, 09:31 AM
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
0
Vasil
Telerik team
answered on 15 Jul 2013, 08:36 AM
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.
0
Massimiliano
Top achievements
Rank 1
answered on 15 Jul 2013, 09:47 AM
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.
0
Bernard
Top achievements
Rank 2
answered on 18 Jul 2013, 01:49 AM
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.
0
Mohammed BENKHDAR
Top achievements
Rank 1
answered on 14 Aug 2014, 05:31 PM
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?
0
Nencho
Telerik team
answered on 19 Aug 2014, 07:29 AM
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.

 
0
Mohammed BENKHDAR
Top achievements
Rank 1
answered on 10 Feb 2015, 01:30 PM
Hello,

Thank you it's works fine
Asked by
Darryl
Top achievements
Rank 1
Answers by
Kalina
Telerik team
Darryl
Top achievements
Rank 1
Alex Dybenko
Top achievements
Rank 2
Tom
Top achievements
Rank 2
Bozhidar
Telerik team
BB
Top achievements
Rank 1
Ivo
Telerik team
Muthukumar
Top achievements
Rank 2
Massimiliano
Top achievements
Rank 1
Vasil
Telerik team
Bernard
Top achievements
Rank 2
Mohammed BENKHDAR
Top achievements
Rank 1
Nencho
Telerik team
Share this question
or