Re: Full Text Search Timing Out with only 20 total users

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9483D46A-8F82-4494-BB5B-92D3C5E435AE@xxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Translate week number to date of the week.
    ...    Is there a quick way to convert week number to start ... RRRR') + rownum - 1 dt ... SQL> with date_wk as ( ... Reporting the dates having the desired week of the year ...
    (comp.databases.oracle.server)
  • Re: Select enumeration of intergers
    ... connect by rownum < 1000 ... Some years since I knew any SQL but I got a kick out of the exchange where Mikito said that UNION should be identical to OR and the Oracle 'oracle' gave an example where it's not. ... It also would allow a way to define a kind of enumeration of a finite domain/data type in a way that none of these products seem to, ...
    (comp.databases.theory)
  • Regarding some update SQL with a running sequence
    ... column of a table depending on its rownum given a particular order by ... EMPNO SAL NUM ... Could anybody please suggest some workaround SQL for this problem? ...
    (comp.databases.oracle.server)
  • Re: How can this be ? (rownum question)
    ... SQL> select countfrom user_objects; ... from (select rownum idx, object_name from user_objects) inv ... from (select rownum idx, ...
    (comp.databases.oracle.server)
Loading