在TQuery结果集中查找记录的最快方法 [英] Fastest way to locate record in TQuery resultset
问题描述
我想知道最好的(最快的)读取方法是在Tquery SQL语句的结果集中找到一条记录。
I am wondering what the best (read: fastest) way is to locate a record in a resultset of a Tquery SQL statement.
直到现在我使用的是TQuery.Locate,如果我是对的,那是唯一可用于在结果集中搜索的语句。那么我们如何优化呢?
Until now i'm using TQuery.Locate and if i'm right that is the only statement that can be used to search in the resultset. So how can we optimize this?
我有一些想法,但还没有时间在大型数据集上进行比较:
I have a few ideas, but haven't had the time yet to compare them all on large datasets:
假设我们有一个包含以下字段的表:
Let say we have a table with the following fields:
Create Table aTable (
ID int,
Name1 varchar(50),
Name2 varchar(50));
以及以下查询:
SELECT ID, Name1, Name2 from aTable
我们要找到一个按其ID记录到结果集中的记录
We want to locate a record by its ID in the resulting set
- 如果aTable的ID有索引,定位会更快吗?
- 如果在SQL语句中添加按ID排序,定位会更快吗?
对此有何想法?
要澄清其用法:该查询由Reportbuilder Dataview执行,然后通过数据管道(即TQuery.Dataset)使其可用。在自定义报告中,我需要根据更高级别的ID来传递管道。因此,不使用查询不适用于此处。我只是想知道我上面的任何建议是否可以加快速度。
To clarify the use of this: The query is executed by a Reportbuilder Dataview, and then made available through a datapipeline (which is the TQuery.Dataset). In the custom report I need to travel the pipeline based on some higher level ID. So NOT using a query is not applicable here. I'm just wondering if any of my suggestions above would speed things up.
推荐答案
以下一些技巧可能对您有所帮助
Here are some tips that you might find helpful
- 仅在索引列上使用Locate()方法
- 使用 order by 仅在索引列上
- 在打开查询之前使用准备
- 使用DisableControls / EnableControls
- 根据需要使用升序/降序索引(或同时使用两者)
- 尝试尝试快速报告
- Use Locate() method only on indexed columns
- Use "order by" ONLY on indexed columns
- Use prepare before opening query
- Use DisableControls / EnableControls
- Use Ascending / Descending index (or both) depending on your needs
- Give a try to FastReports
如果您对大型数据集拥有主数据/明细,请不要获取所有明细记录-换句话说-不要使用本地主数据明细-数据库引擎会为您提供仅所需的记录。
If you have master / detail on large datasets DO NOT fetch all details record - in other words - don't use local master detail - let the DB engine gives you only the desired records.
这篇关于在TQuery结果集中查找记录的最快方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!