|
|
You're in the
sql server full text search
group:Full Text Search Timing Out with only 20 total users
sql server full text search:
I am having a major issue with search and fulltext indexes. Here is the code I am using to add a fulltext to the User table: CREATE FULLTEXT INDEX ON dbo.[User] ( LastName ,FirstName ,EmailAddress ,Logon ,PhoneNumber) KEY INDEX pk_User_UserID It appears that after there is no activity for a period of time (say 2 hours) that searching screams to a halt and times out. There are currently only 20 users so the amount of data being retrieved from the stored procedure is very minimal. Once the search times out, the 2nd attempt seems to work just fine, even for 2 million users. Some facts that may or may not have anything to do with this odd behavior is the search is using dynamic sql and the ranking function SET @sql = N'SELECT u.Logon, act.Name AS AccountTypeName….(more columns called here)' + 'RANK() OVER (ORDER BY ' + @OrderBy + ') AS RowNum' + ' FROM dbo.[User] AS u LEFT OUTER JOIN dbo.Organization ….(more joins here)’ + ' JOIN #AccountTypeCodeList AS tcl ON act.Code = tcl.Code' + ' WHERE ''' + @SearchArgument +''' = '''' OR CONTAINS(u.*, ''' + @SearchArgument + ''')' + ' AND ISNULL(u.TerminationDate, '+ @NumTom + ')' + '>' + @NumNow This would be read as: SELECT u.Logon , act.Name AS AccountTypeName….(more columns called here) ,RANK() OVER (ORDER BY @OrderBy) AS RowNum FROM dbo.[User] AS u LEFT OUTER JOIN dbo.Organization ….(more joins here)’ JOIN #AccountTypeCodeList AS tcl ON …… WHERE @SearchArgument = '' OR CONTAINS(u.*, @SearchArgument) AND ISNULL(u.TerminationDate, @NumTom) > @NumNow The final search that is being used as the output is below: SELECT Logon ,RowNum… FROM @SearchUser WHERE RowNum BETWEEN ISNULL(@StartRowIndex,0) and ISNULL(@StartRowIndex,0)+@NumRows ORDER BY RowNum; Any help would be greatly appreciated. Thanks, Dan E.
study the execution plan to see if it uses a lazy spool. This seems to cause
these sorts of performance problems. I also don't understand why you are using a temp table, this also could be your problem. Also post the schema and indexes for the user and organization table. Can you check to see if the query works better without the rank function. -- relevantNoise - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Daniel Eimen" <Daniel Eimen@discussions.microsoft.com> wrote in message news:9483D46A-8F82-4494-BB5B-92D3C5E435AE@microsoft.com... > Hi SQL group, > > I am having a major issue with search and fulltext indexes. Here is the > code > I am using to add a fulltext to the User table: > > CREATE FULLTEXT INDEX ON dbo.[User] > ( LastName > ,FirstName > ,EmailAddress > ,Logon > ,PhoneNumber) > KEY INDEX pk_User_UserID > > It appears that after there is no activity for a period of time (say 2 > hours) that searching screams to a halt and times out. There are currently > only 20 users so the amount of data being retrieved from the stored > procedure > is very minimal. Once the search times out, the 2nd attempt seems to work > just fine, even for 2 million users. > > Some facts that may or may not have anything to do with this odd behavior > is > the search is using dynamic sql and the ranking function > > SET @sql = N'SELECT u.Logon, act.Name AS AccountTypeName..(more columns > called here)' > + 'RANK() OVER (ORDER BY ' + @OrderBy + ') AS RowNum' > + ' FROM dbo.[User] AS u LEFT OUTER JOIN dbo.Organization > ..(more joins here)' > + ' JOIN #AccountTypeCodeList AS tcl ON act.Code = > tcl.Code' > + ' WHERE ''' + @SearchArgument +''' = '''' OR > CONTAINS(u.*, ''' + @SearchArgument + ''')' > + ' AND ISNULL(u.TerminationDate, '+ @NumTom + ')' > + '>' + @NumNow > > This would be read as: > > SELECT u.Logon > , act.Name AS AccountTypeName..(more columns called here) > ,RANK() OVER (ORDER BY @OrderBy) AS RowNum > FROM dbo.[User] AS u > LEFT OUTER > JOIN dbo.Organization ..(more joins here)' > JOIN #AccountTypeCodeList AS tcl ON .. > WHERE @SearchArgument = '' > OR CONTAINS(u.*, @SearchArgument) > AND ISNULL(u.TerminationDate, @NumTom) > @NumNow > > > The final search that is being used as the output is below: > > SELECT Logon > ,RowNum. > FROM @SearchUser > WHERE RowNum BETWEEN ISNULL(@StartRowIndex,0) and > ISNULL(@StartRowIndex,0)+@NumRows > ORDER BY RowNum; > > Any help would be greatly appreciated. Thanks, > Dan E. >
I still don't know why this was the case, but the server was having major
issues with the full text indexing. When just performing a simply query (no joins) with CONTAINS, this query alone took 30 seconds to run. I installed sql server on a different box and my query with the multiple joins and dynamic sql ran very quickly. [quoted text, click to view] "Hilary Cotter" wrote:
> study the execution plan to see if it uses a lazy spool. This seems to cause > these sorts of performance problems. > > I also don't understand why you are using a temp table, this also could be > your problem. > > Also post the schema and indexes for the user and organization table. Can > you check to see if the query works better without the rank function. > > -- > relevantNoise - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Daniel Eimen" <Daniel Eimen@discussions.microsoft.com> wrote in message > news:9483D46A-8F82-4494-BB5B-92D3C5E435AE@microsoft.com... > > Hi SQL group, > > > > I am having a major issue with search and fulltext indexes. Here is the > > code > > I am using to add a fulltext to the User table: > > > > CREATE FULLTEXT INDEX ON dbo.[User] > > ( LastName > > ,FirstName > > ,EmailAddress > > ,Logon > > ,PhoneNumber) > > KEY INDEX pk_User_UserID > > > > It appears that after there is no activity for a period of time (say 2 > > hours) that searching screams to a halt and times out. There are currently > > only 20 users so the amount of data being retrieved from the stored > > procedure > > is very minimal. Once the search times out, the 2nd attempt seems to work > > just fine, even for 2 million users. > > > > Some facts that may or may not have anything to do with this odd behavior > > is > > the search is using dynamic sql and the ranking function > > > > SET @sql = N'SELECT u.Logon, act.Name AS AccountTypeName..(more columns > > called here)' > > + 'RANK() OVER (ORDER BY ' + @OrderBy + ') AS RowNum' > > + ' FROM dbo.[User] AS u LEFT OUTER JOIN dbo.Organization > > ..(more joins here)' > > + ' JOIN #AccountTypeCodeList AS tcl ON act.Code = > > tcl.Code' > > + ' WHERE ''' + @SearchArgument +''' = '''' OR > > CONTAINS(u.*, ''' + @SearchArgument + ''')' > > + ' AND ISNULL(u.TerminationDate, '+ @NumTom + ')' > > + '>' + @NumNow > > > > This would be read as: > > > > SELECT u.Logon > > , act.Name AS AccountTypeName..(more columns called here) > > ,RANK() OVER (ORDER BY @OrderBy) AS RowNum > > FROM dbo.[User] AS u > > LEFT OUTER > > JOIN dbo.Organization ..(more joins here)' > > JOIN #AccountTypeCodeList AS tcl ON .. > > WHERE @SearchArgument = '' > > OR CONTAINS(u.*, @SearchArgument) > > AND ISNULL(u.TerminationDate, @NumTom) > @NumNow > > > > > > The final search that is being used as the output is below: > > > > SELECT Logon > > ,RowNum. > > FROM @SearchUser > > WHERE RowNum BETWEEN ISNULL(@StartRowIndex,0) and > > ISNULL(@StartRowIndex,0)+@NumRows > > ORDER BY RowNum; > > > > Any help would be greatly appreciated. Thanks, > > Dan E. > > > >
Have you compared execution plans?
ML --- Matija Lah, SQL Server MVP
I would like to redirect my attention to a different matter that Hilary has
mentioned earlier that may be causing this issue. Let me rephrase: I want to run a query like this to obtain the RANK() (for dynamic ordering of the result) and to be able to return only a subset of data for paging (so if I want to display 10 rows per page, I can do this): SELECT u.Logon .....(more columns called here) ,RANK() OVER (ORDER BY @OrderBy) AS RowNum FROM dbo.[User] AS u LEFT OUTER JOIN .... WHERE @SearchArgument = '' OR CONTAINS(u.*, @SearchArgument) I do not want to display this select statement to the user since this is not the subset of results (so I can specifiy in my query 10 results per page, or 20 results per page). Also, I want to get the total count of results. Currently, the way I get the totalcount is the by performing a bulk insert into a temporary table which is very expensive. I currently do not know of a way to get the Total count and the end query which uses this temporary table: The final search that is being used (using the temporary table) as the output is below: SELECT Logon, ..... ,RowNum. FROM @SearchUser WHERE RowNum BETWEEN ISNULL(@StartRowIndex,0) and ISNULL(@StartRowIndex,0)+@NumRows ORDER BY RowNum; Here is my main question: 1) how can I get total count of results and the SELECT statement right above without using a temporary table, and do you see anywhere else where my logic is off? Thanks for everyone's help, Dan E. [quoted text, click to view] "ML" wrote:
> Have you compared execution plans? > > > ML > > --- > Matija Lah, SQL Server MVP
I would like to redirect my attention to a different matter that Hilary has
mentioned earlier that may be causing this issue. Let me rephrase: I want to run a query like this to obtain the RANK() (for dynamic ordering of the result) and to be able to return only a subset of data for paging (so if I want to display 10 rows per page, I can do this): SELECT u.Logon .....(more columns called here) ,RANK() OVER (ORDER BY @OrderBy) AS RowNum FROM dbo.[User] AS u LEFT OUTER JOIN .... WHERE @SearchArgument = '' OR CONTAINS(u.*, @SearchArgument) I do not want to display this select statement to the user since this is not the subset of results (so I can specifiy in my query 10 results per page, or 20 results per page). Also, I want to get the total count of results. Currently, the way I get the totalcount is the by performing a bulk insert into a temporary table which is very expensive. I currently do not know of a way to get the Total count and the end query which uses this temporary table: The final search that is being used (using the temporary table) as the output is below: SELECT Logon, ..... ,RowNum. FROM @SearchUser WHERE RowNum BETWEEN ISNULL(@StartRowIndex,0) and ISNULL(@StartRowIndex,0)+@NumRows ORDER BY RowNum; Here is my main question: 1) how can I get total count of results and the SELECT statement right above without using a temporary table, and do you see anywhere else where my logic is off? Thanks for everyone's help, Dan E. [quoted text, click to view] "ML" wrote:
> Have you compared execution plans? > > > ML > > --- > Matija Lah, SQL Server MVP
Where and why do you need the total row count?
ML --- Matija Lah, SQL Server MVP
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |