Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 I need bright idea to solve db optimization prblem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 12/03/2010 :  06:30:25  Show Profile  Reply with Quote
Hi guys, I would really appreciate your help with this problem I'm having.
I need advices, educated guesses, previous experiences, whatever you can give me.

OK, here's the problem I'm dealing with.

1) I work for a company producing ERP solutions. We make our software in .NET C#, combined with SQL Server, and sometimes OLAP.

2) The problem occurs when user clicks on the lookup table. Sometimes loading those tables take a lot of time (cca. 30 seconds).

3) The main cause of this problems are:
3.a) We're always loading the complete set of data in lookup, and then doing the full text search in application
3.b) Those tables can in years acquire tens of millions of row with hundred columns (views that has joined tables)

I'd like to always load only first top 100 results (like google does).
Most of my problems would be solved with changing the approach with select queries that gather only TOP 100 results.
But then again, the problem remains when the traffic over those tables is large, that many tables are locked, so it takes more time to load.

What were my ideas of how to solve it:
- maybe to use warehouse with denormalised data to avoid joining, and locked tables (this would take a lot o disk space).
In that case I would need to have some sort of triggers to keep the data fresh.

- I did a little research and found that Sphinx search engine might help me. (

Well.. now you.. what do you think... any advices.. I'm kind of new in this field, but got this R&D task in front of me, and I have a feeling that some of you probably already dealt with these kind of things.

Thnx for any kind of help or advice!

Cybernetic Yak Master

11752 Posts

Posted - 12/03/2010 :  09:30:40  Show Profile  Visit spirit1's Homepage  Reply with Quote
Have you tried using SQL Server's full text search?
Also a friend of mine is using Lucene for this kind of thing.

Lookup tables don't usually change much so they're perfect for caching. Have you thought about that?
Also why do you have locking on a lookup table?

Causing trouble since 1980
Speed up SSMS development: <- version 1.9 out!

SQL Server MVP
Go to Top of Page


United Kingdom
22859 Posts

Posted - 12/03/2010 :  11:17:38  Show Profile  Reply with Quote
We require minimum 3 characters, and in some places 4, before we will do "predictive lookup" (which I guess is similar to your Lookup)

For all predictive lookup we have single-column index that maps onto the user query. So "get first 100 records" is very fast.

We retrieve, say, 100 rows in predictive lookup. But we only display, say, 10 to the user. If they type the next character and 10 results are in the previous resultset, or the resultset as <=99 rows, then we do not make another server-round-trip.

For Dialog Box - more like "Advanced Search" then Predictive Lookup then you should have a query with one or many criteria. This can be optimised so that it is fast. For example, if they type Customer ID and Name then make sure the first-cut search is on Customer ID as that will be unique / single hit. Whereas searching for NAME LIKE '%'+@SearchName+'%' will be slow!

I haven't used Spinx or Lucene, but I have heard of them of course ... but clients who are using Full Text Search find that where they have several Full Text tests which are then combined (JOIN "Car CONTAINS Ford" AND JOIN "Model CONTAINS Escort" type logic) then it scales very badly - may be improved in SQL 2008 though.
Go to Top of Page

Starting Member

2 Posts

Posted - 12/05/2010 :  13:39:21  Show Profile  Reply with Quote
Thank you very much for your answers, and sorry for a delay in my reply.

Yes, I forgot to mention that ill use it mostly with full text search over all the columns on a table.
Does this mean I should use Spinx/Lucene rather then SQL server full text search ?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000