Boost query performance with avoiding basic mistake with Select statement. Performance Tip – #42

 

Problem:-

My query is slow what are the basic things I can do to get good performance without going for indexes.

Solution:-

May be this solution help you which I am describing here or it is possible you already aware of it.

 

(a) Avoid function in column :-The most basic tip is avoid any function in where clause which applied on table column.

lets understand this by few examples

Suppose your query written as follows

SELECT * FROM person.person WHERE Substring(FirstName,1,1)= 'K'

OR

SELECT * FROM person.person WHERE LEFT(FirstName,1)= 'K'

(b ) Avoid implicit conversion:-  Try to provide exact data type to avoid implicit conversion.

For example if we have firstname column in table having data type nvarchar(50) and we are comparing it with Varchar(100) then at the time of query it convert implicitly.

Lets understand with following query

DECLARE @Name AS VARCHAR(50)
SET @Name = 'K%'
SELECT * FROM Person.Person WHERE FirstName Like @name

When we run above query we get following query plan

If you see highlighted yellow it shows that query implicitly converted for changing data type

Now above query can be written as follow to avoid implicitly conversion

DECLARE @Name AS NVARCHAR(50)
SET @Name = N'K%'
SELECT * FROM Person.Person WHERE FirstName Like @name

Now when you run above query  you will get following query plan

So if you see there is no extra conversion in above figure.

I hope this small tips help you in  performance.

Thanks

RJ

Determine all SQL SERVER instance in entire network or at least your machine :) TIP #41

 

Problem :-

Sometimes it may require to determine all the available instance of SQL Server in network.

Solution:-

One of the easy way to determine the instance with SQLCMD.

Lets understand this by following steps

1) Open dos prompt

2) write SQLCMD – L   OR SQLCMD/L

Above command with return instance on your machine as well on network

See below screen snap for detail

Enjoy !!!

RJ

Getting Comma separated value for table reference column is easy TIP #40

 

Problem :-

Most of the time we have one master table and one child table which content reference of master table. We have to fetch all the child  column as comma separated  for master table column. for example  I have a product category table and product table.  We want all the product as a comma separated in one column corresponding to each category.

Solution:-

lets understand this by an example. I am taking Adventureworks2012 database in the example.  below is default view

Now after processing we want below result

Now to achieve this we will write following command

DECLARE @CategoryProducts AS TABLE (ProductCategoryId INT,
Category VARCHAR(500),
Products VARCHAR(5000))

INSERT INTO @CategoryProducts(ProductCategoryId,Category )
SELECT pc.ProductCategoryID,pc.Name
FROM Production.ProductCategory pc

UPDATE tmp
SET Products = (SELECT p.Name + ','
                FROM  Production.Product p
                WHERE p.ProductSubcategoryID = tmp.ProductCategoryId
                ORDER BY p.Name
                FOR XML PATH (''))
FROM @CategoryProducts tmp

SELECT * FROM @CategoryProducts WHERE Products IS NOT NULL

 

So using XML path it easy to get what we want.

I hope this may help you somewhere

Enjoy!!!

RJ

Dense_Rank is exact same but it avoid gaps in ranking TIP#39

 

As discussed in last tip #37 Dense rank having exactly same syntax like Rank the only difference between rank & dense rank is that “dense_Rank fill the gap or avoid gap in ranking means if two row set have same ranking then just after record will have just next ranking.

I am taking same example as we discussed in 27 and using Dense_Rank function.

The syntax is as follow

SELECT  Dense_Rank() OVER (Partition BY column name Order by column name ASC/DESC) , other columns FROM tableName

Now example

 

I will club all this four in next tip and discuss difference.

Enjoy!!

RJ

Rename column name by SQL command TIP #38

 

Recently, One of my friends asked How to rename a column by SQL command frankly speaking I am not used too of using this command I prefer UI interface.

Lets understand this by an example.

Suppose I have a users table in which there is a column with name status which should be statusId but by typo mistake I added satus column.

Now below is command to rename column

SP_RENAME 'table.columnName’,’newcolumnname’,’COLUMN’

See below snap to resolve above problem of renaming satus to statusId

Enjoy!!!

RJ

Ranking by Rank() function then what about Row_NUBER() TIP #37

 

In last post we have discussed Row_Number() function. In similar way we have RANK function. By the name it is clear that we provide  some kind of ranking for rows.

