Speed up a web service for auto complete and avoid too many method calls

Question

So I've got my jquery autocomplete 'working,' but its a little fidgety since I call the webservice method each time a keydown() fires so I get lots of methods hanging and sometimes to get the "auto" to work I have to type it out and backspace a bit because i'm assuming it got its return value a little slow. I've limited the query results to 8 to mininmize time. Is there anything i can do to make this a little snappier? This thing seems near useless if I don't get it a little more responsive.

javascript

$("#clientAutoNames").keydown(function () {
        $.ajax({
            type: "POST",
            url: "WebService.asmx/LoadData",
            data: "{'input':" + JSON.stringify($("#clientAutoNames").val()) + "}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (data) {
                if (data.d != null) {
                    var serviceScript = data.d;
                }
                $("#autoNames").html(serviceScript);
                $('#clientAutoNames').autocomplete({
                    minLength: 2,
                    source: autoNames,
                    delay: 100,
                    focus: function (event, ui) {
                        $('#project').val(ui.item.label);
                        return false;
                    },
                    select: function (event, ui) {
                        $('#clientAutoNames').val(ui.item.label);
                        $('#projectid').val(ui.item.value);
                        $('#project-description').html(ui.item.desc);
                        pkey = $('#project-id').val;
                        return false;
                    }
                })
            .data("autocomplete")._renderItem = function (ul, item) {
                return $("<li></li>")
                    .data("item.autocomplete", item)
                    .append("<a>" + item.label + "<br>" + item.desc + "</a>")
                    .appendTo(ul);
            }
            }
        });
    });

WebService.asmx

<WebMethod()> _
Public Function LoadData(ByVal input As String) As String
    Dim result As String = "<script>var autoNames = ["
    Dim sqlOut As Data.SqlClient.SqlDataReader
    Dim connstring As String = *Datasource*

    Dim strSql As String = "SELECT TOP 2 * FROM v_Clients WHERE (SearchName Like '" + input + "%') ORDER BY SearchName"
    Dim cnn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connstring)
    Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(strSql, cnn)
    cnn.Open()

    sqlOut = cmd.ExecuteReader()
    Dim c As Integer = 0
    While sqlOut.Read()

        result = result + "{"
        result = result + "value: '" + sqlOut("ContactID").ToString() + "',"
        result = result + "label: '" + sqlOut("SearchName").ToString() + "',"
        'result = result + "desc: '" + title + " from " + company + "',"
        result = result + "},"

    End While
    result = result + "];</script>"
    sqlOut.Close()
    cnn.Close()

    Return result
End Function

I'm sure I'm just going about this slightly wrong or not doing a better balance of calls or something.

Greatly appreciated!

Answer 1

First off, you MUST adjust your query. You're not using parameterized queries, so you're just begging for a SQL injection attack.

Again, you MUST fix that first!

Once you've done that, play with the "delay" value. I assume that's the amount of time it waits between keystokes before sending the query to the server? You want it to wait for your users to pause in their typing, and then send the request, to avoid overloading your server with extraneous calls.

You should also use a try..catch..finally and dispose of your connection when you're done with it.

By the way, if your query is performing poorly, try adding an index on that column. 'Like' statements are terrible if you don't have an index...

Answer 2

You should first consider caching the results that are coming back from the database before doing anything else. This will limit the amount of queries that you are executing against the database. If you are running these queries through a single web server, consider using the System.Web.Cache for caching the results.

Any other advice on how to optimize will depend on more information than you have provided -- i.e. are you running across a web farm, how many database servers do you have, what are your performance requirements, etc.

I would also recommend you read the Wikipedia entry on SQL injection. I believe ASP.NET will prevent requests that include singe quotes by default, but you should still fix your code.

Answer 3

Some suggestions for "snappier"

Depending on the requirements, you might build in preventative measures on the client side against too many server calls and implied call latency. For example,

• auto complete can only kick in after at least 3 characters have been typed; otherwise the results are too broad to matter;
• the list should continually get smaller as the user types more, so cache the original list on the client-side and filter it as the user continues typing more chars. If the user backspaces past the original result set then grab a new one from the server and do the same with it.
• employ a client-side timer to issue calls no sooner than 2 or 3 seconds apart. keydown would detect a change in the text box and when your timer fires it looks at that flag to determine if it should pull data again

Granted, some of these might not contribute to snappiness, but it depends on how you use them. For example, the timer delay doesn't logically contribute to speed but then again comparing the alternative of the server being inundated with calls and responding slower, it might. Mileage always varies with this type of optimization and you often trade up server work for client speed.