Efficient database search?

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.

We have an index (the primary key) column defined, but i mean a real index-based search (like the one Windows XP does it)

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.

Yes, an index-based search solution could be useful here. You could try Lucene, for instance.

Thanks for your tipps!

I have evaluated lucene already, but it is not an option since it lacks support for umlauts.

Quote:

Original post by gamma_strahler I have evaluated lucene already, but it is not an option since it lacks support for umlauts.

Are you sure? Lucene supported unicode last time I checked.

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.

@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.