sql \n 查询_探索SQL查询提示选项（快速N）

sql \n 查询

In this article, we will introduce SQL queries hint and will analyze the OPTION(Fast ‘N’) hint in detail.

在本文中，我们将介绍SQL查询提示，并将详细分析OPTION（Fast'N'）提示。

介绍 (Introduction)

Once the user submits any query to SQL Server, it creates an optimized and cost-effective execution plan depending upon the statistics, indexes, and data distribution. In most of the cases, it is an effective plan, and it does not require any intervention for a query performance if all other things like statistics, indexes are in proper form.

一旦用户向SQL Server提交了任何查询，它就会根据统计信息，索引和数据分布创建一个优化且具有成本效益的执行计划。 在大多数情况下，这是一个有效的计划，并且如果所有其他信息（例如统计信息，索引）都采用适当的形式，则不需要任何干预即可提高查询性能。

In case you are new to Execution Plans in SQL Server, you can refer to this article, SQL Server Execution Plans overview to understand about them.

如果您不熟悉SQL Server中的执行计划，则可以参考本文SQL Server执行计划概述以了解它们。

SQL Server provides query hints in which you can force query optimizer to use a particular operator in the execution plan. To use a specific operator, SQL Server might need to change other operators as well. We have the following query hints in SQL Server.

SQL Server提供了查询提示，您可以在其中强制查询优化器在执行计划中使用特定的运算符。 若要使用特定的运算符，SQL Server可能还需要更改其他运算符。 我们在SQL Server中具有以下查询提示。

表提示 (Table Hints)

Suppose you want to use a specific index for your query to avoid a table scan, we can use table hints. One of the popular table hints is WITH(NOLOCK) to avoid default transaction isolation level and avoid locking issues in Select statements.

假设您要为查询使用特定索引以避免表扫描，我们可以使用表提示。 常用的表提示之一是WITH（NOLOCK），以避免默认的事务隔离级别，并避免在Select语句中锁定问题。

SQL查询提示 (SQL Queries Hints)

It instructs query optimizer to apply the hint throughout the SQL query execution. Some examples are Recompile, MAXDOP, OPTIMIZE FOR UNKNOWN.

它指示查询优化器在整个SQL查询执行过程中应用提示。 例如重新编译，MAXDOP，优化未知。

In this article, we will explore the query hint OPTION (FAST ‘N’) to improve the performance of a query for retrieving the specific number of rows quickly along with its impact.

在本文中，我们将探索查询提示OPTION（快速'N'），以提高查询的性能，以快速检索特定的行数及其影响。

先决条件 (Pre-requisites)

In this article, I will use the following SQL Server environment.

在本文中，我将使用以下SQL Server环境。

• SQL Server 2019 – 15.0.2070.41 – RTM – GDR
  SQL Server 2019 – 15.0.2070.41 – RTM – GDR
• {AdventureWorks] sample database. If you do not have it in your instance, you can download the .bak file from GitHub and restore the backup file. Change the compatibility level as per the SQL instance version
  {AdventureWorks]示例数据库。 如果您的实例中没有该文件，则可以从GitHub下载.bak文件并还原备份文件。 根据SQL实例版本更改兼容性级别

在SQL查询中探索OPTION（FAST'N'）提示 (Exploring OPTION(FAST ‘N’) hint in SQL queries)

Suppose you execute the following SQL script that retrieves the records from using a Select statement. It uses an INNER JOIN between [SalesOrderDetails] table and [Product] table.

假设您执行以下SQL脚本，该脚本使用Select语句检索记录。 它在[SalesOrderDetails]表和[Product]表之间使用INNER JOIN。

SELECT *
FROM [AdventureWorks].[Sales].[SalesOrderDetail] s
     INNER JOIN [AdventureWorks].[Production].[Product] p ON p.ProductID = s.ProductID;

This query returns 121,317 rows and took 2 seconds on my laptop, as shown below:

该查询返回121,317行，并在我的笔记本电脑上花费了2秒钟，如下所示：

Let’s enable the actual execution plan for this query using the CTRL+M shortcut key. We will also enable statistics and IO profile for this session using the SET STATISTICS IO, TIME statement.

让我们使用CTRL + M快捷键为该查询启用实际的执行计划。 我们还将使用SET STATISTICS IO，TIME语句为此会话启用统计信息和IO配置文件。

