如何优化慢查询？ [英] How to optimize a slow query?
问题描述
您好，我是新手创建数据库和编码的。通过研究，我创建了一个有效的数据库。但是，我已经填写了测试数据并发现用于生成报告的一些查询运行缓慢。
每个查询使用1或2个主表。两个表中的一个包含73,000条记录，另一个表包含2,000,000条记录。
直接对这两个表中的数据起作用的查询需要30秒才能执行才能显示73,000条记录。使用此主查询的第二个查询大致花费相同的时间，而第三个查询大约需要一分钟。后两个查询也显示73000条记录。
查询必须返回第一个表中所有73,000条记录的数据，同时满足第二个表中2,000,000条记录的条件。 br />
我正在使用访问2007.由于我缺乏经验，我不知道我是否应该考虑这些时间过多或不过分。这将是前端用户必须等待他们的报告的时间。
我预计通过正常使用需要大约10年的时间来填充数据库中的大量数据。从技术上讲，如果我将数据存档并每隔几年重新开始就应该永远不会那么慢。不过，我想知道我是否以愚蠢的方式做事，如果有更好的方法来完成我需要的东西。
如果这些时间不多，请在这里停下来节省一些阅读时间。提前感谢您的回复。然而，如果那些时候发臭，我会发布勇敢的额外细节。
数据库的目的是让员工记录生产力。他们通过前端输入信息，也可以查看报告等。
第一个主表名为M_Associates_Daysworked with fields（这个有73000条记录）：
Daysworked_ID（autonum），Associate_ID（数字，外键），Work_Date（日期/时间），Hours_worked（数字），覆盖（是/否），然后是两个数字字段注册和救护车。员工每天都有一条记录，Associate_ID和Work_Date组成一个复合键。
第二个主表名为M_Associates_Productivity（这是一个有2个百万条记录）并且有字段：
Associate_ID（外键），Daysworked_ID（外键），Account_Number（文本10个字符）。以下4个字段是员工的是/否数据类型，表示在每个Account_Number上执行的工作类型。
第一个查询（Daily_Prod1）应该计算表M_Associates_Productivity中的帐号对于每个标准并基于此进行计算。这就是SQL：
Hello, I''m new to creating databases and coding in general. Through research I have created a DB that works. However, I have filled it with test data and find that a few queries which are used to generate reports run slowly.
Each Query uses 1 or 2 main tables. One of the two tables holds 73,000 records and the other table holds 2,000,000 records.
A query that works directly on the data found in those two tables takes 30 seconds to execute in order to display 73,000 records. A second query that uses this main query takes roughly the same amount of time, while a third query takes roughly a minute. Both of the latter queries also display the 73000 records.
The queries must return data for all 73,000 records in the first table, while meeting criteria for the 2,000,000 records in the 2nd table.
I am using access 2007. With my lack of experience I do not know if I should consider these kinds of times excessive or not. This would be the time users of the front end would have to wait for their reports.
I anticipate that it would take roughly 10 years to fill the DB with that much data through normal use. So technically, if I archive the data and start fresh every few years it should never get that slow. Still, I would like to know if i''m doing things in a stupid way and if there are better ways to accomplish what I need.
If those times are not excessive, please save yourself some reading time by stopping here. Thank you in advance for your response. If however, those times stink, I shall post additional details for the courageous.
The purpose of the DB is to allow employees to log productivity. They enter information through a frontend, and can also view reports etc.
The first main table is called M_Associates_Daysworked with fields(this one has the 73000 records):
Daysworked_ID(autonum), Associate_ID(number,foreign key), Work_Date(Date/time), Hours_worked(number), Override(yes/no), and then two number fields Registration and Ambulances. There is one record for every day an employee works, and Associate_ID and Work_Date make up a composite key.
The second main table is called M_Associates_Productivity (this is the one with 2 million records) and has fields:
Associate_ID(Foreign key), Daysworked_ID(Foreign key), Account_Number(text 10 characters). The following 4 fields are Yes/No data type for employees to indicate the kind of work performed on each Account_Number.
The first query (Daily_Prod1) is supposed to count account numbers from table M_Associates_Productivity for each criteria and perform calculations based on that. This is the SQL for that:
推荐答案
让我先说出来有多惊讶当我检查你的计数（通常是一个指标，虽然很粗糙，一个成员的海报经验如何）时，我看到它只读了一个帖子。我可以很容易地预料到一个更大的数字，并祝贺你提出了一个非常好的问题。在我看来，在项目早期对您的假设进行此类测试是一个非常好的迹象，即您认真对待这一主题。总是让像我们这样的人回答问题感到满意。
事实上，我觉得你报告的时间太过分了。也就是说，你已经解决问题的那种挑剔的方式，并将其解决了，让我感兴趣的是阅读到底。
我会看看每个依次查询并查看是否有任何注释（为了便于阅读，我可能会重新格式化SQL）：
[ Daily_Prod1 ]
Let me first say how surprised I was, when I checked your post-count (generally an indicator, though rough, of how experienced a poster a member is), to see it read only one post. I could easily have expected a much larger number and congratulate you on a very well laid out question. Such testing of your hypotheses so early on in the project is a very good sign, in my view, that you approach the subject seriously. Always gratifying to people like us answering questions.
In fact, I don''t feel the times you report are at all excessive. That said, the fastidious way that you''ve approached the problem, and laid it out as well, interested me enough to read to the end.
I''ll look at each query in turn and see if there are any comments to make (I may reformat the SQL for purposes of readability) :
[Daily_Prod1]
我只是撇开了这个问题所以请原谅我，如果你已经在你的问题的某个地方回答了它，但你为什么创建了索引你的桌子？
I only skimmed the question so pardon me if you already answered it somewhere in your question but what indexes did you create for your tables?
我没有涵盖那个非常重要的一点兔子。你有这样的好处： - ）
I didn''t cover that very important point Rabbit. It''s good that you have :-)
这篇关于如何优化慢查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
