Radcomboboc with webmethods

11 posts, 0 answers
  1. Fabio Cirillo
    Fabio Cirillo avatar
    181 posts
    Member since:
    Jan 2010

    Posted 16 Jan 2013 Link to this post

    Hi,
    i wrote this code into vb net:
    Private Const ItemsCityPerRequest As Integer = 10
    Public Shared Function GetCityNames(ByVal context As RadComboBoxContext) As RadComboBoxData
    Dim data As DataTable = Getcomuni(context.Text)
    Dim comboData As New RadComboBoxData()
    Dim itemOffset As Integer = context.NumberOfItems
    Dim endOffset As Integer = Math.Min(itemOffset + ItemsCityPerRequest, data.Rows.Count)
    comboData.EndOfItems = endOffset = data.Rows.Count
    Dim result As New List(Of RadComboBoxItemData)(endOffset - itemOffset)
    For i As Integer = itemOffset To endOffset - 1
    Dim itemData As New RadComboBoxItemData()
    itemData.Text = data.Rows(i)("descrizione").ToString()
    itemData.Value = data.Rows(i)("id").ToString()
    result.Add(itemData)
    Next
    REM ottengo il messaggio dei record selezionati
    comboData.Message = GetStatusMessage(endOffset, data.Rows.Count)
    comboData.Items = result.ToArray()
    Return comboData
    End Function
    Private Shared Function GetStatusMessage(ByVal offset As Integer, ByVal total As Integer) As String
    If total <= 0 Then
    Return "Nessun dato"
    End If
    Return [String].Format("Record <b>1</b>-<b>{0}</b> out of <b>{1}</b>", offset, total)
    End Function
    Private Shared Function Getcategoria(ByVal text As String) As DataTable
    Dim adapter As New SqlDataAdapter("SELECT * from Tab_categorie WHERE descrizione LIKE @text + '%'", ConfigurationManager.ConnectionStrings("TrycontactString").ConnectionString)
    adapter.SelectCommand.Parameters.AddWithValue("@text", text)
    Dim data As New DataTable()
    adapter.Fill(data)
    Return data
    End Function

    and

    aspx code:
    <telerik:RadComboBox ID="Radcity" Runat="server" Height="145px"
    LoadingMessage="Caricamento..." Width="270px"
    EmptyMessage="Seleziona la tua città" EnableLoadOnDemand="True"
    EnableVirtualScrolling="True" ShowMoreResultsBox="True" Filter="StartsWith">
    <WebServiceSettings Method="GetCityNames" Path="index.aspx" />
    </telerik:RadComboBox>

    now, the table witn name tab_comuni has 8152 record, and I would like the code that loads the records was fast.
    then how do I change the sql query? because when I click on the combobox before you see the 10 selected records, I have to wait several seconds and then when I flow the combo until the end and start loading the other 10 records and so on, waits in the combo empty space. I welcome any advice.

    or is there a way to display the drop-down only when the user begins to writing andthe search text? I think it is faster and avoids loading in memory over 8100 records.

    I could also use the object AutoCompleteBox with webMethods?

    hello and thank you


  2. Hristo Valyavicharski
    Admin
    Hristo Valyavicharski avatar
    975 posts

    Posted 18 Jan 2013 Link to this post

    Hi Fabio,

    There are few things you can do to improve the SQL search performance.
    First enable  Full-Text Search and Create Full-Text Catalog, the replace LIKE with CONTAINS.

    Your SQL query will be something similar to: 

    SELECT * FROM Tab_categorie WHERE CONTAINS(descrizione, @text)

    for additional information please check this out: Improve the Performance of Full-Text Indexes

    Hope this helps

    All the best,
    Hristo Valyavicharski
    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. Fabio Cirillo
    Fabio Cirillo avatar
    181 posts
    Member since:
    Jan 2010

    Posted 18 Jan 2013 Link to this post

    Hi
    thanks you for the help, but now i dont use the index full-text because my provider does not have this service. So I have to use only the "like". Now how do you think I can find the solution only when the user starts writing the research? Because with the code I posted as soon as the focus is on radcombo immediately start searching for the 8100 record. Maybe I could use a trick that is defined by default to load the records in order of the letter "A". What do you think
  4. Hristo Valyavicharski
    Admin
    Hristo Valyavicharski avatar
    975 posts

    Posted 22 Jan 2013 Link to this post

    Hello Fabio,

    Using RadComboBox with WebServices is the best way to achieve good performance. I would recommend you to try to find where exactly is the delay. Then optimize this peace of code. For example: try to run the 'like' SQL command without the RadComboBox and see how long does it take to filter this 8000 items. You could run it directly on the SQL server and after that call the WebMethod to see if this is SQL or a connection throughput issue. According to me you should improve the SQL because the combo just displays the returned results. 

    Please make sure that EnableItemCaching property is set to true. It indicates whether the combobox should cache items loaded on demand via WebService. Other thing you can try is to set MinFilterLength. It sets the minimum length of the typed text before the control initiates a request for new Items when EnableLoadOnDemand is True

    Please let me know if I can assist you further

    Regards,
    Hristo Valyavicharski
    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.
  5. Fabio Cirillo
    Fabio Cirillo avatar
    181 posts
    Member since:
    Jan 2010

    Posted 22 Jan 2013 Link to this post

    Hi,
    unfortunately I can not use the full text of sql. I can further improve my sql syntax in your opinion? If so, how? I will try to settle the options in the AutoCompleteBox as you told me but because with the radcombobox is better to use the webservice? I thought the WebMethod was the best performance as in speed of data processing.
    I run this sql query:

    SELECT * from Tab_comuni WHERE description LIKE'' + '%'

    directly on sql server 2008

    that would be the same as that used in the webMethods and the processing time was: 00:00:02.5231443

    then I run also this other query:

    select * from where tab_comuni PATINDEX ('' + '%', description)> 0

    and the processing time was: 00:00:02.3291332

    therefore faster. Why patindex is faster than like?

    in these pages I never find the asmx page to see how the webservice ... how should I do?
    http://demos.telerik.com/aspnet-ajax/combobox/examples/populatingwithdata/autocompletesql/defaultvb.aspx
    http://demos.telerik.com/aspnet-ajax/combobox/examples/programming/performance/defaultvb.aspx
  6. Hristo Valyavicharski
    Admin
    Hristo Valyavicharski avatar
    975 posts

    Posted 24 Jan 2013 Link to this post

    Hi Fabio,

    You can see the .asmx code as you click on the Tab in Demo and select ComboBoxWebService.vb.

    Two seconds is not that fast. Every time you type a letter in the combo it calls GetCityNames webmethod which calls the database and execute the sql query. This will take about 2 seconds for every letter. For example if you want to type "hello" you will execute this sql query 5 times or total about 10 seconds.

    It might be helpful to add Clustered Index on that column in the SQL server but it wouldn't be faster than the Full-Text search. Last but not least your SQL server may need a hardware upgrade. Please watch this video to see how my local machines handles 250 000 records without Full-Text or Indexes. It takes less than a second to execute the same query. 

    Kind regards,
    Hristo Valyavicharski
    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.
  7. Fabio Cirillo
    Fabio Cirillo avatar
    181 posts
    Member since:
    Jan 2010

    Posted 24 Jan 2013 Link to this post

    hi,
    first of all thanks for the video. According to me you say it's better to use a webservice and not a page methos to load data from database to a combo or autocomplete? Third I wanted to clarify and pesno it is very important that the web page loads on my laptop where I write the code, but the server where the database is not on my computer but remto on a server in America. Do you think I live in Italy and the line and I think this could create problems of speed. When you send the web pages on a server in America, I will direct the tests with a different reality that web pages and databases in the same provider.
    I'll show you how I created the table in sql maybe there is something to improve. :-)

    CREATE TABLE [dbo].[Tab_comuni](
        [Id] [smallint] NOT NULL,
        [Idprovincia] [tinyint] NULL,
        [Idregione] [tinyint] NULL,
        [Descrizione] [nvarchar](30) NULL,
     CONSTRAINT [PK_Tab_comuni] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
     
    GO
  8. Hristo Valyavicharski
    Admin
    Hristo Valyavicharski avatar
    975 posts

    Posted 28 Jan 2013 Link to this post

    Hi Fabio,

    There is nothing you can do, to improve the script you use to create the table. The distance is not important. This what matters is the connection throughput and the speed of your sql server. 

    Regards,
    Hristo Valyavicharski
    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.
  9. Fabio Cirillo
    Fabio Cirillo avatar
    181 posts
    Member since:
    Jan 2010

    Posted 28 Jan 2013 Link to this post

    So the important is the speed of the remote server where there's the sql database and the local speed internet
  10. Hristo Valyavicharski
    Admin
    Hristo Valyavicharski avatar
    975 posts

    Posted 29 Jan 2013 Link to this post

    Yes that's correct.
     
    All the best,
    Hristo Valyavicharski
    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.
  11. sujatha
    sujatha avatar
    2 posts
    Member since:
    Dec 2017

    Posted 19 Feb 2018 in reply to Fabio Cirillo Link to this post

    hai fabio 

    this is good question to clarify the doubt.

    no there is no way to display the drop-down, when user begins to writing and the search text in web methods

Back to Top