SET STATISTICS IO, TIME ON
SELECT *
FROM [AdventureWorks].[Sales].[SalesOrderDetail] s
     INNER JOIN [AdventureWorks].[Production].[Product] p ON p.ProductID = s.ProductID;

In the below actual execution plan, it uses a clustered index scan and index match ( inner join) as two costly operators.

在下面的实际执行计划中，它使用聚簇索引扫描和索引匹配（内部联接）作为两个昂贵的运算符。

If we look at the message tab, you can see logical reads, physical reads, CPU time, elapsed time. To analyze these outputs, we can copy the message tab output in statistics parser. You get output in a tabular format, and it helps you to analyze the output quickly.

如果我们查看消息选项卡，则可以看到逻辑读取，物理读取，CPU时间，经过时间。 要分析这些输出，我们可以将消息选项卡的输出复制到统计分析器中 。 您以表格格式获得输出，它可以帮助您快速分析输出。

Here, you can see we have a total of 1261 logical reads, out of which 1246 logical reads are for the [SalesOrderDetails] table.

在这里，您可以看到我们总共有1261个逻辑读取，其中1246个逻辑读取用于[SalesOrderDetails]表。

Sometimes we see that SQL Queries takes a long time to complete, and we want to view or display the specified number of rows as soon as possible in a client application. SQL Server provides a query hint OPTION(FAST ‘N’) for retrieving the N number of rows quickly while continuing query execution. This Fast query hint tells (forces) SQL Queries optimizer to give a specific number of rows (represented by ‘N’) quickly without waiting for the complete data set to appear.

有时，我们看到SQL查询需要很长时间才能完成，并且我们想在客户端应用程序中尽快查看或显示指定的行数。 SQL Server提供查询提示OPTION（FAST'N'），以便在继续执行查询的同时快速检索N个行。 此快速查询提示告诉（强制）SQL Queries优化器快速给出特定数量的行（用'N'表示），而不必等待完整的数据集出现。

You can think of it as a user application where you do not want the user to wait for their data. They can view N number of rows quickly as they refresh the web page. For example, let’s say we want to retrieve one row quickly so we can specify a query hint, as shown below in the above query.

您可以将其视为用户应用程序，您不希望用户等待他们的数据。 他们刷新网页时可以快速查看N行。 例如，假设我们要快速检索一行，以便我们可以指定查询提示，如上面的查询中所示。

SET STATISTICS IO, TIME ON
SELECT *
FROM [AdventureWorks].[Sales].[SalesOrderDetail] s
     INNER JOIN [AdventureWorks].[Production].[Product] p ON p.ProductID = s.ProductID
   OPTION ( FAST 1);

It quickly populates one row in the output while the rest of the rows are being retrieved. Let’s view the execution plan of the query above with OPTION(FAST 1).

它会快速填充输出中的一行，同时检索其余的行。 让我们用OPTION（FAST 1）查看上面查询的执行计划。

In the below execution plan, it changes the costly operators as below.

在下面的执行计划中，它如下更改了昂贵的运算符。

• The Clustered Index Scan:35%
  聚集索引扫描：35％
• The clustered index seeks: 67%
  聚集指数寻求：67％

We have not changed the query, but still, it changes the execution plan to satisfy the query hint specified.

我们没有更改查询，但是仍然更改了执行计划以满足指定的查询提示。

The FAST ‘N’ query hint allows the optimizer to return a specified number of rows as quickly as possible for SQL Queries. Imagine you have a custom application where users put a specific condition and wait for data to appear.

FAST'N'查询提示使优化程序可以针对SQL查询尽快返回指定的行数。 假设您有一个自定义应用程序，其中用户放置了特定条件并等待数据出现。

It will increase user experience if the SQL query returns the results quicker. Suppose we use a FAST 75 table hint, to return the first 75 records of the results set while still working to return the remaining rows. It allows users to start working with the data before the rest of the screen loads with data.

如果SQL查询更快地返回结果，它将增加用户体验。 假设我们使用FAST 75表提示，以返回结果集的前75条记录，同时仍在工作以返回其余行。 它允许用户在屏幕其余部分加载数据之前开始使用数据。

Let’s look at the below image showing the tooltip for the select operator in the query without query hint and with query hint Option Fast(1).

让我们看下面的图像，该图像显示了查询中select运算符的工具提示，其中没有查询提示，但带有查询提示Option Fast（1）。

• The query optimizer uses 121317 estimated number of rows per execution without any query hint. We can note that the total number of rows returned by the above select statement in SQL Server is also 121317 rows
  查询优化器使用121317估计的每次执行行数，而没有任何查询提示。 我们可以注意到，SQL Server中上述select语句返回的总行数也为121317行
