By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,969 Members | 2,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,969 IT Pros & Developers. It's quick & easy.

Horrible performance when executing stored procedure

P: n/a
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

Nov 8 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
--
http://www.openmymind.net/
http://www.codebetter.com/
<st****@gmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
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

Nov 8 '06 #2

P: n/a
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)
<st****@gmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
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

Nov 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.