Query Optimization Tips

Before optimize any query clean buffer first after we can see what's exactly going on

  - DBCC dropcleanbuffer
  - DBCC FreeProcCatch

For See Statistsics Also See In Execution Plan

  - Set Statistics IO ON
  - Set Statistics Time ON

For Optimization We are Concentrate on 3 Most Statistic

1) NO of logical reads
2) CPU Time
3) Query Cost (QC)

Both 1st and 2nd You will Get From Upper Commands
But For QC Use Execution Plan

See and Note All Statistic and Then Apply This Instruction For optimization that Given Below:

1) Limit no of colums return from query
2) Create Primary Key
3) Create index which are using in workhours
4) Limit the no of rows by using top
5) Working with join have index on the column in the join
6) If you use multiple columns in where or order by clause then create compound index
7) If you are trying to fetch unique values use group by instead of distinct
8) Use exists in behalf of in().
9) set nocount on

Query Optimization OR and Union

When we execute query with OR clause,filtering the records from two 
different field. Oracle Database can not use index to filter the that.

1) create Index Idx_empno on emp(empno)

2) create Index Idx_salary on emp(salary)

Select * from emp
where empno = 7319 or salary > 15000

In this query Both the field have an index then also you can see
that it takes to much time because of "or" indexing field not work because of OR

so use Union to overcome this,

Select * from emp where empno = 7319
Union
Select * from emp where salary > 15000

 

Replace word in all table..

--To replace all occurences of 'America' with 'USA' in all Tables of database
EXEC SearchReplace 'America', 'USA'


CREATE PROC SearchReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
      ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' =  REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

System Configuration,Files and paths of Database,OS information

1) System Configuration
SELECT *
FROM sys.configurations
ORDER BY name OPTION (RECOMPILE);

2) Filename and Paths of Database
SELECT DB_NAME([database_id])AS [DBName],
name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) [SizeinMB]
FROM sys.master_files
ORDER BY DB_NAME([database_id])

3)OS level information
SELECT *
FROM sys.dm_os_sys_info

Xp_cmdshell For OS commands

Introduced in sql server 2005
xp_cmdshall option is a server configuration option that enables system administrator.


"xp_cmdshell" is an extended stored procedure provided by Microsoft and stored
in the master database. This procedure allows you to issue operating system commands
 directly to the Windows command shell via T-SQL code


-- To allow advanced options to be changed.


EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
________________________________________________________________
exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'
________________________________________________________________
exec master.dbo.xp_cmdshell 'mkdir "c:\temp\SQL Agent Output\new_job\"'
________________________________________________________________
DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell 'copy c:\temp\doesnotexist.txt c:\temp\workfile.txt'
print @rc
IF @rc <> 0
BEGIN
  PRINT 'Copy Failure Skip work'
END
ELSE
BEGIN
  Print 'Copy worked now we can do some more stuff'
  ....
END
________________________________________________________________

Server-side paging with Row_number()

If you are a programmer working with SQL Server, you must have found it little embarrassing to display information which spans across multiple pages (web pages). SQL Server 2000 did not allow you to retrieve a specific range of records, say, records 51 to 100 ordered by a certain column.

For example, assume that you are working on a web page which lists the names of all the cities in different countries. Assume that you need to display 25 records in a page. The database has 50,000 records consisting all the cities/towns across the globe. In the above scenario, it really makes sense to retrieve only the required records. for example, in the first page, retrieve 1 to 25 records. When the user clicks on "next" button, retrieve records 26 to 50 and so on. at this stage the user might click on another column to change the sort order. Earlier it was ordered by city name but now the display is based on Zip code.

With SQL Server 2000, it was not very easy to achieve this. Some times people used temp tables achieve this. Others put the paging responsibility to the application which retrieved all the records and then displayed the information needed for the current page. (this approach will not only overload server resources, but also degrades performance of the application as well as the database server.)

SQL Server 2005 introduces a helpful function ROW_NUMBER() which helps in this scenario. Using ROW_NUMBER()  you can generate a sequence number based on a given sort order and then select specific records from the results. Here is an example:

ROW_NUMBER() OVER (ORDER BY City) as Seq
The syntax ideally says the following. "Order the records by City, and then assign a serial number to each record". You can use it in a query as follows.

SELECT * FROM
(
    SELECT  ROW_NUMBER() OVER (ORDER BY City) AS row, *
    FROM Cities
) AS a WHERE row BETWEEN 101 AND 125

SQL Server CE 4.0 introduced a new TSQL extension that makes paging queries much easier. For example, to fetch rows 21 to 30, a query can be written like this.

SELECT * FROM Orders ORDER BY OrderID
OFFSET 20 ROWS
FETCH NEXT 10 ROW

Trigger with example

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.

Syntex:
CREATE TRIGGER [owner.]trigger_name

