Hi guys,

Sorry guys if this is not the appropriate forum for this question. Im just not sure where should this belong. I just thought that it could also have something to do with the SQL.

I have an auto suggest dropdown which was a bit faster intially. Now that the keywords are a couple of thousands, the suggest dropdown is becoming slow and it takes about 3 seconds before it shows.

I am using .asp, sql server2000 using the patindex to search for the keywords.

Any idea to make it a bit faster?

Thanks.

Could you provide a data sample from you table as well as several examples of your most common searches.

Simi

You probably need to re-index your table, or provide some additional indexes.

Either run your stored proc (or in line sql) in SSMS with "Include actual execution plan" turned on.
If you're missing an INDEX, it will indicate that. Otherwise, you will have to look through the exec plan and try to fix "Index Scans" to use "Index Seeks". This normally requires adding a missing field to a join.

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

I don't think anyone can really tell you without a lot more information about the architecture of your application. Perhaps your database is perfect but your ASP (.NET ?) is badly implemented, or vice versa.

3000???? That's FAR too many entries for autosuggest. Cut down on the entries or redesign that part of the application to narrow down the choices somehow.

Craig Berntson
MCSD, Visual C# MVP, www.craigberntson.com/blog

Craigber,
Having 3k different possibilities isn't far fetched. Choosing to show them all to the user is.
Normally you only bring back 10-15. Reducing the amount of data that is passed through the web service call is one of the few places to optimize this.

If he's actually loading all 3k into the page, severly bloating the whole thing, then I agreee with you. If he's bringing the data back from an AJAX web call, that's still bad, but at least he's waiting for the user to give him some input.

mayamanako,
Check this out:
http://jqueryui.com/demos/autocomplete/

Lodlaiden

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.