Solved

Full-text search was not installed properly in SQL

Posted on 2006-06-18
6
471 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 500 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 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