Sql Performance Tips

Hi here some more important tips on Sql Performance,that will help you most:

Try To Avoid Sub Queries: Always try to avoid sub queries. Because sub queries hit logs many times that’s why log file get overloaded, so in place of sub queries use temporary table which create log into tempdb and which will not cause the slow performance.
A few tips for using temp tables in an efficient manner.
• Only include columns or rows you actually instead on using
• Do not use the select into statement; It places a lock on system objects
• Always delete the temp table yourself... that’s just a waste of resources
• If the temp db is not already on its own disk consider doing this will help greatly In closing I would like to say, I personally think that temp tables are a great tool and, if used correctly can be the best situation.

Use SET NOCOUNT ON statement: In a Stored Procedure many times you have seen that with every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is useful in debugging but it is useless after that. By setting SET NOCOUNT to ON, we can disable this feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO


Not to use “sp_” in User Defined Stored procedure: As it is cleared that in sys stored procedure are started with “sp_”. So if you are creating a sp by naming convention starts with “sp_” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)
If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25
The compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

Important Topic working with Temporary Tables

Temp tables use (or the lack of use) has a great impact on a lot of different aspects of the database.
Temp tables can cause a very negative impact on performance if used incorrectly and even if you use them in an efficient t manner you will still be taking a performance hit.
By using derived tables (Sub queries) you will hit your data in a more efficient way.
That being said sometimes temp tables can be the best solution for your current situation.
Some benefits of tem tables are
• They are sometimes much easier to create then trying to formulate the same result by using a sub query
• Makes production code cleaner; sub queries can get really messy.
• in data analysis tasks or in situations where the code will never hit a production database they are one of our greatest tools

This being said it is imposable to overlook the performance bottle neck that these little guys can inflict on a database. Let’s take a look at what is involved when SQL encounters a tem temp table
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read activity)
5) DROP TABLE (write activity)
4) Release the locks
This represents a lot of activity under the hood for the database engine and will usually result in poorly performing chunks of code.
This being said I have come across many situations where I have used temp tables in my production code as a last resort. As with everything it depends on the situation.

One example of this is when the situation arises when you need to use a record set generated from one stored procedure in another stored procedure as this is the only way to do this without using a physical table.

A few tips for using temp tables in a efficient manner.
• Only include columns or rows you actually instead on using
• Do not use the select into statement; It places a lock on system objects
• Always delete the temp table yourself... that’s just a waste of resources
• If the temp db is not already on its own disk consider doing this This will help greatly
In closing I would like to say, I personally think that temp tables are a great tool and , if used correctly can be the best situation. As always we must understand the impact of these little guys before we start using them.

Types of Temporary tables
There are two types of temporary data types. They are namely Local and global. Let me first give you and example to start the temporary table. Following example is taken from Books on Line of Microsoft® SQL Server™ 2000.

"The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server" 1

"For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect."2

Note: 1,2 above is taken from the book on line sub heading Creating and Modifying

Unlike in the access days, you do not have to delete these temporary tables manually, instead you can rely SQL Server to do it automatically.

SQL Tuning/SQL Optimization Techniques

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example:
Write the query as


SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:


SELECT * FROM student_details;


2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.


For Example: Write the query as

SELECT subject, count(subject)

FROM student_details

WHERE subject != 'Science'

AND subject != 'Maths'

GROUP BY subject;

Instead of:


SELECT subject, count(subject)

FROM student_details

GROUP BY subject

HAVING subject!= 'Vancouver' AND subject!= 'Toronto';


3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.


For Example: Write the query as

SELECT name

FROM employee

WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)

FROM employee_details)

AND dept = 'Electronics';


Instead of:

SELECT name

FROM employee

WHERE salary = (SELECT MAX(salary) FROM employee_details)

AND age = (SELECT MAX(age) FROM employee_details)

AND emp_dept = 'Electronics';


4) Use operator EXISTS, IN and table joins appropriately in your query.


a) Usually IN has the slowest performance.

b) IN is efficient when most of the filter criteria is in the sub-query.

c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as

Select * from product p

