Difference in order of execution and design in SQL Server.

Click For Summary

Discussion Overview

The discussion centers around the order of execution in SQL Server queries, specifically why queries are not written in the same order as they are executed and whether this affects execution speed. Participants explore the implications of execution order, indexing, and performance in different database environments.

Discussion Character

  • Technical explanation
  • Debate/contested

Main Points Raised

  • Some participants assert that SQL Server executes queries in the order of FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY, questioning why this order is not reflected in query writing.
  • Others argue that the SQL engine constructs an execution tree to determine the most efficient way to execute the query, suggesting that the written order does not impact performance.
  • One participant notes that using too many index tables can negatively affect performance in high transaction environments, such as OLTP systems, due to the overhead of maintaining indexes during frequent data modifications.
  • Another participant mentions that SQL Server generates error messages when queries are written in an order that violates the expected execution sequence, indicating a strict adherence to this order in practice.

Areas of Agreement / Disagreement

Participants express differing views on whether the order of query writing affects execution speed, with some emphasizing the importance of execution order while others focus on the SQL engine's optimization processes. The discussion remains unresolved regarding the implications of query order on performance.

Contextual Notes

Limitations include the lack of consensus on the impact of query writing order on execution speed and the potential for confusion regarding the use of indexes in different database setups.

WWGD
Science Advisor
Homework Helper
Messages
7,806
Reaction score
13,120
Hi, given Sql server executes queries order from, where, group by, having, select, order by. Why don't we write queries in same order? Would that improve speed of execution ?
 
Technology news on Phys.org
No the sql engine constructs an execution tree and decides how to best execute the query.

Some systems have a describe or explain command that will show how the query will be executed and the actual tables it will use.

It can be quite surprising too. As an example you might have built an index table to speed your query only to discover that it’s not being used. This may mean a different index table is needed.

One caveat is it can be a negative to create too many index tables especially in a high performance application where data is added and deleted constantly. Each time you add or drop data the associated index tables needed to be added to or deleted from.
 
  • Like
Likes   Reactions: WWGD
jedishrfu said:
No the sql engine constructs an execution tree and decides how to best execute the query.

Some systems have a describe or explain command that will show how the query will be executed and the actual tables it will use.

It can be quite surprising too. As an example you might have built an index table to speed your query only to discover that it’s not being used. This may mean a different index table is needed.

One caveat is it can be a negative to create too many index tables especially in a high performance application where data is added and deleted constantly. Each time you add or drop data the associated index tables needed to be added to or deleted from.
I guess that is why you do not use many indexes in OLTP setup? I mean, since they do mostly inserts, updates and deletes.
 
jedishrfu said:
No the sql engine constructs an execution tree and decides how to best execute the query.

Some systems have a describe or explain command that will show how the query will be executed and the actual tables it will use.

It can be quite surprising too. As an example you might have built an index table to speed your query only to discover that it’s not being used. This may mean a different index table is needed.

One caveat is it can be a negative to create too many index tables especially in a high performance application where data is added and deleted constantly. Each time you add or drop data the associated index tables needed to be added to or deleted from.
It seems from doing informal research that SQL Server will not execute queries that are not done in the order : SELECT FROM WHERE GROUP BY HAVING ORDER BY. I get error messages every time I try any query that violates this order sequence.
 

Similar threads

Speeding Up SQL Query: Grouping & Joining
  • · Replies 51 ·
2
Replies
51
Views
6K
What is intermediate-advanced SQL and how to learn it?
  • · Replies 6 ·
Replies
6
Views
4K
Embarrassingly Simple SQL Server query: List Even numbers in....
  • · Replies 7 ·
Replies
7
Views
2K
Amateur Dev Q: Creating Front End for SQL Server DB
  • · Replies 18 ·
Replies
18
Views
4K
What does it mean that MySQL is a server application?
  • · Replies 16 ·
Replies
16
Views
3K
Why can't there be a Database tool that has the best of everything?
  • · Replies 5 ·
Replies
5
Views
3K
Python Query runs fine in MySQL, but returns empty list in Python
  • · Replies 7 ·
Replies
7
Views
5K
SQL Stored Procedures as Front End?
  • · Replies 4 ·
Replies
4
Views
2K
Python Python Socket library to create a server and client scripts
  • · Replies 9 ·
Replies
9
Views
5K
Remembering T-SQL: SET vs SELECT and OBJECT_ID()
  • · Replies 1 ·
Replies
1
Views
1K