hmm..interesting problem

Results 1 to 2 of 2

Thread: hmm..interesting problem

  1. #1
    stelios Guest

    Default hmm..interesting problem

    Im implementing a search engine for an Access database..and it has to go through a big number of columns and tables..anyway..the problem is that the query gets too complex in SQL and doesnt compute (funny --it even has an exact number of queries that stops)..So i do it in two separate recordsets dividing the columns..but this is where the problems start..sometimes there might not be any rsults in the first recordset and it stops without going into the second..Moreover, sometimes dependin on thesearch i get double is there a way after the search has been made to put the two recorsets together to get one distinct recordset or something<BR>and..<BR>How can i get the record set work out the query if the first one doesnt have any reults..Help! really important

  2. #2
    Join Date
    Dec 1969

    Default but wrong solution, wrong problem...

    You are trying to use a RELATIONAL database when what you really need is a "Full Text" database. Even if/when you get this working with Access, it will run really slow and put a horrific hit on your server whenever it is used. What you are doing is kind of like using one of those Swiss Army knives (that is, Access) to assemble a new car (where you should be using a factory assembly line). Not only will it take you a long time and tie up the entire line while you do it, it probably won&#039;t do a particularly good job (nicks in the paint, screws half tightened...or, in DB terms, searches that return too few or too many results or duplicate the same result or or or).<BR><BR>The only readily available full text search capability in the PC world that obeys ODBC rules seems to be SQL Server 7, which *does* allow you to specify that certain fields in certain tables shall be full-text-indexed. And *then* you can *begin* to get some reasonable results.<BR><BR>One hopes that *someday* some of the better full-text search engines (most/all run on Unix) will be made available to ASP via ActiveX calls, but I wouldn&#039;t hold my breath until it happens. In the meantime, unless your DB is *truly* tiny, then it&#039;s time to bite the bullet and move to SQL Server 7.<BR><BR>If your DB truly *is* tiny, then maybe the best thing you could do would be to add another table to the DB. And this table has only two fields: The foreign key back to the main table and...ready for this?...a text field containing all the text/words from all the searchable fields of the main table!<BR><BR>Now your LIKE &#039;%xxx%&#039; queries only have to search *one* field. A tremendous performance improvement at the cost of *less* than doubling the disk space used. So if your current DB is, for example, only 50MB, then for another 50MB you get much easier searching and much improved performance.<BR><BR>But you&#039;ll *STILL* never be able to ask the typical full-text database queries such as "find records where &#039;ancient&#039; is within six words of &#039;civilization&#039;". [Okay: a lie. You could. By, after finding the records containing the two word, using VBS to figure out how far apart the words are. Can you say "even slower"??]<BR><BR>Enough. Please do consider putting away your Swiss Army knife and purchasing the *right* tool for this kind of job.<BR><BR>

Posting Permissions

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