Pierwsze słowa

Możesz bezpiecznie zignorować poniższe sekcje (i włącznie) DOŁĄCZENIA: Rozpoczęcie, jeśli chcesz po prostu złamać kod. Tło i wyniki po prostu służyć jako kontekst. Proszę spojrzeć na historię edycji przed 06.10.2015, jeśli chcesz zobaczyć, jak początkowo wyglądał kod.

Cel

Ostatecznie chcę obliczyć interpolowane współrzędne GPS dla nadajnika ( Xlub Xmit) na podstawie znaczników DateTime dostępnych danych GPS w tabeli, SecondTablektóre bezpośrednio towarzyszą obserwacji w tabeli FirstTable.

Moja najbliższa cel do osiągnięcia ostatecznego celu jest dowiedzieć się, jak najlepiej dołączyć FirstTabledo SecondTabledostać te flankujące punktach czasowych. Później mogę wykorzystać te informacje, aby obliczyć pośrednie współrzędne GPS przy założeniu liniowego dopasowania wzdłuż układu współrzędnych prostokątnych (wymyślne słowa, które mówią, że nie obchodzi mnie, że Ziemia jest kulą w tej skali).

pytania

1. Czy istnieje bardziej skuteczny sposób generowania najbliższych znaczników czasu przed i po?
   • Naprawione przeze mnie, po prostu chwytając „po”, a następnie zdobywając „przed” tylko w związku z „po”.
2. Czy istnieje bardziej intuicyjny sposób, który nie wymaga (A<>B OR A=B)struktury.
   • Byrdzeye zapewnił podstawowe alternatywy, jednak moje doświadczenie w „świecie rzeczywistym” nie zgadzało się z tym, że wszystkie 4 jego strategie łączenia działały tak samo. Ale w pełni dziękuję mu za zajęcie się alternatywnymi stylami łączenia.
3. Wszelkie inne myśli, sztuczki i porady, które możesz mieć.
   • Thusfar zarówno byrdzeye i Phrancis były bardzo pomocne w tym względzie. Przekonałem się, że rada Phrancis została doskonale rozłożona i zapewniła pomoc na krytycznym etapie, więc dam mu przewagę.

Będę wdzięczny za wszelką dodatkową pomoc, jaką mogę otrzymać w związku z pytaniem 3. Punkty kulminacyjne odzwierciedlają, kto moim zdaniem najbardziej pomógł w danym pytaniu.

Definicje tabel

Pół-wizualna reprezentacja

Pierwsza tabela

Fields
  RecTStamp | DateTime  --can contain milliseconds via VBA code (see Ref 1) 
  ReceivID  | LONG
  XmitID    | TEXT(25)
Keys and Indices
  PK_DT     | Primary, Unique, No Null, Compound
    XmitID    | ASC
    RecTStamp | ASC
    ReceivID  | ASC
  UK_DRX    | Unique, No Null, Compound
    RecTStamp | ASC
    ReceivID  | ASC
    XmitID    | ASC

SecondTable

Fields
  X_ID      | LONG AUTONUMBER -- seeded after main table has been created and already sorted on the primary key
  XTStamp   | DateTime --will not contain partial seconds
  Latitude  | Double   --these are in decimal degrees, not degrees/minutes/seconds
  Longitude | Double   --this way straight decimal math can be performed
Keys and Indices
  PK_D      | Primary, Unique, No Null, Simple
    XTStamp   | ASC
  UIDX_ID   | Unique, No Null, Simple
    X_ID      | ASC

Tabela ReceiverDetails

Fields
  ReceivID                      | LONG
  Receiver_Location_Description | TEXT -- NULL OK
  Beginning                     | DateTime --no partial seconds
  Ending                        | DateTime --no partial seconds
  Lat                           | DOUBLE
  Lon                           | DOUBLE
Keys and Indicies
  PK_RID  | Primary, Unique, No Null, Simple
    ReceivID | ASC

Tabela ValidXmitters

Field (and primary key)
  XmitID    | TEXT(25) -- primary, unique, no null, simple

Skrzypce SQL ...

... abyś mógł bawić się definicjami tabel i kodem To pytanie dotyczy MSAccess, ale jak zauważył Phrancis, nie ma stylu skrzypowania SQL dla Access. Powinieneś więc móc przejść tutaj, aby zobaczyć moje definicje tabel i kod oparty na odpowiedzi Phrancis :
http://sqlfiddle.com/#!6/e9942/4 (link zewnętrzny)

DOŁĄCZENIA: Zaczynam

