Search Algorithm/Full Text Search


Hi ,

I want to search 27 millions of records within 2-3 seconds. when I run the T-SQL query it will take 45 seconds to give me result.

Please give me some proper search algorithm to sort this out.

Can I use Full Text Search
Can I take this data in xml file and do search on that

I used .net to accomplished this search task.

waiting for positive reply.

Posted On: Tuesday 20th of November 2012 12:03:22 AM Total Views:  66
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Query from text box

I am trying to get a query to return a value between the input of a text box and the option of a drop down list box.. I would like to have both ddlb's but this is proving to be too much as of yet.... anyway, here is what I have so far... Code: "SELECT * from dbo.contractBilled " & whereStmt & " OR bdate BETWEEN '" & (txtFrom) & "' AND '" & (ddlbTo) & "' order by bdate desc" As is, the code runs but no filtering takes place... if I change to txtFrom.text I get an error - object required txtFrom any ideas
VIEWS ON THIS POST

130

Posted on:

Tuesday 20th November 2012
View Replies!

The data types varchar and text are incompatible in the add operator.

I get the following error: The data types varchar and text are incompatible in the add operator. when I execute the following: SELECT Title FROM vtdThesisInfo WHERE Title like '%'+ @searchCriteria + '%' (it's a stored procedure). I am using SQL Server 2005
VIEWS ON THIS POST

193

Posted on:

Tuesday 20th November 2012
View Replies!

Query for asp page - search page with 7 tables

I'm working on an ASP (classic) search page that needs to query 7 tables. The tables have in common a StudentID. I've experimented several different ways, including a page with 7 separate queries, as well as nested loops, etc. I would like to, if possible, only have one query. Thus, I've tested such a query in Query Analyzer and have wound up with a huge set of results. In order to eliminate redundant data (many rows for each student), and have just one row for each student, is there a recommended technique I've been looking at the coalesce function - are there other ways to do this
VIEWS ON THIS POST

128

Posted on:

Tuesday 20th November 2012
View Replies!

Full-text index search (NOISE WORDS)

Hi , I have implemented a full-text index search succesfully on one table. The problem is that there is some "noise words" that I would like to remove from the list on my server. Apperently the file is located at: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData I see there is noise.txt files for the different languages, but if I remove a word from the file it still gives me an error when I run my Asp.Net application again. The error: Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words. There is also Xml files in the same folder for each language. Do I need to change anything in the Xml files. Please advise. Thank you for your time. Jo.
VIEWS ON THIS POST

118

Posted on:

Tuesday 20th November 2012
View Replies!

Data type for column which has numeric and text data

The employee ID column of the table has a mix of letters and numbers. For instance, A valid employee id could be 42599, JAPAN For instance, For the field names: Code: CREATE TABLE [Quota].[dbo].[Position] ( [# Position Name] nvarchar (255) NULL, [Effective Start Date] datetime NULL, [Description] nvarchar (255) NULL, [Incentive Start Date] datetime NULL, [Incentive End Date] datetime NULL, [Employee ID] nvarchar (255) NULL, [Title] nvarchar (255) NULL, [Person Name] nvarchar (255) NULL ) data could be Code: Insert into POSITION values ('AMERICAS', '', '', '01/01/2008', '', 'AMERICAS', 'Region','AMERICAS Region (AMERICAS)') Insert into POSITION values ('CS CM - US - Scheidemantel.D', '07/01/2009', '', '01/01/2008', '', '40881', 'Account Executive', 'David Scheidemantel (40881)') What should be the data type that accommodate both numeric and text data in the Employee ID When I run the import process through the DTS and make sure to transform the Employee ID to nvar(255), then it brings in only the text data in the Employee ID field during the import process. It fails to bring in the numeric employee id. Ideally since Employee ID is not a number in my data. However, when I execute the individual Insert statement in the Query Analyzer, it inserts with no issues. Any ideas
VIEWS ON THIS POST

279

Posted on:

Tuesday 20th November 2012
View Replies!

PhpMyAdmin to import a text file to a table

I am trying to import data to SQLServer DB using PhpMyAdmin. The data is in Microsoft Access. I figured out how to export data from Access table to .csv file. My question is how do i import this data to SQLServer DB I tried using LOAD DATA method but it gives me an error.
VIEWS ON THIS POST

229

Posted on:

Tuesday 20th November 2012
View Replies!

Cannot paste my text into column

Hi , I'm have a strange situation with SQL Server 2000. I am trying to paste this into a VARCHAR (8000): Admittedly this is a long piece of text but running a little letter count application I know that it is only 1211 characters long. However when I paste the above text into SQL Server I get this warning: "The value you entered not consistent with the data type or length of the column." I read on the web a little and discovered that somebody had this problem and they had used NVARCHAR instead which fixed it for them, this has not worked for myself though. Does anyone have any idea what SQL is objecting to
VIEWS ON THIS POST

114

Posted on:

Tuesday 20th November 2012
View Replies!

advance search sql query problem

I have tried do an advance search using the below code but it displays everying in the database it does not filter out the results based on the criteria the user wants, does anyone have any suggestions on where I have gone wrong in my sql statement. the sql statement is in a sub which is executed after validate. strphYSpecialty = trim(Request("Specialty")) strmedicalschool = trim(Request("medicalschool")) strphylastname = trim(Request("lastname")) strBoardStatus = trim(Request("BoardStatus")) strphyemail = trim(Request("email")) txtsql= "SELECT a.PhysicianId, a.PhySpecialty, a.phyemail, a.Phyhomephone,a.Phylastname, a.Phyfirstname, b.MedicalSchool, b.Internship, b.Residency, b.SalaryExpected,"_ & " b.BoardStatus,b.LicenseStates,b.SalaryExpected, b.SponsEmpVisaStatus"_ & " from Physiciancontact AS a inner JOIN physicianqualifications AS b ON a.physicianid = b.physicianid)"_ & " Where Specialty LIKE '" & strphYSpecialty & "%' " _ & " or MedicalSchool LIKE '" & strmedicalschool & "%' " _ & " or lastname LIKE '" & strphylastname& "%' " _ & " or BoardStatus LIKE '" & strphYSpecialty & "%' " _ & " or email LIKE '" & stremail & "%' " _ & " order by a.customerID;"
VIEWS ON THIS POST

208

Posted on:

Tuesday 20th November 2012
View Replies!

Extract text from a field

Hi in my tags field I have tagged dates between ^ eg: ^2007/04/16^ how in sql do I extract that text between the ^^ bear in mind i'm using that date in the same query to pull records.
VIEWS ON THIS POST

106

Posted on:

Tuesday 20th November 2012
View Replies!

Varchar vs text

If I have a varchar field that allows 500 characters, should it be a text field instead How many characters should my largest varchar be please
VIEWS ON THIS POST

88

Posted on:

Tuesday 20th November 2012
View Replies!

Probs with fulltext enabling

my code so far: Code: 'database connection details DBConnect = "Provider=MSDASQL; Driver={SQL Server}; Server=***; Database=master; UID=***; PWD=****;" Set dbConn = Server.CreateObject("ADODB.Connection") dbConn.ConnectionTimeout = Application("dbConn_ConnectionTimeout") dbConn.CommandTimeout = Application("dbConn_CommandTimeout") dbConn.Open DBConnect 'get the domain name + .co.za to use for db name serverName = Request.ServerVariables("Server_Name") serverName = Replace(serverName, "www.", "") serverName = Replace(serverName, ".", "") sqlCheck = "Select * from master.dbo.sysdatabases where name = '" & serverName & "'" set rs = dbConn.Execute(sqlCheck) if rs.EOF then 'create the db dbConn.Execute("CREATE DATABASE " & serverName & "") response.write "DATABASE " & serverName & " Created" 'enable new database for fulltext indexing dbConn.Execute("use " & serverName & " exec sp_fulltext_database 'enable'") response.write "fulltext indexing enabled on " & serverName & "" 'create contents table sqlContents = "exec sp_AddContents '" & serverName & "' " dbConn.Execute(sqlContents) response.write "Table contents Created" 'create full text indexing on contents 'sqlFullContents = "exec sp_Fulltext_Contents '" & serverName & "'" 'dbConn.Execute(sqlFullContents) catName = serverName & "_contents_catalog" dbConn.Execute("use " & serverName & " exec sp_fulltext_database 'enable'") dbConn.Execute("use " & serverName & " exec sp_fulltext_catalog '" & catName & "','create'") dbConn.Execute("use " & serverName & " exec sp_fulltext_table @tabname='contents', @action='create', @ftcat=[" & CatName & "], @keyname=[idkey]") response.write "Full text indexing on contents" and i'm getting this msg in the browser: Code: DATABASE ghaapjghinternetcoza Created fulltext indexing enabled on ghaapjghinternetcoza Table contents Created Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'ghaapjghinternetcoza'. C:\WEBSITES\GHAAP\../global/connections/dbCreate.asp, line 42 line 42: Code: dbConn.Execute("use " & serverName & " exec sp_fulltext_table @tabname='contents', @action='create', @ftcat=[" & CatName & "], @keyname=[idkey]") any ideas on this problem i've searched high and low Shem
VIEWS ON THIS POST

69

Posted on:

Tuesday 20th November 2012
View Replies!

SQL search statement Syntax

I have the following select statement comprised of a couple variables... Code: Set rs = conn.execute("SELECT * From tblMain " &strQueryString &strSearchField &strOrderBy ) This syntax works great for me as long as the strSearchField is a numeral, if I plug in a letter of any kind I get an error. I suspect I need to add quotes somewhere.. Here is where I set my variables... Code: strSearchField = CStr(request("Search") strSrchType = CStr(request("SrchType")) strWhere = " WHERE " strLike = " LIKE " If strSearchField "" AND strSrchType "" THEN strQueryString = strWhere strQueryString = strQueryString &strSrchType strQueryString = strQuerystring &strLike Else strQueryString = " " End IF ANy Help
VIEWS ON THIS POST

90

Posted on:

Tuesday 20th November 2012
View Replies!

sql text/memo field type problem

Hi all, I am new to the SQL server and had transferred data from an Access database. went well except one field - a memo type. Scenario: I had copied-and-pasted MS Powerpoint slide text to database. (Didn't want to retype data) As you know (from a MS user standpoint)this causes a minor problem with extra spaces and the curly quotes. The database formats these 2 things as question marks. Not too many to retype; however, on larger text fields I am "locked" out of the records that are relatively large. I am confused as to why I am able to alter the smaller (but same memo tyoe field data) I have it set up for nvchar 4000. These are Powerpoint texts. The text is no where near 4000 but I wasn't able to edit the larger input Please tell me where I am going wrong. I am used to a simple Access datase with either 1. text field or 2. a 64k memo field. What should I set this field to and perhaps I am missing some other setting on SQL server.
VIEWS ON THIS POST

283

Posted on:

Tuesday 20th November 2012
View Replies!

Select distinct with text datatype

Here is the code I'm trying to execute. SELECT DISTINCT Projects.PID, Projects.P_Name, Projects.P_Overview, Projects.P_Due_Date, Projects.P_Start_Date, Projects.P_Complete_Date, Projects.P_LastMod_Date, Users_2.UID, Users_2.F_Name, Users_2.L_Name FROM Users INNER JOIN Projects_Team ON Users.UID = Projects_Team.PT_Users_FK RIGHT OUTER JOIN Projects ON Projects_Team.PT_Projects_FK = Projects.PID LEFT OUTER JOIN Users Users_2 ON Projects.P_LastMod_By_FK = Users_2.UID LEFT OUTER JOIN Users Users_1 ON Projects.P_Created_By_FK = Users_1.UID; Obviously this query has multiple joins. The problem I'm having is that P_Overview is a text field, so obviously it won't work. The PID field is the primary key for this query, and I just want to eliminate duplicate PID records. I saw in another thread, someone had suggested placing the text field in it's own table and referencing it using a foreign key. How would this be done Wouldn't it still be pulled into the "select distinct" statement
VIEWS ON THIS POST

121

Posted on:

Tuesday 20th November 2012
View Replies!

Problem with CONTAINS and Free-text

Hiya, I'm trying to use the following to search 4 fields in total, 2 in one table two in another and return them as one recordset. strSQL = "SELECT id, tableID, tagline, content FROM tblNews WHERE CONTAINS (tagline, '" & searchString & "') OR CONTAINS (content, '%" & searchString & "%') UNION ALL SELECT id, tableID, tagline, content FROM tblDatabank WHERE CONTAINS (tagline, '" & searchString & "') OR CONTAINS (content, '" & searchString & "') ORDER BY id" I get the following error: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table 'tblNews' because it is not full-text indexed. I'm sure if I sort that out it will give me the same error on tblDatabank. I've tried to FREETEXT enable both tables with Enterprise Manager on SQL Server 7.0, but when I right click the tables in question the "Full-text index table" options are all greyed out. Where am I going wrong please
VIEWS ON THIS POST

136

Posted on:

Tuesday 20th November 2012
View Replies!

Character lenght of Ntext column

I am trying to count the characters contained in each record of my MS SQL server table. The below example works ifr the StateName column is of varchar datatypem, but it is ntext unfortunetly. Anyone has ideas how to get the amount of characters in each row The below example works for varchar columns: [highlight="SQL"]SELECT Len(StateName) FROM State WHERE StateName is not null[/highlight]
VIEWS ON THIS POST

100

Posted on:

Tuesday 20th November 2012
View Replies!

Auto save text to DB

, I use an Update button to save text from a .NET text box from a webpage to SQL server. Would much prefer to use some built in .NET or SQL server funtion to auto save anything I type. Should I just write a function to update on leaving the text box or is there a much better way to do this
VIEWS ON THIS POST

95

Posted on:

Tuesday 20th November 2012
View Replies!

Trying to refine search

Hi all, Need some help with a simple search page I've created in ASP. The SQL statement is as follows: "SELECT * FROM table WHERE make= '" & Request.Form("make") & "'" I have a previous page with a form on it and a form input box called 'make', the form posts the contents of 'make' to the page containing the above SQL statement. If I enter exactly what I know to be in "make" I get a result because I know I'm using the equals to in the SQL. I want the SQL to match any part of the form field 'make', I have tried using the LIKE statement but it to only returns results if it finds an exact match: "SELECT * FROM table WHERE make LIKE '" & Request.Form("make") & "'" I have tried to use various wild cards etc but with no sucess, any help would be gratefully recieved
VIEWS ON THIS POST

139

Posted on:

Monday 26th November 2012
View Replies!

Name searching

Hi all, I'm develping a name searching module in asp.net and sqlserver 2000 The Modules seraches a name aganist the table "Customers" on columns "FirstName" , "MiddleName" And "LastName" Can i use Soundex to this matching, since the content will also be in arabic, a search for "Kamel" should return all the following: - Alkamel - Elkamell - Al Kamel - El Kamel - AlKamal - ElKamal - Al Kamal - El Kamal Please provide your thoughts abt soundex and full text search
VIEWS ON THIS POST

148

Posted on:

Monday 26th November 2012
View Replies!