• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Full-text search was not installed properly in SQL

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
Mannezman
Asked:
Mannezman
1 Solution
 
LowfatspreadCommented:
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
 
MannezmanAuthor Commented:
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
 
Anthony PerkinsCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
Also, the following question is considered abandoned, please attend to it:
http://www.experts-exchange.com/Databases/Q_21846158.html
0
 
LowfatspreadCommented:
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
 
DireOrbAntCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now