Sign in to follow this  
gamma_strahler

Efficient database search?

Recommended Posts

Hi, i have the order to improve the fuzzy search of our business application. Unfortunately our database connections (MSSQL) are very slow ( a single search query by name takes over 5 seconds for just 10,000 records. ) would it be worthwhile to implmenent an index-based search? or are there any other preferable ways that could improve the speed of our searches? thanks for any help gamma_strahler

Share this post


Link to post
Share on other sites
Quote:
Original post by gamma_strahler
would it be worthwhile to implmenent an index-based search? or are there any other preferable ways that could improve the speed of our searches?

Well, if you don't have an index defined on the name column that's probably worth doing first.

Share this post


Link to post
Share on other sites
Is it the DB query which is slow, or is the DB on the other end of a slow remote connection? If the query is actually taking several seconds, I'd be worried the DB was just badly designed. But without seeing it I don't know how much we can help. There are profiling tools for SQLServer, surely? I'd expect you get some for free from MS.

Share this post


Link to post
Share on other sites
Quote:
Original post by d000hg
Is it the DB query which is slow, or is the DB on the other end of a slow remote connection? If the query is actually taking several seconds, I'd be worried the DB was just badly designed. But without seeing it I don't know how much we can help. There are profiling tools for SQLServer, surely? I'd expect you get some for free from MS.



depends which version of SQL server you are using, I think 2005 includes something.


It is possible you can post an example of some SQL code you are using to perform the search?

Are you using database views, they're lots of reasons why database queries take a long time but as suggested above mostly are down to bad design. i.e. if you've got lots of nested "select" statements, or calling lots of functions or even performing searches on certain data types.

Using Indexed views places lots of design restrictions on you database design, you shouldn't need then unless you need a super-super fast view. It is very annoying and time consuming redesigning an existing database to take advantage of this.

You can enabled Full-Text searching, which is nice, but again not always required.

A "SELECT * FROM Table WHERE TextField LIKE '%something%'" has a fairly quick response time (our database searching is based on this)... And again generally the only reason for a slow-down as you described is usually down to a badly designed database view.

Share this post


Link to post
Share on other sites
@moosedude

The problem aren´t the SQL´s (we have checked that extensively). The bottleneck here is that there are too many connections (our customers have too many users that are simultaneously logged in and make a lot of queries, uodates and so on at high frequencies ). To make it more difficult, many of them work on terminal servers which lack of resources like RAM and CPU power. So you could thay in this case the sql´s are the problem, but i think that it is not possible to optimize these sql´s further. I can post an example later.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this