Mannezman
asked on
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
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
ASKER
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
SQL = "SELECT * FROM Station INNER JOIN xLookup_Market ON Station.xMarketID=xLookup_
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
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
SQL = "SELECT * FROM Station INNER JOIN xLookup_Market ON Station.xMarketID=xLookup_
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, the following question is considered abandoned, please attend to it:
https://www.experts-exchange.com/questions/21846158/ASP-not-adding-records-to-Access.html
https://www.experts-exchange.com/questions/21846158/ASP-not-adding-records-to-Access.html
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?
what is the purpose/intention of the query you've shown?
do you really have 150000 different gas stations?
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.
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.
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)