ON[owner.] table_name

FOR[INSERT/UPDATE/DELETE] AS

IF UPDATE(column_name)

[{AND/OR} UPDATE(COLUMN_NAME)...]

{ sql_statements }

INSERT trigger

When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out.

The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables.

DELETE trigger

When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE trigger

When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

For e.g
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2008R2 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

Simple Example of Cursor

Using cursor Back up of all database

DECLARE @DataBaseName VARCHAR(50)
DECLARE @Path VARCHAR(200)
DECLARE @DataBaseFileName VARCHAR(200)

SET @Path = 'E:\Backup\'

DECLARE DataBase_Cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('form')

OPEN DataBase_Cursor

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @DataBaseFileName = @Path + @DataBaseName + '.BAK'
BACKUP DATABASE @DataBaseName TO DISK = @DataBaseFileName

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName
END

CLOSE DataBase_Cursor
DEALLOCATE DataBase_Cursor

CSV Data (Comma Seperated value)

Declare @table table (CityName varchar(50))

insert into @table values ('London')
insert into @table values ('Birmingham')
insert into @table values ('Manchester')
insert into @table values ('Glasgow')

1._________________________________________________________________
declare @csv as varchar(500)
select @csv = case  when @csv is null then ''
else @csv + ',' end + CityName from @table
select @csv as csv

2.__________________________________________________________________
declare @csv as varchar(500)
SELECT @CSV = COALESCE(@csv + ', ','') + CityName
FROM @table
select @csv as csv

3.__________________________________________________________________
declare @csv as varchar(500)
set @csv = (select CityName + ',' from @table for xml path(''))
select @csv as csv

Date Formates

SELECT CONVERT(CHAR(101),getdate(),0) as "Feb  1 2012 11:11AM"
SELECT CONVERT(CHAR(101),getdate(),1) as "02/01/12"
SELECT CONVERT(CHAR(101),getdate(),2) as "12.02.01"
SELECT CONVERT(CHAR(101),getdate(),3) as "01/02/12"
SELECT CONVERT(CHAR(101),getdate(),4) as "01.02.12"
SELECT CONVERT(CHAR(101),getdate(),5) as "01-02-12"
SELECT CONVERT(CHAR(101),getdate(),6) as "01 Feb 12"
SELECT CONVERT(CHAR(101),getdate(),7) as "Feb 01,12"
SELECT CONVERT(CHAR(101),getdate(),8) as "11:13:35"
SELECT CONVERT(CHAR(101),getdate(),9) as "Feb  1 2012 11:13:53:717AM"
SELECT CONVERT(CHAR(101),getdate(),10)as "02-01-12"
SELECT CONVERT(CHAR(101),getdate(),11)as "12/02/01"
SELECT CONVERT(CHAR(101),getdate(),12)as "120201"
SELECT CONVERT(CHAR(101),getdate(),13)as "01 Feb 2012 11:15:15:107"
SELECT CONVERT(CHAR(101),getdate(),14)as "11:15:48:340"
SELECT CONVERT(CHAR(101),getdate(),20)as "2012-02-01 11:16:09"
SELECT CONVERT(CHAR(101),getdate(),21)as "2012-02-01 11:16:36.530"
SELECT CONVERT(CHAR(101),getdate(),22)as "02/01/12 11:17:11 AM"
SELECT CONVERT(CHAR(101),getdate(),23) as "2012-02-01"

SELECT CONVERT(CHAR(101),getdate(),24) as "15:10:46"
SELECT CONVERT(CHAR(101),getdate(),25) as "2012-02-01 15:11:22.310"
SELECT CONVERT(CHAR(101),getdate(),100) as "Feb  1 2012  3:11PM"
SELECT CONVERT(CHAR(101),getdate(),101) as "02/01/2012"
SELECT CONVERT(CHAR(101),getdate(),102) as "2012.02.01"
SELECT CONVERT(CHAR(101),getdate(),103) as "01/02/2012"
SELECT CONVERT(CHAR(101),getdate(),104) as "01.02.2012"
SELECT CONVERT(CHAR(101),getdate(),105) as "01-02-2012"
SELECT CONVERT(CHAR(101),getdate(),106) as "01 Feb 2012"
SELECT CONVERT(CHAR(101),getdate(),107) as "Feb 01,2012"
SELECT CONVERT(CHAR(101),getdate(),102) as "2012.02.01"
SELECT CONVERT(CHAR(101),getdate(),109) as "Feb  1 2012  3:14:15:187PM"
SELECT CONVERT(CHAR(101),getdate(),110) as "02-01-2012"
SELECT CONVERT(CHAR(101),getdate(),111) as "2012/02/01"
SELECT CONVERT(CHAR(101),getdate(),112) as "20120201"
SELECT CONVERT(CHAR(101),getdate(),113) as "01 Feb 2012 15:15:10:700"
SELECT CONVERT(CHAR(101),getdate(),114) as "15:15:36:607"
SELECT CONVERT(CHAR(101),getdate(),120) as "2012-02-01 15:15:55"
SELECT CONVERT(CHAR(101),getdate(),121) as "2012-02-01 15:16:21.857"
SELECT CONVERT(CHAR(101),getdate(),126) as "2012-02-01T15:16:38.937"
SELECT CONVERT(CHAR(101),getdate(),130) as "9 ???? ????? 1433  3:16:58:200PM"
SELECT CONVERT(CHAR(101),getdate(),131) as " 9/03/1433  3:17:13:327PM"

