日期/时间点/间隔的(相关)数据库性能 [英] (relational) database performance for a date/time point/interval

问题描述

所以我正在用Access SQL做一个项目，进展很好.我已经学到了很多有关Access和VBA的知识，这个站点在此过程中很有帮助.

So I am doing a project in Access SQL and it has come along nicely. I have learned a lot about Access and VBA and this site has been helpful in the process.

现在，我面临的问题是性能，由于我对这种SQL工作经验很少，因此我来这里想一想.

Now I am facing a problem which is performance and since I have little experience in this kind of SQL work I come here for some thoughts.

我有一个约20个表的关系数据库，其中包含约100个路段，这些路段代表路线的各个部分. Access数据库本质上是一个地图，在上面我绘制了一些可以动态着色的路线(通过线)-颜色由特定问题确定并从数据库中计算出来.

I have a ~20 table relational database for around 100 sections which represent parts of a route. The Access database is essentially a map on which I drew several routes (via lines) that can be coloured dynamically - the color is determined by the specific question and calculated out of the database.

这是一张可以更好地说明它的图片.您不能单击访问中的线条，因此按钮的颜色和宽度设置为与线条相同，并且可以单击以获取更多信息.

Here is a picture which explains it better. You can not click on lines in access so the buttons are set to be identical in colour and width to the lines and are clickable for more information.

用户可以选择一个日期，并根据询问的问题显示路线的进度.到目前为止，这些问题始终是二进制是或否"(绿色或红色).

The user can chose a date and it will display the progress of the route according to the question asked. Up to now, these questions were always binary "yes, or no" (green or red).

我发现，由于查询的复杂性，我不得不在启动时为每个查询准备一个临时数据库，否则无法顺利滚动日期.

I have found that because of the complexity of the queries I have to pretty much prepare a temporary database for each query at startup, otherwise it is not possible to scroll through dates smoothly.

所以无论如何，这是我的具体问题:

So anyway here is my specific problem:

该路线的每个部分在某个日期可以处于不同的阶段(认为是构造阶段).从阶段0"到完成"

Each section of the route can be in different phases (think construction) at a certain date. From "phase 0" to "done"

将执行代表项目阶段的新行.所有部分大约有8个可能的阶段，这些阶段可能在不同的时间发生-事情是这样-每个部分的顺序不同，并非所有阶段都在所有部分上发生.

A new line is to be implemented which represents phases of a project. There are around 8 possible phases for all sections, which can happen at different times and - here is the thing - in a different order for each section AND not all phases happen on all sections.

我在数据库中拥有的只是每个阶段的开始日期，而不是结束日期.阶段的顺序几乎取决于开始日期的顺序. 每个阶段至少每个阶段只能发生一次，因此是这样. 如您所见，对于这种以性能为中心的程序而言，这是一件卑鄙的事情.

What I have in the database are only starting dates - not ending dates - for each phase. The order of the phases has pretty much be determined by the order of the starting date. At least each phase can only happen once for each section, so there is that. As you can see this is a shitty thing for this kind of performance centric program.

我确定它将涉及一个或几个临时数据库. 我的想法:

I am certain it will involve one or several temporary databases. My ideas:

1. 将所有日期汇总到新表的一行中.由于设置了阶段数，因此每个阶段都有相应的列-如果需要，则在何时开始和在何时结束.现在需要遍历每个循环，并检查用户日期是否落入哪个阶段.所以: "SectionID-阶段1需要阶段1开始阶段1结束....."
   优势:

1. Aggregate all dates into one row of a new table. Since the number of phases is set, there are columns for each phase - if it is needed, when it starts and when it ends. A loop now needs to go through each and check if the user-date falls into which phase. So: "SectionID - phase1needed phase1start phase1end ....."
   Advantage:

• 一个人可以手动确认数据并很好地以辅助形式显示
• 它使数据库保持小规模
  缺点:
• 实际的循环需要经历所有阶段(最坏的情况)才能找到正确的阶段.

• One can confirm the data manually and display it in secondary forms well
• It keeps the database small
  Disadvantage:
• The actual loop needs to go through (At worst) all phases to find the correct one.

计算一个新数据库，该数据库仅是"IdSection-Date-Phase"，并计算一个区间中每个节"和每天"的相位.
优势:

Calculate a new database which is just "IdSection - Date - Phase" and calculate a phase for each Section and EVERY Day in an interval.
Advantage:

• 这会将运行时计算保持为每节一个查询
• 访问应处理大量数据
  缺点:
• 我无法手动检查所有部分的操作是否正确
• 在启动时会花很长时间，就像花很长时间一样
• 该数据库中将需要很多条目

• This keeps the runtime calculations to one query per section
• Access should work with large amounts of data
  Disadvantage:
