?
Solved

Full-text search was not installed properly in SQL

Posted on 2006-06-18
6
Medium Priority
?
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16930941
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
ID: 16933469
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 2000 total points
ID: 16936238
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16936262
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
ID: 16938798
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
ID: 16939551
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

718 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