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.
精彩评论