SP speed via VB.NET compared with Query Analyzer

[Darin]

I have a stored procedure with 15 arguments that, when run using Query
Analyzer takes 2.5 minutes to run. In my VB.NET code, when I go to run
the same SP with the same parameters, I get a timeout message after 15
minutes.

Why would VB take 5 times longer to run the same SP?

Below is how I run it in VB:

strcommand=new sqlcommand(in_sp, xconn)
strcommand.commandtype=commandtype.storedprocedure
for each xpar in in_param
xparam=new sqlparameter(xpar.setname, xpar.setvalue)
strcommand.parameters.add(xparam)
next
strcommand.timeout=900 '15 minutes
strcommand.executescalar

In_Param is my own class that has the parameter name and value in it.


TIA
Darin

 

[Jeremy Cowles]

This is not an ADO.NET 'group,
(news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet),
however, I would reccomend running the SqlServer Profiler. Have you tried
this to see *exactly* what is getting executed?

Again, the ADO.NET 'group or SQL Server 'group is going to be much more
insightful when it comes to this type of thing.

HTH,
Jeremy

 

[Alex Clark]

Hi,
Just a suggestion, but if you've enabled SQL Debugging in your project
properties, this will have a fairly moderate effect on performance when
executing SP's. Failing that, you might have more luck in the ADO.NET group
as I suspect this is something that's not directly related to VB.NET as much
as it is data access in the Framework, ie you'd probably get the same
behaviour if you were doing the same thing in C#.

Cheers,
Alex Clark