Radcomboboc with webmethods

[Fabio Cirillo]

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

[Hristo Valyavicharski]

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

[Fabio Cirillo]

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

[Hristo Valyavicharski]

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

[Fabio Cirillo]

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

[Hristo Valyavicharski]

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

[Fabio Cirillo]

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

[Hristo Valyavicharski]

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

[Fabio Cirillo]

So the important is the speed of the remote server where there's the sql database and the local speed internet

[Hristo Valyavicharski]

Yes that's correct.
 
All the best,
Hristo Valyavicharski
the Telerik team

[sujatha]

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