开发者

Whats the most efficient way to handle this function?

a newbie needs some help with logic flow:

I'm using the Auto Complete extender control that ships with the Asp.net Ajax toolkit. This extender is hooked up to a web service, which will run a function that queries the database each time a user types in to the text box. The database we will be working with will have thousands and thousands of records, so performance is what I'm shooting for. Here is my function in the web service:

   Public Function PatronList(ByVal prefixText As String, ByVal count As Integer) As String()

    Dim MyPatrons As New List(Of String)

    Dim MyConn = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Dim sqlPatronList As New SqlDataSource(MyConn, "spBarredList " & prefixText)
    Dim tblPatronList As System.Data.DataView = sqlPatronList.Select(New DataSourceSelectArguments)

    Dim CurPatronRow As Long
    Dim MaxPatronRow As Long = tblPatronList.Count
    For CurPatronRow = 0 To MaxPatronRow - 1

        MyPatrons.Add(tblPatronList.Item(CurPatronRow).Item("FullName"))
    Next

    Return MyPatrons.ToArray

End Function

"spBarredList" is a stored procedure that does a like statement on 3 columns when 3 characters are passed in. So here's my question:

Would it be more efficient to do it this way, or just select all the records into a dataset, add them into the array, and then search the arra开发者_C百科y? Also is it bad that a new instance of a data source and list are getting created each time this function runs?

Thanks in advance.


It is generally better to let SQL do the filtering (from an efficiency standpoint). This is, of course, assuming that your SP is not horribly inefficient. Generally though, SQL is much faster at filtering data than traversing an array would be.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