找到30个第一个结果后，如何强制SQL进行breack搜索？ [英] how force to SQL that breack search after finding 30 first result?

问题描述

大家好

1

hi all
1

select TOP 30 fName from names where fName like 'Sa%'

2

2

select * fName from names where fName like 'Sa%'

优先：在速度方面有什么不同？

秒：实际上，我不希望SQL引擎找到所有查询结果然后返回30个顶部，我想在找到30个第一个结果后，它停止搜索节省时间，是否可能或在SQL编码字段中？

感谢所有

first:what is different between 1 and 2 In terms of speed?
second:in fact,i don't want SQL engine find all query result then return 30 top,i want to after finding 30 first result ,it stopped searching for saving time,is it possible or in SQL coding field?
thanks all

推荐答案

您的第一个查询将给出包含fName以'Sa'开头的确切30条记录（如果您的表'名称'包含超过30条满意的记录条件）



&



第二个查询将为您提供表'name'中的所有记录满足上述条件。



因为你只需要30条记录所以去第一次查询。

Hi,
your first query will give the exact 30 records which contains fName starts with 'Sa' (if your table 'names' contains more than 30 records with satisfied conditions)

&

the second query will give you all the records from the table 'names' which fulfill the above conditions.

As you required only 30 records so go for 1st query.

差异是什么在速度与声明一和二，唯一确定的事情是，在第一个只有30或更少的记录被转移到呼叫者。在第二个中，所有行都被传输，可能超过30个。因此，第一个中的网络流量可能较少，这可能使查询更快。



实际执行的差异是什么，取决于。关键是，TOP表达式在执行计划中的评估时间越早越好。然而，这是由数据库在创建最佳执行计划时决定的。



一个好的经验法则是，永远不要超过需要。如果您只需要前30行，则使用TOP返回这些行。但是，你没有指定任何订单，因此前30个记录将是随机记录。



另一个经验法则是你应该注意索引。索引是快速数据访问和良好执行计划的关键。良好的索引将帮助您构建快速语句。



那么您如何知道数据库用于获取数据的计划。如果使用Sql Server Management Studio，则可以使用估计或实际的执行计划说明。通过解释计划，您将看到如何执行该声明，并且您还能够查明执行中的问题，例如缺少索引等。



更多信息，请参阅显示预计执行计划 [ ^ ]

What comes to the differences in speed with statement one and two, the only certain thing is that in the first one only 30 or less records are transferred to the caller. In the second one all the rows are transferred which may be more than 30. Because of this there probably is less network traffic in the first one which may make the query faster.

What comes to the differences in actual execution, it depends. The key is, how early the TOP expression can be evaluated in the execution plan, the earlier the better. However this is decided by the database when the optimal execution plan is created.

A good rule of thumb is, never fetch more than needed. If you need just first 30 rows then use TOP to return those. However, you haven't specified any ordering so the top 30 records would be 'random' ones.

Another rule of thumb is that you should take care if indexing. Indexes are the key to fast data access and for good execution plans. Good indexing will help you to build fast statements.

So how would you know what plan the database has for fetching the data. If you use Sql Server Management Studio, you can utilize the execution plan explanation, either estimated or actual. By interpreting the plan you'll see how the statement will be executed and you're also able to pinpoint the problems in executions, for example missing indices etc.

For more info, see Display the Estimated Execution Plan[^]

如果您只想获取有关时间的30条记录，请阅读sql分页。



教程25：有效地分析大量数据 [ ^ ]

通过查询结果进行分页 [ ^ ]



您可以使用 LinqToSql [ ^ ]获得所需的记录部分。

使用LINQ进行有效寻呼（示例代码） [ ^ ]

If you want to get only 30 records at time, please read about sql pagination.

Tutorial 25: Efficiently Paging Through Large Amounts of Data[^]
Paging Through a Query Result[^]

You can use LinqToSql[^] to get desired portion of records.
Effective Paging Using LINQ (Sample Code)[^]

这篇关于找到30个第一个结果后，如何强制SQL进行breack搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！