Super fast autocomplete grinds to virtually a halt when uploaded to host

07-06-2009 3:29 PM |

Hi,

 Any thoughts on this one? I have a data grid templated autocomplete, that is super fast on my local machine, run from within VS. It's selecting items via a web service, also on my PC, from a database table, also on my PC, that has 5000 rows.

After deploying the webservice to my host, the autocomplete takes between 9 and 15 seconds to return. My host has a seperate database server from the web server, but I still wouldn't expect it to be that slow. I've played with autocomplete settings such as minimum populate time, as well as indexing the table, but I don't see how that would make a difference, seeing as it's fine on my PC.

The service is using LINQ to SQL to retreive the data.

Here are some findings:

Webservice on a remote server - Database on remote server
Slow: Up to 15seconds

WebService on remote server - Database local
Slow: Up to 15 seconds

WebService Local - Database local
Fast (No factory markup on service): 2 seconds

WebService local - database remote
Fastish (No factory markup on service): 4 seconds.

Any ideas?

Drew

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-06-2009 5:25 PM |

No ideas anyone? I just used fiddler to see what was going on and was surprised to see the whole 5000 rows returned? Is fiddler reporting what is returning to my client pc or what's happening on the server? If it's my client pc, then returning the whole table seems a bit inefficient. Can someone explain how the silverlight autocomplete works? When I used the vanilla Ajax autocomplete control a couple of years back, I seem to remember filtering at the database, Select...where..like '%autocomplete.text%', and so the whole table was not returned. Is the silverlight control doing the filtering here? My Linq is selecting the whole table. How do I change this behaviour?

 Thanks

Drew

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 4:10 AM |

You can do something like this: var first3WAOrders = ( from c in customers from o in c.Orders where c.Region == "WA" select new {c.CustomerID, o.OrderID, o.OrderDate} ) .Take(3); The "Take" Method only selects the amount you would like to return. I suggest a take of about 50. Remember to order it properly frist. Might help you with further query problems: http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 6:57 AM |

Thanks so much for replying Roet. I'll certainly try this tonight. Before I do though, I need to know if it actually effects the problem I have. I'm hoping someone can clarify how the Autocomplete engine works. Let's say I have a table of names of 5000 rows. I want to search a Mr. Smith, of which there are say 4 Mr. Smiths in the table. Currently, I type in the letters, s,m,i, and my Web Service selects the whole table. Now what is happening? Is the whole table being returned to the Autocomplete control, and then filtered by the control? And where is this filtering occuring? On the web server or on the client PC? If I use the .Take method mentioned about to select the first 50 rows, will that work if the filtering is actually done by the Autocomplete control and not the webservice linq query? Will the control now say return all s,m,i entries in the top 50 rows, or select the top 50 s,m,i entries?  In the past with the standard Ajax Autocomplete control, this was straightforward as the filtering was done by SQL Server, and so only the required results came back across the network.

 Drew

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 7:11 AM |

The Silverlight application runs on the client, and so the AutoCompleteBox does too. What you need to do when user types "Smith", is to make a webservice call like "GetContactsByName(string Name" and there run a query like this:

List persons =  (from p in db.Persons
                         where p.Name.Contains(searchText)                         
                         orderby p.Name
                         select p).Take(50).ToList();

Then, send that list back to the client. It is, of course, way faster than passing the entire database and start filtering on the client with the autocompletebox.

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 7:24 AM |

Great, I'll try it tonight!

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 1:38 PM |

Unfortunately, this does not appear to work. It's as I feared, now only the first 50 rows of the table is returned. Since Smith is not in the first 50 rows, the autocomplete does not display Smith. The filtering is done by the control itself after all the data is returned to the client. Not sure what to do. Maybe is just can't be used to look through large datasets.

 Drew

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 2:49 PM |

Could you give me the query that you are running? Because if you have the 'Contains' method in it, only the top 50 records (or less) containing Smith, would be returned.

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 4:33 PM |

I appreciate your help, thanks. The query is:

' Perfomance()

Dim list = (From theList In suburbList.Suburbs Select theList).Take(50)

End Function

There is no Contains because, the Silverlight Tookit Autocomplete control does the Contains part itself. I can not send the contents of the Autocomplete box to the webservice above, but rather send the results of the webservice to the control that then filters what was sent to it. I think that's how it works, anyway.

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 4:44 PM |

Yes the Silverlight Toolkit Autocomplete CAN do that for you, but that is only useful if you have the complete collection on the client side, which is not the case. You want to search/filter the WHOLE collection for Smith. And that hole collection is on the SQL database, so you should pass the entered string in the autocompletebox to the server, filter your database on that string, and then return the first 50 records of that query. Thats how it should work. The Autobox can filter, but when you use webservices as the source, you should put autocomplete and filter off. I suggest reading the following: http://www.jeff.wilcox.name/2008/11/autocompletebox-missing-guide/ Pay attention to the "Connecting to a web service" part. You will find what you need there! :)

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-07-2009 5:41 PM |

I see, I'm glad you've explained that to me. This is how I did it in the past with non silverlight ajax autocompletes, using the where clause at the database.

Thanks for your time, Roet,

Drew

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-12-2009 1:19 PM |

Mate, i don't know if this helps but a while back i had a situation where i had my Sql server on once instance of Windows and then on a virtual machine on the same box i had my web server. My pages were in asp.net connecting to SQLSever 2005. Things went dog slow!!!!!! I put them on the same machine and like magic things went fast again.

Re: Super fast autocomplete grinds to virtually a halt when uploaded to host

07-12-2009 4:35 PM |

Hi, thanks. That makes sense as it cuts out the traffic between the web server and the database server. Unfurtunately the host I am with has two seperate servers, but it's reassuring knowing that if the project is a success we can get our own servers and put them on the same box.