It syntax is exact same like ROW_NUMBER().

Now the question comes in mind if it is same then why SQL SERVER introduce new function.

so there is slightly difference

ROW_NUMBER() will generate sequential number regardless of  duplicate rows in partition.

While  RANK() will generate sequential number for unique row in partition and use same sequence for duplicate and skip sequence which lies between duplicate.

For example in  below query we want to know total dues rank according to  territory and  modified date.

so we write following

SELECT TerritoryID,TotalDue,ModifiedDate,SalesOrderNumber ,RANK() OVER (Partition by ModifiedDate,TerritoryID ORDER BY TotalDue) ranks FROM [Sales].[SalesOrderHeader]

When we run it we get following result

enjoy !!!

RJ

Different way to find 2nd minimum Salary TIP #36

 

Mostly people faced this question in interview find highest salary so I am just trying to explain different ways

let us understand this by following example

suppose we have following table of employees with salary

Query 1:-  by  aggregate function

SELECT MAX (fltBasicSalary)
FROM @tblEmployeeSalary
WHERE fltBasicSalary IN (SELECT DISTINCT TOP 2 fltBasicSalary
FROM @tblEmployeeSalary
ORDER BY fltBasicSalary ASC)

Query 2:- by Inline Query

SELECT MIN(fltBasicSalary)
FROM @tblEmployeeSalary e1
WHERE 2 <=(SELECT COUNT(*)
FROM @tblEmployeeSalary e2 WHERE e1.fltBasicSalary >= e2.fltBasicSalary);

Query 3:- BY Row_Number()

SELECT tmp.fltBasicSalary
FROM (SELECT DISTINCT fltBasicSalary ,
ROW_NUMBER()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber
FROM @tblEmployeeSalary)tmp
WHERE tmp.intRowNumber = 2

Query 4:-  BY Rank()

SELECT tmp.fltBasicSalary
FROM (SELECT DISTINCT fltBasicSalary ,
RANK()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber
FROM @tblEmployeeSalary)tmp
WHERE tmp.intRowNumber = 2

Query 5:- Group by  & Sub query

SELECT TOP 1 fltBasicSalary
FROM (SELECT TOP 2 fltBasicSalary
FROM @tblEmployeeSalary
GROUP BY fltBasicSalary ORDER BY fltBasicSalary ASC) AS tmp ORDER BY fltBasicSalary DESC

I hope you like it.

Enjoy !!!

RJ

Row_Number() function for providing sequence number as per your wish TIP #35

 

Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER.

Lets understand this by following example

In this example we have  person table which have firstname, last name, middlename  columns. Now we want to fetch records  with a extra column ROW_Number() so we write following query

SELECT ROW_NUMBER() OVER (ORDER BY LastName) As ROWNO,FirstName,MiddleName,LastName  FROM [Person].[Person]

When we run above query we get following result set. If you see below result set we have one extra column which is ROWNO

Lets understand the query in this we are getting sequence or Row number order by Last name.

There is one more option with ROW_NUMBER which is partition by

lets understand this by below query what we want for we want row number for each record based on last name means whenever new last name introduce row number start again from one. to achieve this we have to write following query

SELECT ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY LastName) As rowNo,FirstName,MiddleName,LastName  FROM [Person].[Person]

See below snap when we run we get following result

If you see above snap you will find that when the Last name change row number also change so we did partition by last name means for each change.

I hope you might have used earlier but I thought It might be possible it will helpful to someone who not aware.

Enjoy!!

RJ

Get Random records on each fetch using TABLESAMPLE TIP #34

 

As discussed in previous tip which is TIP #33 we can get random result set by using NEWID function.

Now same can be achieve by using one more option which is called TABLESAMPLE.

We have following syntax for it

SELECT * FROM tableName TABLESAME (Number ROWS/PERCENT)

For example I am using same Adventureworks database and fetching top 10 records from Person table which should be different each time.

So, I will write following select query 

SELECT TOP(10) FirstName,Lastname,MiddleName from Person.Person TABLESAMPLE(10 Percent)

OR

SELECT TOP(10) FirstName,LastName, MiddleName From Person.Person TABLESAMPLE(1000 Rows)

When , I run it I got following records.

So this is another a way of  fetching random numbers.

Enjoy !!!

RJ

