In your trace are you using sp:StmtStarting and sp:StmntCompleted?  If not add those as well as the query plan XML from the performance events.  The query plan will generate between the two sp: events mentioned.  Re-run your trace and check the query plan as well as the following in the sp:stmntCompleted line:

• Duration
• Reads
• Writes
• CPU

This will give you a little more information as to what is occurring.   If the time to complete the statement is quick and the trace hangs on the completed statement in the trace before the next statement records, then I would tend to believe the issue is network/client related and not SQL Server.

Thanks Lou.

Yes I'm using those settings. The XML execution plan is less than a second and the command actually being run is

exec sp_prepexec @p1 output,NULL,N'SELECT CommPlanID, CommPlanKey FROM tarCommPlan WHERE CompanyID = 'X' AND CompanyID = 'X'

I don't feel it is SQL, so maybe it is just something peculiar in our data that the SO Entry activity does not like. Very strange and frustrating.

Thanks Lou.

Yes I'm using those settings. The XML execution plan is less than a second and the command actually being run is

exec sp_prepexec @p1 output,NULL,N'SELECT CommPlanID, CommPlanKey FROM tarCommPlan WHERE CompanyID = 'X' AND CompanyID = 'X'

I don't feel it is SQL, so maybe it is just something peculiar in our data that the SO Entry activity does not like. Very strange and frustrating.

Jim,

I know you said it's fast in SQL but I'd try and rule out it 100%.  First I would delete and rebuild the indexes.  If that doesn't work then I'd drop and reload the table.  I assume it's slow when you access it on a test server...  When is the last time you ran DBCC ('DB')?

John

Thanks John.

I typically run DBCC as part of a routine maintenance schedule every weekend and yes, same on the test server. Index rebuild did not work unfortunately.

To double check I ran a full maintenance process on the DB but no luck there.

Ok my first suggestions are unhelpful.  Upon actual review I see that the code is loading a combo box.  I will bet you a trip to England (actually let's make it Paris) that if you delete 90% of your commission plans it will be fast.  Your only hope is to either reduce the number of commission plans or get someone to customize the screen so it's a lookup instead of a combo box.  :(

Thanks John, that confirms my suspicions. I will try suggesting this to Sage.