How to prove that using subquery queries in SQL kills server performance

Question

One of my tasks is to maintain our database, usually we have problems with a lack of performance when receiving reports and working with this database.
When I start looking for queries that our ERP transfer to the database, I see a lot of completely unnecessary query subqueries in the main queries. Since I am not a member of the developers who are the creators of the program we use, they do not really like me when I criticize their code and work. Let's say they do not consider my review as serious statements. So I ask you some questions about the subquery in SQL

Does subselect take much longer than other external connections?
Is there any blog, article, or anything where I have a subtask, it is recommended not to use?
How can I prove that if we avoid subselesct in the request, the request will be faster?

Our database server MSSQL2005

Answer 1

", ". , SQL Profiler. , ( , ). " " , , .

• SQL Server Profiler ( 2005)

SQL Server Management Studio (SSMS) Query- > Include Actual Execution Plan (CTRL + M)

, , ( " " ) !

, , , , , .

. , :

SET STATISTICS IO ON
GO

-- Run your query here

SET STATISTICS IO OFF
GO

.

, , SQL Server 2008 Query Performance Tuning Distilled, , .

Answer 2

, , - SQL Profiler ( , ) . , .

, , , / .

, , , .

Answer 3

MSSQL, postrgesql . "EXPLAIN", . , , .

, . ( , )

, , , , -query .

, , , ..

UPDATE: , MSSQL → QueryPlan

Answer 4

, , , EXISTS .

, SQL - . . , . , , , , . .

, , , , SQL- : -)

Answer 5

subselect , ?

.

, :

SELECT  *
FROM    mytable
WHERE   mycol NOT IN
        (
        SELECT  othercol
        FROM    othertable
        )

, :

SELECT  m.*
FROM    mytable m
LEFT JOIN
        othertable o
ON      o.othercol = m.mycol
WHERE   o.othercol IS NULL

:

• NOT IN vs. NOT EXISTS LEFT JOIN/IS NULL: SQL Server

- , -, , ?

, .

- , , .

How can I prove that if we avoid subselesct in the request, the request will be faster?

Write a query without subqueries that are faster.

If you post your request here, we may be able to improve it. However, the subquery version may be faster.

Answer 6

Try rewriting some of the queries to perform a subselect and compare the runtime.

Share and enjoy.