How to improve performance of Database Operations in application

 Hello Guys.

                   Today I am going to explain some tips that I have realized to improve the performance of web application or portal in context of database operation. We all know that without any database no dynamic website/application or portal can function.  So it’s very important for us to effectively work with database. Following are some of the points which we need to consider while designing and developing our application so that its performance is always better. And we wouldn’t have to worry after the application development that how to optimize this application as its performance is very poor with respect to others.

Dynamic SQL Statement in SQL Server

Dynamic SQL:

• 
  

  A dynamic sql statement is a set of sql statements which are constructed at execution time.

  
  


• 
  

  We may have to face certain condition in our application development where we might have to retrieve records from different table based on different conditions then in that scenario we do use dynamic SQL.

  
  


• 
  

  These dynamic Sql statement doesn’t parsed at compile time so it may introduce security vulnerabilities in our databse so we should try to avoid using dynamic sql as much as possible.

  
  


• 
  

  There are two ways to execute a dynamic sql statement in sql server:-

  
  

sp_executesql
EXECUTE()

• 
  

  Although these two methods produces same results but there might be certain scenario where it may produce different results.

  
  

Following is little description about the above two methods:

1.       sp_executesql :-

• 
  

  It is a system stored procedure.

  
  


• 
  

  It allows parameters to be passed IN or OUT of the dynamic sql statement.

  
  


• 
  

  It is less susceptible to SQL Injection.

  
  


• 
  

  Higher chance for sql string to remain in cache which results better performance when the same sql statement is executed.

  
  


• 
  

  Clean code hence easier to read and maintain.

  
  


• 
  

  Support parameter substitution hence more preferable than EXECUTE command.

  
  


• 
  

  Syntax:-

  
  

                       sp_executesql [@sqlstmt ],[ @ParameterDefinitionList],[ @ParameterValueList ]

2.       EXECUTE():-

• 
  

  When we use this command the parameters should be converted to characters.

  
  


• 
  

  Syntax:-

  
  

EXECUTE (@sqlStmt)

Example:-

Create procedure sp_GetSalesHistory

(

                @WhereClouse nvarchar(2000)=NULL,

                @TotalRowsReturned  INT OPUTPUT

)

AS

BEGIN

                DECLARE @SelectStmt         nvarchar(),

                DECLARE @FullStmt             nvarchar(),

                DECLARE @ParameterList   nvarchar()

                SET @ ParameterList   = ’@TotalRowsReturned  INT  OUTPUT ’

SET @ SelectStmt       =  ‘SELECT @ TotalRowsReturned   = COUNT(*) FROM SalesHistory’

SET @ FullStmt           = @ SelectStmt     + ISNULL(@WhereClouse,’ ’)

PRINT @ FullStmt

EXECUTE sp_executesql @ FullStmt           ,@ ParameterList   ,@ TotalRowsReturned  =@ TotalRowsReturned  OUTPUT

END

What is a singleton in C#.NET?

 

A singleton is a design pattern used when only one instance of an object is created and shared; that is, it only allows one instance of itself to be created. Any attempt to create another instance simply returns a reference to the first one. Singleton classes are created by defining all class constructors as private. In addition, a private static member is created as the same type of the class, along with a public static member that returns an instance of the class. Here is a basic example:

public class SingletonExample {

private static SingletonExample _Instance;

private SingletonExample () { }

public static SingletonExample GetInstance() {

if (_Instance == null)  {

_Instance = new SingletonExample ();

}

return _Instance;

}

}

 

Web Services Vs WCF

Number To Word Conversion In C#

HI guys,

               Today i am going to write a program that converts any no input into the system into Word. This program might be asked sometimes during the interview to check the Logic of candidate. so here is the program...