SQL Query Optimization

-

SQL Query Optimization:
1. Use Indexes
2. Avoid SELECT DISTINCT
3. Use WHERE Clause instead of HAVING CLAUSE
     SELECT name FROM table_name WHERE age>=18
     SELECT age COUNT(A) AS Students FROM table_name GROUP BY age HAVING COUNT(A)>1;
4. Avoid Queries inside a Loop
5. Use Select instead of Select *
6. Add Explain to the Beginning of Queries
7. Keep Wild cards at the End of Phrases
8. Use Exist() instead of Count()
9. Avoid Cartesian Products
10. Consider Denormalization
11. Optimize JOIN Operations
      Create queries with INNER JOIN (not WHERE or cross join)
12. Use LIMIT to sample query results
13. Run your query during off-peak hours

SQL Performance Tuning Tools
1. SQL Sentry (SolarWinds)
2. SQL Doctor (IDERA)
3. Profiler (Microsoft)
4. SQL Defrag Manager (IDERA)
5. DB Optimizer (IDERA)
6. SQL Check (IDERA)
7. Foglight for SQL Server (Quest Software)
8. SQL Index Manager (Red Gate Software)
9. Qure Optimizer (DBSophic)
10. DBArtisan® (IDERA)
11. dbWatch Control Center (dbWatch)
12. Plan Explorer (SolarWinds / SentryOne)
13. SQL Diagnostic Manager (IDERA)
14. Database Performance Analyzer (SolarWinds)
15. Applications Manager (ManageEngine)
16. NaviCat for SQL Server (NaviCat)

Comments

Post a Comment

Popular posts from this blog

PL/SQL

-
PL/SQL PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). PL/SQL can execute a number of queries in one block using single command. One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications. The PL/SQL engine resides in the Oracle engine. The Oracle engine can process not only single SQL statement but also block of many statements. Structure of PL/SQL Block: DECLARE     declaration statements; BEGIN     executable statements EXCEPTIONS     exception handling statements END; / Example : SET SERVEROUTPUT ON; SQL> DECLARE        var varchar2(40) := 'I love GeeksForGeeks' ;      BEGIN        dbms_output.put_line(var);      END;      / PL/SQL Functions PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are...
Read more

JAVA8 Features

-
Java8 Features 1) Lambda Expressions : Concise functional code using ->. Lambda Expression basically expresses an instance of the functional interface, Lambda Expression provides a clear and concise way to represent a method of the functional interface using an expression. 2) Functional Interfaces : Single-method interfaces. An interface that contains only one abstract method is known as a functional interface. There is no restriction, that can have n number of default and static methods inside a functional interface. A functional interface is an interface that contains only one abstract method.  @FunctionalInterface annotation is used to ensure that the functional interface can’t have more than one abstract method. Runnable, ActionListener, and Comparable are some of the examples of functional interfaces.   Runnable –> This interface only contains the run() method.   Comparable –> This interface only contains the compareTo() method.   ActionListener –> ...
Read more

Build Automation

-
ANT: Ant was the first build tool released in 2000. Ant was developed on procedural programming idea. Ant was improved with an ability to accept plug-ins and dependency management over the network, with the help on Apache-IVY. Drawbacks: 1) XML is used as a format to write the build scripts. 2) Being hierarchical is not good for procedural programming, and 3) XML is relatively unmanageable.   MAVEN: Maven was introduced in 2004. Maven relied on the conventions and was able to download the dependencies over the network. Benefits : Life cycle of Maven, while following the same life cycle for multiple projects continuously. Problems: 1) Maven does not handle the conflicts between versions of the same library. 2) Complex customised build scripts are difficult to write in Maven, as compared to writing the build scripts in ANT. GRADLE : Gradle came into picture in 2012 Features  Declarative builds and build-by-convention 1) Gradle is available with separate Domain Specific Language ...
Read more