where EXISTS
(select * from order_items o

where o.product_id = p.product_id)

Instead of:

Select * from product p

where product_id IN

(select product_id from order_items


5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.


For Example: Write the query as

SELECT d.dept_id, d.dept

FROM dept d

WHERE EXISTS ( SELECT 'X'
FROM employee e
WHERE e.dept = d.dept);
Instead of:

SELECT DISTINCT d.dept_id, d.dept

FROM dept d,employee e

WHERE e.dept = e.dept;


6) Try to use UNION ALL in place of UNION.


For Example: Write the query as

SELECT id, first_name

FROM student_details_class10

UNION ALL

SELECT id, first_name

FROM sports_team;

Instead of:

SELECT id, first_name, subject

FROM student_details_class10

UNION

SELECT id, first_name

FROM sports_team;




7) Be careful while using conditions in WHERE clause.


For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age

FROM student_details

WHERE first_name LIKE 'Chan%';
Instead of:

SELECT id, first_name, age

FROM student_details

WHERE SUBSTR(first_name,1,3) = 'Cha';


Write the query as

SELECT id, first_name, age

FROM student_details

WHERE first_name LIKE NVL ( :name, '%');
Instead of:

SELECT id, first_name, age

FROM student_details

WHERE first_name = NVL ( :name, first_name);


Write the query as

SELECT product_id, product_name

FROM product

WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:

SELECT product_id, product_name

FROM product

WHERE unit_price >= MAX(unit_price)

and unit_price <= MIN(unit_price)


Write the query as

SELECT id, name, salary

FROM employee

WHERE dept = 'Electronics'

AND location = 'Bangalore';
Instead of:

SELECT id, name, salary

FROM employee

WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary

FROM employee

WHERE salary < 25000;

Instead of:

SELECT id, name, salary

FROM employee

WHERE salary + 10000 < 35000;


Write the query as

SELECT id, first_name, age

FROM student_details

WHERE age > 10;
Instead of:

SELECT id, first_name, age

FROM student_details

WHERE age NOT = 10;


8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively.
DECODE can also be made used in place of GROUP BY or ORDER BY clause.


For Example: Write the query as

SELECT id
FROM employee

WHERE name LIKE 'Ramesh%'

and location = 'Bangalore';
Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id
FROM employee

WHERE name LIKE 'Ramesh%';


9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.
a) Use single case for all SQL verbs

b) Begin all SQL verbs on a new line

c) Separate all words with a single space

d) Right or left aligning verbs within the initial SQL verb

Sql Connector class in C#.net

HI Developers,

Under bellow I have written a code for sql Connector class,this class is more similar to MicrosftApplicationdataBlock class.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Sql;
using System.Configuration;


