SP TimeOut in .NET code, not in Management Studio

Question

If I run the following in Management Studio (SQL Server 2008) :

exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] '4AFD6633-CB90-4165-913D-EE3EA74708DA', '7EF7CCB2-E09F-4408-AE2D-F857C063F2C1'

I get the result back in less than a second

I however I run it in VB.Net like this :

Using aConnection = New System.Data.SqlClient.SqlConnection(*** Some Connection String ***)
  aConnection.Open()
  Dim aCmd = aConnection.CreateCommand()
  aCmd.CommandText = "exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] '4AFD6633-CB90-4165-913D-EE3EA74708DA', '7EF7CCB2-E09F-4408-AE2D-F857C063F2C1'"
  aCmd.ExecuteNonQuery()
  aConnection.Close()
End Using

it times out (I know that ExecuteNonQuery does not return data, but I tried to keep the code as small as possible).

I've used the same DB, UserID and password in code as in the Management Studio en the Isolation Level is Read Comitted.

Anybody has any idea ?

Answer 1

Try turning on SQL Profiler and comparing what is being processed during the two calls.

Also, in Management Studio run these command prior to testing your procedure :

CHECKPOINT
DBCC DROPCLEANBUFFERS

These commands will make sure that your SSMS testing is getting a fresh start when it runs that procedure. It is likely that, rather than your VB.NET giving an erroneously slow result, your SSMS testing is giving a false quick one due to prior executions.

CHECKPOINT
DROPCLEANBUFFERS
SO post on subject