SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

scajic
Starting Member

Croatia
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. (http://sphinxsearch.com/).

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!



spirit1
Cybernetic Yak Master

Slovenia
11749 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
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!

SQL Server MVP
Go to Top of Page

Kristen
Test

United Kingdom
22403 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

scajic
Starting Member

Croatia
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  
 New 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.05 seconds. Powered By: Snitz Forums 2000