I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in a
common word like "damage" I get like 32,000 records. Now, the
server handles those records in about one second but the ASP page that
returns the results takes about one MINUTE to download. When I
save the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
I am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.
'Open sql connection SqlConnection1.Open()
Dim datareader2 As SqlClient.SqlDataReader datareader2 = cmdFindRowsWithKeyword.ExecuteReader Dim strMsg As String Dim intRowToFlag As Integer Dim strRowsToGet As String Dim strKeywordAsTyped As String Dim strKeywordAllCaps As String Dim strKeywordAllLower As String Dim strKeywordFirstLetterCap As String Dim FirstLetter As String
'Assign keyword as typed to variable strKeywordAsTyped = txtSearchFor.Text 'Assign keyword as typed to variable then convert it to all uppercase strKeywordAllCaps = txtSearchFor.Text strKeywordAllCaps = strKeywordAllCaps.ToUpper 'Assign keyword as typed to variable then convert it to all lowercase strKeywordAllLower = txtSearchFor.Text strKeywordAllLower = strKeywordAllLower.ToLower 'Assign keyword as typed to variable then convert it so just the first letter is in uppercase strKeywordFirstLetterCap = txtSearchFor.Text FirstLetter = strKeywordFirstLetterCap.Chars(0) FirstLetter = FirstLetter.ToUpper strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1) strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)
'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row. If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then
cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag SqlConnection2.Open() Dim datareader3 As SqlClient.SqlDataReader datareader3 = cmdFlagRowsWithKeyword.ExecuteReader datareader3.Close() SqlConnection2.Close()
I am trying to run a full text serach on one field, a Varchar 2000. say the field contains: (before you break the seal of your new product box, please be careful to read all the instructions) ...for example I search for keywords that may be in this field Like: product box seal instructions and this row is included in the result set
but I would like to leave out words like all pronouns and 'a' and 'I' ...words that aren't going to matter to the search.
Does someone know where I can stgart in doing this full text searching?
I`ve just starting playing around with version 7.0 and was wondering if anyone knows how to implement the Full-text Searching feature. Its sound like a neat feature if I could just get it to work. Thanks in advance Joe
I've assumed responsibilty for a sqlserver 2000 that has many databases and has only 1 database that uses full text searching. That 1 application now wishes for me to update the noise words file to remove the single characters.
As far as I can tell, no other application is using the full text searching since I don't see any catalogs created for any of them.
So, if no other databases and applications in this sqlserver have catalogs created for them, can I safely assume that changing the noise words file will not impact any of them even if the other databases do have text fields in some tables?
I've used CONTAINS on a varchar field. in SQL SERVER 2000
the query was "Select name from description where CONTAINS(name,' "donot*" ')
say if I search for "Select name from description where CONTAINS(name,' "donot a*" ') ---it doesn't return rows.
as might be it is seeing that 'a' as the starting letter of AND key word which is used in CONTAINS but how to tell that it is my next letter in the search
Are there any plans to add full-text searching capability to SQLCE? I know it's available in SQL Server Express but it would still be useful for an embedded/desktop application -- SQLite recently added this.
I was wondering when is it worth using Full Text Searching as opposed to using LIKE? Also, since I will be using third party for hosting my site, generally, do hosts support Full Text Searching?
I'm wrtiting a local site search egine but I need to make use of functions like FREETEXTABLE for instance. In order to use this I have to have the' Full Text Search table' enabled for a table. Now the Microsoft Search Service is running in MSDE; of that I'm sure. However when I try to enable the full text searching on a table or database the option that I'm supposed to choose is grayed out which means I can't select it. What's wrong? What do I have to do to be able to use that option? I' short of time and would appreciate n answer a.s.a.p. Thanks
I have created the full text catalogs for my tables, so that I can search all the tables at the same time, from any of the fields in the catalogs.
I have created the following procedure:-
create procedure usp_full_text(@findtext varchar(255)) as select set_id as URN, input_date as date_of_record from set_records where freetext (*, @findtext) There are a few union selects under this, but
If I search for perhapse 'SMITH' from within access, then this will bring back all the records which have smith in them somewhere, which is good, however if I repeat the search for 'smit' then I get no records returned.
How can I change the code to find the parts of words, or string of charactors, and how can I set this to do a soundex search?
Hopefully all this is possible, i just need pointers on how to achieve this, or I could be sat there for days.
For one day, this SPROC executes very quickly to return results on a Full Text catalog.
ALTER Procedure dbo.sp_RSSHarvestedHeadlines_FullTextSearch ( @ORKeywords varchar(4000) = 'xxxx', @ANDKeywords varchar(4000) = 'xxxx', @NOTKeywords varchar(4000) = 'xxxx', @SourceID int = -1, @IsHidden bit = null ) As set nocount on
SELECT HHL.HeadlineID, HHL.Title, HHL.Link, HHL.[Description], HHL.PubDate, HHL.GMTDateAdded, RSSSources.SourceTitle, RSSSources.SourceLink FROM RSSHarvestedHeadlines HHL INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID WHERE HHL.PublishedFlag = 0 AND (@IsHidden is null OR HHL.HideFlag = @IsHidden) AND (@SourceID = -1 OR HHL.SourceID = @SourceID) AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords))) AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords))) AND ( @NOTKeywords = 'xxxx' OR ( (NOT CONTAINS(HHL.Title, @NOTKeywords) AND NOT CONTAINS(HHL.Description, @NOTKeywords)) ) )
ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC
But somethign happens overnight and in the morning the sproc times out. While running (even from a new query window on the SQL 2005 server itself) it utilizes 100% CPU until it times out.
When I pass default parameters to the sproc (not using any part of the query that uses Full Text) the sproc returns every record in the database very quickly. No hang ups.
But the moment I add any text in say the @ORKeywords parameter, for example, the sproc utilizes 100% CPU for maybe 15 seconds and then times out.
By accident I discovered that I can fix this temporarily by copying the database. I don't use the new copy or anything. Just the act of copying the database fixes it. The sproc then executes normally, and quickly. But the next morning it's back to slow again.
Note, over night I am adding about 1000 records to the table.
Would automatic updates to the FT Catalog choke on 1000 records?
Also note that one of the fields being cataloged is a "Text" field (blob). Would that cause this?
Would what text is being added to the table matter? What if an invalid character was added (like some european character or a control character)? Would FT indexing hang up on that?
I am at a loss.
The following code will recreate the table:
CREATE TABLE [dbo].[RSSHarvestedHeadlines]( [HeadlineID] [int] IDENTITY(1,1) NOT NULL, [SourceID] [int] NOT NULL, [Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Link] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PubDate] [datetime] NULL, [GMTDateAdded] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTDateAdded] DEFAULT (getutcdate()), [GMTLastHarvested] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTLastHarvested] DEFAULT (getutcdate()), [HideFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_HideFlag] DEFAULT ((0)), [PublishedFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_PublishedFlag] DEFAULT ((0)), [EditStamp] [timestamp] NOT NULL, CONSTRAINT [PK_RSSHarvestedHeadlines] PRIMARY KEY CLUSTERED ( [HeadlineID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
For one day, this SPROC executes very quickly to return results on a Full Text catalog.
ALTER Procedure dbo.sp_RSSHarvestedHeadlines_FullTextSearch ( @ORKeywords varchar(4000) = 'xxxx', @ANDKeywords varchar(4000) = 'xxxx', @NOTKeywords varchar(4000) = 'xxxx', @SourceID int = -1, @IsHidden bit = null ) As set nocount on
SELECT HHL.HeadlineID, HHL.Title, HHL.Link, HHL.[Description], HHL.PubDate, HHL.GMTDateAdded, RSSSources.SourceTitle, RSSSources.SourceLink FROM RSSHarvestedHeadlines HHL INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID WHERE HHL.PublishedFlag = 0 AND (@IsHidden is null OR HHL.HideFlag = @IsHidden) AND (@SourceID = -1 OR HHL.SourceID = @SourceID) AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords))) AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords))) AND ( @NOTKeywords = 'xxxx' OR ( (NOT CONTAINS(HHL.Title, @NOTKeywords) AND NOT CONTAINS(HHL.Description, @NOTKeywords)) ) )
ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC
But somethign happens overnight and in the morning the sproc times out. While running (even from a new query window on the SQL 2005 server itself) it utilizes 100% CPU until it times out.
When I pass default parameters to the sproc (not using any part of the query that uses Full Text) the sproc returns every record in the database very quickly. No hang ups.
But the moment I add any text in say the @ORKeywords parameter, for example, the sproc utilizes 100% CPU for maybe 15 seconds and then times out.
By accident I discovered that I can fix this temporarily by copying the database. I don't use the new copy or anything. Just the act of copying the database fixes it. The sproc then executes normally, and quickly. But the next morning it's back to slow again.
Note, over night I am adding about 1000 records to the table.
Would automatic updates to the FT Catalog choke on 1000 records?
Also note that one of the fields being cataloged is a "Text" field (blob). Would that cause this?
Would what text is being added to the table matter? What if an invalid character was added (like some european character or a control character)? Would FT indexing hang up on that?
I am at a loss.
The following code will recreate the table:
CREATE TABLE [dbo].[RSSHarvestedHeadlines]( [HeadlineID] [int] IDENTITY(1,1) NOT NULL, [SourceID] [int] NOT NULL, [Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Link] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PubDate] [datetime] NULL, [GMTDateAdded] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTDateAdded] DEFAULT (getutcdate()), [GMTLastHarvested] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTLastHarvested] DEFAULT (getutcdate()), [HideFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_HideFlag] DEFAULT ((0)), [PublishedFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_PublishedFlag] DEFAULT ((0)), [EditStamp] [timestamp] NOT NULL, CONSTRAINT [PK_RSSHarvestedHeadlines] PRIMARY KEY CLUSTERED ( [HeadlineID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I'm using the Full text functionality of SQL 2005 Express Advanced and it works well.
However, the first time a query is run it take 20 seconds or so to actually return the search results. Thereafter, the search results are almost immediate.
That would be fine, except that if no further search is performed for approx 15 minutes or so, then we are back to the first scenario where it takes 20 seconds to return the results.
I assume this means that the full text catalog is not being held in cache at the server? Is there anyway to force it to do this?
My first thoughts were that Full Text within 2005 was an excellent tool, but if the first search always takes a long time then I will have to re-think as I can be sure that customers will get extremely annoyed at the response.
Do you know if there is any way around this or if there are any fixes imminent for this problem.....indeed, is it a problem or am I missing a fundamental setup issue?
What would be the implications if I created a small console application to perform a full text search once every minute at the server to keep the index cached?
I have installed the advanced services edition but cannot find out how to build and populate full text catalogs. Nothing shows on the MSSMSE tool and when I try to use a sql command to create a new catalog it says that the full text service is not installed. Am I missing something?
I am searching for the key word 'Platform Customer Support' using full text search. My code is as below
Set @KeywordSearch = 'Platform Customer Support'
Select AA, BB, CC, DD from SM9..TableName A Right Outer Join SM9_Experiment..TableName C On A.IncdTouchedGSF like '%' + C.SM9GroupName + '%' Where ( Contains(A.[Description], @KeyWordSearch) And A.OpenTime Between @StartDate and @EndDate And C.Classification = @GroupNameClassification )
The code is throwing:
Msg 7630, Level 15, State 3, Line 46 Syntax error near 'Customer' in the full-text search condition 'Platform Customer Support.
I have a query that returns raw tick data from a table. Unfortunately aftereven a few days there are hundreds of thousands of rows so the followingquery is not efficient.SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? ANDRawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' ANDContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE Status= 'I' AND ContractCode = ?))The most obvious solution then is to get all tick data with status ='I'(Imported) for a contract, process it and then move it to another table forarchiving. I am faced with a problem however: After selecting all data for acontract with status='I' the application was updating these records to a newstatus of 'P' (processed). Unfortunately another application is continuningto feed in live data and so it is possible that we will inadvertantly updateunprocessed data to 'P'.Question: Is it possible to select all records with status 'I' (from abovequery) and update their status to 'P' in one sequence?I am not a programmer, but if this is possible I should be able to implementthe query.Many thanks.Steve
I have this problem with reporting services wherein my report contains an Area Chart. My plot points is about 100,000 records. The problem is sometimes the Chart is displaying right and sometimes its not. The users need to refresh the report again for the chart to display right. This doesn't happen if my records to plot contains around 10,000 records.
Does RS have a limation in plotting records in an Area Chart? Any solutions?
Hi, My full text search on 2 millions records is taking time to show the result. I have created full text catalog in RAM drive to make the retrival process faster. But still its taking more than 1 minute to get the matching pattern. I am using SQL server 2005. I have 2 columns (id,text) in my table..
This is my unique index script
USE [SAMPLE] GO CREATE UNIQUE NONCLUSTERED INDEX [ui_productid] ON [dbo].[Products] ( [id] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..
This is my primary key index script.. USE [SAMPLE] GO ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..
This is my query..
SELECT D.[id], D.productname FROM dbo.Products AS D WHERE CONTAINS(productname, 'ford')
What should i do to show the result in 3-4 seconds.
Hi We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM.
I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ?
If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results
I have enabled Full Index Searching on my SQL Server 2005 database and it is working correctly. The problem I am having is this. When I search for the word "Spider-Man" I get results containing "Spiderman, Spider-Man and Spider Man" which is what I want. However, when I search on "Spiderman" I do NOT get results containing "Spider-Man or Spider Man" Also when I search on "Spider Man", I only get "Spider-Man" and not "Spiderman" Is there an EASY way to get results on any form of SpiderMan on my search?
I want to create a stored procedure which takes a project name and returns any project with a name similar or = to the input parameter. This is so the user can search for a project although the spelling may be incorrect or whatever.....
Any hints on how to start this? It seems that I could use SOUNDEX or DIFFERENCE, but I don't know where to start.
I have a products table with product attributes in a second table, together they describe a full product. I have a product title, a list of providers, description text, and keywords. I would like to do a search across these fields, and so far my research has shown that the Full Text Search component of SQL Server is the way to go. However, I am not sure this will be possible based on what is installed on the hosted server, so I am wondering if there is a unique, cool way of doing this without Full Text Search?
I need to search all user written SPs which have particular text in them. One way to do it is to open each SP in some notepad or word processor and search for the particular text.Is there any efficient way to do it ??
Hi I am developing windows application. I want to find records which has a particular number which is entered in textbox. and result is displayed in datagrid. If the entered number didnt find in database it must be displayed msg. i tried it like as below but not working. qlConnection conn = new SqlConnection(); conn.ConnectionString = "Server=EBSERVER;UID=sa;Database=Airport-Clearance;"; //SqlConnection sqlconn = objcs.GetConnection(); //MessageBox.Show("Connected"); //sqlconn.Open(); //Do what ever SqlDataAdapter filling = new SqlDataAdapter("select * from Airport where awb='" + txtawb.Text ,conn); DataSet displaying = new DataSet(); conn.Open(); filling.Fill(displaying); dataGrid1.DataSource = displaying.DefaultViewManager; conn.Close(); Waiting for reply. Warmest regards, ASIF