Moja obecna strategia „wewnętrznej odwagi” DOŁĄCZ

Najpierw utwórz FirstTable_rekeyed z kolejnością kolumn i złożonym kluczem podstawowym, (RecTStamp, ReceivID, XmitID)wszystkie indeksowane / sortowane ASC. Utworzyłem również indeksy dla każdej kolumny osobno. Następnie wypełnij go w ten sposób.

INSERT INTO FirstTable_rekeyed (RecTStamp, ReceivID, XmitID)
  SELECT DISTINCT ROW RecTStamp, ReceivID, XmitID
  FROM FirstTable
  WHERE XmitID IN (SELECT XmitID from ValidXmitters)
  ORDER BY RecTStamp, ReceivID, XmitID;

Powyższe zapytanie wypełnia nową tabelę 153006 rekordami i zwraca w ciągu około 10 sekund.

Poniższa czynność kończy się w ciągu sekundy lub dwóch, gdy cała metoda jest opakowana w „WYBIERZ Licznik (*) OD (...)”, gdy używana jest metoda podzapytania TOP 1

SELECT 
    ReceiverRecord.RecTStamp, 
    ReceiverRecord.ReceivID, 
    ReceiverRecord.XmitID,
    (SELECT TOP 1 XmitGPS.X_ID FROM SecondTable as XmitGPS WHERE ReceiverRecord.RecTStamp < XmitGPS.XTStamp ORDER BY XmitGPS.X_ID) AS AfterXmit_ID
    FROM FirstTable_rekeyed AS ReceiverRecord
    -- INNER JOIN SecondTable AS XmitGPS ON (ReceiverRecord.RecTStamp < XmitGPS.XTStamp)
         GROUP BY RecTStamp, ReceivID, XmitID;
-- No separate join needed for the Top 1 method, but it would be required for the other methods. 
-- Additionally no restriction of the returned set is needed if I create the _rekeyed table.
-- May not need GROUP BY either. Could try ORDER BY.
-- The three AfterXmit_ID alternatives below take longer than 3 minutes to complete (or do not ever complete).
  -- FIRST(XmitGPS.X_ID)
  -- MIN(XmitGPS.X_ID)
  -- MIN(SWITCH(XmitGPS.XTStamp > ReceiverRecord.RecTStamp, XmitGPS.X_ID, Null))

Poprzednie zapytanie DOŁĄCZ „wewnętrzne wnętrzności”

Po pierwsze (szybki ... ale niewystarczająco dobry)

SELECT 
  A.RecTStamp,
  A.ReceivID,
  A.XmitID,
  MAX(IIF(B.XTStamp<= A.RecTStamp,B.XTStamp,Null)) as BeforeXTStamp,
  MIN(IIF(B.XTStamp > A.RecTStamp,B.XTStamp,Null)) as AfterXTStamp
FROM FirstTable as A
INNER JOIN SecondTable as B ON 
  (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)
GROUP BY A.RecTStamp, A.ReceivID, A.XmitID
  -- alternative for BeforeXTStamp MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp)
  -- alternatives for AfterXTStamp (see "Aside" note below)
  -- 1.0/(MAX(1.0/(-(B.XTStamp>A.RecTStamp)*B.XTStamp)))
  -- -1.0/(MIN(1.0/((B.XTStamp>A.RecTStamp)*B.XTStamp)))

Drugi (wolniejszy)

SELECT
  A.RecTStamp, AbyB1.XTStamp AS BeforeXTStamp, AbyB2.XTStamp AS AfterXTStamp
FROM (FirstTable AS A INNER JOIN 
  (select top 1 B1.XTStamp, A1.RecTStamp 
   from SecondTable as B1, FirstTable as A1
   where B1.XTStamp<=A1.RecTStamp
   order by B1.XTStamp DESC) AS AbyB1 --MAX (time points before)
ON A.RecTStamp = AbyB1.RecTStamp) INNER JOIN 
  (select top 1 B2.XTStamp, A2.RecTStamp 
   from SecondTable as B2, FirstTable as A2
   where B2.XTStamp>A2.RecTStamp
   order by B2.XTStamp ASC) AS AbyB2 --MIN (time points after)
ON A.RecTStamp = AbyB2.RecTStamp; 

tło

Mam tabelę telemetryczną (alias jako A) zawierającą prawie 1 milion wpisów ze złożonym kluczem podstawowym opartym na DateTimepieczęci, identyfikatorze nadajnika i identyfikatorze urządzenia rejestrującego. Ze względu na okoliczności, na które nie mam wpływu, moim językiem SQL jest standardowy Jet DB w Microsoft Access (użytkownicy będą używać wersji 2007 i późniejszych). Tylko około 200 000 tych wpisów jest istotnych dla zapytania z powodu ID nadajnika.

