Microsoft Technologies, ASP.NET & SQL SERVER Tips with Rajat Jaiswal

Month: May 2009

First of all really sorry that you have to wait for 2 weeks for my new article actually I stuck in some real problem in my office project and that is Performance improvement of a store procedure and that’s why our current topic is “SQL Server Performance Improvement“
Here are some important points which I find and used to improve performance of my store procedure. I hope this will help you to.
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 column name,”
18) Use Sp_ExecuteSQL instead of EXECUTE
19)Use Explicitly Index “With( INDEX( INDEXNAME)) with table.
20) Maximize the thread.
The above 20 points I used and my store procedure is fast.
If you people try to use above 20 points then it will beneficial to you also.
Rest if you need any kind of help of me and my SQL Expert friends then you can just put comment.
Thanks & enjoy SQL Server
Your Host & friends

Today we just try to make a sample project so I am taking here task to make a simple friend List in that list we can add friends and view friends.

So just follow the step as I mention below and we together make it easy

Step 1:- Add A project for Asp.net MVC as shown in below fig.

Step 2:- once you done with this you will find default folders of Views, Model and controller.

The point to remember here is all the presentation layer comes in views folder and if there is sub folder in views so there must be a controller with that particular name.

As you see there is home folder in Views so in controller folder there must be Home controller exists.

The view name or we can say our page name will be the function or procedure in controller.

Suppose there is page in views –> Product –> ProductList.aspx

So in our project there must be a product controller and in that controller there must be a sub routine for product List.

There is also a shared folder in our application which is mainly used to keep shared views like Master Page & other custom controls.

Step 3:- Define views in home folder

1) Index.aspx :- it will be our default page. This page having default text and pictures of you

2) AddFriend.aspx :- It is used to add friends in our database.

3)ViewFriends.aspx:- it is used to search friend by location.

3)SearchFriendsByArea.aspx:- it is used to find location of friend on Google map.

Step 4:- Now we create a database for it. In our database there is single table which is “TblFriends” with field lngId, strFirstName, strLastName, strEmailAddress, strAddress, strCity, strCountry, strphone,strBrief intro.

Step 5:-Now and LINQ to SQL class which is “FriendsDb.dbml” in Model. This is use to intract with Database.

Step 6:- We have Home Controller now we have to add different method in according to our View, Like Index, AddFriend, viewFriends, SearchFriendByArea

Once you done with this. Now we think about how the data will save from add friend.aspx. For this we just mention another function in home Controller which is Save Friend.

Save Method have all the parameter which is on the form like, strFirstName , strLastName etc.

Once you got all the require parameter we just going for add as shown below.

Function SaveFriend(ByVal FirstName As String, ByVal LastName As String, ByVal Mobile As String, ByVal Email As String, ByVal Address As String, ByVal city As String, ByVal country As String, ByVal BriefIntro As String) As ActionResult

‘Require validation…

‘ Basic parameter validation

Dim blnValidate As Boolean = True

Dim errors As New List(Of String)

If FirstName.Trim = String.Empty Then

errors.Add(“Your first name is required!”)

blnValidate = False

End If

If LastName.Trim = String.Empty Then

errors.Add(“Your Last name is required!”)

End If

If city.Trim = String.Empty Then

errors.Add(“your city is required!”)

End If

If country.Trim = String.Empty Then

errors.Add(“Country is required!”)

End If

If Address.Trim = String.Empty Then

errors.Add(“Address is required!”)

End If

‘Save Data i

If blnValidate = True Then

Dim clsFriend As New MyFriend

clsFriend.strFirstName = FirstName

clsFriend.strLastName = LastName

If Mobile.Trim <> String.Empty Then

clsFriend.strMobile = Mobile

Else

clsFriend.strMobile = Nothing

End If

If Email.Trim <> String.Empty Then

clsFriend.strEmail = Email

Else

clsFriend.strEmail = Nothing

End If

If country.Trim <> String.Empty Then

clsFriend.strCountry = country

Else

clsFriend.strCountry = Nothing

End If

If city.Trim <> String.Empty Then

clsFriend.strCity = city

Else

clsFriend.strCity = Nothing

End If

If BriefIntro.Trim <> String.Empty Then

clsFriend.strBriefIntro = BriefIntro

Else

clsFriend.strBriefIntro = Nothing

End If

If Address.Trim <> String.Empty Then

clsFriend.strAddress = Address

Else

clsFriend.strAddress = Nothing

End If

db.MyFriends.InsertOnSubmit(clsFriend)

db.SubmitChanges()

ViewData(“Title”) = “Record is Saved”

Return RedirectToAction(“ViewFriends”)

Else

ViewData(“Errors”) = errors

Return View(“AddFriend”)

End If

End Function ‘SaveFriend

Step 7:- for searching we will do following thing

Function SearchFriendsByArea(ByVal Area As String, ByVal city As String, ByVal country As String)

‘Search Friends And return

Dim mylist = From f In db.MyFriends Where f.strAddress Like Area Or f.strCity Like city Or f.strCountry Like country Select f Order By f.strFirstName, f.strLastName

Return View(mylist.ToList)

End Function ‘SearchFriendsByArea

Here the things to understand the Return view Parameter. The Return view parameter is again a data collection which we will provide to search Friend’s View.

On this page we have to consider only one thing which is as follows

Inherits System.Web.Mvc.ViewPage(Of List(Of MyFriend))

Means we provide View Data a data list of Linq Class which is MyFriends.