Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scajic
Starting Member
2 Posts |
 Posted - 2010-12-03 : 06:30:25
|
| 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 application3.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
11752 Posts |
Posted - 2010-12-03 : 09:30:40
|
| 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 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!SQL Server MVP |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 11:17:38
|
| 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. |
|
|
|
scajic
Starting Member
2 Posts |
Posted - 2010-12-05 : 13:39:21
|
| 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 ? |
|
|
|
|
|
|
|
|
I need bright idea to solve db optimization prblem