Istnieje druga tabela telemetryczna (alias B), która obejmuje około 50 000 pozycji z jednym DateTimekluczem podstawowym

W pierwszym kroku skupiłem się na znalezieniu najbliższych znaczników czasowych znaczków w pierwszym stole z drugiego stołu.

DOŁĄCZ wyniki

Dziwactwa, które odkryłem ...

... po drodze podczas debugowania

To naprawdę dziwne pisać JOINlogikę, FROM FirstTable as A INNER JOIN SecondTable as B ON (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)która, jak zauważył @byrdzeye w komentarzu (który od tego czasu zniknął), jest formą połączenia krzyżowego. Należy zauważyć, że zastąpienie LEFT OUTER JOINprzez INNER JOINw powyższym kodzie pojawia się nie mieć wpływu na ilość lub tożsamości linii zwróconych. Nie mogę też pominąć klauzuli ON ani powiedzieć ON (1=1). Po prostu użycie przecinka do połączenia (zamiast INNERlub LEFT OUTER JOIN) powoduje, że Count(select * from A) * Count(select * from B)wiersze są zwracane w tym zapytaniu, a nie tylko jeden wiersz na tabelę A, gdy JOINzwraca jawnie (A <> B LUB A = B) . To oczywiście nie jest odpowiednie. FIRSTnie wydaje się być dostępny do użycia, biorąc pod uwagę złożony klucz podstawowy.

Drugi JOINstyl, choć prawdopodobnie bardziej czytelny, cierpi z powodu spowolnienia. Może to być spowodowane tym, że dodatkowe dwa wewnętrzne JOINsą wymagane w stosunku do większego stołu, a także dwa CROSS JOINw obu opcjach.

Poza tym: Zastąpienie IIFklauzuli słowem MIN/ MAXwydaje się zwracać tę samą liczbę pozycji.
MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp)
działa dla MAXznacznika czasu „Przed” ( ), ale nie działa bezpośrednio dla „Po” ( MIN) w następujący sposób:
MIN(-(B.XTStamp>A.RecTStamp)*B.XTStamp)
ponieważ minimum wynosi zawsze 0 dla FALSEwarunku. Ta wartość 0 jest mniejsza niż jakakolwiek epoka po epoce DOUBLE(której DateTimepole jest podzbiorem w programie Access i na którą to obliczenie przekształca pole). Metody IIFi MIN/ MAXAlternatywy zaproponowane dla wartości AfterXTStamp działają, ponieważ dzielenie przez zero ( FALSE) generuje wartości zerowe, które funkcje agregujące MIN i MAX pomijają.

Następne kroki

