MS Access中本地服务器上的慢SQL代码

I've got a particular SQL statement which takes about 30 seconds to perform, and I'm wondering if anyone can see a problem with it, or where I need additional indexing.

我有一个特定的SQL语句需要大约30秒才能执行,我想知道是否有人可以看到它的问题,或者我需要额外的索引。

The code is on a subform in Access, which shows results dependent on the content of five fields in the master form. There are nearly 5000 records in the table that's being queried. The Access project is stored and run from a terminal server session on the actual SQL server, so I don't think it's a network issue, and there's another form which is very similar that uses the same type of querying...

代码位于Access中的子窗体上,该窗体显示的结果取决于主窗体中五个字段的内容。表中有近5000条记录正在被查询。 Access项目是从实际SQL服务器上的终端服务器会话存储和运行的,所以我不认为这是一个网络问题,并且还有另一种形式非常类似于使用相同类型的查询...

Thanks

PG

SELECT TabDrawer.DrawerName, TabDrawer.DrawerSortCode, TabDrawer.DrawerAccountNo, TabDrawer.DrawerPostCode, QryAllTransactons.TPCChequeNumber, tabdrawer.drawerref
FROM TabDrawer LEFT JOIN QryAllTransactons ON  TabDrawer.DrawerRef=QryAllTransactons.tpcdrawer
WHERE (Forms!FrmSearchCompany!SearchName Is Null 
      Or [drawername] Like Forms!FrmSearchCompany!SearchName & "*") 
      And (Forms!FrmSearchCompany.SearchPostcode Is Null 
      Or [Drawerpostcode] Like Forms!FrmSearchCompany!Searchpostcode & "*") 
      And (Forms!FrmSearchCompany!SearchSortCode Is Null 
      Or [drawersortcode] Like Forms!FrmSearchCompany!Searchsortcode & "*") 
      And (Forms!FrmSearchCompany!Searchaccount Is Null 
      Or [draweraccountno] Like Forms!FrmSearchCompany!Searchaccount & "*") 
      And (Forms!FrmSearchCompany!Searchcheque Is Null 
      Or [tpcchequenumber] Like Forms!FrmSearchCompany!Searchcheque & "*");
    ");

---

EDIT

The Hold up seems to be in the union query that forms the QryAllTransactons query.

保持似乎在组成QryAllTransactons查询的联合查询中。

SELECT 
  "TPC" AS Type, 
  TabTPC.TPCRef, 
  TabTPC.TPCBranch, 
  TabTPC.TPCDate, 
  TabTPC.TPCChequeNumber, 
  TabTPC.TPCChequeValue, 
  TabTPC.TPCFee, 
  TabTPC.TPCAction,
  TabTPC.TPCMember, 
  tabtpc.tpcdrawer,
  TabTPC.TPCUser,
  TabTPC.TPCDiscount,
  tabcustomers.*
FROM 
  TabTPC 
  INNER JOIN TabCustomers ON TabTPC.TPCMember = TabCustomers.CustomerID

UNION ALL 

SELECT 
 "CTP" AS Type, 
  TabCTP.CTPRef, 
  TabCTP.CTPBranch, 
  TabCTP.CTPDate, 
  TabCTP.CTPChequeNumb, 
  TabCTP.CTPAmount, 
  TabCTP.CTPFee, 
  TabCTP.CTPAction,
  TabCTP.CTPMember,
  0 as CTPXXX,
  TabCTP.CTPUser,
  TabCTP.CTPDiscount, 
  TABCUSTOMERS.*
FROM 
  TabCTP  
  INNER JOIN TabCustomers ON Tabctp.ctpMember = TabCustomers.CustomerID;

I've done a fair bit of work with simple union queries, but never had this before...

我用简单的联合查询完成了一些工作,但之前从未有过...

6 个解决方案

#1

Two things. Since this is an Access database with a SQL Server backend, you may find a considerable speed improvement by converting this to a stored proc.

两件事情。由于这是一个带有SQL Server后端的Access数据库,因此您可以通过将其转换为存储过程来获得相当大的速度提升。

Second, do you really need to return all those fields, especially in the tabCustomers table? Never return more fields than you actually intend to use and you will improve performance.

其次,你真的需要返回所有这些字段,特别是在tabCustomers表中吗?永远不要返回比实际打算使用更多的字段,您将提高性能。

#2

At first, try compacting and repairing the .mdb file.

首先,尝试压缩和修复.mdb文件。

Then, simplify your WHERE clause:

然后,简化WHERE子句:

WHERE
  [drawername] Like Nz(Forms!FrmSearchCompany!SearchName, "") & "*"
  And 
  [Drawerpostcode] Like Nz(Forms!FrmSearchCompany!Searchpostcode, "") & "*"
  And 
  [drawersortcode] Like Nz(Forms!FrmSearchCompany!Searchsortcode, "") & "*"
  And 
  [draweraccountno] Like Nz(Forms!FrmSearchCompany!Searchaccount, "") & "*"
  And 
  [tpcchequenumber] Like Nz(Forms!FrmSearchCompany!Searchcheque, "") & "*"

Does it still run slowly?

它仍然运行缓慢吗?

EDIT

As it turned out, the question was not clear in that it is an up-sized Access Database with an SQL Server back end-and an Access Project front-end.

