Tuesday 10 November 2015

Tips for Dot Net Programmers

Today I am going to share some tips for Dot Net programmers and using C# as main reference language, hope these tips can be helpful to get performance and maintain code standards:
  • Always try to use “using block” with critical and expensive resource like connection, stream and etc. If it is not possible to use using block then explicitly call close/similar method.
  • If any generics is not useful or local instances of generics is going out of scope then explicitly call clear for that item.
  • Please consider catch block to handle unexpected or uncontrolled flow. It is always advisable to avoid exception. For example try to check divide by zero instead of handling it in catch block.
  • Depending on application structure and requirements, we may perform required actions and then may bubble up exception using “throw” statement instead of “throw ex”;
  • We may implement logging for exception.
  • It is highly recommended to decompose a function or method if it is exceeding the visible screen. Rule of thumb is any function should be visible without vertical scroll.
  • We may prefer to make helper classes to perform specific set of operations like: file writing, data converter and etc.
  • Try to avoid value assignment to enum members until unless, these are flagged enums or may have specific purpose like association with database value.
  • Get latest before Check in and make sure that code is compliable before and after check in.
  • Always remove unused namespaces (Context Menu è Organize using è Remove and Sort).
  • It is preferable to reformat code (in Code View using Ctrl + K + D).
  • Use conditional expression in Single, First or FirstorDefault with collection when single item is required.
  • If more than one property is required from a collection object then get that object once from collection, instead of using search for each property.
  • Use Constants or Enums instead of hardcoded values.
  • Use string.Empty instead of “”.
  • If catch does not use Exception object then it is better to omit declaration of Exception.
  • Define required variable at top of scope.
  • Arrange code in regions.
  • Please avoid spelling mistakes.
  • If some item is output of processing and is only required in specific block then better to perform that processing in that block.
  • Remove commented code and unused variables.
  • There should not be any dead or unreachable code sections.
If you are working in C#, then I may recommend you to go through following articles by Microsoft:

Sunday 8 November 2015

Tips for SQL Server Database Design

Database designing is a very vast and very crucial phase. If you belong to group whom goes with Database First Approach, then any decision you make in phase may have huge impacts. I would like to highlight few important points which can be worthy to consider for better database designing and performance.  
  • Always try to device a naming convention and few standards like field size, datatypes, and general assumptions before you start designing database. These can save lot of time and efforts. These can even play vital role in performance.
  • Consider Demoralization, particularly for very large data. But as rule of thumb, first Normalize your Database. It is a good idea to use Indexed view for Demoralization.
  • It will be better to define Primary Key and Foreign Key Relationships.
  • Clearly finalize Database Isolation Level. 
  • Clearly define sequence of involvement to tables in transaction to avoid deadlocks.
  • It is good practice to have one Identity Column in a table, either it is primary key or not.
  • Precisely plan Indexing, and keep in mind, it is ongoing process. Be specific to choose column for clustered and non clustered indexes. In general usage of columns in join, where, order by and group by directly affect your index requirements.
  • You need a good plan for index defragmentation, particularly when there are lots of insert, delete and update operations.
  • Consider new technologies and out of box solution. In general, I may prefer out of box features instead using old custom methodologies until unless there is some specific reason to do so like backward compatibility.
    • Consider built in Table Partitioning feature instead of manual table partitioning for large data tables.
    • Consider File Stream introduced in SQL Server 2008 for File Storage.
    • Consider new DataTypes over old wherever possible, like Varchar(max) over Text, Date over DateTime and etc.
    • Consider In Memory Optimized Tables. Now RAM is very cheap, and such data cache techniques can make a great difference in performance.
  • Consider splitting database into multiple files based on your database size.
  • Consider separation of database files and log files on different hard disk.
  • Always use appropriate datatype, for example if you have to store English data only then you don't need to use nvarchar, or nchar, if you need to store small value set, then there is no need to use bigint.
  • Consider Summary Table or Pre Calculated Data for very large data.
  • Plan a clear strategy for performance and maintenance. In many cases you have to address Performance vs Maintenance. Ideally you must go with some acceptable combination of both, but this decision is directly dependent on your needs and budget.
  • It will be better to same standard for Database objects (Table and Column) and Application Data Model. Although it may not give any performance gain, but it can facilitate developers.
  • If you are planning to use Triggers or Cursors then reevaluate alternate approaches.

Tips for SQL Programing

There are lots of articles on Query Optimization, here I am going to highlight few general points those can make a minor to major difference in performance or quality of code with very little efforts. These are general points, but few things and examples are specifically for SQL Server.
  • Always try to end your SQL statement with semicolon (;).
  • It is preferable to use object name with schema for your tables and other objects. So use dbo.MyTable instead of MyTable.
  • Try to avoid * in Select query, instead use column names. It is better to use Select Coloumn1, Column2, Column3 From dbo.MyTable;
  • It is better to use alias with table name and related columns. So it is better to use with respect to above example: Select tbl.Coloumn1, tbl.Column2, tbl.Column3 From dbo.MyTable as tbl;.
  • Always try to involve as few columns in query as possible. Rule of thumb is not to add columns in query which are not required. 
  • Avoid unnecessary data casting in query.
  • Although SQL Query Optimizer rearrange conditions execution sequence, yet, it is better to add required conditions in Join Statement if applicable.
  • If possible, then consider proper usage of Indexes. If you use an Indexed column with expression (some function, or operator like +, -) then, indexing will not give any benefit as it is ignored in a such case.
  • Avoid Dynamic SQL, in general, stored procedure give better performance. There are few cases where dynamic queries are faster like dynamic sorting, dynamic filters, but dynamic queries may have some other challenges.
  • Try to be specific in database transaction.
  • It should be better to have both operands of same type in a condition.
  • There are many situations, where cursors can be avoided with CTE and loops with temp/memory Table.
  • If you have small data to hold temporary then memory table is better choice over temp table in general.
  • If you are going to fetch data in pages then, fetch and offset is a good option introduced in SQL Server 2012. If you are using SQL Server 2005/2008 then CTE is better approach.
  • Surprisingly, SQL Server do not have short circuit And, OR. It may or may not short circuit your conditions depending on Query Plan.
  • Union All is faster than Union operator. So precisely, use what is required. 
  • Operators OR, Like, In are very expensive operators. There can be alternatives, for example you can use Union All instead of OR.
  • In general if you use Distinct or Group by with out any aggregate function, they will provide same performance. Practically, it will be rare to have both in same query.
  • Please be aware of operator precedence in SQL.
  • It will be better to be aware of latest technologies and features. In general, I may prefer out of box features instead using old custom methodologies until unless there is a reason.
  • If you are going to write a complex sp, and using same data or table again and again, then consider CTE or Temp/Memory Table.