RadAutoCompleteBox and very large SQL table

[Darryl]

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?

[Kalina]

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

[Darryl]

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? 

[Kalina]

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

[Darryl]

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

[Kalina]

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

[Darryl]

Thanks Kalina - I look forward to seeing what you can come up with.

[Kalina]

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

[Alex Dybenko]

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

[Tom]

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.

[Bozhidar]

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

[Tom]

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

[Bozhidar]

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

[Tom]

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

[BB]

Is there something similar to this for the WPF version of the autocompletebox ?

[Ivo]

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

[Muthukumar]

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

[Bozhidar]

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

[Muthukumar]

Thank you so much.!

Regards,
Muthukumar V

[Massimiliano]

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?

[Bozhidar]

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

[Massimiliano]

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

[Vasil]

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

[Massimiliano]

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.

[Bernard]

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.

[Mohammed BENKHDAR]

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?

[Nencho]

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

 

 

[Mohammed BENKHDAR]

Hello,

Thank you it's works fine