• In the other case, we force SQL Queries optimizer to use the query hint. It uses the 1 row as the estimated number of rows per execution. You might get quick results for the specified number of rows, but it put SQL Server query optimizer to prepare a poor execution plan
  在其他情况下，我们强制SQL Queries优化器使用查询提示。 它使用1行作为每次执行的估计行数。 对于指定的行数，您可能会很快得到结果，但是它使SQL Server查询优化器准备了糟糕的执行计划

Similar to comparing the actual execution plan, let’s compare the logical reads of both the queries.

与比较实际执行计划类似，让我们比较两个查询的逻辑读取。

• [SalesOrderDetail] table logical reads are the same in both queries 1246
  [SalesOrderDetail]表逻辑读取在两个查询中均相同1246
• You can note a significant increase in the logical reads for the [Product] table. Previously it had 15 logical reads, but we get 242634 logical reads for it with query hint
  您可以注意到[Product]表的逻辑读取显着增加。 以前它有15个逻辑读，但是我们通过查询提示获得了242634个逻辑读

Let’s explore a few more examples with different values of ‘N’ in the query hint in a single query window. We use the Go operator to execute these queries in separate query batches.

让我们探索单个查询窗口中查询提示中具有不同“ N”值的更多示例。 我们使用Go运算符在单独的查询批中执行这些查询。

• Query with Option(fast 1)
  使用选项查询（快速1）
• Query with Option(fast 10)
  使用选项查询（快速10）
• Query with Option(fast 100)
  使用选项查询（快速100）
• Query with Option(fast 1000)
  用选项查询（快速1000）

From the below screenshot, we see that as we increase the value in ‘N’ in the query hint, the overall cost gets increased in comparison with the other batches. It shows 57% query cost for FAST 1000 while it has a 5% query cost for FAST 1 SQL Queries hint.

从下面的屏幕截图中，我们看到随着查询提示中'N'值的增加，总成本与其他批次相比有所增加。 它显示FAST 1000的查询成本为57％，而FAST 1 SQL Queries提示的查询成本为5％。

In the above screenshot, we can see execution plans with different values of fast query hint. It highlights that query without any hint is fastest. In this case, SQL Server was able to create the optimized execution plan. Once we specify a fast query hint, optimizer stops thinking and prepares an execution plan by estimating the number of rows we specify in the hint. It works fine with the small data set and low value of fast query hint. As we increase the row counts, query cost becomes significant, and it might cause a performance bottleneck instead of getting the benefit of it.

在上面的屏幕截图中，我们可以看到具有不同值的快速查询提示的执行计划。 它突出显示查询没有任何提示是最快的。 在这种情况下，SQL Server能够创建优化的执行计划。 一旦我们指定了快速查询提示，优化器就会停止思考，并通过估计在提示中指定的行数来准备执行计划。 它适用于较小的数据集和较低的快速查询提示值。 随着我们增加行数，查询成本变得很高，并且可能导致性能瓶颈，而无法从中受益。

Query hint is a sword of double edges. You should avoid using the hint and let optimizer creates the best execution plan. Sometimes you see an immediate benefit in query performance once you use a hint, but as data grows, it might not be suitable for your requirements. You should analyze the query plan, focus on the cost operators, resources, statistics, indexes, their fragmentations to fix issues in the long run.

查询提示是一把双刃剑。 您应该避免使用提示，而让优化器创建最佳执行计划。 有时，一旦使用了提示，您就会发现查询性能立即受益，但是随着数据的增长，它可能不适合您的要求。 您应该分析查询计划，重点关注成本运营商，资源，统计信息，索引及其碎片，以从长期解决问题。

结论 (Conclusion)

In this article, we explored SQL Queries hints and focused on the OPTION(FAST ‘N’) hint. You should never use this hint in a production environment without proper testing in non-prod systems. If you plan to use them, look at the query execution plans, their costs, logical reads, and physical reads.

在本文中，我们探讨了SQL查询提示，并重点介绍了OPTION（FAST'N'）提示。 没有在非产品系统中进行正确的测试，切勿在生产环境中使用此提示。 如果计划使用它们，请查看查询执行计划，它们的成本，逻辑读取和物理读取。

翻译自: https://www.sqlshack.com/explore-sql-queries-hint-option-fast-n/

sql \n 查询