SQL SERVER - 15 Best Practices for Better Database Performance

1. Store relevant and necessary information in the database instead of application structure or array.

2. Use normalized tables in the database. Small multiple tables are usually better than one large table.

3. If you use any enumerated field create look up for it in the database itself to maintain database integrity.

4. Keep primary key of lesser chars or integer. It is easier to process small width keys.

5. Store image paths or URLs in database instead of images. It has less overhead.

6. Use proper database types for the fields. If StartDate is database filed use datetime as datatypes instead of VARCHAR(20).

7. Specify column names instead of using * in SELECT statement.

8. Use LIKE clause properly. If you are looking for exact match use “=” instead.

9. Write SQL keyword in capital letters for readability purpose.

10. Using JOIN is better for performance then using sub queries or nested queries.

11. Use stored procedures. They are faster and help in maintainability as well security of the database.

12. Use comments for readability as well guidelines for next developer who comes to modify the same code. Proper documentation of application will also aid help too.

13. Proper indexing will improve the speed of operations in the database.

14. Make sure to test it any of the database programming as well administrative changes.
15. Use Fill Factor to 70 percent, when the table’s data modified very often.

If you are importing large volumes of data from other sources, update statistics often, this will help the DB engine to make use of the indexes that were defined.
Recompile stored procedures after updating the stats.

Don’t put many queries with different execution plans in stored procedures (e.g. using IF’s or CASE). Sql Server caches only one execution plan per sproc.
However, if you have to do this, consider using WITH RECOMPILE option to get optimum results. Often it is better to recompile each time than use poor plans.
CREATE PROCEDURE dbo.Products_GetAll WITH RECOMPILE AS

Avoid using cursors

Don’t use triggers.

Select a row in an asp:GridView without using a Select Command

Select a row in an asp:GridView without using a Select Command

ASP.Net's GridViews can be quite useful, but beware of binding them to huge datasets as this has an overhead on the ViewState.

Often you'll want to display a number of columns on each line and row space becomes an issue. What's worse is you then have to create a SELECT command button to be able to access that line's data.



Use the following code on the event OnRowDataBound to eliminate the need for the SELECT command field and save yourself some valuable space.

Here is the HTML to create a GridView, I'm displaying a list of people, and the key for each record is the PERSON_ID.

AutoGenerateColumns="False"
DataKeyNames="PERSON_ID"
DataSourceID="PeopleDataObject"
Width="200px"
OnRowDataBound="PeopleGridView_RowDataBound"
AllowPaging="True">

DataField="USER_NAME"
HeaderText="Name"
SortExpression="USER_NAME" >




The key event to note is the OnRowDataBound, use the following code to create SELECT functionality on the row.

protected void PeopleGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes["onmouseover"] = "this.style.cursor='hand';this.style.textDecoration='underline';";
e.Row.Attributes["onmouseout"] = "this.style.textDecoration='none';";

e.Row.Attributes["onclick"] = ClientScript.GetPostBackClientHyperlink(this.PeopleGridView, "Select$" + e.Row.RowIndex);
}
}

Each row will then behave like a link, and when you select one it can drive the behavior of another control(s) on your page, possibly a DetailsView allowing you to INSERT a complete record to the database.

Passing Object as a parameter in ObjectDataSource

Passing Object as a parameter in ObjectDataSource:
"OBJParam ObjParam = new OBJParam();
ObjParam.Value = 'no';
ObjParam.SomeValue = 'Yes';
e.InputParameters['ObjParam'] = ObjParam;"