Sqlserver Query optimization Tips

Hello friends, I have learn some useful tips that i fill i need to put on blog so i am sharing with you, so below is the list of Query optimization Tips.

1. Restrict the Query result. Means return the required row and column from the query, This helps in reducing network traffic.

2. User Store procedure in place of the Queries. Store procedure is compile object so next time i will use execution plan created by the sqlserver engine.

3. Avoid Cursor that will improve your performance. Cursor fetches records row by row so it will make round trips.

4. Avoid Trigger if not needed. It will execute every time when data gets updated.

5. Use Table variable in place of the Temporary table. Temporary table will create in Tempdb it’s overhead to create table. Table variable is doesn’t need to create in the memory and it’s automatically remove when transaction complete.

6. Use “where” in place of Having clause.

7. Use “SET NOCOUNT ON” in store procedure, It’s stops sending message of rowcount and reduce traffic.

8.Use Dynamic Query with this syntax.Check below link
http://sqlmca.wordpress.com/2010/11/10/use-of-dynamic-sqlquery-and-its-optimization/

9. Use indexing where you need.(use it carefully)

10. Update your statistics in regular interval. Update Statistics will rebuild your indexes and give your good performance.

11. Check your execution plan for the No table scan.

I hope that will be useful tips for you and you can use it practically.
If you like it or any query give your valuable time by writing comments.

Tags: cursor, Dynamic Sql, optimization, Procedure, Query optimization

USE of XML datatype, Read XML and Write XML in Sqlserver

ML data type is very useful in SQLserver 2005 and 2008.

Check the below script and learn how to use XML data type.