Idąc dalej, chciałbym znaleźć znaczniki czasu w drugiej tabeli, które bezpośrednio flankują znaczniki czasu w pierwszej tabeli i wykonać liniową interpolację wartości danych z drugiej tabeli na podstawie odległości czasu od tych punktów (tj. Jeśli znacznik czasu od pierwsza tabela to 25% drogi między „przed” a „po”, chciałbym, aby 25% obliczonej wartości pochodziło z danych wartości 2. tabeli związanych z punktem „po” i 75% z „przed” ). Używając zmienionego typu łączenia jako części wnętrzności i po sugerowanych odpowiedziach poniżej tworzę ...

    SELECT
        AvgGPS.XmitID,
        StrDateIso8601Msec(AvgGPS.RecTStamp) AS RecTStamp_ms,
        -- StrDateIso8601MSec is a VBA function returning a TEXT string in yyyy-mm-dd hh:nn:ss.lll format
        AvgGPS.ReceivID,
        RD.Receiver_Location_Description,
        RD.Lat AS Receiver_Lat,
        RD.Lon AS Receiver_Lon,
        AvgGPS.Before_Lat * (1 - AvgGPS.AfterWeight) + AvgGPS.After_Lat * AvgGPS.AfterWeight AS Xmit_Lat,
        AvgGPS.Before_Lon * (1 - AvgGPS.AfterWeight) + AvgGPS.After_Lon * AvgGPS.AfterWeight AS Xmit_Lon,
        AvgGPS.RecTStamp AS RecTStamp_basic
    FROM ( SELECT 
        AfterTimestampID.RecTStamp,
        AfterTimestampID.XmitID,
        AfterTimestampID.ReceivID,
        GPSBefore.BeforeXTStamp, 
        GPSBefore.Latitude AS Before_Lat, 
        GPSBefore.Longitude AS Before_Lon,
        GPSAfter.AfterXTStamp, 
        GPSAfter.Latitude AS After_Lat, 
        GPSAfter.Longitude AS After_Lon,
        ( (AfterTimestampID.RecTStamp - GPSBefore.XTStamp) / (GPSAfter.XTStamp - GPSBefore.XTStamp) ) AS AfterWeight
        FROM (
            (SELECT 
                ReceiverRecord.RecTStamp, 
                ReceiverRecord.ReceivID, 
                ReceiverRecord.XmitID,
               (SELECT TOP 1 XmitGPS.X_ID FROM SecondTable as XmitGPS WHERE ReceiverRecord.RecTStamp < XmitGPS.XTStamp ORDER BY XmitGPS.X_ID) AS AfterXmit_ID
             FROM FirstTable AS ReceiverRecord 
             -- WHERE ReceiverRecord.XmitID IN (select XmitID from ValidXmitters)
             GROUP BY RecTStamp, ReceivID, XmitID
            ) AS AfterTimestampID INNER JOIN SecondTable AS GPSAfter ON AfterTimestampID.AfterXmit_ID = GPSAfter.X_ID
        ) INNER JOIN SecondTable AS GPSBefore ON AfterTimestampID.AfterXmit_ID = GPSBefore.X_ID + 1
    ) AS AvgGPS INNER JOIN ReceiverDetails AS RD ON (AvgGPS.ReceivID = RD.ReceivID) AND (AvgGPS.RecTStamp BETWEEN RD.Beginning AND RD.Ending)
    ORDER BY AvgGPS.RecTStamp, AvgGPS.ReceivID;

... która zwraca 152928 rekordów, co odpowiada (przynajmniej w przybliżeniu) ostatecznej liczbie oczekiwanych rekordów. Czas pracy to prawdopodobnie 5-10 minut na moim i7-4790, 16 GB pamięci RAM, bez dysku SSD i systemu Win 8.1 Pro.

Odniesienie 1: MS Access może obsłużyć milisekundowe wartości czasu - naprawdę i towarzyszący mu plik źródłowy [08080011.txt]

Muszę najpierw pochwalić twoją odwagę, aby zrobić coś takiego z Access DB, co z mojego doświadczenia jest bardzo trudne do zrobienia czegoś podobnego do SQL. W każdym razie przejdę do recenzji.

Najpierw dołącz

Twoje IIFpozycje polowe mogą skorzystać z systemu switch zamiast. Wydaje się, że czasami tak jest, szczególnie w przypadku SQL, że SWITCH(bardziej znany jak CASEw typowym SQL) jest dość szybki, gdy dokonuje prostych porównań w ciele SELECT. Składnia w twoim przypadku byłaby prawie identyczna, chociaż przełącznik można rozszerzyć, aby objąć dużą porcję porównań w jednym polu. Coś do rozważenia.

  SWITCH (
    expr1, val1,
    expr2, val2,
    val3        -- default value or "else"
  )

Przełącznik może również pomóc w zwiększeniu czytelności w większych instrukcjach. W kontekście:

  MAX(SWITCH(B.XTStamp <= A.RecTStamp,B.XTStamp,Null)) as BeforeXTStamp,
  --alternatively MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp) as BeforeXTStamp,
  MIN(SWITCH(B.XTStamp>A.RecTStamp,B.XTStamp,Null)) as AfterXTStamp

Jeśli chodzi o samo dołączenie, myślę, że (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)jest tak dobre, jak to tylko możliwe, biorąc pod uwagę to, co próbujesz zrobić. Nie jest tak szybko, ale nie spodziewałbym się, że tak też będzie.

Drugie dołączenie

Powiedziałeś, że to wolniej. Jest również mniej czytelny z punktu widzenia kodu. Biorąc pod uwagę równie zadowalające zestawy wyników od 1 do 2, powiedziałbym, że idź do 1. Przynajmniej oczywiste jest, co próbujesz zrobić w ten sposób. Podkwerendy często nie są bardzo szybkie (choć często nieuniknione), szczególnie w tym przypadku dodajesz dodatkowe łączenie w każdym, co z pewnością komplikuje plan wykonania.

Jedna uwaga, widziałem, że użyłeś starej składni złączenia ANSI-89. Najlepiej tego uniknąć, wydajność będzie taka sama lub lepsza dzięki bardziej nowoczesnej składni łączenia, a są one mniej dwuznaczne lub łatwiejsze do odczytania, trudniejsze do popełnienia błędów.

