[quoted text, click to view]

Yes, but it's not recommended - you should really use the SqlCommand
with the CommandType = StoredProcedure, and fill parameters.

Marc
================================================================
Marc Scheuner May The Source Be With You!

Hi,

Like I said, using the SqlParameters collection is a more "clean" way of
programming, and you don't have to worry about the order of parameters, but
the way that I want to do it makes it easier for debugging purposes. Mind
you there is nothing stopping me from using the SqlParameter collection and
I can always comment my method just to highlight my parameters in debug
mode, get all the values from the immediate window, and test in Query
Analyzer.

Thanx for your help !

E

--

[quoted text, click to view]

and the more recommended and the more performant, too - it's just a
matter of fact that using the CommandType = StoredProc results in a
more efficient call than calling the SP through a "EXEC <spname>"
using a CommandType = Text.

Marc
================================================================
Marc Scheuner May The Source Be With You!

Hi,

When developping an app, performance is key to me and my colleagues, so I
would have to convince them that this way actually "performs" better. So
how much really of a better performance do you get by using CommandType =
StoredProc vs. "EXEC <spname>" using a CommandType = Text ? Is it just
because CommandType = Text must parse the text in order for it to call the
stored procedure ? Either way, both methods are still calling the already
compiled stored procedure so I would only see a slight drop in performance
when CommandType = Text must be parsed. I guess the best way would be to
actually do a performance test but if you have any more input on this
subject, it would be greatly appreciated.

Thanx

E

--

[quoted text, click to view]

Amongst other things (from the "ADO.NET Best Practices" document by
Microsoft,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/adonetbest.asp?frame=true&hidetoc=true)

"Best Practices with SqlCommand

A quick tip for executing stored procedures using the SqlCommand: If
you are calling a stored procedure, specify a CommandType of
StoredProcedure for the CommandType property of the SqlCommand. This
removes the need to parse the command before execution, by explicitly
identifying it as a stored procedure."

Also, as mentioned by SQL Server architect Gert Drapers at TechEd
2004, using the "Execute <sproc name>" way of doing things is a so
called "Text Event", which is inherently less efficient than when
using the CommandType = StoredProc, in which case the stored proc is
called using a RPC (remote procedure call) event.

I can't quantify in terms of milliseconds or % CPU Load, but since
both a best practices document describes it, and SQL Server architects
recommend it, I guess there's a good reason why you should do it that
way! ;-)

Marc

================================================================
Marc Scheuner May The Source Be With You!

I am still curious to see however exactly how efficient and performing it
is between the two. Because yes I agree that a program should be written
with efficient code, except let's say that you want to test a Stored
Procedure in Query Analyzer, what would be the fastest way for you to get
the values of your parameters ? At least if you had Par1 + Par2 + Par3 +
Par4 + Par5, all you have to do is highlight your code, copy it, paste it
in the immediate window and BAM ! you have the results of your parameters
which you can copy and paste it in Query Analyzer. With the "more
effecient" way, you would have to get each and every value of your
parameters one at a time. I just see my way a more convenient way to get
the values of my parameters quick and easy. I know it's less efficient,
but to what point ? A performance test would do. For fun, I will run a
performance test calling a stored procedure like 1,000 times using both
methods and see what happens ! I'll keep you posted.

E

--

I just happened to do a test like this the other day...
* we wanted to return all columns of one row using straight sql, so we
passed in "select * from customer where key = 123", it took .03
seconds.
* then we put the select inside a stored proc, called the proc, passed
in the 123 argument, got the row back in 0.00 seconds.
To do this right we should have executed both thousands of times, but
life is short.
Yes, its faster, but I sure hate having logic spread out everywhere.
Now I have 50,000 lines of VB code, Plus 200 stored proc names to keep
in my head.