• I can not manually check if what I did was correct for all sections
• Will take long at startup, like really long
• It will take a lot of entries in that db

现在，我问您希望使用哪一种，或者是否有其他方法? 我无法真正改变自己拥有的数据点.

Now I ask which you would prefer, or even if there is a different method? I can not really change much about the points of data I have.

简而言之，我必须显示不同阶段的时间间隔，并且在数据库中，我只有开始时间点，没有阶段的完整顺序.

In short I have to display intervals of time of different phases and in the database I only have starting points of time, no complete order of the phases.

谢谢您的想法，在这些事情上的任何经验都会有所帮助

Thank you for your thoughts, any experiences in these sort of things will help

推荐答案

如果我对您的理解正确，那么您将获得一系列类似于以下形式的数据:

If I understand you properly, you have a series of data similar to the form:

Section 1, Phase 7, Start Date = 11/07/2012
Section 1, Phase 2, Start Date = 12/14/2012
Section 1, Phase 3, Start Date = 12/28/2012
Section 2, Phase 1, Start Date = 11/04/2012
Section 2, Phase 9, Start Date = 12/30/2012
Section 3, Phase 4, Start Date = 11/19/2012
Section 3, Phase 5, Start Date = 12/06/2012
Section 3, Phase 3, Start Date = 12/11/2012

您想回答一个问题，例如"2012年12月15日每个部分处于哪个阶段?"，对吗?

and you want to answer a question like "What phase is each section in on 12/15/2012?", is that correct?

在这种情况下，答案应类似于以下形式:

The answer in this case should look something like the form:

Section 1, Phase 2
Section 2, Phase 1
Section 3, Phase 3

为此，我假设您有一个名为SECTION_PHASES的表，其中包含以下字段:

In order to do this, I'll assume you have a table called SECTION_PHASES with the following fields:

SECTION    Number
PHASE      Number
START_DATE Date/Time

您需要做的是找出当前输入日期之前发生的每个部分的最大开始日期，因为那是下一个阶段更改之前最近的活动阶段.完成此操作后，您可以将该信息重新添加到主表中，以确定该日期之后的阶段.

What you need to do is figure out the maximum start date for each section that happened before your current input date, because that is the most recently active phase before the next phase change. Once you do that, you can join that information back into your main table to determine what the phase was after that date.

您需要执行一个查询SECTION_MAX_DATES，然后在其SQL视图中具有以下代码:

You need to make one query SECTION_MAX_DATES that then has the following code in its SQL View:

SELECT [SECTION_PHASES].SECTION, Max([SECTION_PHASES].START_DATE) AS target_date
FROM SECTION_PHASES
WHERE [SECTION_PHASES].START_DATE<#12/15/2012#
GROUP BY [SECTION_PHASES].SECTION
ORDER BY [SECTION_PHASES].SECTION;

一旦保存了该查询，就可以将其作为子查询加入到原始表中.现在，进行另一个查询SECTION_PHASE_AT_DATE，其中包括您的原始表和上一个查询，然后在其SQL视图中输入以下代码:

Once you have that query saved, you can join it as a subquery back to your original table. Now, make another query SECTION_PHASE_AT_DATE which includes your original table and the previous query, then enter the following code in its SQL View:

SELECT SECTION_PHASES.SECTION, SECTION_PHASES.PHASE, SECTION_PHASES.START_DATE
FROM SECTION_MAX_DATES INNER JOIN SECTION_PHASES ON (SECTION_MAX_DATES.target_date=SECTION_PHASES.START_DATE) AND (SECTION_MAX_DATES.SECTION=SECTION_PHASES.SECTION)
ORDER BY SECTION_PHASES.SECTION;

如果我正确理解了您的问题，该查询将为您提供所追求的结果.如果我完全了解您，给定阶段的新开始日期表明了新日期之前先前当前的任何阶段的结束，则无需计算结束日期.

That query will give you the result you are after, if I understand your question correctly. There is no need to calculate the end dates if I understand you properly that a new start date for a given phase indicates the end of whatever phase was previously-current prior to the new date.

您仍然需要解决一些极端情况，例如，如果某个节在给定日期之前尚未注册阶段，则会发生什么情况.我还将留给您了解如何在两个查询中的第一个查询的WHERE子句中参数化日期，考虑到已经取得的进步，这对于您来说可能是微不足道的！但是，我认为这是您正在寻找的SQL结构，用于解决问题的数据/计算部分.

You'll still have a few edge cases to work out, like what happens if a section doesn't have a phase registered yet prior to the given date. I'll also leave it to you to figure out how to parameterize the date in the WHERE clause of the 1st of the two queries, which is probably trivial for you given the progress you made already! However, I think this is the SQL structure you were looking for to solve the data/calculation part of your problem.

这篇关于日期/时间点/间隔的(相关)数据库性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！