Get different records or random order at each time when you fetch record by newId() TIP#33

Hello friends,

Sometimes your project require whenever a page load or user search then each time you need to show a random order or we can say different records or order.

To achieve this we can use following command

SELECT * FROM yourtableName order by NewID()

in below snap I am fetching person table of Adventureworks database

 

So each time when I press F5 I get different records or order.

The only thing which need to remember here NEWID() make your fetch slow on large collection so the best idea is use it with selected record set.

I hope this will help you somewhere enjoy.

Thanks

Rajat Jaiswal

Easy way to find result set of a stored procedure without running it- TIP #32

 

Sometimes , We need to know what is the structure of stored procedure ? , How many columns will be return ?

So to determine this we have easy stored procedure provided by SQL Server which is sp_describe_First_Result_Set.

Below is the template to run it. You can provide your stored procedure name as variable

Execute sp_Describe_First_Result_set ‘ProcedureName’

In below snap I used Adventureworks database and try to find what will be the output columns of the stored procedure “uspgetBillOfMaterials”

As you see in below image after running above query we got all the result set columns

I hope this tip help you somewhere.

Enjoy !!!

Performance tips :- Different reports to analyze your SQL Server who is making it slow. TIP#31

Dear Friends,

One of the really important part of any database is performance. Everyone complaining your system is slow , your database performance is slow but why it is slow ?

Most of the time we are not aware what is the source?  Which SQL statement creating problem?

Lets understand this thing why our SQL Server is slow ?

Which Query is culprit of making our server slow ?

Which query eating CPU ?

All these question is easily answered by SQL Server itself but most of the time we are not aware of this.

SQL Server provides a unique option which is reporting. These reports has several option like

1) TOP Queries by TOTAL CPU

2) TOP Queries by TOTAL IO  and many more as shown in below figure

You can get this option by right click on SQL server Instance as shown in below figure.

Now you can select any reports provided in the option for example when I select TOP queries by total CPU time then I get following report

So, If you see in above image you will get top CPU consuming statement.

With the help of these reports you at least aware where you need to work more.

So be prepare for that.

I hope this tip will help you to dig your performance dragger and make your customer happy by resolving those panic statements.

Enjoy !!!

Rajat Jaiswal

Import the data in main table from another table if that data not exist in main table Tip #30

 

Dear Friends,

Sometimes we get task like, we need to import data in a target table from a source table and the condition is that the data which we are going to import should not exist in target table .

To achieve this we have two ways

1) Left outer join

2) Except clause

Lets understand this by an example

Suppose in my database I have a table with name tblUser  which has few columns and few rows as shown below

Now suppose I have a table with name @tblToBeImport which has some data related to Username & Password.

In this table some rows having same username & password which already exist in our source table as shown in figure

Now our motive is to first fetch those data from tblToBeImport (source table) which need to be insert in tblUser table (target table)

It means if username & password already exists in tbluser then we will not insert it else we will create a new entry.

Here , We are using below left outer join to find the rows which not exists in tblUser but exists in tblToBeImport

In below snap you will find there we applied Left outer join to achieve this

SELECT tmp.username,Tmp.uPassword
FROM @tblToBeImport tmp
LEFT OUTER JOIN tblUser u ON u.UserName = tmp.username
             AND u.Password = tmp.uPassword
WHERE u.UserId IS NULL

Now, same can be achieve using EXCEPT clause  as shown in below figure

 
SELECT username,
       uPassword
FROM @tblToBeImport
EXCEPT
SELECT[UserName]
      ,[Password]
FROM [dbo].[tblUser]       

Now my personal experience Left outer join is very slow on large table comparatively EXCEPT clause.

I prefer to user Except clause .

Enjoy!!

PARSENAME function TIP#29

As the name suggested PARSENAME is the SQL Server function which provide help in parsing.

Lets understand this by an example

Suppose , I have an IPAddress variable which I need to parse

DECLARE @IPAddress AS VARCHAR(100)
SET @IPAddress='412.312.800.713'

SELECT PARSENAME (@IPAddress,1)

The syntax of the parsename is simple

PARSENAME(String,stringPartIndex)

Only few things which  I have observed and limitation of parse name

1) It only works with dot (“.”) delimiter

2) It can parse up to 4 value only

3) Index will start from 1

see the result of above example below

Enjoy !!!

Thanks