SQL Server Performance Forum – Threads Archive

Output variables versus a Select?

Hi, I’m creating a web applicating using .NET. Is it more faster/performant to use OUTPUT varialbes in a stored procedure to return 3 results back or should I just do a SELECT statement? eg. @MyParamter INT OUTPUT or do: SELELCT @MyParameter ?

---

From a SQL Server perspective it’s a little less work to do the simple SELECT. If you run the same query written two different ways, with the following on:
–Server Trace
–Execution Plan You will see that the query with OUTPUT produces more reads, has more steps in the execution plan, and has a higher subtree cost. It also requires more server trips, resulting in bigger packet traffic. You need to weigh this with the cost of processing a recordset in the application vs receiving an output to process. The decision will come down to a case by case basis unless you just make a blanket rule and decide to be lighter on the SQL Server or the application side. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Not using a recordset on the client end is often a big performance gain. At least on standard ADO where you could add the AdExecuteNoRecords option when running a stored procedure via the Command object. A similar option exist in ADO.NET so it should be the same there. You can see an example of this if you use the Data Access Application Block (DAAB) from Microsoft (version 2.0):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp There is also a version 3.0 of the DAAB available here:
http://www.gotdotnet.com/Community/Workspaces/workspace.aspx?id=c20d12b0-af52-402b-9b7c-aaeb21d1f431

---

I am new to ASP.NET/SQL Server/C# programming and have the same question. Does anyone know where I could find an example of how to accomplish this using SQL Server .Net provider? Thanks,
Ernie

---

Have you looked at examples from www.asp.net? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

OK I have to disagree with derrickleggett. I use OUTPUT variables all the time depending on the situation they can improve performance of the overall app (but don’t expect a miracle). Upon checking with the Execution Plan and Server Trace all of mine produced the exact same reads and overall load at the server with either the OUTPUT variable version or a recordset version. derrickleggett’s situation had to have some other factor not included in mine. It will have decreases network utilization and has a smaller memory footprint if used properly but these are programming factors. When you want to use output variables and return no recordset you need to make sure of the following. 1) Make sure you use a SQLCommand Object and it is set to Stored Procedure. 2) Create and Append your parameters to the Command Object. 3) When executing the Command use the ExecuteNonQuery method as this let’s ADO not to expect a recordset to return. Do a search on ExecuteNonQuery or adExecuteNoRecords (the non .NET way) on google with site: microsoft.com in the criteria to get a a good deal of info back.

---

Using output variables can improve the performance if you’re only getting one row back. Otherwise, the performance drastically decreases because of round trip calls and higher processing. SQL Server is optimized for set-based processing. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

I see where yo are going but i depends for a correct answer if they are referring to 3 resulting rows or 3 resulting columns of a row such as first_nme, last_name and date_of_birth represent.

---

What????? Context is everything Antares. Of course, it depends on what they want. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>