Solved

Full-text search was not installed properly in SQL

Posted on 2006-06-18
6
431 Views
Last Modified: 2012-06-21
I have SQL database running through ASP in a Web Broswer.  My users are running into serious problems with speed - the pages that need to search through large tables, or run multiple queries are running horribly slow, especially when many people are logged in.

I suspect the problem is that the tables are not indexed, but I can't get them to index.  When I try to use the Indexing wizard in SQL Server 8, the wizard goes all the way through until it tells me that I don't have acess to the folder I am trying to save the index to and the that "Full-text search was not installed properly."  THe folder is a folder I work in and build files in all the time, so that is not true, but it seems that there is a problem with indexing being installed.  

I have tried enabling full text searching and indexing in the SQL Query Analyzer, but it doesn't seem to take.  I keep getting this same error.

Does anyone know what I can do?

Thanks
0
Comment
Question by:Mannezman
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
post some examples of the searches you're attempting...

does your SQL server agent account have permission on the folder..?

what exactly are the error messages you are getting  ?

what OS are you running  (inc Service packs) ?
which edition / service packs of SQL 2000 are you running?

what else do you have running on the server (other than SQL Server)?


have you started the Indexing Service ...? (check services)


0
 

Author Comment

by:Mannezman
Comment Utility
We're running Server 2003 with the most current Service Pack.

This server is just being used for this project and some dead file storage.  We've souped up the server (up to 2 gigs of memory) which added some speed, but it's still noticeably slow on certain pages or when there are more than 1 or 2 users.

Indexing Service is turned on in the server's Services.

In SQL itself, in the Support Services I have Full-Text Search turned on, but I have not been able to build any Full Text Catalogs or Indexing on any tables in my real database or a much smaller test database.

And the Active Server Pages search code links two tables, a small Market table and a large (150,000 records) table of Gas Stations:
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM Station INNER JOIN xLookup_Market ON Station.xMarketID=xLookup_Market.MarketID
WHERE StationName > '0' AND (StationName LIKE '%" & request("SearchName") & "%' OR StationNamePayment LIKE '%" & request("SearchName") & "%')  ORDER BY StationName ;"
RS.Open SQL,Conn,1,2

QUESTIONS
1.) My understanding is that the massive Gas Stations table is slowing down the search because it is not indexed.  I'm not sure if that is the problem, but I could use any help at this point.

2.) Users come and go frequently (temporary staff), and the managers of that staff don't have direct acess to the SQL database manager interface, so we aren't creating Users in the SQL database for each person.  Everyone is just using the default user.  Does this compromise speed if multiple people are using the database, or is that irrelevant?

thank you for your help


0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
You really need to do some reading up into Full-Text Search.

First it runs as a separate service.  Indexes are created separately from "standard" table indexes and the index has to be populated manually, scheduled or done in the background.

Secondly your query will never take advantage of a Full-Text Search (or any table index for that matter) as it uses LIKE which forces a table scan, hence the lousy performance.

When you have successfully created your Full-Text Index and done a full population than you query will look something like this:

SELECT *
FROM      Station
      INNER JOIN xLookup_Market ON Station.xMarketID = xLookup_Market.MarketID
WHERE      Station.StationName > '0'
      AND CONTAINS(Station.*, '" & request("SearchName") & "*')
ORDER BY Station.StationName
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Also, the following question is considered abandoned, please attend to it:
http://www.experts-exchange.com/Databases/Q_21846158.html
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
ok... indexing isn't going to solve this problem as acperkins says since you doing like searches it will have to scan the whole table...

what is the purpose/intention of the query you've shown?

do you really have 150000 different gas stations?

0
 
LVL 26

Expert Comment

by:DireOrbAnt
Comment Utility
You should also only return what you need from the SELECT. Never use *. If you only need a name and say an ID, return only those two fields.
As I understand it, some of the fields inside the Station table are large (I assume TEXT) fields. Returning these if not needed is a big drag.

However, like the others are telling you, the big performance issue is probably because of the LIKE %Name% statement in one or two large fields (StationName and StationNamePayment).

Implementing Full-Text search is well worth it, but if you can search from the start of the field (LIKE Name% vs LIKE %Name%) and if you index those two fields (non full-text indexes), you will have a very good performance as well.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now