FROM (FirstTable AS A INNER JOIN 
  (select top 1 B1.XTStamp, A1.RecTStamp 
   from SecondTable as B1
   inner join FirstTable as A1
     on B1.XTStamp <= A1.RecTStamp
   order by B1.XTStamp DESC) AS AbyB1 --MAX (time points before)

Nazywanie rzeczy

Myślę, że sposób, w jaki nazywane są twoje rzeczy, jest w najlepszym razie nieprzydatny, aw najgorszym tajemniczy. A, B, A1, B1itp. jako aliasy tabel, które moim zdaniem mogłyby być lepsze. Myślę też, że nazwy pól nie są zbyt dobre, ale zdaję sobie sprawę, że możesz nie mieć nad tym kontroli. Po prostu szybko zacytuję Kod Bezkodowy na temat nazywania rzeczy i zostawię to w tym miejscu ...

„Invective!” - odpowiedziała kapłanka. „Czasownik wymawiający rzeczowniki!”

Zapytanie „Następne kroki”

Nie mogłem zrozumieć, w jaki sposób został napisany, musiałem zabrać go do edytora tekstu i wprowadzić kilka zmian stylu, aby był bardziej czytelny. Wiem, że edytor SQL Access jest niezgrabny, więc zwykle piszę zapytania w dobrym edytorze, takim jak Notepad ++ lub Sublime Text. Niektóre zmiany stylistyczne, które zastosowałem, aby były bardziej czytelne:

• Wcięcie 4 spacje zamiast 2 spacji
• Przestrzenie wokół operatorów matematycznych i porównawczych
• Bardziej naturalne umiejscowienie nawiasów klamrowych i wcięcia (poszedłem z nawiasami klamrowymi w stylu Java, ale może również być w stylu C, według twoich preferencji)

Jak się okazuje, jest to bardzo skomplikowane zapytanie. Aby to zrozumieć, muszę zacząć od najgłębszego zapytania - IDzestawu danych, który, jak rozumiem, jest taki sam jak Twoje pierwsze dołączenie. Zwraca identyfikatory i znaczniki czasu urządzeń, w których znaczniki czasu przed / po są najbliższe, w podzbiorze urządzeń, którymi jesteś zainteresowany. Zamiast IDtego wywołać to ClosestTimestampID.

Twoje Detdołączenie jest używane tylko raz:

Przez resztę czasu łączy się tylko z wartościami, które już masz ClosestTimestampID. Zamiast tego powinniśmy móc to zrobić:

    ) AS ClosestTimestampID
    INNER JOIN SecondTable AS TL1 
        ON ClosestTimestampID.BeforeXTStamp = TL1.XTStamp) 
    INNER JOIN SecondTable AS TL2 
        ON ClosestTimestampID.AfterXTStamp = TL2.XTStamp
    WHERE ClosestTimestampID.XmitID IN (<limited subset S>)

Może nie będzie to ogromny wzrost wydajności, ale wszystko, co możemy zrobić, aby pomóc biednemu optymalizatorowi Jet DB, pomoże!

Nie mogę oprzeć się wrażeniu, że obliczenia / algorytm BeforeWeighti AfterWeightktórych używasz do interpolacji można zrobić lepiej, ale niestety nie jestem bardzo dobry z nich.

Jedną z sugestii, aby uniknąć awarii (choć nie jest to idealne w zależności od aplikacji), byłoby rozbicie zagnieżdżonych podzapytań na własne tabele i zaktualizowanie ich w razie potrzeby. Nie jestem pewien, jak często potrzebujesz odświeżać dane źródłowe, ale jeśli nie jest to zbyt często, możesz pomyśleć o napisaniu kodu VBA, aby zaplanować aktualizację tabel i tabel pochodnych, i po prostu zostaw swoje najbardziej zewnętrzne zapytanie do pobrania z tych tabel zamiast oryginalnego źródła. Tylko myśl, jak powiedziałem, nie jest idealna, ale biorąc pod uwagę narzędzie, możesz nie mieć wyboru.

Wszystko razem:

