CommandType = TEXT vs Stored Procedure.

[Jack Jackson]

No. The SQL text will be passed to SQL Server, which will execute it on the
server, and return the selected records. Using stored procedures is fine, but
the performance difference is quite small in most cases.

Some people prefer SELECT statements and others prefer stored
procedures.

If you repeatedly execute the same SELECT statement changing only
values in the WHERE clause, then I think you get better performance by
using parameters for the values as that lets SQL Server cache the
execution plan of the SELECT.

I usually use SELECT statements rather than stored procedures. When I
have to make changes to the code as well as the SELECT statement, I
only have to change the app, instead of changing both the app and the
stored procedure. It also reduces the chance of the app and the
stored procedure not matching due to an app being updated but the
database not updated, or the reverse.

I use stored procedures for complex procedures that need to use
temporary tables and cursors, complex queries that are used in
multiple places, and for queries that are changeable by the end user.

 

[Greg]

BlankI've seen some examples where individuals pull data from SQL Server using the Command object in different ways. I've noticed in some cases the CommandType = Text is used and in other times the CommandType = Stored Procedure. Now, I always use CommandType = Stored Procedure as that is my preference.

What my question is though is whether there are performance considerations that must be considerred here? It's always been my belief that you use Stored Procedures when you are extracting one or more records from a table in SQL Server. You do not use CommandType = Text to select your data.

My question is:

CommandType = Text
CommandText = "Select * FROM tblCustomer " & _
"WHERE CustomerType = "RETAIL" " & _
"ORDER BY CustomerName"

In my cases I use the following:

CommandType = Stored Procedure
CommandText = "dbo.usp_Return_All_Retail_Customers"

Now, in sample one woudl the work much in the same manner as a MS Access query where all the data from the table is transferred over the network and then the worksstation filters the necessary records and returns them. So, would this not get rid of the performance gains offered by using SQL Server?

I use example 2 becuase the Stored Procedure is doing all the processing on the server and the results are only returned to the workstation, which would result in faster performance, correct?

So, in closing, am I correct that using the Text CommandType in this case is slower than using a Stored Procedure CommandType instead. Of course, this is assuming a very large volume of data.

Also, are there any processing / performance considerations when using the CommandType of Text when processing INSERT, DELETE statements as well?

Thanks in advance for your help.

Greg

 

[Cor Ligthert [MVP]]

Hi,

Almost nothing to add, only as I often write, let the maintainability be
your guide.

(In fact is this written in this answer thread)

Cor

 

[Michel Posseth [MCP]]

Some people prefer SELECT statements and others prefer stored
procedures.

If you repeatedly execute the same SELECT statement changing only
values in the WHERE clause, then I think you get better performance by
using parameters for the values as that lets SQL Server cache the
execution plan of the SELECT.

I usually use SELECT statements rather than stored procedures. When I
have to make changes to the code as well as the SELECT statement, I
only have to change the app, instead of changing both the app and the
stored procedure. It also reduces the chance of the app and the
stored procedure not matching due to an app being updated but the
database not updated, or the reverse.

Well in my office the argument to do everyhting with SP`s or IO routines in
seperate dll`s is that we do not want to have to send out a complete version
of our app just because we had some minor DB changes

I use stored procedures for complex procedures that need to use
temporary tables and cursors, complex queries that are used in
multiple places, and for queries that are changeable by the end user

I think it is just a mather of preference , and how familiar you are with
SQL Server ( or your prefered RDBMS ) Transact - SQL has become one of my
favorite toys you must know how manny progs i have seen where data tasks
are handled in the client application ( calculations on data fields, filters
on records etc etc )
where with some simple querying on the server side the performance of the
app boosted through the roof .

For verry small apps i can inmagine that someone uses plain sql , however
for buiness apps i would favor stored procedures not for perfomance , but
for grouping , maintainability , easier debugging , easier optimizing (
wich wil give you ... extra perfomance ) etc etc etc

In my opinion the TS is doing fine when using SP`s he did not choose the
easy way but the way with the most beuatifull view and perspective but
that is just my opinion .

Regards

Michel Posseth

 

[Cor Ligthert[MVP]]

Steve,

When I was reading your message I only could think: "Does Steve's
application more than managing the cursor"

Normally this should only be a minor issue to make a good solution.

Cor