进入一个大查询的许多查询运行速度非常慢。 [英] Many queries feeding into one big query runs very slowly.
问题描述
我有11个表，每个表有一个大数据集。然后，我对11个表（11个查询）中的每一个都有一个查询，使用< =提示条件为相应表中的每一行提取人员ID和数据生效日期。
这会导致数据集减少，显示在提示日期或之前开始的所有记录。对于这11个查询中的每一个，我随后创建了一个新查询，该查询在人员ID上提取最大生效日期分组。这样就产生了一个数据集，我可以使用左连接连接到原始表，为我提供每个人员ID的最新记录，该记录在提示标准之前或之前开始。
我需要这个复杂的设置，因为我需要先将数据集减少到从提示日期（用户输入的日期）或之前开始的日期。
然后我有一个最终查询从所有11个简化数据集中提取数据，导致一个查询每个人ID的每个数据集都有一条记录。
这种3分层方法导致最终查询需要几分钟才能运行，我希望能够解决这个问题。
nayone是否对如何加速有任何建议这个？我尝试索引数据来自的表中的主键，但这根本没有帮助。我在想，也许子查询而不是全新的查询可能会有所不同，但我不熟悉它们，我不清楚为什么将查询合并到一个SQL流中会更快然后有两个单独的查询。
我知道一些VBA，并且可以使用它来加快速度，但这些是使用和/或标准的非常简单的左连接查询。再一次，我不知道如何使用VBA加快速度，但我愿意接受建议。
提前谢谢！
您好，欢迎来到 Bytes
先来几个额外的问题：
是日期字段你的表格是真实的日期/时间字段还是以文本形式存储？
您在查询中使用任何计算的表达式吗？
是否与11个表格中的每个表格相同？
您的数据集有多大？每桌1000个？ 10000？ 100000？
嗨！
首先，对我来说，声音是一个规范化问题。
我当然可以不确定你的数据库是否确定。
所以，看看这里（88.1 KB，61 views）
Hi,
I have 11 tables each with a large data set. I then have have one query for each of the 11 tables (11 queries) that pulls the person ID and the data effective date for each line in the respective tables, using a <= prompt criteria.
This results in a reduced data set showing all of the records that started on or before the prompt date. For each of these 11 queries I then created a new query that pulls the maximum effective date grouping on person ID. This results in a data set that I can tie back to the original table using a left join giving me the newest record for each Person ID, that starts on or before the prompt criteria.
I need this complex setup because I need to first reduce my data set to dates starting on or before the prompt date (which the user types in).
I then have one final query that pulls data from all 11 reduced data sets resulting in one query that has one record from each data set per Person ID.
This 3 tiered approach results in a final query that takes several minutes to run and I would like to get that down.
Does nayone have any suggestions on how to speed this up? I tried indexing the primary keys in the tables that the data comes from but that didn''t help at all. I was thinking that maybe sub queries instead of completely new queries might make a difference but I''m not to familiar with them and I wasn''t clear on why consolidating the queries into one stream of SQL would be faster then having two seperate queries.
I know some VBA and would be fine with using that to speed things up but these are pretty simple left join queries using and/or criteria. Again, I don''t know how VBA could be used to speed that up but I am open to suggestions.
Thanks in advance!
Hi and welcome to Bytes
First a few extra questions:
Is the date field in your tables a "real" date/time field or is it stored as text?
Do you use any calculated expressilns in your queries?
Is it the same date you promt for each of the 11 tables?
How large are your datasets? 1000 per table? 10000? 100000?
Hi !
First of all for me sound as a normalization problem.
Of course I can''t be sure without check your database.
So, take a look here http://bytes.com/topic/access/insigh...ble-structures and, if you decide that your database is a good one from this point of view, inform us.
You wrote:
This results in a data set that I can tie back to the original table using a left join ...
This sound as a cycle and a possible cause for your problem.
As far as I can think that approach is not necessary in a normalized database.
Again, I can''t be sure about this.
I have attached a sample to show what I am talking about (thanks for the quick responses).
Import_Address is one of the 11 tables I have.
I then use the queries to isolate the maximum effective date (that is less then the prompt date) for each assigment number.
Current Address is the last step, which uses the dates which I determined in the Max_effective_date query in a left join to pull the ''current'' address information for each person.
Regarding your questions, my tables are normalized, the data sets range between 20,000 and 75,000 rows per table (11 tables total) and many tables have 20+ fields.
There are some calculated expressions (pulling several last name, first name or nick name if there is one together into a ''full name'' field for example). There are also some that determine monthly rates based on annualized numbers.
Each of the 11 tables has a query that uses the same prompt text (so that all 11 are using the same date and I only have to type it in once). This query pulls the effective dates and assignment numbers for rows that have effective dates before or on the prompt date. These together create a primary key which is then filtered by the next query that pulls the maximum effective date for each assignment number. As seen in the attached.
I think it may pay to condense the 3 queries into one but I''m not sure if that would speed things up and I haven''t had any experience with sub queries.
Thanks for your input.
这篇关于进入一个大查询的许多查询运行速度非常慢。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
