Thursday 13 August 2009

Creating Proxy Object with Using Generic

public class ObjectA : MarshalByRefObject
{
public void MethodA()
{
Console.WriteLine("MethodA is called.");
}
}

public class ObjectB : MarshalByRefObject
{
public void MethodB()
{
Console.WriteLine("MethoB is called");
}
}
public class GenericProxyManager : RealProxy where TObject : new()
{
private TObject _objectInstance;

public GenericProxyManager(TObject objectInstance) : base(typeof(TObject))
{
this._objectInstance = objectInstance;
}

public static TObject GetProxyObject()
{
TObject objectInstance = new TObject();
GenericProxyManager proxyObject = new GenericProxyManager(objectInstance);
TObject transparentProxyObject = (TObject)proxyObject.GetTransparentProxy();
return transparentProxyObject;
}

public override IMessage Invoke(IMessage msg)
{
IMethodCallMessage message = (IMethodCallMessage)msg;

// Unless you are sure the implementation covers only methods, you need to write some additional code for properties ( get/set )
if (message != null)
{
Console.WriteLine(message.MethodName + " has been handled before it called");
// Write necessary code here - this is before you invoke the method
object methodRetval = message.MethodBase.Invoke(_objectInstance, message.InArgs);
// Write necessary code here - this is after you invoke the method
ReturnMessage retVal = new ReturnMessage(methodRetval, null, 0, message.LogicalCallContext, message);
return retVal;
}
return null;
}
}


Lets execute the following code now
ObjectA obj = GenericProxyManager.GetProxyObject();
obj.MethodA();

ObjectB obj2 = GenericProxyManager.GetProxyObject();
obj2.MethodB();

Result will be

MethodA has been handled before it called
MethodA is called.
MethodB has been handled before it called
MethoB is called

Wednesday 12 August 2009

Database Normalization

Normalization is a process of certain rules to be applied while a database is being designed. The goal of this process is to prevent unnecesary data repeats and ensuring data dependencies and efficent use of data.

So what are these rules? There are five known normalization forms. Lets explain these one by one;

First Normal Form (1NF):
One row can contain only one information for a specific data.

Example;
WRONG: We have a Cars table and this table has a column called FuelType as nvarchar(255) and we are storing the data as
Cars
CarIDFuelType
1"Petrol, Diesel"
2"Diesel"
3"Electric, Diesel"




CORRECT: We need to create a new table called FuelTypes which will store all the fuel types and instead of using FuelType nvarchar(255) column, FuelTypeID int should be used and needs to be related to FuelTypes table.
FuelTypes
FuelTypeIDName
1"Petrol"
2"Diesel"
3"Electric"

Cars
CarIDFuelTypeID
11
12
22
33
32

Second Normal Form (2NF)

A table that has a lookup column (which is primary key in other table) can not contain any information from that lookup table.

Example;
WRONG:
Services
ServiceIDWorkerIDWorkerName
12Serkant
23Catherine

Workers
WorkerIDWorkerName
2Serkant
3Catherine


CORRECT:
Services
ServiceIDWorkerID
12
23



Workers
WorkerIDWorkerName
2Serkant
3Catherine

Third Normal Form (3NF)

A table whose non-primary key fields are dependent only on the primary key and therefore have no dependence any other non-primary key field in the table.

Example;
WRONG:
Services
ServiceIDWorkerNameWorkerPhone
1Serkant0777 777 7777
2Daniel0777 888 8888


CORRECT:
Services
ServiceIDWorkerID
12
23

Workers
WorkerIDWorkerNameWorkerPhone
2Serkant0777 777 7777
3Daniel0777 888 8888


Fourth Normal Form (4NF)


A table can not contain two or more 1:n or n:m relationships that are not directly related.

Example;
WRONG:
Book Deliveries
BookstoreBookArea
Bookstore ABook AArea A
Bookstore ABook AArea B
Bookstore BBook BArea B
Bookstore CBook BArea C


CORRECT:
Bookstore Books
BookstoreBook
Bookstore ABook A
Bookstore BBook A
Bookstore BBook B


Bookstore Delivery Areas
BookstoreArea
Bookstore AArea A
Bookstore BArea A
Bookstore BArea B
Bookstore BArea C


Fifth Normal Form (5NF)

This form deals with cases where information can be reconstructed from smaller pieces of information that can be maintained with less redundancy.

