存储过程可以提高访问数据库的性能，为什么? [英] does stored procedure enhance the performance in an acess databank,why?

问题描述

哪个可以增强Access中的性能?

Which one enhace the performance in an Access:

• 具有存储过程
• 用VB代码编写查询

• 或在控件属性的数据源中编写查询

• Having stored procedures
• writing our query in VB code

• or writing our query in Datasource of our control property

从逻辑上讲，第一个应该提高性能，但是在这里我读到另一件事.您是什么意思?

logicaly the first one should enhance the performance but here I have read another thing.What do you mean?

您能从技术上向我解释一下还是给我看看参考?

could you please explain me technicaly or show me a reference?

推荐答案

如果您正在谈论MDB文件，则必须使用存储过程"来引用已保存的查询.因为查询计划与查询一起存储，所以保存的查询比内联SQL执行得更快.当您执行内联SQL时，必须将SQL编译成查询计划.

If you're talking about an MDB file, then you must be using "stored procedure" to refer to saved queries. Saved queries can execute more quickly than in-line SQL, because the query plan is stored with the query; when you execute inline SQL, the SQL has to be compiled into a query plan.

如果将查询写入控件的DataSource属性，Access将创建一个隐藏的已保存查询，因此性能应相似.

If you write a query into the DataSource property of a control, Access creates a hidden saved query, so the performance should be similar.

更笼统地说，您是否在问这个问题，是因为您或您的用户发现效果不佳?如果是这样，性能在哪里较差?这些信息可能会帮助我们提出一个改进性能的好机会.

More generally, are you asking this question because you or your users have observed poor performance? If so, where is the performance poor? That information might help us suggest a good opportunity for improving the performance.

例如，假设您的表单是定单表单，并且您有显示客户姓名和地址的文本框.表单的记录源是[Orders]表.名称文本框作为其控制源

For example, suppose your form is an order form, and you have text boxes showing the customer's name and address. The record source of the form is the [Orders] table. The name text box has as its control source

SELECT [CustomerName] FROM [Customers] WHERE [CustomerID] = [CustomerIdTextBox]

类似地，地址文本框的控制源:

Similarly, the control source of the address text box:

SELECT [Address] FROM [Customers] WHERE [CustomerID] = [CustomerIdTextBox]

在这种情况下，您可以通过更改记录源来显着提高性能:

In this case, you'll improve your performance markedly by changing the record source:

SELECT [Orders].*, [Customers].[CustomerName], [Customers].[Address]
FROM [Orders] INNER JOIN [Customers] ON [Orders].[CustomerID] = [Customers].[ID]

然后将名称文本框的控制源更改为[CustomerName]，并将地址文本框的控制源更改为[Address].

Then change the control source for the name text box to [CustomerName] and for the address text box to [Address].

为什么这样更快?在第一个设计中，您有两个查询(或者更确切地说，是对要从[Customers]表中提取的每个项目的查询).在新设计中，您只有一个查询.

Why is this faster? In the first design, you had two queries (or, rather, a query for each item you're pulling from the [Customers] table). In the new design, you have only one query.

另一方面，如果您是出于理论上的问题而询问性能的，那么一种方法可能比另一种方法要快，那么您应该使用最易于维护的方法.如果您无法观察到性能差异，那么使用性能更高的解决方案没有任何好处.

If, on the other hand, you are asking about performance because of a theoretical idea that one approach might be faster than another, then you should use the approach that is simplest to maintain. If you can't observe the difference in performance, there's no benefit to using a more performant solution.

这篇关于存储过程可以提高访问数据库的性能，为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！