Poor Searching Performance after upgrade from TRIM 5 to TRIM 6R2

  • KM719538
  • 24-Apr-2009
  • 24-Apr-2009

Archived Content: This information is no longer maintained and is provided "as is" for your convenience.


After an upgrade from TRIM 5 to TRIM 6R2, the sql server profiler trace showed many values like: exec sp_cursorfetch 180150003,16,1,1. This slowed all searching queries for example, an 'all locations' search take many seconds to return 15,000 rows when it should be sub-second.


Sql Server 2005/2000
TRIM 6R2 (previously upgraded from TRIM V5)
1.        Database drivers are up-to-date.
2.        Other installations on-site perform fine.
3.        Database statistics up-to-date, database integrity check run.
4.        Poor SqlServer performance e.g. All locations search take many seconds to return 15,000 rows when it should be sub-second.
5.        SqlServer Profiler trace shows the following happening on the database:

exec sp_cursoropen @p1 output,N'SELECT uri,lcSrchName FROM TSLOCATION WITH (NOLOCK)   WHERE ( uri <> 0) ORDER BY lcSrchName ASC OPTION (FAST 30) ',@p3 output,@p4 output,@p5 output

select @p1, @p3, @p4, @p5
exec sp_cursorfetch 180150003,16,1,1
exec sp_cursorfetch 180150003,16,2,1
exec sp_cursorfetch 180150003,16,3,1
exec sp_cursorfetch 180150003,16,4,1


TRIM is using ‘server-side’ cursors. TRIM should not do this, it should use ‘client-side’ cursors with all the results being batched as one query and sent directly to the client for processing. This was discovered on a customer’s site where a 15000 rows query returning all locations was taking 8 seconds instead of sub-second on other servers. This happens because TRIM will open a cursor on the server and iterate through all 15000 rows before returning to the client.  This shows up as a single database query in the profiler with no exec sp_cursorfetch commands.
This issue is very well explained in the following article:
This issue is caused by a rogue obsolete setting from older versions of TRIM being migrated with the current version. The setting was deprecated in 6, release 2, but is still explained in the help file: Help ->Contents->Search (cursors)->Options Dialog Box


Check the TRIM config file for this setting:
C:\Program Files\TRIM Context\ServerData\TRIMconfig.tcfg
Open with : wordpad
Look for the XML Tag:  <SQLServerKeysetCursor>0</SQLServerKeysetCursor>
A setting of zero switches the system NOT to use server-side cursors.
Better still re-register the dataset in the TRIM Enterprise Studio as this setting has been known to change back after a workgroup server restart.