namespace DAL
{
public static class SqlConnector
{
private static string connection_str = string.Empty;
public static SqlConnection getOpenSqlCon()
{
connection_str = Convert.ToString(ConfigurationSettings.AppSettings["con"]);
SqlConnection con = new SqlConnection(connection_str);
if (con.State != ConnectionState.Open)
{
try
{
con.Open();
}
catch (Exception ex) { }
finally { }
}
return con;
}
public static Boolean closeSqlCon(SqlConnection con)
{
bool result = false;
if (con.State != ConnectionState.Closed)
{
try
{
con.Close();
result = true;
}
catch (Exception) { }
finally { }
}
return result;
}
public static DataSet getDataSet(string query)
{
SqlConnection con = SqlConnector.getOpenSqlCon();
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex) { }
finally { }
SqlConnector.closeSqlCon(con);
return ds;
}
public static int execQ(string query)
{
SqlConnection con = SqlConnector.getOpenSqlCon();
SqlCommand command = new SqlCommand(query, con);
int result = 0;
try
{
result = command.ExecuteNonQuery();
}
catch (Exception ex) { }
finally { }
SqlConnector.closeSqlCon(con);
return result;
}
public static object execQScalar(string query)
{
SqlConnection con = SqlConnector.getOpenSqlCon();
SqlCommand command = new SqlCommand(query, con);
object result = null;
try
{
result = command.ExecuteScalar();
}
catch (Exception ex) { }
finally { }
SqlConnector.closeSqlCon(con);
return result;
}
/// <summary>
/// Executes a stored procedure
/// </summary>
/// <param name="strStoredProc"></param>
/// <param name="sqlParams"></param>
public static void ExecuteSP(string strStoredProc, SqlParameter[] sqlParams)
{
SqlConnection con = SqlConnector.getOpenSqlCon();
SqlCommand SqlComm = new SqlCommand(strStoredProc, con);
SqlComm.CommandType = CommandType.StoredProcedure;
SqlComm.CommandTimeout = 780;
if (sqlParams != null) {
foreach (SqlParameter sqlParam in sqlParams) {
SqlComm.Parameters.Add(sqlParam);
}
}
try {
SqlComm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally {
SqlComm = null;
sqlParams = null;
SqlConnector.closeSqlCon(con);
}
}
public static void ExecuteSP(string strStoredProc, SqlParameter[] sqlParams,SqlTransaction tran,SqlConnection con)
{
SqlCommand SqlComm = new SqlCommand(strStoredProc, con,tran);
SqlComm.CommandType = CommandType.StoredProcedure;
SqlComm.CommandTimeout = 780;
if (sqlParams != null)
{
foreach (SqlParameter sqlParam in sqlParams)
{
SqlComm.Parameters.Add(sqlParam);
}
}
try
{
SqlComm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlComm = null;
sqlParams = null;
}

}
/// <summary>
///
/// </summary>
/// <param name="strStoredProc"></param>
/// <param name="sqlParams"></param>
public static DataSet getDataSetExecuteSP(string strStoredProc, SqlParameter[] sqlParams)
{
SqlConnection con = SqlConnector.getOpenSqlCon();
SqlCommand SqlComm = new SqlCommand(strStoredProc, con);
SqlComm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(SqlComm);
DataSet ds = new DataSet();
SqlComm.CommandTimeout =780;
if (sqlParams != null) {
foreach (SqlParameter sqlParam in sqlParams) {
SqlComm.Parameters.Add(sqlParam);
}
}
try
{
da.Fill(ds);
}
catch (Exception ex)
{
}
finally
{
SqlComm = null;
sqlParams = null;
SqlConnector.closeSqlCon(con);
}
return ds;
}
public static DataSet getDataSetExecuteSP(string strStoredProc, SqlParameter[] sqlParams,SqlConnection con, SqlTransaction tran)
{
SqlCommand SqlComm = new SqlCommand(strStoredProc, con,tran);
SqlComm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(SqlComm);
DataSet ds = new DataSet();
SqlComm.CommandTimeout = 780;
if (sqlParams != null)

{

foreach (SqlParameter sqlParam in sqlParams)
{
SqlComm.Parameters.Add(sqlParam);
}
}
try
{
da.Fill(ds);
}
catch (Exception ex)
{
}
finally
{
SqlComm = null;
sqlParams = null;
}
return ds;
}
public static DataTable getDataTableExecuteSP(string strStoredProc, SqlParameter[] sqlParams)
{
SqlConnection con = SqlConnector.getOpenSqlCon();
SqlCommand SqlComm = new SqlCommand(strStoredProc, con);
SqlComm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(SqlComm);
DataTable dt = new DataTable();
SqlComm.CommandTimeout = 780;
if (sqlParams != null)
{
foreach (SqlParameter sqlParam in sqlParams)
{
SqlComm.Parameters.Add(sqlParam);
}
}
try
{
da.Fill(dt);
}
catch (Exception ex)
{
}
finally
{
SqlComm = null;
sqlParams = null;
SqlConnector.closeSqlCon(con);
}
return dt;
}
public static int ExecuteNonQuery(string strStoredProc, SqlParameter[] sqlParams)
{
SqlConnection con = SqlConnector.getOpenSqlCon();
SqlCommand SqlComm = new SqlCommand(strStoredProc, con);
SqlComm.CommandType = CommandType.StoredProcedure;
int result = 0;
SqlComm.CommandTimeout = 780;
if (sqlParams != null)
{
foreach (SqlParameter sqlParam in sqlParams)
{
SqlComm.Parameters.Add(sqlParam);
}
}
try
{
result = SqlComm.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
SqlComm = null;
sqlParams = null;
SqlConnector.closeSqlCon(con);
}
return result;
}
}

}

