Macronimous Web Design

1) Increase SQL Server stored procedure performance with following three tips:

        Stored procedures plays a vital role in enhancing the performance of the database. The following three tips will help us to maximize database performance, when stored procedures are used.

Use NOCOUNT:
           If NOCOUNT option is turned off, on each operation: stored procedures returns information concerning the number of rows affected and it increases network overhead. By default the Option NOCOUNT will be turned off.  If we turn on NOCOUNT option, stored procedures will block row count information so that it will reduce the network overhead occupied with information communication to the user.

Use return values:
           Queries are used to validate information or to return a single value. The return value of stored procedure can be used to retrieve a single value from a query and mainly useful when inserting a new record. The information necessary for retrieval is the primary key value. Place the statement “RETURN <value>” at the end of the query to utilize the return value in stored procedure.                 
        
Optimize table access with NOLOCK:
          Transaction safety is not mandatory for most of the database access. This is apparent in MySQL database product that it does not contribute any record locking capability. Any access to a database table or a stored procedure in SQL will make tremendous performance gains when a table hint is used, which lets the SQL engine ignore and not perform locks for a given function.

Liked the post? Feel free to Subscribe to this Feed

Related Posts

• Database Optimization Techniques to Increase SQL SERVER Performance – Part III – Index on multiple columns

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.