An Index can change your Life... ehh...Query I mean....

Most of the time I get a question from Co-developer asking “Hey Bridewin, I am stuck with a query… I have written it but output is coming slow?!?... can you look into it?”

I look at them with a smile. I always ask a question, have you written it correctly? If yes is the answer then what could be wrong? Why is the output coming so slow?

What will I do to bring the output quickly? Definitely I won’t be coming and doing any magic. Well I would be carrying out some standard steps.

1. Check the query to see if it is bringing some unnecessary data.
2. Check execution plan.
3. Are the necessary Indexes present in all the required columns of “Where” clauses?

In this article I would be focusing on Indexes because most of the time 2 to 3 years experienced developers are always stuck with Indexes.

Prior to creating indexes make sure you have written your query in ANSI 92 compliant or it would also be fine if you have written Oracle specific queries or SQL server specific queries.

The advantage of writing ANSI 92 compliant query is that any (Oracle/ SQL Server/ Sybase/ MySQL) DBA can help you out with the query if you get stuck with optimization problem.

If you write Oracle specific query or SQL Server specific query then only Oracle DBA/Query Tuner or SQL Server DBA/Query Tuner can help you out.

Now let’s get back to the actual topic Indexes…

So firstly let’s understand why do we need an Index?

Yes let’s ask this question in a different way to our self… why do we need an Index in a book may be text book… Yes, so that we can quickly browse through that particular page instead of scanning through the entire book... Similarly in a table instead of scanning through all records we can simply point to a particular record using Index.

Now the second question would be there are plenty of Indexes available in a database which one do I choose?

Correct if you are up to creating an Index in a table creating a right index is very necessary otherwise it would be of no use! Your query may run slower than now or may show no performance improvement. Now Indexes are database dependent, names and type of indexes vary from database to database.

I will list down the required Indexes for SQL Server and Oracle and its uses one by one…

SQL Server:

1. Clustered Index- This is useful when you have a unique data in a particular column something like a column having primary key. Normally a column with primary key has clustered index.
2. Non-Clustered Index- This is useful where a table has a clustered Index and some query is going to be executed frequently. Then Non-clustered index can be created on other column.
3. Unique Index- This is useful where there is ample of unique data very similar on the lines of having unique keys.
4. Column Store Index- This is useful where there is high use of read select query and large amount of data is retrieved. Normally used in data warehouses.
5. Computed Index- This is useful when the data of the column is computed like (Column 1 * Column 2 = Column 3) here column 3 can have computed index.
6. Spatial Index- This useful in GIS databases where data type of the column is Geometry.
7. XML Index- This is useful where the column has XML datatype.
8. Full-Text index – This is useful where we want to do word search on a particular column having large data, containing lot of text etc…

Oracle:

In Oracle indexes are not that complicated compared to SQL server.

1. B-Tree Index- This is useful where there are lot of data in particular column. If you know that data in the column is going to be unique then use Unique keyword while creating the index this will optimum result.
2. Function based Index- Oracle does not have any restriction like SQL server it allows to create index on Functions. This is useful where functions are going to be applied on particular column something like upper/lower etc.. This Index is not suitable for Geo spatial (GIS) database.
3. Bit map Index- This is useful where there is large amount of data and ad hoc queries, but a low level of concurrent DML statements.

After creating the required indexes on daily basis statistics need to be gathered, otherwise these indexes would be of no use and performance of query will gradually decrease.

Now after reading and understanding you will be able to choose the correct index for your table and improve the performance of your query.

Cheers!!!

MVC Versus Budweiser... it's a Challenge...

Happy New Year to all my readers! Today its a challenge weather MVC or Budweiser would finish first.

Can you code drunken?!?!

It's a funny question right? Lets see...

Open a bottle of Beer or Budweiser I prefer Budweiser instead of Beer!

Now lets start coding... We will take step by step approach..

1. Create Database First
2. Create Model
3. Create Controller
4. Finally Unit Testing

So now we know what approach to follow.

Lets create a Ms SQL database first lets name it ZeroCodeMethod. Now create a table inside that say for UserMaster simply by doing New Table.

