Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: Database search optimization problem

    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 have tens of millions of rows with hundred columns (views that has joined tables)

    I think part of the solution is in different approach:
    I'd like to only send first top 100 results from server to client. 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 of 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. (Sphinx | Open Source Search Server).

    Well.. let's hear you now.. 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!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Have you looked into using Indexed views?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    That is a great idea,
    but how would it preform if I make full-text search on all columns in indexed view with millions of rows and 100 columns ?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Excuse me?
    Are you using "fulltext search" in MSSQL Server, or are you "always loading the complete set of data in lookup, and then doing the full text search in application".

    Please be clear and concise in your posts.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2010
    Posts
    3
    Temporarily I'm "always loading the complete set of data in lookup, and then doing the full text search in application".

    But I'd like to do that on the server side.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, then why aren't you using SQL Server's FullText search feature?
    And are you doing a true "full text search"?
    Give us some examples of typical searches that would be performed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2005
    Posts
    319
    It sounds like you are using SQL Server as a file system.

    Do what blindman says and use its fulltext search feature.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Every morning, when going to work, I start up my Porsche with a key that has a Toyota emblem. Coincidentally, the steering wheel of my Porsche also has a Toyota emblem. In fact, I can't find a Porsche emblem anywhere on my Porsche...Wonder why???
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Robert....I'm starting to worry that you are slowly turning into Brett Kaiser.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, a couple of things that are still going for me, - I am not from Jersey, and I haven't started sneaking in margaritas to work (though leaving earlier to go to a beer joint was crossing my mind )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You had me at "I'm not from Jersey".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Jealousy

    We are soooo used to it

    And your next President of the United States will hail from the Garden State
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Great. "The Situation" for President.
    Actually, can imagine him in a close runoff with Palin in the next Republican primary.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •