SQL Server Performance Improvement

Hello Friends,

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

Why and Why not Asp.net 3.5 MVC Projects ? Asp.net 3.5 MVC Project series – IInd

Hello 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.

mvc1

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. 

mvc2

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

mvc3

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

mvc4

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

mvc5

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

mvc6

 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.

Now we can use it in aspx page.

<%  For Each mfriend As MyAddressBook.MyFriend In ViewData.Model%>

            <tr>

                <td>

                    <% =mfriend.strFirstName%>

                </td>

                <td>

                    <% If mfriend.strLastName Is Nothing = False AndAlso mfriend.strLastName.Trim <> String.Empty Then%>

                    <%=mfriend.strLastName%>

                    <%Else%>

                    &nbsp;

                    <% End If%>

                </td>

                <td>

                    <% If mfriend.strAddress Is Nothing = False AndAlso mfriend.strAddress.Trim <> String.Empty Then%>

                    <%=mfriend.strAddress%>

                    <% Else%>

                    &nbsp;

                    <% End If%>

                </td>

                <td>

                    <%=mfriend.strCity%>

                </td>

                <td>

                    <%=mfriend.strCountry%>

                </td>

                <td>

                    <% If mfriend.strEmail Is Nothing = False AndAlso mfriend.strEmail.Trim <> String.Empty Then%>

                    <%=mfriend.strEmail%>

                    <%Else%>

                    &nbsp;

                    <% End If%>

                </td>

            </tr>

            <% Next%>

 So in this way we just add records and view data.

You can download source Code from http://indiandotnet.tk

Thanks

Enjoy programming 🙂

Keep doing great Job 🙂