Create Table Patient(FirstName Varchar(20),LastName Varchar(20))
insert into Patient
values(‘Jack’,'Sparrow’)
insert into Patient
values(‘Peter’,'Parker’)
insert into Patient
values(‘John’,'Black’)

Below is the simple Feature of the XML.
You can set your table data in the XML type of variables
And read that.
Important feature is you can pass that XML type variables
To any procedure and you can read and process that data.
It’s like you pass table from one procedure to another procedure.

DECLARE @xmlDoc XML
SET @xmlDoc =
(
SELECT FirstName,LastName FROM Patient
FOR XML AUTO
)
SELECT @xmlDoc

Below code will fetch data from xml variable created above.

select
SelCat.CatDet.value(‘@FirstName’, ‘varchar(20)’) as FirstName,
SelCat.CatDet.value(‘@LastName ‘, ‘varchar(20)’) as LastName
from
@xmlDoc.nodes(‘Patient’) as SelCat(CatDet)

Below is the example of Write the rows to the XML TYPE
And you can read that as rows in any procedure.
This is useful when you have to pass block of rows
Without defining the table name.
Below is the example of that.

DECLARE @xmlDoc XML
SET @xmlDoc =
(
SELECT FirstName,LastName FROM Patient
FOR XML RAW
)

Below code will fetch data from xml variable created above.

SELECT
SelXML.xmlDetail.value(‘@FirstName’, ‘Varchar(20)’) AS FirstName,
SelXML.xmlDetail.value(‘@LastName’, ‘varchar(20)’) AS LastName
FROM
@xmlDoc.nodes(‘row’) AS SelXML(xmlDetail)
drop table Patient

I hope this post is useful for you and upgrade your knowledge.

Use of Dynamic SqlQuery and its Optimization

Before writing this article I have many misconceptions for using the dynamic sql in sqlserver.
We are basically using the Execute statement for executing the dynamic query in sqlserver.
There are two options to execute the dynamic query
1) Execute @SqlQuery
2) Execute Sp_executesql @Sqlquery
In this article I will explore the Second option and how useful that option in Development.
Below is the example of Sp_executesql.

DECLARE @IntPatientID int;

DECLARE @SQLString nvarchar(500);

DECLARE @ParmDefinition nvarchar(500);

DECLARE @EncouterCount int;

SET @IntPatientID = 286;
SET @SQLString = N’Select @EncouterCnt=count(EncounterID) from Pat_Encounter where PatientID = @PatientID’;
SET @ParmDefinition = N’@PatientID int, @EncouterCnt int OUTPUT’;

EXECUTE sp_executesql @SQLString, @ParmDefinition, @PatientID = @IntPatientID, @EncouterCnt=@EncouterCount OUTPUT;

SELECT @EncouterCount;

In Execute option you will not get option of parameter you must define the variable in the dynamic sql to achieve the functionality.
In Sp_executesql you will get input and output parameter and that’s very useful in Development.
Another thing is Execute is not reuse Execution query plan else you are not define same value in condition of where.
Sp_executesql is reuse the Execution Plan and give fast output then Execute clause.
For the Optimization Sp_executesql is very handy in procedure.
You will get syntax from below link.

http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx

I hope this article is useful to you and enhance your knowledge of sqlserver.

Use of CASE … WHEN in Where condition and optional parameter in SQL Procedure

I think every body know how to use the CASE in the select query.
Today i will explain use of CASE in the where condition with some specific logic triks.
Example :
DECLARE @Recordstatus AS BIT
SET @Recordstatus = 1
SELECT * FROM dbo.ADT_Columns
WHERE RecordStatus = (CASE @Recordstatus WHEN @Recordstatus THEN 0 ELSE 1 END)

if CASE clause returns some value then you use CASE in Where condition.

Another thing i will explain is logical use of case which remove the use of If condition and also use the optional parameter in procedure.
I think many developers know that feature but i think nobody uses this option  in sqlserver for the procedure level we use this at .net or clientside mostly but it is very useful feature. When you use one procedure at many places then you need not to pass this parameter at every place.
In below example i will cover both the topics.
– Run below code in AdventureWorks DB
– GetAddress ‘All’
– Procedure with IF condition
CREATE PROCEDURE GetAddress @city VARCHAR(50)
AS
BEGIN
IF @city = ‘All’
BEGIN
– Returns all Records whithout any condition
SELECT * FROM Person.Address
END
ELSE
BEGIN
– Returns all Records which match the condition
SELECT * FROM Person.Address
WHERE City LIKE @City
END
END
– GetAddressWihtCase ‘London’
– GetAddressWihtCase  ‘All’ or GetAddressWihtCase  ‘All’
– Procedure without IF condition and Optional Parameters
CREATE PROCEDURE GetAddressWihtCase @city VARCHAR(50) = ‘All’
– OPtional parameter
AS
BEGIN
SELECT * FROM Person.Address
WHERE City LIKE (CASE @city WHEN ‘All’ THEN City ELSE @city END)
– Use it’s own value in the condition this is very useful thing and independent to
– Technology you can use in Oracle or in MySQl too.

END

I hope this article is useful to enhance your knowledge about Sqlserver.If you like this article
then please give comment on this article.

Tags: Case in Where Condition, CASE WHEN

Table value parameter new feature in SQLSERVER 2008.

Today i will explain very  nice and useful feature of the Sqlserver 2008.In the past i was facing many problem in reading and writing data in the Database from .Net environment for bulk insert and delete.I personally use XML to read the data from vb.net for implement bulk insert and delete.
After understanding this feature this thing is very easly done in future.
Below is the example of,
how we can pass Table as parameter to Procedure?

– Step 1
– Create table Student

CREATE TABLE [dbo].[Student](
[Student_ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](80) NOT NULL,
[City] [varchar](100) NOT NULL
) ON [PRIMARY]

GO

Step 2

– Insert the Rows in the Student Table
Insert into Student
values(‘Jhon’,'NewYork’)
Insert into Student
values(‘Mac’,'Paris’)
Insert into Student
values(‘Rahul’,'Delhi’)
Insert into Student
values(‘Jay’,'Mumbai’)
Insert into Student
values(‘Jack’,'London’)

select * from Student

Step 3
– very important
– Create the StudentTabelType as userdefine datatype of Table
CREATE TYPE [StudentTableType] AS TABLE
(
[Name] [varchar](80) NOT NULL,
[City] [varchar](100) NOT NULL
)
GO

– Create the Procedure which get the Table as input parameter
Create PROCEDURE StudentDetail(@StudentTable StudentTableType READONLY)
AS
BEGIN
INSERT INTO Student
SELECT * FROM @StudentTable

– You can create Tabel variable like this
–DECLARE @StudentTab StudentTableType
–insert into @StudentTab
–values (‘Rajesh’,'Bhopal’)
–insert into Student
–select * from @Studenttab
END
GO
You must pass Table variable as read only you can not prform
any DML command on this pass Table.
Like that you can pass table in the procedure and use for the further manipulation.

Now, one of the important thing is how to pass from .net environment.

Declar the datatable with two columns name and city.
Declare DTStudent as new datatable
DTstudent.Columns.add(“Name”)
DTstudent.Columns.add(“City”)

Insert some Records in this DTStudent Table.
Create Connection and command object and set all it’s property to establish connection to sqlserver.

Add parameter to command
Dim sqlcommad as new SqlClient.SqlCommand
Use the Structured datatype for the parameter and set the datatable to the
value of the parameter.

sqlcmd.Parameters.Add(“StudentTable”, SqlDbType.Structured).value = StudentTable

This feature is very useful in bulk insert and delete of data.

I hope this article is useful to you,give me feed back on this article.

Tags: Bulk Delete, Bulk Insert, New feature of Sqlserver 2008, parameter, Sql 2008, Table as parameter, table as parameter in Procedure, Table Value Parameter

How to get Start Date and End Date of Week from Given Date?

Today I will explain how you get the Start Date and End Date of week from any date.One of my colleague has requirement something like that he has week no and he want the start date and End date of that week.

There are many ways to find that solution but I got one solution that is useful to you if you want something like that in future.

Below is the Query from that you can get the start date and end date of the week.In this Query Replace order date with any date and you get the results from this query.

SELECT

CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST – DATEPART(dw, orderdate) – 6, orderdate)), 101) startdate

CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST – DATEPART(dw, orderdate), OrderDate)), 101) enddate,

from tbl_dietorder_trn (Any Table )

You can use Getdate() function and check this.

In this I use the @@DATEFIRST Function. By using this function you can set the first day of week. Sunday is default first day of week so it’s default value is 7.

This Query to find start date and End date id different from other because of @@DATEFIRST function.

You can set that function value between 1 to 7.Base on that thing this query is give you the result.

1 -  Monday
2 -  Tuesday
3 -  Wednesday
4 -  Thursday
5 -  Friday
6 -  Saturday
7 -  Sunday

You can set the value of @@DATEFIRST.

Set Datefirst 1

You can get this information from below link about @@DATEFIRST Function.

http://msdn.microsoft.com/en-us/library/ms187766.aspx

my question to you,
can any one know other than this Query that give First and Last Date of week?
I hope this is useful to you in future.Please give your feedback.

Tags: DATEFIRST, Week, week Enddate, week start Date and end date, Week Startdate, WeekDay

Top with Ties option

Today, I will explain how we can use Ties option with Top.Basically Sqlserver reaturn all the rows from the select Query but with Top Option We can specify criteria on that basis Select Query Return the Records.

–Select Query

Select SaleitemQty from Sales

This Query will return the alll the SaleitemQty from Sales table.

–Select Query with Top Clause

Select Top 10 SaleitemQty from Sales

This Query will return the top 10 SaleitemQty from the Sales table.

Now, we come to Top clause with Ties option.

Create table like this

Create Table Sales (CityName varchar(100),SaleQty int)

Insert Rows in this table

Insert into Sales values(‘Delhi’,15)

Insert into Sales values(‘Bombay’,17)

Insert into Sales values(‘London’,20)

Insert into Sales values(‘Newyork’,22)

Insert into Sales values(‘Tokyo’,15)

Now, we check first for the Only Top option.

– Query with Top clause only

select Top 4 SaleQty,cityname from sales order by SaleQty desc

– Result Set of Top clause

SaleQty     cityname

———– ———————————————————–

22          Newyork

20          London

17          Bombay

15          Tokyo

Like that you can see in the result set that, there are Two tables that have saleQty 15 but only one City comes in the result set.

– Query with Top clause and Ties Option

select Top 4 with Ties SaleQty,cityname from sales order by SaleQty desc

– Result Set of Ties option

SaleQty     cityname

———– ———————————————————–

22          Newyork

20          London

17          Bombay

15          Delhi

15          Tokyo

Ties option alllows the last place to include multiple rows if those rows have eaual values in the columns used in the order by clause.

In this example Two city has same SaleQty 15, with ties option we can get both the citys In the result set with same SaleQty.

I hope this example will clear the Ties option with Top clause.

Tags: Ties option, Top Clause, Top with Ties, Tsql

How to get Data from One procedure to another Procedure or Dynamic Sql?

Today I will explain the very small but very useful features which I use in the past for the procedures. When I am new to sqlserver I am facing many problems this is one of this that I will explain to you today.

That you all know that, User defines Function has limitation that we can not use #temp table or Table variable in UDF. Because of that we can not get the data from another function in the UDF. At that time I find the solution that we are now create the procedure and we get data from that procedure for further manipulation. Question is how to get data from another procedure?. You can get the answer of this question in this post..

e.g., suppose procedure ReturnStudentDetail return the table with two fields Name and City so how can you get that table in another procedure for further manipulation.

–Code

– Create Table

Create table #StudentDetail

(Name varchar (50), City varchar (100))

– Below code give you data in the procedure

Insert into #StudentDetail

Exec ReturnStudentDetail

This syntax works only if you create table like this. You also use Dynamic Sql output for inserting data in #temp table.

– Create Table

Create table #StudentDetail

(Name varchar (50), City varchar (100))

Declare @sql as varchar (500)

Set @sql = ‘Select Name, City from tbl_StudentDetail where ……..’

– Below code give you data in table

Insert into #StudentDetail

Exec (@sql)

So, this is how you can get the data from another procedure or dynamic sql in Table.

Basic thing is whatever the structure return on the basis of that you have to create the table and use that table to get data from procedure or dynamic sql.

I hope this feature is useful for in future. If any query feel free to ask me.

Tags: Dynamic Sql, Procedure, Sql Tricks

How to get Data from Dataset into Sqlserver Table by using OPENXML Method?

Some time we need data from Dataset into Sqlserver for insertion or updation in the Sqlserver Tables.

For example if you need to insert an image then you can use this method to insert the data in Sqlserver tables.

Today, I will explain how to get data from Dataset into Sqlserver Table.

– Code for .Net

Dim ds as new Dataset

Dim dt as new Table

dt.TableName = “MyTable”

ds.Tables.add(dt)

Dim lStrWriter As New StringWriter

Dim lStr As String

ds.WriteXML(lStrWriter)

lstr = lStrWriter.Tostring

Pass this lstr variable to the procedure as parameter – @xmldoc.

Now write the code procedure to get the data from Dataset.

— Code in Sqlserver Store Procedure

CREATE PROCEDURE [dbo].[TestXML]

@xmldoc text — Give Text datatype for getting xml from dataset

as

———Read the Datset  From XML —————–

– call sp_xml_preparedocument

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * into #TestTable

FROM OPENXML (@idoc, ‘/NewDataSet/MyTable’,2)

WITH (pk_Userid  BigInt,

UserName  Varchar(10))

EXEC sp_xml_removedocument @idoc

———- End Read ———-

END

BY the use of this procedure you get all the data from dataset in to the ”#Testtable” and we can use this as Sqlserver table in procedure.

I hope this article will be useful to you, Please give feedback on this article.

Tags: Data from dataset to Sql, Openxml, read XML in sql, XML to SQL

How to Convert Cursor into While Loop In Sqlserver ?

I personally face this issues in many Store Procedures i.e. Cursor taking too much time to execute. I am sure many of you will be having similar problem.

I have worked on that issue and found following solution. It may not be perfect for cursor optimization but it does work in my case.

– Original Cursor Code —

Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint

declare CUR_MEDICATIONORDERID cursor FOR
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()

OPEN CUR_MEDICATIONORDERID

FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId

WHILE @@FETCH_STATUS =0
BEGIN

Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId

Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”

FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId

END

CLOSE CUR_MEDICATIONORDERID
DEALLOCATE CUR_MEDICATIONORDERID

——————-

– Modified Cursor Code (faster) –

Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint

Declare Table for the fields you need in the cursor.

declare @IDList table (ID Bigint)

Insert into @IDList
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()

while (select count(ID) from @IDList) > 0
begin
select top 1 @fk_MedicationOrderId = ID from @IDList

Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId

Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”

delete from @IDList where ID = @fk_MedicationOrderId

end

——————-

What I have done here is to convert “cursor” into “while loop”. It is just to get your data in any temporary table or table variable after that just delete the rows from that at end of loop.

It helps in improving performance, I have personally tried it in many stored procedures.

Let me know if you find any other useful way of improving performance.

Tags: convert cursor, cursor, optimization, remove cursor