SQL - How to Write Optimized Query

In this article we discuss following:

• SQL
• Optimize query tips
• Why Triggers are not Good

SQL Optimize query is a highly rated skill in current IT sector. With invention of Relational Database models, demand of Data manipulation and Management also increased. This lead to formulation of a universal language to serve mankind - SQL. 

SQL stands for Structured Query Language. SQL is basic language in order to communicate with a RDBMS. Data is present in various inter-connected tables inside a database. Different joining conditions are applied and data is presented in form of crisp Information.

SQL:

SQL works with famous RDBMS engines such as MS Sql Server, Oracle, MySql and IBM Informix etc etc. Data Manipulation (DML) and Data Definition (DDL) are two basic operations performed using SQL on a database. A basic query to read first 10 records from Customer table is :

                                        "SELECT TOP 10 * FROM MyDatabase.dbo.Customers"

OPTIMIZE:

i: This query is risky and it need optimization. Optimization is Time based , IO based or Memory based. For making this query execute irrespective of other transactions/queries running on Customers table, we need No lock :

                                         "SELECT TOP 10 * FROM MyDatabase.dbo.Customers WITH(NO LOCK)"

WIHT(NO LOCK) avoids locking Customer table and makes transactions smooth and keeps the database relax.

ii: Another tip to optimize SQL query is to Avoid sub-queries. Sub queries slow down the processing time e.g.

--------------------------------------------------------------------------------------------------------------------------

SLOW QUERY TIME:

SELECT tableA.ID, tableA.NAME, 

(select TotalOrders FROM tableB where tableB.ID = tableA.ID) AS totalOrders  

FROM tableA

OPTIMIZED QUERY:

SELECT tableA.ID, tableA.NAME, tableB.TotalOrders

FROM tableA ,  tableB on  tableB.ID = tableA.ID

---------------------------------------------------------------------------------------------------------------------------

iii- Avoid using Triggers on tables having frequent DML transactions. Tables having too many inserts, reads and updates must not possess any triggers because triggers make transaction deadlocks and may lead to bottlenecks causing massive downtime at database level. Triggers are Slow Poison, that will eventually someday create data loss/ transaction level deadlocks.