Best way to Remove Duplicate Records with help of CTE in sql

Nice method to avoid using temp/derived and group by operation in removing duplicate records.


COMMON TABLE EXPRESSION(CTE)

DROP TABLE DuplicateRcordTableCREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)INSERT INTO DuplicateRcordTableSELECT 1, 1UNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 2UNION ALLSELECT 1, 2 --duplicateUNION ALLSELECT 1, 3UNION ALLSELECT 1, 4SELECT * FROM DuplicateRcordTableWITH CTE (COl1,Col2, DuplicateCount)AS(SELECT COl1,Col2,ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount DuplicateRcordTable)DELETE
FROM CTEWHERE
GO

Thanks

Puneet Kumar

DuplicateCount > 1

FROM

Index Rebuilt SQL Query

QUERY to rebuild all indexes
USE DBNAME
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

Regards
Puneet Kumar

Remove unrecognized characters while exporting excel from your display result

Hi Guys,

Many times you have find the issue of some unrecognized characters is coming while  exporting excel from your display result.

To resolve this issue many developers handled it via replacing  special character in other format in the database or in dataset, or doing some other things (Jugaadr) .But I have find the absolute solution for this, which I want to share you all ,The solution of the issue is :

Add two line in your excel export  code.

Response.Charset = "";

Response.ContentEncoding = System.Text.Encoding.Default;

By adding this you will never find any unrecognized character in excel while exporting.

Hope it will help you all.

Puneet Special Zone

Your favrouite Puneet Special Zone is now converted into happydeveloperzone

Hi All,

I have to inform you that my previous blog Puneet Special Zone (puneetspecialzone.007sites.com) has been expired.Now I have created this new blog for all developers,hope you all will like this blog as you are familiar with Puneet Special Zone.

So guys start asking your quries in Happy Developer Zone regarding sql ,I am again available for you all for support.

Thanks
Puneet Kumar Srivastava
Sr. Software Developers
Agilyst,Chandigarh
09803479904

Creating Global Table in T-Sql,and its advantage............

Hi,Here I am submitting an intresting topic related to global table, As every sql developer known red query. But the major problem is that ,in red query you can not create temporary table.But with use of global table you can create a temporary table with in in red query.

Example:

Declare @sql Varchar(Max)
set @sql ='Select * into #tempemp from table tblEmployee'
exec(@sql)

 It will gives you an error, so here you can create a Global table,which is also a temporary table but have a global access...

Declare @sql Varchar(Max)
set @sql ='Select * into ##tempemp from table tblEmployee'
exec(@sql)

Now this sql statement will execute easily..
In the above example ##tempemp is used as a gloabal table.

Hope this intresting thing will help to all developers...........

cheers

Puneet Kumar

 

How to achieve comma seperated entries in a table,expand in seperate rows by using xml.......

Hi,
 If you have data like this in your table:

AppId   Gender
1               x
2              y
3               x, y
4              x, y, z

and  you need to transform like this:
AppID          Gender
1                  x
2                  y
3                  x
3                  y
4                  x
4                  y
4                  z

Then you can achieve it without using loop or cursor, like this :

DECLARE @t TABLE (AppID INT, Gender VARCHAR(100))

INSERT INTO @t(AppID, Gender)

SELECT 1, 'x' UNION ALL

SELECT 2, 'y' UNION ALL

SELECT 3, 'x,y' UNION ALL

SELECT 4, 'x,y,z'

SELECT AppID,

i.value('.', 'VARCHAR(20)') AS Item

FROM

(

    SELECT

    AppID, Gender,

    CAST('<i>' + REPLACE(Gender, ',', '</i><i>') + '</i>' AS XML) AS GenderXml

    FROM @t

) a

CROSS APPLY GenderXml.nodes('//i') x(i)

Thanks

Puneet Kumar