SELECT
    InGPS.XmitID,
    StrDateIso8601Msec(InGPS.RecTStamp) AS RecTStamp_ms,
       -- StrDateIso8601MSec is a VBA function returning a TEXT string in yyyy-mm-dd hh:nn:ss.lll format
    InGPS.ReceivID,
    RD.Receiver_Location_Description,
    RD.Lat AS Receiver_Lat,
    RD.Lon AS Receiver_Lon,
    InGPS.Before_Lat * InGPS.BeforeWeight + InGPS.After_Lat * InGPS.AfterWeight AS Xmit_Lat,
    InGPS.Before_Lon * InGPS.BeforeWeight + InGPS.After_Lon * InGPS.AfterWeight AS Xmit_Lon,
    InGPS.RecTStamp AS RecTStamp_basic
FROM (
    SELECT 
        ClosestTimestampID.RecTStamp,
        ClosestTimestampID.XmitID,
        ClosestTimestampID.ReceivID,
        ClosestTimestampID.BeforeXTStamp, 
        TL1.Latitude AS Before_Lat, 
        TL1.Longitude AS Before_Lon,
        (1 - ((ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) 
            / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp))) AS BeforeWeight,
        ClosestTimestampID.AfterXTStamp, 
        TL2.Latitude AS After_Lat, 
        TL2.Longitude AS After_Lon,
        (     (ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) 
            / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp)) AS AfterWeight
        FROM (((
            SELECT 
                A.RecTStamp, 
                A.ReceivID, 
                A.XmitID,
                MAX(SWITCH(B.XTStamp <= A.RecTStamp, B.XTStamp, Null)) AS BeforeXTStamp,
                MIN(SWITCH(B.XTStamp > A.RecTStamp, B.XTStamp, Null)) AS AfterXTStamp
            FROM FirstTable AS A
            INNER JOIN SecondTable AS B 
                ON (A.RecTStamp <> B.XTStamp OR A.RecTStamp = B.XTStamp)
            WHERE A.XmitID IN (<limited subset S>)
            GROUP BY A.RecTStamp, ReceivID, XmitID
        ) AS ClosestTimestampID
        INNER JOIN FirstTable AS Det 
            ON (Det.XmitID = ClosestTimestampID.XmitID) 
            AND (Det.ReceivID = ClosestTimestampID.ReceivID) 
            AND (Det.RecTStamp = ClosestTimestampID.RecTStamp)) 
        INNER JOIN SecondTable AS TL1 
            ON ClosestTimestampID.BeforeXTStamp = TL1.XTStamp) 
        INNER JOIN SecondTable AS TL2 
            ON ClosestTimestampID.AfterXTStamp = TL2.XTStamp
        WHERE Det.XmitID IN (<limited subset S>)
    ) AS InGPS
INNER JOIN ReceiverDetails AS RD 
    ON (InGPS.ReceivID = RD.ReceivID) 
    AND (InGPS.RecTStamp BETWEEN <valid parameters from another table>)
ORDER BY StrDateIso8601Msec(InGPS.RecTStamp), InGPS.ReceivID;

• Dodano dodatkowe atrybuty i warunki filtrowania.
• Każda forma łączenia krzyżowego jest eliminowana przez użycie zagnieżdżonych zapytań min. I maks. To największy wzrost wydajności.
• Minimalne i maksymalne wartości boków zwracane przez najbardziej zagnieżdżone zapytanie wewnętrzne są wartościami klucza podstawowego (skany), które są używane do pobierania dodatkowych atrybutów boków (lat i lon) przy użyciu obliczeń końcowych (dostęp ma odpowiednik zastosowania).
• Atrybuty tabel podstawowych są pobierane i filtrowane w wewnętrznym zapytaniu i powinny poprawić wydajność.
• Nie ma potrzeby formatowania (StrDateIso8601Msec) wartości czasu do sortowania. Korzystanie z wartości datetime z tabeli jest równoważne.

Plany wykonania SQL Server (ponieważ Access nie może tego pokazać)
Bez ostatecznego zamówienia, ponieważ jego kosztowne:
Skanowanie indeksu klastrowanego [ReceiverDetails]. [PK_ReceiverDetails] Koszt 16%
Indeks klastrowany Szukaj [FirstTable]. [PK_FirstTable] Koszt 19%
Indeks klastrowany Szukaj [SecondTable]. [PK_SecondTable] Koszt 16%
Indeks klastrowany Szukaj [SecondTable]. [PK_SecondTable] Koszt 16%
Indeks klastrowy Szukaj [SecondTable]. [PK_SecondTable] [TL2] Koszt 16%
Indeks klastrowany Szukaj [SecondTable]. [PK_SecondTable] [TL1] Koszt 16%

