SQL - Query Optimization Techniquess - I

Below given are some facts those can be used to optimize our query performance

• Unique Key: If there is no primary key defined on a table sql uses "Table Scan" operator.table scan operator reads records one by one until it gets required record, so this operator is costly if table having large number of rows.
  When we create primary key on table it improve performance drastically. As displayed in picture.

• Scan Vs Seek: If we've clustered / non clustered index on table then sql uses 'Index Scan' or 'Index Seek' operators.Index Scan : Touches every row whether it is qualified or not, start to end. So cost associated with this search is proportional total number of rows.
  This scan is right choice if table is small or most of the rows are qualified for condition of query.
  Index Seek : Index seek touches only rows / pages those are qualified for condition of query.
  So, if we provide some condition sql prefer to choose 'Index Seek', Even if condition provided result record number is large then optimizer change the plan uses Scan instead of Seek.
  This is right choice if table having large number of rows or qualified rows count is very much smaller(few) compare to all rows. 

SQL - Basics Sql execution plan

Below given is high level diagram shows how Sql queries get executed

Sql query processing steps : There are some steps for sql statements execution

1. Parsing : Parser checks syntax of sql statement including spellings, reserved keywords etc, and prepare sequence tree.
2. Normalization : Normalization process mainly perform binding for objects of sequence tree, checks tables and columns available or not, loading metadata, adding required information for conversations to sequence tree. Replacing views with their definitions.
3. Compilation : Compilation process include extraction of true transact statements(select, insert, update, delete etc) to pass to optimizer, because whole code may include variable declarations, assignments, loops, if conditions etc those not required optimization.
   

SOLID Principles

SOLID is an acronym for basic object oriented programming & design principals. Applying these principle on system makes system easy to maintain and extendable with time.

• Single Responsibility Principle (SRP)
• Open Closed Principle (OCP)
• Liskov Substitution Principle (LSP)
• Interface Segregation Principle (ISP)
• Dependency Inversion Principle (DIP) 

Also refer nice article on https://www.syncfusion.com/blogs/post/mastering-solid-principles-csharp 

• Single Responsibility Principle (SRP): Single responsibility principle says a software module / class should have only one reason to modify. It means if any class having more than one reasons to modify, this is violation of SRP.

    public class Circle

    {

        private int _radius;

        public int Radius

        {

            get

            {

                return _radius;

            }

            set

            {

                value = _radius;

            }

        }

        public int Area()

        {

            return Radius * Radius * 22 / 7;

        }

        public string PrintHTMLArea()

        {

            return "<h2>" + Area().ToString() + "</h2>";

        }

    }

Above class contains two responsibility
Calculating Area
Printing Area

If properties need to add/modify or print method need to be modified or need to add new print method, class need to be updated. So this is violation of SRP, entity class should have only entity specific things like variables / properties.