SQL Tips

This is a handy tip courtesy of Matt Woodward

you actually pay a penalty by not referring to the port directly in your connection strings.

For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number. If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.

Another tip from the Communications at A.C.M. magazine (06/2011 Vol. 54 No. 6) – 10 Rules for Scalable Performance in ‘Simple Operation’ Datastores (Pg 78) –

For security reasons, RDBMSs insist onthe application being run in a separate address space, using ODBC or JDBC for DBMS interaction. The overhead of these communication protocols is high; running a SQL transaction requires several back-and-forth messages over TCP/IP. Consequently, any programmer seriously interested in performance runs transactions using a stored-procedure interface, rather than SQL commands over ODBC/ JDBC. In the case of stored procedures, a transaction is a single over-and-back message. The DBMS further reduces communication overhead by batching multiple transactions in one call.

Popularity: 3% [?]