Przy ostatecznym zamówieniu według:
Sortuj Koszt 36%
Skanowanie indeksu klastrowego [ReceiverDetails]. [PK_ReceiverDetails] Koszt 10%
Indeks klastrowany Szukaj [FirstTable]. [PK_FirstTable] Koszt 12%
Indeks klastrowany Szukaj [SecondTable]. [PK_SecondTable] Koszt 10%
Indeks klastrowany Szukaj [SecondTable]. [PK_SecondTable] Koszt 10%
Indeks klastrowany Szukaj [SecondTable]. [PK_SecondTable] [TL2] Koszt 10%
Indeks klastrowany Szukaj [SecondTable] [ PK_SecondTable] [TL1] Koszt 10%

Kod:

select
     ClosestTimestampID.XmitID
    --,StrDateIso8601Msec(InGPS.RecTStamp) AS RecTStamp_ms
    ,ClosestTimestampID.ReceivID
    ,ClosestTimestampID.Receiver_Location_Description
    ,ClosestTimestampID.Lat
    ,ClosestTimestampID.Lon
,[TL1].[Latitude] * (1 - ((ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp))) + [TL2].[Latitude] * ((ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp)) AS Xmit_Lat
,[TL1].[Longitude] * (1 - ((ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp))) + [TL2].[Longitude] * ((ClosestTimestampID.RecTStamp - ClosestTimestampID.BeforeXTStamp) / (ClosestTimestampID.AfterXTStamp - ClosestTimestampID.BeforeXTStamp)) AS Xmit_Lon
    ,ClosestTimestampID.RecTStamp as RecTStamp_basic
from (
        (
            (
                select
                     FirstTable.RecTStamp
                    ,FirstTable.ReceivID
                    ,FirstTable.XmitID
                    ,ReceiverDetails.Receiver_Location_Description
                    ,ReceiverDetails.Lat
                    ,ReceiverDetails.Lon
                    ,(
                        select max(XTStamp) as val
                        from SecondTable
                        where XTStamp <= FirstTable.RecTStamp
                     ) as BeforeXTStamp
                    ,(
                        select min(XTStamp) as val
                        from SecondTable
                        where XTStamp > FirstTable.RecTStamp
                     ) as AfterXTStamp
                from FirstTable
                inner join ReceiverDetails
                on ReceiverDetails.ReceivID = FirstTable.ReceivID
                where FirstTable.RecTStamp between #1/1/1990# and #1/1/2020#
                and FirstTable.XmitID in (100,110)
            ) as ClosestTimestampID
            inner join SecondTable as TL1
            on ClosestTimestampID.BeforeXTStamp = TL1.XTStamp
        )
        inner join SecondTable as TL2
        on ClosestTimestampID.AfterXTStamp = TL2.XTStamp
    )
order by ClosestTimestampID.RecTStamp, ClosestTimestampID.ReceivID;

Testowanie wydajności mojego zapytania względem zapytania zawierającego połączenie krzyżowe.