Count millions of records

COUNT_BIG() :

It works like a count function, difference is that count returns int value while COUNT_BIG() returns bigint.
while counting bulk of records at that time use count_big().

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * ) 

Count Duplication

create table books
(
 id int identity(1,1),
 custname varchar(25),
 bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ravi', 'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'nikhi', 'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ram', 'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'misi','evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'michel','evil')

Ans :-


SELECT bookname,COUNT(*) NoOfbooks
FROM books
GROUP BY bookname
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Split Function

create FUNCTION Naimish_split
( @string varchar(max),  @Delimeter varchar(2))
RETURNS @temp TABLE
(
id int identity(1,1),
name varchar(25)
)
AS
begin
set @string = replace(@string,' ','')
declare @count int , @countDest int,@no int
select @count = 0,@countDest = LEN(@String) - LEN(REPLACE(@String, @Delimeter, ''))
while @count < @countDest
begin
set @no =  charindex(@Delimeter,@string,1)-1
insert into @temp (name) values(left(@string,@no))
set @string = right(@string,len(@string)- (@no+1))
set @count = @count + 1
end
insert into @temp(name) values(@string)
return
end

--select * from Naimish_split('Prashant,naimish,ravi,vimal,jugal,Bhadresh,mohit' , ',')

O/P:

csv values in single query

create table emailData
(
id int identity(1,1),
email varchar(30)
)
insert into emaildata values('nbravrani@india.com')
insert into emaildata values('mohit.raiyani@india.com')
insert into emaildata values('jkundal@india.com')
insert into emaildata values('vimal@india.com')
insert into emaildata values('ravijohsi@india.com')
insert into emaildata values('sutariya.bhadresh@india.com')
insert into emaildata values('riteshpatadiya@india.com')
insert into emaildata values('ajay@india.com')


Que:
O/P like(Comma seperated using single query):
nbravrani@india.com,mohit.raiyani@india.com,.....

ANS:
select email + ',' from emailData for xml path('')

Get Random record

Que : How To get  Random Record every time from a table using a single query ?

create table _record
(
   id int identity(1,1),
   name varchar(15),
  address varchar(15)
)

insert into _record values('naimish','Gondal')
insert into _record values('vimal','Devchadi')
insert into _record values('jugal','junagadh')
insert into _record values('ravi','Amreli')
insert into _record values('Bhadresh','Bhader')
insert into _record values('Mohit','Rajkot')

sqlchallenge 1

Give bookname which is more then one time and number of books also


create table books
(
id int identity(1,1),
custname varchar(25),
bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'naimish', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'ravi', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'jugal', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'jugal', N'3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'naimish', N'3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'nikhi', N'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'ram', N'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'misi', N'evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'michel', N'evil')

like this.

sqlchallenge 2

id           Bookname
----------------------
1           GlobleWorld
2           Tunner
3           Roster in city
4          GNom
5          Three mistake
6          other,Not specified
7          The Secret
8          inspiration

give result order by bookname but last row must be a "other,Not specified"
like this

-------------------------------------------------------------------------

id           Bookname
----------------------
1           GlobleWorld
4           GNom 
8           inspiration 
3           Roster in city 
7          The Secret  
5          Three mistake
2           Tunner
6          other,Not specified

Some inbuilt useful store procedures.

--Drop all table from database
EXEC sp_MSForEachTable "Drop TABLE ?"

--Delete data from all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

--truncate data from all tables
EXEC sp_MSForEachTable "Truncate table ?"

--Desable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

--Enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--we want to reseed identity
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

--Shrink Database
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Nth Highest query

create table emp
(
empid int identity(1,1) primary key,
empname varchar(25),
salary int,
deptid int references dept(deptid)
)
insert into emp values ('naimish',900,1)
insert into emp values ('ravi',2200,2)
insert into emp values ('vimal',1000,2)
insert into emp values ('ajay',1400,3)
insert into emp values ('jugal',1100,3)
insert into emp values ('ritesh',1500,1)
insert into emp values ('gati',3000,2)
insert into emp values ('mohit',1600,1)

select * from
(
select ROW_NUMBER() over(order by salary desc)rank,* from emp
)as t
where rank  = 1