Managing Core SQL Server 2008 R2 Technologies - Microsoft SQL Server 2008 R2 Administration - page 188

set @param_definition = N'@param int, @Sales_person int output';
set @param_value = 10;
exec sp_executesql @sqlstring, @param_definition, @param = @param_
value, @Sales_person = @col2 output;
Let us execute a similar query using ADO.NET (default client-server application behavior).
Here, the data type and length of each parameter must be specified to avoid any slowdown
due to cache expansion:
cmd.CommandText = "SELECT Person.Name FROM dbo.Person WHERE Person.
Type = @1";
cmd.Parameters.Add("@1", SqlDbType.NVarChar).Value = "16";
cmd.Parameters.Add("@1", SqlDbType.NVarChar).Value = "97";
//this will have the cache expansion causing slowdown of performance.
(@1 nvarchar(1)) SELECT Person.Name FROM dbo.Person WHERE Person.Type
= @1
(@1 nvarchar(2)) SELECT Person.Name FROM dbo.Person WHERE Person.Type
= @1
// if the length is specified, there won't be any cache distend.
cmd.CommandText = " SELECT Person.Name FROM dbo.Person WHERE Person.
Type = @1";
cmd.Parameters.Add("@1", SqlDbType.NVarChar, 128).Value = "16";
(@1 nvarchar(128)) SELECT Person.Name FROM dbo.Person WHERE Person.
Type = @1
For frequently executed queries, use stored procedures and batching within the code. Always
limit the number of round-trips to the server and the number of columns to be returned.
The default connection timeout is 30 seconds and before reporting a connection failure,
it retries connections. If, for any reason, a huge volume of data is transmitted, it is ideal
to build the re-try logic in the application. To find out more about SQL Azure connection
retry, refer to the following MSDN blog post: http://blogs.msdn.com/b/bartr/
archive/2010/06/18/sql-azure-connection-retry.asp x .
If, for any reason, there is a connection failure, reconnect immediately.
If the connection failure persists, wait for 10 seconds and retry. Check the network for any
transient errors.
Also check the status of the SQL Azure service as per your geo-location. Refer to http://
www.microsoft.com/windowsazure/support/status/servicedashboard.aspx for
the current status.
You can also use the SQL Azure web page to report any live-site issue if the connectivity errors
are persistent.