Pierwsza tabela została załadowana z 13 rekordami, a druga tabela z 1 000 000.
Plany wykonania mojego zapytania niewiele się zmieniły od tego, co zostało opublikowane.
Plany wykonania dla połączenia krzyżowego:
Pętle zagnieżdżone Koszt 81% za pomocą INNER JOIN SecondTable AS B ON (A.RecTStamp <> B.XTStamp OR A.RecTStamp = B.XTStamp
zagnieżdżonych pętli spada do 75%, jeśli używa się CROSS JOIN SecondTable AS B' or ',SecondTable AS B
agregacji strumienia 8%
Skanowanie indeksu [SecondTable] [UK_ID] [B] 6% Bufor
tabeli 5%
Kilka innych Szukanie indeksu klastrowego i szukanie indeksu (podobny do mojego zapytania jak opublikowano) z Kosztem 0%.

Czas realizacji wynosi 0,007 i 8-9 sekund dla mojego zapytania i połączenia krzyżowego.
Porównanie kosztów 0% i 100%.

Załadowałem FirstTable z 50 000 rekordów i jednym rekordem do ReceiverDetails dla warunków przyłączenia i uruchomiłem moje zapytanie.
50,013 zwrócone między 0,9 a 1,0 sekundą.

Uruchomiłem drugie zapytanie z łączeniem krzyżowym i pozwoliłem mu działać przez około 20 minut, zanim go zabiłem.
Jeśli zapytanie o sprzężenie krzyżowe zostanie przefiltrowane, aby zwrócić tylko oryginalną wartość 13, czas wykonania ponownie wynosi 8–9 sekund.
Umiejscowienie warunku filtru było najbardziej wewnętrzne, najbardziej zewnętrzne i oba. Bez różnicy.

Istnieje różnica między tymi dwoma warunkami łączenia na korzyść CROSS JOIN, pierwszy używa predykatu, CROSS JOIN nie:
INNER JOIN SecondTable AS B ON (A.RecTStamp <> B.XTStamp OR A.RecTStamp = B.XTStamp) CROSS JOIN SecondTable AS B

Dodając drugą odpowiedź, nie lepszą niż pierwsza, ale bez zmiany któregokolwiek z przedstawionych wymagań, istnieje kilka sposobów, aby przekonać Access do poddania się i wyglądać na zgryźliwego. „Zmaterializuj” powikłania po trochu, używając „wyzwalaczy”. Tabele dostępu nie mają wyzwalaczy, więc przechwytują i wstrzykują surowe procesy.

--*** Create a table for flank values.
    create table Flank (
         RecTStamp      datetime not null
        ,BeforeXTStamp  datetime null
        ,AfterXTStamp   datetime null
        ,constraint PK_Flank primary key clustered ( RecTStamp asc )
        )

--*** Create a FlankUpdateLoop sub. (create what is missing)
    -- loop until rowcount < 5000 or rowcount = 0
    -- a 5K limit appears to be manageable for Access, especially for the initial population.
    insert into Flank (
         RecTStamp
        ,BeforeXTStamp
        ,AfterXTStamp
        )
    select top 5000 FirstTable.RecTStamp
        ,(
            select max(XTStamp) as val
            from SecondTable
            where XTStamp <= FirstTable.RecTStamp
            ) as BeforeXTStamp
        ,(
            select min(XTStamp) as val
            from SecondTable
            where XTStamp > FirstTable.RecTStamp
            ) as AfterXTStamp
    from FirstTable
    left join Flank
        on FirstTable.RecTStamp = Flank.RecTStamp
    where Flank.RecTStamp is null;

--*** For FirstTable Adds, Changes or Deletes:
    delete from Flank where Flank.RecTStamp = CRUD_RecTStamp
    execute FlankUpdateLoop --See above. This will handle Adds, Changes or Deletes.

--*** For SecondTable Adds, Changes or Deletes:
    --delete from Flank where the old value is immediately before and after the new flank value.
    --They may or may not get be assigned a new value. Let FlankUpdate figure it out.

    --execute deletes for both beforextstamp and afterxtstamp
    --then update flank

    delete *
    from flank
    where beforextstamp between (
                    select min(beforextstamp)
                    from flank
                    where beforextstamp >= '3/16/2009 10:00:46 AM'
                    ) and (
                    select max(beforextstamp)
                    from flank
                    where beforextstamp <= '3/16/2009 10:00:46 AM'
                    );

    delete *
    from flank
    where afterxtstamp between (
                    select min(afterxtstamp)
                    from flank
                    where afterxtstamp >= '3/16/2009 10:00:46 AM'
                    ) and (
                    select max(afterxtstamp)
                    from flank
                    where afterxtstamp <= '3/16/2009 10:00:46 AM'
                    );

    execute FlankUpdateLoop

--*** Final Report Query***--
    --Should execute without issues including 'deferred execution' problem.
    --Add filters as needed.
    select FirstTable.XmitID
        ,FirstTable.ReceivID
        ,ReceiverDetails.Lat
        ,ReceiverDetails.Lon
        ,BeforeTable.Latitude * (1 - ((FirstTable.RecTStamp - BeforeXTStamp) / (AfterXTStamp - BeforeXTStamp))) + AfterTable.Latitude * ((FirstTable.RecTStamp - BeforeXTStamp) / (AfterXTStamp - BeforeXTStamp)) as Xmit_Lat
        ,BeforeTable.Longitude * (1 - ((FirstTable.RecTStamp - BeforeXTStamp) / (AfterXTStamp - BeforeXTStamp))) + AfterTable.Longitude * ((FirstTable.RecTStamp - BeforeXTStamp) / (AfterXTStamp - BeforeXTStamp)) as Xmit_Lon
        ,FirstTable.RecTStamp as RecTStamp_basic
    from (((
        FirstTable
    inner join Flank on FirstTable.RecTStamp = Flank.RecTStamp)
    inner join SecondTable as BeforeTable on Flank.BeforeXTStamp = BeforeTable.XTStamp)
    inner join SecondTable as AfterTable on Flank.AfterXTStamp = AfterTable.XTStamp)
    inner join ReceiverDetails on FirstTable.ReceivID = ReceiverDetails.ReceivID
    order by FirstTable.RecTStamp;