Load-on-demand Slow Performance

[Bob Gibilaro]

My "load on demand" is extremely slow.  There are about 250,000 records in the table but I understood that even with this number of records the load-on-demand should have acceptable performance.  Please take a look at the code here and let me know if there's a setting somewhere which I'm missing.  When I click in the combobox it says "loading..." for minutes before anything shows up in the list.  Thanks.

ASPX:

<telerik:RadComboBox ID="rcbProductCode" runat="server" Width="250px" Height="200px"
 AllowCustomText="True" ShowToggleImage="True" ShowMoreResultsBox="true"
 EnableLoadOnDemand="True" MarkFirstMatch="True"
 OnItemsRequested="rcbProductCode_ItemsRequested" EnableVirtualScrolling="true" >
</telerik:RadComboBox>

VB.NET:

Protected Sub rcbProductCode_ItemsRequested(ByVal o As Object, ByVal e As Telerik.Web.UI.RadComboBoxItemsRequestedEventArgs)

Dim sql As String = _
    "SELECT PRODUCT = RTRIM(PRODUCT_CODE) + ' - ' + RTRIM(PRODUCT_DESC), PRODUCT_CODE " & _
    "FROM AllianceSync.DBO.FDA_PRODUCT (NOLOCK)  " & _
    "WHERE PRODUCT_CODE like '" + e.Text + "%'"

Dim connTmp As New SqlConnection(strConnString)
connTmp.Open()
Dim adapter As New SqlDataAdapter(sql, connTmp)
Dim data As New DataTable()
adapter.Fill(data)

Dim rcb As Telerik.Web.UI.RadComboBox = CType(o, Telerik.Web.UI.RadComboBox)
Try
    Dim itemsPerRequest As Integer = 10
    Dim itemOffset As Integer = e.NumberOfItems
    Dim endOffset As Integer = itemOffset + itemsPerRequest
    If endOffset > data.Rows.Count Then
        endOffset = data.Rows.Count
    End If

    If endOffset = data.Rows.Count Then
        e.EndOfItems = True
    End If
    Dim i As Integer = itemOffset
    While i < endOffset
        rcb.Items.Add(New Telerik.Web.UI.RadComboBoxItem(data.Rows(i)("PRODUCT").ToString(), data.Rows(i)("PRODUCT_CODE").ToString()))
        i = i + 1
    End While

    If data.Rows.Count > 0 Then
        e.Message = [String].Format("Items <b>1</b>-<b>{0}</b> out of <b>{1}</b>", endOffset.ToString(), data.Rows.Count.ToString())
    Else
        e.Message = "No matches"
    End If
Catch
    e.Message = "No matches"
End Try

[paul]

Hi Bob,

Have you tried anything like adding some tracing to locate where the long load times are?

I would check to see where slow execution is occurring... for example how long does it take for the adapter.fill to complete. and how long for the while loop to finish.


-Paul

[Bob Gibilaro]

Paul,
I stepped through in debug and it appears that the slowness occurs at the adapter.Fill(data).  It took nearly 3 minutes to complete.  Once I got past that line I did an F5 to "Continue" and the rest of the code completed immediately.
Thanks

[paul]

I thought that would be the problem, I've see large adapter fills run very slowly. I have used data readers before to work around that...

You might try something like this but I cant say wether it will really help or not.

dim reader as datareader = cmd
dim I as int = 0
while reader.read()

if i >= offset and I <= items per request
Add to item list code here... reader("Colname")

if i > items per request
reader.dispose or stop
exit while

i++
end while

This isn't complete or accurate code(obviously) I'm at work and don't have time to go into detail, but I think the idea will work.

Let me know how things go.


-Paul