Monday, April 16, 2012

How to avoid multiple database request to improve performance

It is not good to execute multiple db request for loading single page.  Review your database code to see if you have request paths that go to the database more than once. Each of those round-trips decreases the number of requests per second your application can serve. By returning multiple resultsets in a single database request, you can cut the total time spent communicating with the database.

In order to improve performance you should execute single stored proc and bring multiple resultset in to single db request.  In this article i will explain you how to avoid multiple database request and how to bring multiple resultset into single db request.

Consider a scenario of loading a Product Page, which displays

  • Product Information and
  • Product Review Information

In order to bring 2 database request in single db request, your sql server stored proc should be declared as below.

SQL Server Stored Proc

@ProductId bigint,

--Product Information
Select ProductId,
From Product
Where ProductId = @ProductId

--Product Review Information
Select ReviewerName,
From ProductReview
Where ProductId = @ProductId, C# Code to bring multiple db request into single db request

Code Inside Data Access Class Library (DAL)

public DataSet GetProductDetails()
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = "GetProductDetails";
cmdToExecute.CommandType = CommandType.StoredProcedure;
DataSet dsResultSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

    var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
    string strConnString = conString.ConnectionString;
    SqlConnection conn = new SqlConnection(strConnString);

    cmdToExecute.Connection = conn;

    cmdToExecute.Parameters.Add(new SqlParameter("@ ProductId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 19, 0, "", DataRowVersion.Proposed, _productId));

    //Open Connection

    // Assign proper name to multiple table
    adapter.TableMappings.Add("Table", "ProductInfo");
    adapter.TableMappings.Add("Table1", "ProductReviewInfo");

    return dsResultSet;              
catch (Exception ex)
    // some error occured. 
    throw new Exception("DB Request error.", ex);

Code Inside .aspx.cs page

protected void Page_Load(object sender, EventArgs e)
   if (Request.QueryString[ProductId] != null)
      long ProductId = Convert.ToInt64(Request.QueryString[ProductId].ToString());  
      DataSet dsData = new DataSet();

      //Assuming you have Product class in DAL
      ProductInfo objProduct = new ProductInfo();
      objProduct.ProductId = ProductId;
      dsData = objProduct.GetProductDetails();

      DataTable dtProductInfo = dsData.Tables["ProductInfo"];
      DataTable dtProductReviews = dsData.Tables["ProductReviewInfo"];

      //Now you have data table containing information
      //Make necessary assignment to controls


Hope above code gave you basic idea of why it is important to avoid multiple db request and how to bring multiple recordset with single db request.

Saturday, April 07, 2012 Security Considerations

If you are web developer or architect, I would recommend reading this ebook.  It explains how to make your website secured.   I believe not most of users need to consider "ALL" those points, but you should perform some of important points to secure your website.

I was planning to make a list of important considerations from this book, but since this book is very well written i personally like most of points i have read so far.

And finally if you have developed website which is accessible through internet, than check whether your website do pass against security scan. website security analyser 

Hope this book help you too :)

Most Recent Post

Subscribe Blog via Email

Enter your email address:

Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape