Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Exclamation Unanswered: Database Search Engine

    Hi folks,
    Whts up........??? M back, after a long gap.
    I have come across with a major issue. And u know wht th issue is.........???
    It is about th DATABASE SEARCH TOOL. I have a database of around 30 tables. Now I wud like to have [COLOR=]a[/COLOR]a search engine on my .asp page. There will be a text box on th page and one submit button. After typing some text in th text area n submitting th page, my package sud check tht perticular text in all th COLUMNS of all th TABLES, n whrevr it gets a match (exactly same, or by speech recognition), it sud through th links on th next page.

    Nw i wud like u guys to take this problem, at th earliest n come up with a up to th mark solution.

    Thnkx

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will probably want to use SQL Server's full-text search capability.

    But...

    ...you are going to allow web page viewers to issue non-sargable searches of every value of every column in every table in your database? That sounds insane to me. What kind of performance to you expect?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Aside from the issues the blind dude mentions, I'm sure we've all "played' with this...

    You need dynamic sql and a select against the information schema columns view....

    EDIT: AND a cursor, AND a temp or table variable to hold the results...

    what do you plan to do about dates and numbers?
    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.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh come on! I understand the requirement is somewhat fishy, but this is not a homework where just the solution is needed! If you REALLY need to search for a pattern (even if it's exact) in every field of every table, - why would one want to use dynamic SQL or a cursor or a combination there of? Unless your intention is to kill the server with the second connection while the first one is still doing this insanity?

    You'll need to write a function for all tables that will transpose all the char-based columns into one column with UNION ALL and a CAST to accomodate for the widest field and a WHERE clause for each participant of the UNION with the pattern that you're looking for. The second column should be a table identifier and possibly the third should be the PK from the table (I hope, contrary to "some" opinions that you're using INT/BIGINT for your PKs). Then you fire this function from the stored procedure that further manipulates the results. At THAT point, and at that point only, you may have to use dynamic SQL, and possibly a cursor, if the retrieval of the "links" will involve selection from the tables that appeared from the execution of the function mentioned above.

    EDITED: And while writing the participants of the UNION, make sure to use either READ UNCOMMITTED isolation level, or NOLOCK table hint, UNLESS (!!!) DML operations that may be performed on the affected tables is important.
    Last edited by rdjabarov; 07-09-04 at 12:41.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm speechless.....
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, that's ANOTHER way to skin the cat...

    ...but it's still going to scream bloody murder while you're peeling off the fur.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Sep 2003
    Posts
    364
    Here's a sp I found you can get started with.
    Attached Files Attached Files

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, ANOTHER way would be to implement Full-Text Search and use CONTAINSTABLE while UNIONed ALL 30 times. Either way it's a case of animal abuse
    "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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Yup, ANOTHER way would be to implement Full-Text Search and use CONTAINSTABLE while UNIONed ALL 30 times. Either way it's a case of animal abuse
    Yeah, but as long as it is just a cat...

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Well, that's ANOTHER way to skin the cat...

    ...but it's still going to scream bloody murder while you're peeling off the fur.

    You crack me up......
    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.

  11. #11
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Wink Thnkx

    Thnkx folks,
    Thnkx for ur help. Though I hv'nt started th work suggested by u people............as th week passed was tight. Lemme give a try n see what comes.

    Thnkx 1nc again

  12. #12
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    implement full text indexing on the columns that you wish to search
    and use the following functions
    contains, freetext, containstable and freetextable.
    this is much better than like and dynamic sql because of the "Linguistic based search" ability of the fulltext service
    to search long unstructured text in the where clause use freetext
    to use the most powerfull and most focused of the 4 choose contains in the where clause (contains will find prefix terms , inflectional, generational and other forms of searches)
    to search a table based result set use containstable or freetextable
    the diff is that you place these in the from clause of your select.

    good luck

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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