Code4asp.net

Best Practices for Query Optimization

• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-clustered index
• Non-clustered index should be created on columns of table based on query which is running
• Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY  clause,  d)  SELECT clause
• Do not to use Views or replace views with original source table
• Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
• Remove any adhoc queries and use Stored Procedure instead
• Check if there is atleast 30% HHD is empty – it improves the performance a bit
• If possible move the logic of UDF to SP as well
• Remove * from SELECT and use columns which are only necessary in code
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO,     etc)

Source : SqlAuthority

Sample:
CREATE  PROCEDURE InsertEmployees
/* ———————————————————-
Parameters
In :
@strXML = contains employee list as XML
Example:   ‘<root>
<emp EmpName=”Srinath” EmpSalary=”10000″ />
<emp EmpName=”sree” EmpSalary=”15000″ />
</root>’
———————————————————- */
@strXML varchar(8000)
AS

Declare @intPointer int

exec sp_xml_preparedocument @intPointer output, @strXML

INSERT into employee

SELECT  EmpName, EmpSalary

FROM OpenXml(@intPointer,’/root/emp’,2)

WITH EmpName varchar(20) ‘@EmpName’ , EmpSalary varchar(20) ‘@EmpSalary’)

exec sp_xml_removedocument @intPointer

———————–

EXEC InsertEmployees ‘<root><emp EmpName=”Srinath” EmpSalary=”10000″ /><emp EmpName=”sree” EmpSalary=”15000″ /></root>’

sp_xml_preparedocument

Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes in the XML document: elements, attributes, text, comments, and so on.

sp_xml_preparedocument returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing sp_xml_removedocument.

OPENXML

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

Sample:

OpenXml(@intPointer,’/root/emp’,2)

WITH EmpName varchar(20) ‘@EmpName’ , EmpSalary varchar(20) ‘@EmpSalary’)

sp_xml_removedocument

Removes the internal representation of the XML document specified bythe document handle and invalidates the document handle. To avoidrunning out of memory, will run sp_xml_removedocument to free up thememory.