Thursday, July 1, 2010

SQL server performance turning

Following are some tips for performance turning

1) Try to use where clause for restrict result.
2)Use predecessor "dbo." for tables.
3)Use proper join ("INNER JOIN ,OUTER JOIN ")
4) Try to avoid "OR" condition use "UNION" over there.
5) Try to avoid "IN" Operation .
6) Try To avoid "NOT IN" operation
7) Try to avoid "DISTINCT".
8) Try to avoid "CROSS JOIN".
9) Try to avoid use of Temporary Table. but if needed then define pre structure for that.
9) Define PRIMARY Key & UNIQUE Key Constraint for each table.
10) Try to avoid "HAVING Clause"
11)Include "SET NOCOUNT" at the first of your store Procedure.
12) Try to avoid "CURSOR".
13) Use "UNION ALL" Instead Of "UNION".
14) Try to create INDEX.
15) Create Index On column which is frequently used in Where , order by & Join.
16) Try to create index on Integer Column.
17) try to avoid "SELECT * " instead of it use "SELECT columnname,"
18) Use Sp_ExecuteSQL instead of EXECUTE
19)Use Explicity Index "With( INDEX( INDEXNAME)) with table.
20) Maximize the thread.

Wednesday, February 17, 2010

Recursion in SQL

Stored Procedure for calculating the Recursion

CREATE PROC sp_Factorial
@InPara int,
@OutPara int OUTPUT
AS
DECLARE @InCalc int
DECLARE @OutCalc int
IF @InPara != 1
BEGIN
SELECT @InCalc = @InPara - 1
EXEC sp_Factorial @InCalc, @OutCalc OUTPUT
SELECT @OutPara = @InPara * @OutCalc
END
ELSE
BEGIN
SELECT @OutPara = 1
END
RETURN GO

Following is the code to execute the SP "sp_Factorial"

DECLARE @CalcOut int
DECLARE @CalcIn int
SELECT @CalcIn = 6
EXEC sp_Factorial @CalcIn, @CalcOut OUTPUT
PRINT ' factorial of ' + CAST(@CalcIn AS varchar) + 'is ' + CAST(@CalcOut AS varchar)
GO