Once done with new table creation simply add required columns as shown below.

Once columns are created set the Primary Key and its Identity Field. Please note Primary key should be the Identity field.

That's it our database is ready.

Now lets start the best part coding.

Oh! ya right coding! After few sip you must be having a roller coaster effect!!! Don't worry I'll make the coding part minimum, that's my promise.

Now take a sip of your drink because its going to be a one shot activity for next few minutes.

Now quickly open your Visual Studio mine is 2013 whats yours?

Select New Project > Now Select Web ASP .Net Web Application > give a good name to it I'll say Zero Code Method > Now select MVC and go ahead.

Once done with the preliminary creation of the project simply add a Model to it using Entity Framework as shown in below screenshot

Give a proper name to the model something like MUserMaster identifying the table.

After model has been added then create a new connection it's pretty simple as shown in below screenshot.

Again a Sip of drink! Now create a new connection > Give it a name > Select table UserMaster>  Now do next next.. We all love to do that...

We are done with the Model part.

Now to create Controller simply select Add > New Scaffolded item> Select MVC 5 Controller with views, using Entity Framework.

If you notice there is something that we are getting for free! a typical Indian culture that MVC is giving, by one get one free we created Controller and we got View along with it.

By selecting this template we got a 5 pages made automatically by Visual Studio 2013 itself.

1. Create.cshtml
2. Edit.cshtml
3. Delete.cshtml
4. Details.cshtml
5. Index.cshtml

Now a little bit of coding as promised I'll make it minimum not more than one line.

Open Views>Shared>_Layout.cshtml and then add a pointer that pointing to User Master so that it displays in the menu.

Simply copy paste this line into menu section.

<li>@Html.ActionLink("User Master", "Index", "UserMaster")</li>

As shown in below screenshot.

Now your main page should look like this once compiled in visual studio.

To add a record simply click on the User Master and then click Create.

Now add a record as shown below.

Click on create to create a new record.

Added record should be visible as shown below.

In case if you wish to Edit/Delete/View details click on the respective links.

As part of Indian Culture... Again something free... This site will also work fine in mobile browser because it is using Boot Strap to prove that simply re-size the browser and on the fly the menu will adjust automatically as per the size of the browser.

That's it we are done with the Creation of MVC Zero Coding Method or Database First Method. The advantage of this method is that this has minimum amount code to be written because this is highly reliable on the database part. 

This method is suitable for creation of master or in places where there is less amount of complexity. Remember this may not be suitable for places with high amount of complexity otherwise you will land up scribbling code here there everywhere...

Similarly you will find some other methods like Code First Method etc... Its up-to you which method to follow.

I'm sure that you will be having your drink in the bottle to finish!!!

In case if you were unable run or complete this project successfully just download the code and check what have you missed.

That's all folks!!! Stay tuned for upcoming posts.

You don’t see me…You see my Glamour

