C#.Net 3.0 features: Automatic Property

Microsoft introduced the new features in C#.Net 3.0 called Automatic Property.

No more need to declare the private variable for properties. It automatically creates the private variables for properties. But still you can use the conventional way of declaring the private variables to initialize the specific default value to private variable and also other calculation purposes.

Conventional Method:

class Customer
{
private long m_customerID;
private string m_name;

public string CustomerID
{
get
{
return m_customerID;
}
set
{
m_customerID = value;
}
}

public string Name
{
get
{
return m_name;
}
set
{
m_name = value;
}
}

}




C#.Net 3.0 New Method:

class Customer
{

public string CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

}


From the above declaration, you can find that the no private variable declared.


You may ask the question what would be default value for property. Here is the answer.

1. Value type = 0
2. Reference type = null

Performance difference between EXEC and sp_executesql

I presumed that you already know about the execution plan. Sometimes we may come to situation to use Dynamic SQL instead of direct T-SQL.

If we are using Direct T-SQL (not dynamic) in stored procedure, SQL Server reused execution plan from the cache. i.e. SQL Server will not compile the Stored Procedure again.

If we are using dynamic sql in stored procedure, SQL Server may not use the execution plan. It will recreate the execution plan every time with different string of SQL.

So, we have to think about the performance while using dynamic sql.

To execute the dynamic SQL in stored procedure, we have to use the following way.

1. EXEC (Non- parameterized)
2. sp_executesql (Parameterized)


There will be performance difference between above two.

Execution plan will not be created until you execute the dynamic sql. If you execute the dynamic sql using EXEC, execution plan will be created for every execution even values only changing. If you use sp_executesql, SQL Server Optimizer will try to use same execution plan. Because dynamic sql string will be the same, values only going to change. So it will be treated as Stored Procedure having input parameters.

Use the following query to test,


CREATE TABLE [dbo].[Item]
(
ID INT
)

GO

INSERT INTO [dbo].[Item](ID) VALUES (1)
INSERT INTO [dbo].[Item](ID) VALUES (2)

GO


DBCC FREEPROCCACHE

DECLARE @ItemID INT
DECLARE @Query NVARCHAR(200)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = '

SET @ItemID = 1
EXEC( @Query + @ItemID)

SET @ItemID = 2
EXEC( @Query + @ItemID)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = @ID'

SET @ItemID = 1
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID

SET @ItemID = 2
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID




To view the execution plan, use the following query.

SELECT usecounts, sql FROM sys.syscacheobjects



Results:

UseCounts SQL

1 SELECT * FROM [dbo].[Item] WHERE ID = 1
2 (@ID INT)SELECT * FROM [dbo].[Item] WHERE ID = @ID
1 SELECT usecounts, sql FROM sys.syscacheobjects
1 SELECT * FROM [dbo].[Item] WHERE ID = 2


From the results, executed the dynamic sql using sp_executesql uses same execution plan. EXEC create the execution plan every time.



Conclusion:

Always try to use sp_executesql to execute the dynamic sql to improve the performance.