Archived Content: This information is no longer maintained and is provided "as is" for your convenience.
Summary
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.
Error
Environment:
Sql Server 2005/2000
TRIM 6R2 (previously upgraded from TRIM V5)
Symptoms:
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
etc
etc
etc
|
Cause
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
Fix
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.