Efficiency Of Sql Server On Searching On Text Field
Hi
We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?
The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?
Please advise, thanks for your help!
Li
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Searching Text In A Db Field
Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...declare @s varchar(20)set @s='4,8'EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')
View Replies !
View Related
Searching In TEXT Field
Hi, We are using SQL Server 6.5 and have some TEXT datatype columns in the database which we need to search, based on keywords. Does anyone have experience in using TEXT datatype or can anyone give me some pointers. Also I need to know what is the performance difference between searching thru a text field or searching thru a series of varchar(255) fields, is it easier to search thru the text field by breaking it into a series of varchar(255) character fields or is it okay to search thru the text field. This is urgent.
View Replies !
View Related
Full-Text Searching In SQL Server 2000
I am trying to create a full-text searching in SQL Server 2000, after trying different ways still failed. I use a Windows login to start both MSSQLServer and SQLAgent services, created a full-text catalog in pubs database employee table in EM. Start full population but the status is always idle and never get populated. My Windows login is a member of sysadmin role. I extracted the gatherer log: - The gatherer has started - The initialization has completed - Started Full crawl - MSSAL75://SQLServer/75d7831f Add Error fetching URL, (800700e9 - No process is on the other end of the pipe) - Completed Full crawl As I have been struggling in doing this for several days, if anyone has successfully doing a full-text search, please give me your experience and steps on how you did it successfully. Thanks
View Replies !
View Related
Searching Database Text W/o Using Full-text Indexing
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 While datareader2.Read intRowToFlag = datareader2(0).ToString strMsg = datareader2(1).ToString '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() End If End While datareader2.Close()
View Replies !
View Related
Access Memo Field To SQL Server Text Field
Hi, I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all). I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data. Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
View Replies !
View Related
MS Access Memo Field To SQL Server Text Field
Hi all, i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database. I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example. Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason. I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats. Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds! cheers Dominic
View Replies !
View Related
Searching For A Field Name
Hi all, I have a very big database with me. This database has large number of tables and each table has n number of fields. My trouble now is that I desire to search for a field name called 'empname'. Is there any query possible for me to search all tables and return me the table names which contain fields which match with 'empname'. Adding essence to the porridge I would like to know if I specify something like '%pnam%' it should match any field name which contains 'pnam' as a portion of its name. Is all these possible ?? Thanx for any help in advance, Regards, tvks
View Replies !
View Related
Searching By A Date Field
I'm using the .NET Framework 1.1 together with SQL Server 2005 Beta. I have a search page where you can search a db using a number of fields or combination thereof. Three are text fields (patient id, surname, forename) and I can do any search using one criteria or any combination without any problem. However, I'd also like to search on a dob (datetime field in SQL Server) and I'm having real problems in making this work. The dates are stored in the following way in SQL Server: '1938-05-31 00:00:00.000', whilst they are displayed as 'dd/mm/yyyy' on a web page, which I guess corresponds to my regional settings. My code is shown below. I 've tried any number of combinations for the dob string with no success. I do not get any error, just no records are returned. Does anyone have any idea what I'm doing wrong and how to correct it before this drives me crazy?! TIA for any help. P.S. chxPatID, chxSurname etc are all checkboxes. <code starts> If chxPatID.Checked Then strSQL = strSQL & " patid= '" & txtPatID.Text & "' " strAND = " AND " End If If chxSurname.Checked Then strSQL = strSQL & strAND & " surname ='" & txtSurname.Text & "' " strAND = " AND " End If If chxForename.Checked Then strSQL = strSQL & strAND & " forename='" & txtForename.Text & "' " strAND = " AND " End If If chxDoB.Checked Then strSQL = strSQL & strAND & " dob= " & CDate(Year(txtDoB.Text) & "-" & Month(txtDoB.Text) & "-" & Day(txtDoB.Text)) & "" End If </code ends>
View Replies !
View Related
Storing And Searching A Field
I need to find a better way to find all parts from a particular category. Each part can appear in multiple categories. Currently, I use the id of a catagory and search the txtCatagory field in the position of the id. I have included the query string for you to see. SELECT * FROM tblParts WHERE SUBSTRING(txtCategory,@CatId, 1) = '1' ORDER BY PartNum ASC txtCategory is Data Type Text This currently does work, however is causing some performance problems with my ISP. Any help would be greatly appreciated.
View Replies !
View Related
Searching In A Varchar Field
I have a varchar column which containd comma delimited values like Rec# Fruits 1 Apple, Peach, Strawberry 2 Orange, Mango 3 Banana, Grape ........... Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter. Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ?? I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ? Thanks Sumit.
View Replies !
View Related
Text Searching
Hi again! 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? Thanks, jr.
View Replies !
View Related
Text Searching In SPs
Hi: 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 ?? Rnathan
View Replies !
View Related
Searching A Database Field For Null Value
Hi, I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is; //Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); "; SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"]))); //If UserName is null, display confirmation, else display errorif (UserName == null) ; {UserNameCheckLabel.Text = "Username okay"; String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"]))); }else; {UserNameCheckLabel.Text = "That username is in use"; } I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice! Many thanks, Chima
View Replies !
View Related
Searching A Datetime Field By Time
I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.
View Replies !
View Related
Searching An Image Field (WHERE Clause)
I have an application that currently runs under MSSQL 6.5 that searches the database for keywords. These keywords are in a Microsoft Word document stored in an image datatype field (BLOB datatype). The following query works: SELECT * FROM tData WHERE oleresume LIKE '%test%' returning all records in which the Word document in olereume has the word 'test' in it. When run under MSSQL2K, the following error message is returned by SQL: "Invalid operator for data type. Operator equals LIKE, type equals varchar." I'm stumped. Can anyone explain this or give me a workaround? Thanks, Matthew
View Replies !
View Related
Full Text Searching
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? Thanks, Eric
View Replies !
View Related
Full Text Searching
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?
View Replies !
View Related
Full Text Searching
I have defined a full text index on the firstname column for the employee table. I am using SQL Server 2000 on Windows 2000. I enter a query using SQL Analyzer such as select * from employee where contains (*, 'Brian') I receive the error message : Server meg 7619.level 16, state 1 , line 2 Language/Database cache file could not be found. Does anyone have any ideas Dave
View Replies !
View Related
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
View Replies !
View Related
Free-Text Searching
Hi i'm Free-Text Searching a field in the database, is there any way i can pull back all the ignored words (the,a,and,etc..) otherwise how do you check for them. If a user enters one it just errors with a OLE DB error message, not very nice! Cheers
View Replies !
View Related
Searching Formatted Text
hi guys, i have a problem and i need some help please, i have a column in my database which comes from a column of formatted text (which i dont have any control over Code: Analysis of Receipt 41129 Amount 2.35 Dated 02/01/2002 Type Date Ref1 Ref2 Original Amnt Receipt Discount Balance Adj 02/01/2002 INV41129 O/Balance 2.35 2.35 0.00 0.00 Thing is i need to search the database and pick out all values of the Ref1 column - in the case INV41129 Appreciate any and all help guys Thanks Andy
View Replies !
View Related
Full Text Searching
Hi i am really new to full text searching, I have created a catalog using: sp_fulltext_catalog 'textcatalog','create' and now i want to add a table with: sp_fulltext_table 'Product', 'create','textcatalog', 'ProductID' where product is a table in my database and productID is the primary key to that table. The primary key cannot be null, But i get an error: 'ProductID' is not a valid index to enforce a full-text search key. You must specify a unique, non-nullable, single-column index. Can any one point me in the right direction? Thanks in advance Tuppers!
View Replies !
View Related
Searching Procedure Text
Everyone seems to think this needs something elaborate: CREATE PROCEDURE sp_FindText @text varchar(8000), @findtype varchar(1)='P' AS SET NOCOUNT ON IF @findtype='P' SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @text + '%', text)))-Len(Replace(SubString(text,1, PatIndex('%' + @text + '%', text)),char(13),''))+1 AS Line, --PatIndex('%' + @text + '%', text) AS Position, OBJECT_NAME(id) AS ProcName FROM syscomments WHERE text like '%' + @text + '%' ORDER BY ProcName, Line IF @findtype='C' EXEC('SELECT TABLE_NAME + ''.'' + COLUMN_NAME AS TableColumn FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%' + @text + '%'' ORDER BY TableColumn') IF @findtype='T' EXEC('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''%' + @text + '%'' ORDER BY TABLE_NAME') GO It not only searches procedure and view definition text, it will also find tables, views, and column names: EXEC sp_FindText 'myTable' --or-- EXEC sp_FindText 'myTable', 'P' --finds procedures/views containing 'myTable' in their definition/code EXEC sp_FindText 'myTable', 'T' --finds tables/views containing 'myTable' in their name EXEC sp_FindText 'myCol', 'C' --finds columns containing 'myCol' in their name It's pretty easy to modify and extend, and probably doesn't need dynamic SQL either. It returns line numbers for views and procedures, but these can be incorrect if the procedure or view contains over 4000 characters of code. It does not search tables for values, it's strictly for finding object names.
View Replies !
View Related
Searching Large Text
I am building a generic job site and well I have hit a speed bump. I need to store resumes in the database to be searched on. Well what is the best way to store these full text resumes? so that they can be easily searched on? SQL 2005
View Replies !
View Related
Full-text Searching
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.
View Replies !
View Related
CONTAINS In Full Text Searching
Hi 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 Really I need more clarity on this... Thanxs in advance
View Replies !
View Related
Running A LIKE Statement When Searching For A Date Field...
I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220) SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
View Replies !
View Related
Full Text Searching With Paramters
i have a query in SQL Server 2005 that uses a parameter like so:ALTER Procedure [dbo].[p_FullTextEquipmentSearch] @search as nvarchar(50), as SELECT * FROM EquipmentView where contains(*, @search); what i want to so is add more parameters to the query so that the user can choose either 1 or many for the search can anyone help me on this
View Replies !
View Related
Full Text Not Searching All Fields
I have a major issue with an application that I wrote last year. The business I wrote it for changed from a Win2000 Server to Win2003 SBS. They installed SQL 2000, and we moved over the application. It was previously using SQL7 on the 2000 machine. Anyway, the application searches through the fields and finds data on their film projects. The idea being that they can lease some stock footage, or even reuse some they may have already. It used to work great. Now, however, when they do a search looking for "all of" in the fields, it returns 0 results. The "any of" works still. The query had to change based on the differences of the server, but it works just great. The query analyzer works, and returns 300-or-so results...which is the desired effect. However, in the application, it does not. I know it's right, because I can search on some items, and I can get some limited results, but they seem to only be finding words in the "description" field. It appears that it is only searching through the description of the footage shots, but not the medium (for example: film or video) or the actual show they were filming it for. All of those fields (columns) are in the FT catalog, and I've had another person watch to make sure I included them when I rebuilt it. Now, here's the thing. The EXACT same coding works on an EXACT version of SQL 2000, but it's on a Win2003 Standard Server (not SBS). The Standard server is not theirs, so it can't stay there. The same data is in that DB, and it works just great. So I know it's not my coding, I know it's not my Query, and I know it's not the data. Is there any reason that this SBS server might not search all the fields or all the fields may not populate properly in the Full Text Catalog? This has been going on for months, and I'm completely out of ideas. Can anyone suggest anything? Thanks for any and all thoughts. rob
View Replies !
View Related
Problem With 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
View Replies !
View Related
Full Text Searching Problem
Kind of a no code question, Is it possible to add a word to the index catalog of MS SQL Server 2000 directly? The reason for this is that a web based search engine we are working on fails to return results when given a certain hyphenated acronyms such as "4-H" this string is broken as 4,h and resolved as a noise word and thus no indexed when there are numerous pages contained in the database which have this string. I was wondering if it's possible to just insert special cases of strings like that into the catalog? or do I have to create a custom wordbreaker?
View Replies !
View Related
Full-Text Searching Issue
The Microsoft SQL full-text indexing seems to be working fine, however why does it only work when I insert a value and it fails to work when I insert the @txt varaible like the original StoredProc below ? Replaced "name like '%' + @txt + '%'" with 1. FREETEXT(NAME, 'ginger') --> works 2. FREETEXT(NAME, 'txt') --> fails 3. FREETEXT(NAME, @txt) --> fails 4. FREETEXT(NAME, '@txt') --> fails ------------------------------------------ ** Originial ** CREATE PROCEDURE p_SEARCH_PRODUCTS @TXT VARCHAR(500) AS DECLARE @today DECIMAL set @today = DBO.DATETOMS(GETDATE()) SELECT 0 CATEGORY_ID, P.PRODUCT_ID, 0 CAT_COUNT, P.NAME, P.SHORT_DESC, P.THUMBNAIL, MIN(S.PRICE) PRICE, P.NAME_EXT, 0 PACKAGE_FLAG INTO #OUT FROM PRODUCT P, SKU S WHERE P.PRODUCT_ID IN ( select PRODUCT_ID from product where name like '%' + @txt + '%' ) AND P.PRODUCT_ID=S.PRODUCT_ID AND @today between P.DATE_ACTIVATE AND P.DATE_DEACTIVATE AND P.ACTIVE = 1 AND (P.DELETE_FLAG = 0 OR P.DELETE_FLAG IS NULL) GROUP BY P.PRODUCT_ID,P.NAME,P.SHORT_DESC,P.THUMBNAIL,P.NAM E_EXT UPDATE #OUT SET PACKAGE_FLAG = 1 WHERE PRODUCT_ID IN ( SELECT PRODUCT_GROUP_PRODUCT_ID FROM PRODUCT_GROUP G WHERE PRODUCT_GROUP_SUB_TYPE_ID IN ( SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE PRODUCT_GROUP_TYPE_ID =1) ) -- 1 IS QUALITIES USED FOR PACKAGE OPTIONS OR PRODUCT_ID IN( SELECT PRODUCT_ID FROM PRODUCT_GROUP G WHERE PRODUCT_GROUP_SUB_TYPE_ID IN ( SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE PRODUCT_GROUP_TYPE_ID =1) AND PRODUCT_ID <> PRODUCT_GROUP_PRODUCT_ID ) SELECT top 100 * FROM #OUT GO
View Replies !
View Related
Searching Throug TEXT Fields
I have been trying to write a query that will allow me to search through text fields. This is a problem because SQL doesn't let me use any functions on TEXT datatypes. When I was using access I did it like this: where upper(searchtext) like ('%SEARCHSTRING%') However UPPER doesn't work on text fields (I want the search to be case insensitive) I tried this: where patindex(searchtext, '%SEARCHSTRING%') <> 0 but that is not case-insensitive... Help me SQL gurus, you are my only hope
View Replies !
View Related
Full-Text Searching Of Symbols
I have successfully set up a full-text catalog for a blob field in one of our tables. I am trying to search for words like 'C++' and 'A++' or 'C'. I know who to accomplish the single letter part, by removing it from the NOISE.ENU file, but how do I get it to recognize the ++ part? I need to be able to search on -, _, = and +. If anyone knows anything, it is much appreciated. Jake Massey
View Replies !
View Related
PROBLEMS WITH FULL TEXT SEARCHING
I am very new to SQL and Access projects. 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. Many thanks for any help you can offer.
View Replies !
View Related
Is Column Weighting In Full Text Searching Possible?
Hello, I'm using containstable to do full text searching. I know that you can provide weights on the actual search term entered by the user. However, is it possible to add weights/priorities on certain columns that are being searched? My containstable statement is currently the following: ... containstable(Articles, (Title, Abstract, Keywords, Body), @SearchTerm) ...However, I want the rows that match on the "Title" column to be ranked higher than the other rows.
View Replies !
View Related
Full Text Searching....THOUSANDS Of Records!
Hope I am in the correct section. 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? Thanks! cbmeeks
View Replies !
View Related
Full Text Searching Eventually Utilizes 100% CPU
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] GO
View Replies !
View Related
Full Text Searching Eventually Utilizes 100% CPU
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] GO
View Replies !
View Related
|
TRACKER