事实证明,问题并不明确,因为它是一个带有SQL Server后端的大型Access数据库和一个Access Project前端。

This sheds a different light on the whole problem.

这对整个问题有不同的看法。

Can you explain in more detail how this whole query is intended to be used?

您能否更详细地解释如何使用整个查询?

If you use it to populate the RecordSource of some Form or Report, I think you will be able to refactor the whole thing like this:

如果您使用它来填充某些表单或报表的RecordSource,我认为您将能够像这样重构整个事情:

• make a view on the SQL server that returns the right data

在返回正确数据的SQL服务器上创建一个视图

• query that view with a SQL server syntax, not with Access syntax

使用SQL Server语法查询该视图,而不是使用Access语法

• let the server sort it out

让服务器对其进行排序

#3

How many rows are in QryAllTransactons?

QryAllTransactons中有多少行?

If your result returns 0 rows then Access may be able to see that immediately and stop, but if it returns even a single row then it needs to pull in the entire resultset of QryAllTransactons so that it can do the join internally. That would be my first guess as to what is happening.

如果你的结果返回0行,那么Access可能会立即看到并停止,但如果它返回一行,那么它需要拉入QryAllTransactons的整个结果集,以便它可以在内部进行连接。这将是我对发生的事情的第一次猜测。

Your best bet it usually to do joins on SQL Server. Try creating a view that does the LEFT OUTER JOIN and query against that.

通常最好在SQL Server上进行连接。尝试创建一个执行LEFT OUTER JOIN的视图并对其进行查询。

Your goal, even when Access is running on the SQL Server itself and minimizes network traffic, is to only send to Access what it absolutely needs. Otherwise a large table will still take up memory, etc.

即使Access在SQL Server本身上运行并最大限度地减少网络流量,您的目标也只是向Access发送它绝对需要的内容。否则一个大表仍会占用内存等。

#4

Have you tried running each of the subqueries in the union? Usually optimizers don't spend much time trying to inspect efficiencies between union elements - each one runs on its own merits.

您是否尝试过运行联合中的每个子查询?通常,优化器不会花费太多时间来检查union元素之间的效率 - 每个元素都依赖于它自身的优点。

Given that fact, you could also put the "IF" logic into the procedural code and run each of the tests in some likely order of discovery, without significant additional overhead from more calls.

鉴于这一事实,您还可以将“IF”逻辑放入过程代码中,并以某种可能的发现顺序运行每个测试,而不会产生更多调用的额外开销。

#5

Get rid of those like operators.

摆脱像运营商那样的人。

In your case you don't need them. Just check if the field starts with a given value which you can achive whith something like this:

在你的情况下,你不需要它们。只需检查该字段是否以给定值开头,您可以执行以下操作:

Left([field], Len(value)) = value

This method applied to your query would look like this (did some reformatting for better readability):

应用于您的查询的此方法将如下所示(为了更好的可读性,进行了一些重新格式化):

SELECT
  TabDrawer.DrawerName, 
  TabDrawer.DrawerSortCode, 
  TabDrawer.DrawerAccountNo, 
  TabDrawer.DrawerPostCode, 
  QryAllTransactons.TPCChequeNumber, 
  TabDrawer.DrawerRef
FROM
  TabDrawer 
  LEFT JOIN QryAllTransactons 
    ON TabDrawer.DrawerRef = QryAllTransactons.TpcDrawer
WHERE 
  (Forms!FrmSearchCompany!SearchName Is Null 
  Or Left([drawername], Len(Forms!FrmSearchCompany!SearchName)) = Forms!FrmSearchCompany!SearchName)
And
  (Forms!FrmSearchCompany.SearchPostcode Is Null 
  Or Left([Drawerpostcode], Len(Forms!FrmSearchCompany!Searchpostcode)) = Forms!FrmSearchCompany!Searchpostcode) 
And 
  (Forms!FrmSearchCompany!SearchSortCode Is Null 
  Or Left([drawersortcode], Len(Forms!FrmSearchCompany!Searchsortcode)) = Forms!FrmSearchCompany!Searchsortcode) 
And 
  (Forms!FrmSearchCompany!Searchaccount Is Null 
  Or Left([draweraccountno], Len(Forms!FrmSearchCompany!Searchaccount)) = Forms!FrmSearchCompany!Searchaccount) 
And 
  (Forms!FrmSearchCompany!Searchcheque Is Null 
  Or Left([tpcchequenumber], Len(Forms!FrmSearchCompany!Searchcheque)) = Forms!FrmSearchCompany!Searchcheque)

Note that you're comparing case sensitive. I'm not totally sure if the like operator in MS-Access is case insensitive. Convert both strings to upper- or lowercase, if needed.

请注意,您正在比较区分大小写。我不完全确定MS-Access中的like运算符是否不区分大小写。如果需要,将两个字符串转换为大写或小写。

#6

When you upsized did you make sure the tables were properly indexed? Indexes will speed queries tremendously if used properly (note they may also slow down inserts/updates/deletes, so choose carefully what to index)

当你升迁时,你确保表格被正确编入索引吗?如果使用得当,索引会极大地加快查询速度(注意它们也可能会减慢插入/更新/删除速度,因此请仔细选择索引内容)