It’s been almost approx. 5 days I am riding a Glamour bike, well it’s a stylish bike! Ya the one which I was riding was of an ancient model may be of 2006 or something not very sure! It took almost 2 days for me to understand why I need to pull accelerator while doing kick start!! Oh! Ya.. It does not have a self-start but it`s an awesome bike… Sometime it is very rightly said Old is Gold and in case of bike if we maintain it correctly with timely servicing and everything then yes it would be awesome!!!

As a not very experience person in riding bike… something I noticed is that after a long ride I didn't come to know in which gear was I riding the bike whether it is 2^nd , 3^rd or 4^th. Although I have used 4^th gear very rarely but I wish there was an indicator nearby speedometer stating the gear in which I was riding the bike! I hope Motorcycle Company starts adding this feature inside the upcoming models!! Pardon me if it sounds foolish!!!

Now let’s start comparing what’s there in the Old Glamour and the new Glamour (Programmed Fi):

1. Speedometer & Fuel Indicator: In old it is analogue and in new Fuel Indicator is digital (Now a days almost all new bikes come with digital Speedometer & Fuel Indicator well this bike is an exception?!?!)  It is noticeable that is why I have written!!
2. Starting mechanism: This would be secondary thing the rider would notice in earlier model there was only kick start, but the new one comes with the Kick as well as self-start.
3. Fuel Engine: The owner of the old bike said it can hold up-to 15 litter of fuel but the new one can only hold 12 litter + 1.5 litter! God knows whether the company thought of taking off the extra fuel space! Good if they have actually increased the mileage!!
4. Last but not the least Brakes: Rear brakes remain the same whereas the front brake has been changed to disc brakes! So be bit careful for the front brake holders!!! or you may get a roller coaster effect!!!

Ahh..!! I can gulp all sorts of changes in the bike, car etc… But not the Mars kind of roads we have in India… hope that improves!!!

That’s all for the timing folks stay tuned for the upcoming posts...Cheers!!!

Windows Authentication using ASP .Net in detail

Authentication

Authentication is the process of obtaining identification credentials such as name and password from a user and validating those credentials against some authority. If the credentials are valid, the entity that submitted the credentials is considered an authenticated identity.

In windows authentication users are authenticated against the users available in Windows operating system (local) or against that of users available in Active Directory.

For windows authentication we need to set the authentication mode to windows in web.config.

  <system.web> <authentication mode="Windows" /> </system.web>

Now to impersonate the identity of IIS worker process to that of provided by Windows operating system

We will have to set the impersonate to True by adding <identity impersonate="true" />

So the final code will look like this

<system.web> <authentication mode="Windows" /> <identity impersonate="true" /> </system.web>

Now the question is how do I determine whether I am truly authenticated?  Basically I can twist my question into other way “How to get my username in Windows Authentication?” Well that’s pretty much simple I guess.

Well for that we will have to use System.Security.Principal namespace.

The code for getting complete username along with domain name as well as for getting only username/ userId is shown below:

  

C# Code:

// This is the Ideal way for getting user name along with the domain string user = string.Empty; string userId = string.Empty; user = WindowsIdentity.GetCurrent().Name; // here we will get Ires\bridewin this.myUserName.Text = user; // This the Ideal way to take off the domain name from the user name userId = user.Split('\\')[1]; // So here we will get bridewin this.myUserId.Text = userId;

VB Code:

' This is the Ideal way for getting user name along with the domain Dim user As String = String.Empty Dim userId As String = String.Empty user = WindowsIdentity.GetCurrent().Name ' here we will get Ires\bridewin Me.myUserName.Text = user ' This the Ideal way to take off the domain name from the user name userId = user.Split("\"C)(1) ' So here we will get bridewin Me.myUserId.Text = userId

So far we have seen how to get username/userid now the question is how to determine whether the user is authenticated?

Well for that there is a simple property IsAuthenticated in GetCurrent() of WindowsIdentity class.

A sample code for that is as below:

C# Code:

if (WindowsIdentity.GetCurrent().IsAuthenticated == true) { this.IsAuthenticated.Text = "Yes you are Authenticated"; } else { this.IsAuthenticated.Text = "No you are not Authenticated"; }

VB Code:

If WindowsIdentity.GetCurrent().IsAuthenticated = True Then Me.IsAuthenticated.Text = "Yes you are Authenticated" Else Me.IsAuthenticated.Text = "No you are not Authenticated" End If

So far as I am concerned about authenticating a user things are fine Now what if I want to check whether it really works for some other user?

Ok for that case just go to Control Panel\User Accounts\User Accounts and inside that click on Manage User Accounts and click on add for adding a user.

1. Say for if you want a add a user ‘durandose’.
2. Then Go to Control Panel\User Accounts\User Accounts (In Windows 7 Professional)
3. Now click on Add and now simply add a user ‘durandose’ select a domain if you want to add that person to a specific domain.
4. Once you are done just click on ‘Next’ and select the roll etc.
5. Now try login through that user and access that site, you should be able to get access to that page if everything has been done as per above instruction.

 Ok! If this was Windows Authentication!?! Then What would be Form Authentication, Passport Authentication and LDAP Authentication?!?! :D

Stay tuned for upcoming post!