Example;
WRONG:
SalesmanCompanyProduct
Salesman ACompany AProduct A
Salesman ACompany AProduct B
Salesman BCompany BProduct A
Salesman BCompany BProduct B


CORRECT:

Company Salesmen
CompanySalesman
Company ASalesman A
Company BSalesman B


Company Products
CompanyProduct
Company AProduct A
Company AProduct B
Company BProduct A
Company BProduct B

Salesman Products
SalesmanProduct
Salesman AProduct A
Salesman AProduct B
Salesman BProduct A
Salesman BProduct B

[SQL Server Performance] Speed up your query with covering index

Do you use the same statement often? Are you facing performance issues on that query? Covering index is what you need :) (Ofc depends on your statement/data size)

So what is covering? Covering index is a technique of designing indexes for a table, not a type of index. I will explain this technique with an example.

Lets take Orders table from Northwind sample db. Orders table has a clustered index on CustomerID. We would like to see shipping fee for daily basis. To get this data execute to following query.

select OrderDate, SUM(Freight) from Northwind.dbo.[Orders] where group by OrderDate





As you can see from execution plan, it scans all clustered index data then sorting and grouping the result from index scan.

Lets put an index for OrderDate and Freight fields and see what happens

CREATE INDEX IX_OrderDate_Freight ON dbo.ORDERS(OrderDate, Freight)

then execute the same query

select OrderDate, SUM(Freight) from Northwind.dbo.[Orders] where group by OrderDate







It is directly accessing to the index and getting all the necessary information from index. Normally if sql scans a non-clustered index and there are some fields in the statement which does not exist in the index, Sql Server engine goes to original record to get the field values for every single record. This may cause a performance issue. In this example as it gets all necessary information from index it will speed up the query. Additionally as non-clustered index is sorted, it saves the engine from making additional sorting process.

Note: When you select your index, try to pick small size ones like smallint, integer, datetime. If you have bigger size fields then the implementation may cost more then you had. Try to prevent to put index on the table which has big number of insert/update/delete operations as well. Because everytime a record is changed, indexes will be reviewed from SQL Server Engine to see if they need to be sorted again.

Friday 7 August 2009

Difference between Clustered and Non-Clustered Indexes

Most of the performance issues are being faced is about how indexes are implemented. Although indexing is very benefical, it can cause performance problems if it is not well structured. In this post i am going to explain the difference between Clustered and Non-Clustered, and also on which condition we should prevent using indexing.

Following explains the methods we are going to use ;

* Lists all of a table's data and index pages
DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

* Setting via trace flag 3604, It allows to instruct the engine to send output to the console
DBCC TRACEON(3604)

* DBCC PAGE statement lists the page header, data rows, and row offset table for any data page in a database.
DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache]


Lets have a look how they stored by SQL;


1) Non indexed Table
Tables do not have a default index.

. Create the test table

Create Table TestTable
(
Id Int,
Name Varchar(255)
)

. Insert test data

Insert Into TestTable Values (3, 'Name3')
Insert Into TestTable Values (1, 'Name1')
Insert Into TestTable Values (2, 'Name2')

. Query the table

Select * From TestTable

. See the result

Id Name
-------------- ------------------
3 Name3
1 Name1
1 Name2

As there is no index, data was returned as the same order as they were inserted.

. See the structure of the data
Declare @dbID Int, @TableID Int
Set @dbID = db_id()
Set @TableID = object_id('TestTable')
DBCC ind(@dbID, @TableID, -1)

Here is the result



The columns mean:

* PageFID - the file ID of the page
* PagePID - the page number in the file
* IAMFID - the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they're not self-referential)
* IAMPID - the page number in the file of the IAM page that maps this page
* ObjectID - the ID of the object this page is part of
* IndexID - the ID of the index this page is part of
* PartitionNumber - the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of
* PartitionID - the internal ID of the partition this page is part of
* iam_chain_type - see IAM chains and allocation units in SQL Server 2005
* PageType - the page type. Some common ones are:
1 - data page
2 - index page
3 and 4 - text pages
8 - GAM page
9 - SGAM page
10 - IAM page
11 - PFS page
* IndexLevel - what level the page is at in the index (if at all). Remember that index levels go from 0 at the leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 - where there's a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))
* NextPageFID and NextPagePID - the page ID of the next page in the doubly-linked list of pages at this level of the index
* PrevPageFID and PrevPagePID - the page ID of the previous page in the doubly-linked list of pages at this level of the index


But we are only interested with PagePID, IndexID and PageType