.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Full text performance of a certain query

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
I am doing a full text query on a very simple table and when I include the search term "y5v" in the conditions it slows the query down like 8 to 1. The table is defined as: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblTextSrchData]( [intItemId] [int] NOT NULL, [srchTxt] [varchar](max) NOT NULL, CONSTRAINT [PK_tblTextSrchData] PRIMARY KEY CLUSTERED ( [intItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Here is the query: SELECT II.intItemID, KEY_TBL.[RANK] as Score FROM tblItems II INNER JOIN CONTAINSTABLE(dbo.tblTextSrchData,srchTxt, '("capacitor" OR FORMSOF(INFLECTIONAL, "capacitor") OR "capacitor*") AND ("active" OR FORMSOF(INFLECTIONAL, "active") OR "active*") AND ("y5v" OR FORMSOF(INFLECTIONAL, "y5v") OR "y5v*")') KEY_TBL ON KEY_TBL.[KEY] = II.intItemID If I take any of the additional search terms out it improves performance.  If I remove the yv5 search term the results are returned in less than a second. What seems to be the problem is for some reason when I add yv5 it starts doing these sorts after the full text matching.  These sorts take most of the time.  I'm not sure

View Complete Post

More Related Resource Links

Nested Full Text Query and SQL syntax



Quick question, is it possible to use the SQL TOP syntax in a Full Search Query something like

Select Top 1 title from SCOPE() where = 'All Sites' AND Productid= '1234556'


Can you use nesteed select statments ?

Select title from SCOPE() where = 'All Sites' AND Productid=  (select productid from scope() where scope = 'all sites' AND product = 'example')


Dave Roberts


Is this load full or matching one only... coz of need to know performance



This my Two Table i have one doubt...

For my first table i have select Top 2 from Employee_info 

and second table itz just selct * from MDEp..

What my doubt is on loading the above report?

Is this load all the record from MDep or just matching one based on Employee_info..?

Basic Instincts: Increase LINQ Query Performance


Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008

Full text search in a Word document



  I´m currently programming a web search application in ASP.Net and having problem with full text searching. My requirements are:

1. Save a byte array from SQL server to word document as a temp file in a server side?

2. How to highlight the string I am currently searching in the Word document and return to the user the sentence with the highlighted string (like on Bing or any other search engines)? Is it possible to fullfill this requirement with full-text search integrated in SQL Server 2008?

Thanks in advance,




The file reached the maximum download limit. Check that the full text of the document can be meaning



I'm facing an issue with the indexing.

I have 1 WFE+Index server+DB server.

Index server is not installed with MS FIlter pack 1.0

When crawling, the there will be document with warning in crawl log:
The file reached the maximum download limit. Check that the full text of the document can be meaningfully crawled.

Documents that with warning are such as doc, ppt, xls, docs, ppts and many others.
However, I view into the successful crawled document, there are doucments with ext doc and ppt.

For large file index, there are MaxGrowthFactor + MaxDownLoadSize required to be added into the index server.

As my understanding is, MS Filter Pack should installed into index server(already did, correct me if i'm wrong).

I looked into the Office SharePoint Seach(CA>Services in farm), if the server is appointed to "Use this server as indexing server", then MS Filter Pack is suppose to be installed into that particular server as well.

At the bottom, there also has another option is "Use all web front end for crawling".

The question here is, IF the option "Use all web front end for crawling" is selected.
Does the WED FRONT END Server required to installed the Ms Filte

full-text search error


I have created procedure for full-text search and it works in SQL Management Studio 2008 (SQL Server 2008 Express) but in Visual Studio 2010 not - I have error:

Full-Text Search is not installed, or a full-text component cannot be loaded.

I have read somewhere that only admin can use full-text indexes - so how I can use full-text search in VS2010 ?

Query Performance & Overall Design - SSAS MDX WCF

Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

Maintenance plan to rebuild Full-text index ? (SQL 2005)

Is it possible, through the GUI Wizard, to create a Maintenance plan that rebuilds (rather than reorganise/optimise) a Full text index in 2005 ? The full text index is based on one table only - so would using the "Rebuild index task" in the Wizard do the trick ? I wouldn't mind if the other indexes on the table were rebuilt too, but main concern in the Full text Index. If not - then I guess it would be a scheduled job which does a ALTER FULLTEXT CATALOG catalog_name REBUILD, but I thought it would be nice to keep it within the - Management - Maintenance plans - section of SQL Management Studio. Also - should this be preceeded with a transaction log backup ? Had some issues rebuilding them last night - seemed a Log backup was needed.

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

Consistently running out of page file memory with full text indexer

Using MS SQL Server 2008 SP1 x64 Standard Edtion on Windows 2008 R2 Enterprise, I'm about to full-text index for the first time 1 table and 2 views. The table contains about 250'000 entries with a data space of 180 MB. As soon as I activate the full text indexing, the fdhost.exe task starts to consume slowly but surely all the available page file space (this can be easily watched using the Resource Monitor and the Commit Charge graph on the memory tab). Once all the virtual memory has been consumed, the server becomes unusable since it can't open any new windows any more, and RDP stops working. The machine specs are as follows: 12 GB of RAM 80 GB free on hard disk out of 136 GB 8 CPUs Custom size paging file with sizes between 24 GB - 60 GB (originally, this was system managed size, but then the server ran out of memory sooner) Max SQL server memory set to 6 GB (first 10 GB, then 8 GB) I've set the max fulltext crawl range to 8. During the indexing, the 8 CPUs are bit busy for a while, but not excessively. What is astonishing is that there is almost no use of physical memory during the indexing (I can see an increase from 2 GB to 3 GB which still leaves plenty of RAM available). Does anybody have an idea how I can convince fdhost.exe to consume physical memory and leave the paging memory alone? Or what else can I try?

MDX query using linked server - performance question

Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

query w.r.t to SharePoint 2010 Performance Planning & Extranet Topology

  1.      Which extranet topology to go with in case of exposing Intranet to Remote Employees / Partners ?   My understanding:   There are 3 extranet topologies : Edge Firewall , Back to Back , Split back to back   If Scenario is : a)      Only Employees needs to access Intranet from Home ,we can go for Edge Firewall b)      If partners & Employees , both needs to access Intranet ( it will have some partner sites ) , then we can go for Back to Back OR Split back to back .   Questions: ·         Is Understanding correct ? ·         Is there any factor which can help in making a decision between Back to Back Or Split back to back ?   2.      As part of sizing , I read that we should capture following information -          What should be maximum acceptable CPU utilization in production servers ? ( Example : 70%) -          What will be the concurrent number of users accessing portal during peak hour of a day ? ( Example 1000 users from total of 18000 users ) -          How many request per second are done to portal

Query Sharepoint list, rich text columns returning HTML tags

Hi! So I have an issue that maybe someone of you have encountered as well. I am querying a Sharepoint list and creating a report from the data collected from this list. This all works fine except that one of the columns in this list is of the type "Multiple lines of text" and also supports "rich text" with different fonts, sizes and so on. This has the effect that the text returned from this column is wrapped in HTML tags that specifies how the text should be formatted. Naturally I do not want this HTML code to appear in my report so for now I have used custom code to do a string.replace and replaced for example <div></div> and <br> with "". However, this column also has url references to documents and these links shows up like this "/servername/site/subsite/Gemensamma%20dokument/Mwh.docx">http://servername/site/subsite/Gemensamma%20dokument/Mwh.docx There is some sort of double reference to the document that I guess like the HTML tags are embedded in the answer from the rich text column and this is not so easy to do a string replace on because the url varies with the different document and document names. My question really is if anyone of you have done a similar report that I am doing, and have encountered this fenomen with this sharepoint column and how you did to solve this? I have tried to creat

Full Text Search Return no Result while "Like" does

Someone pls look at the picture and help me I tried Restart the serviceI tried restart the server But the number of row affected is still zero :(

Very slow performance with a query involving JOINS

I am experiencing extremely slow performance on a particular SQL query using SQL compact.  This same query completes in less than one second on my regular SQL server.  With SQL compact, I am actually not even sure if the query ever completes.  As I type this I am testing it from SQL management studio, and it's up to 17 minutes and 40 seconds still executing so far. Since I am not experiencing this on most of my other queries, I have little doubt the slowness is due to the volume of data in these tables, coupled with the fact that I am doing the joins.  I made sure that foreign key constraints are in place for the columns that the joins are based on.  From what I've read, this also ensures that appropriate indexes are created, although I am starting to wonder about that. This is the query: SELECT TOP (200) t.BusinessEntityID, t.BusinessName, t.IsTowTruck, t.IsHospital, t.TowTruckLastUsed, t.BusinessLocked, cn.Address1, cn.City, cn.State, cn.PostalCode, cnp.PhoneNumber as TopPhoneNo, GetDate() as LastUpdatedDate, GetDate() as CreatedDate FROM tblBusinessEntity t LEFT JOIN cnBusinessEntityLocation cn ON cn.cnID in (SELECT TOP (1) cnID FROM cnBusinessEntityLocation where cnBusinessEntityLocation.Active = 1 AND cnBusinessEntityLocation.BusinessEntityID = t.BusinessEntityID

MDX query performance is slow for complex logic implementation in Calculated Members.

We have implemented a calculated members which involves multiple calculated members  called in a nested e.q. Cal1 calls Cal2 and Cal2 calls  Cal3  and Cal3 calls Cal4 .  And when browing Cal1 through Excel it takes around 30 mins for getting result from a Partition having row count around 5 million. Follwing are the information on the scenario I am having: We are having partitioning in the cube. one partition contains around 4-6 million records Processers on Query Server: 4 quad core RAM on the Query Server: 64 GB Calculated members having simple calculation logics are giving results in no time. There is a Calculated members which calls 6 intermediate calculated in nested way and with small Data Set like 5-6 k rows it is running fine.   Any help will be much appreciated.   Regards, Sandeep

Need to convert query string(date - 09/15/2010) to a text format - yyyymmdd

I am fairly new to C # and need some assistance with a form page.  I have created a simple form that requires the user to select a beginning date and ending date.  I then pass the values to my code behind page via query strings.  I have them passing, but I need the format to be change to a different format.  Unfortunately the database does not use the standard date format.  It requires the date to be in a text format as YYYYMMDD.Form: <tr> <td> Beginning Date:</td> <td><asp:TextBox runat="server" ID="begDate"></asp:TextBox> <asp:Image ID="Image2" ImageUrl="~/images/calendar_schedulehs.png" runat="server" /></td> </tr> Code Behind: protected void createReport_Click(object sender, EventArgs e) { StringBuilder url = new StringBuilder("Report/TopCustomer.aspx?"); url.Append("BegDate=" + begDate.Text); url.Append("&EndDate=" + endDate.Text); url.Append("&Brnch=" + ddlLocation.SelectedValue); Response.Redirect(url.ToString()); Current URL: TopCustomer.aspx?BegDate=09/15/2010&EndDate=09/30/2010&Brnch=300I need to have the above URL change to:TopCustomer.aspx?BegDate=20100915&E
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend