Database - Reasons of poor performance of query

Reasons of poor performance of query.

Following are the reasons for the poor performance of a query:

- No indexes.

- Excess recompilations of stored procedures.

- Procedures and triggers without SET NOCOUNT ON.

- Poorly written query with unnecessarily complicated joins.

- Highly normalized database design.

- Excess usage of cursors and temporary tables.

- Queries with predicates that use comparison operators between different columns of the same table.

- Queries with predicates that use operators, and any one of the following are true:

1. There are no statistics on the columns involved on either side of the operators.

2. The distribution of values in the statistics is not uniform, but the query seeks a highly selective value set. This situation can be especially true if the operator is anything other than the equality (=) operator.

3. The predicate uses the not equal to (!=) comparison operator or the NOT logical operator.

- Queries that use any of the SQL Server built-in functions or a scalar-valued, user-defined function whose argument is not a constant value.

- Queries that involve joining columns through arithmetic or string concatenation operators.

- Queries that compare variables whose values are not known when the query is compiled and optimized.
We shouldn't allow input from users during a transaction, we shouldn't open transactions while browsing through data...
Database - Explain Execution Plan
SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call.....
Database - What are B-trees?
Database B-trees....
Post your comment