Horrible performance when executing stored procedure

[staeri]

When I execute a stored procedure with the following code it takes
forever and result in a timeout or a hang:

Sub TransformData()
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand("spImport_amount_transform",
myConnection)
myCommand.CommandType = CommandType.StoredProcedure

myCommand.CommandTimeout = 3600
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub

When I execute the same stored procedure through the Enterprise Manager
it takes less than a second.

What could be wrong and how should I start troubleshooting?

Regards,

SE

 

[Karl Seguin [MVP]]

that's odd.

I'd use SQL Profiler and try to see what's going on.

If you spend all your time executing spImport_amount_transform, then it's
still something to do with the sproc. Maybe when you were executing it from
EM, SQL Server had cached the tables/index/data. Otherwise, SQL Profiler
will show you authentication audits and all that so maybe it's something
wrong with that.

It's very hard to do ad hoc testing of SQL. I've had queries take 1 minute
to run, then I run them again and they take 2 seconds. SQL Server caches
aggresively and in development, when there isn't much activity, it tends to
make things act all bonkers as far as performance (i.e., things run a lot
faster than they should).

Karl

 

[bruce barker \(sqlwork.com\)]

this will be a query plan problem. generally when you get a difference like
this its a difference in settings (quoted identifier, ascii nulls, etc).

a sql profile will tell yo the settings asp.net is using. then run the
trace, you should see the same performance in QA. then you can correct it.

-- bruce (sqlwork.com)