我有一个用户定义的存储过程当调用而不传递显式值时，我只想传递所有位置 (nvarchar(50))，这是表的主键字段：Monitor_Locations（约 850 个条目）

SP 的一部分定义如下（截取）。

ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
     @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
    ,@rangeStart as DateTime = '1970-01-01'
    ,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
DECLARE @loclist as TABLE (
    Location nvarchar(50) PRIMARY KEY
)
IF @Locations is NULL
    INSERT INTO @loclist(Location)
        SELECT Location from Monitor_Locations order by Location
ELSE --irrelevant for this question
    INSERT INTO @loclist(Location)
        SELECT
            ML.Location
        FROM Monitor_Locations as ML join
            tvf_splitstring(@Locations) as ss ON 
                ML.Location=ss.Item OR 
                ML.Location like ss.Item+'[_]%'
        ORDER BY ML.Location;
With Deploys as (
    SELECT
        D.Location,
        MIN(D.Start) as Start,
        MAX(D.[Stop]) as Stop
    FROM
        Deployments as D 
    WHERE 
        D.Stop is not NULL
)

...做一些其他的事情...

为了在将受限站点列表发送到 SP 时提高存储过程的速度，我想将 WHERE 子句替换为

WHERE 
    CASE
        WHEN D.Stop IS NULL THEN 0
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
    END=1

但是 SP 曾经需要 6-8 秒来执行，现在需要 2.5 分钟（对于没有限制列表的调用）。我认为完整列表的每种方式花费的时间大致相同，因为 CASE 的第二个子句应该很快被触发，而第三个子句永远不会被检查。

发生什么了？这段代码：

WHERE 
    CASE
        WHEN D.Stop IS NULL THEN NULL
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --else null
    END is not null

这个计划需要大约 10 分钟的运行时间：

对比这里的WHERE D.Stop is not NULL计划（6s）：

有一次，这个 SP 在这个版本中花费了 1 秒，但是通过更改 SP 然后再变回去，又花了 6 秒。如答案中所述，这可能是由于参数嗅探。

运行时间

我的目标执行时间少于 2 秒，因为这将是 Web 应用程序上频繁执行的 SP，它使用它来填充和限制其他用户选择。基本上，我不希望这是一个明显的瓶颈。初始运行时间大约为 3 分钟，但在添加或更改一些索引后，该时间下降到 6-8 秒范围。

星期一 (2016-08-29)，在进行重大更改之前 没有输入参数的简单 WHERE：5 秒带有 rangeStart 和 rangeEnd 的简单 WHERE：4 秒带有 @Locations 设置为 7 元素 CSV 变量的简单 WHERE CASEd WHERE：最多 10 分钟

重新处理 CLR 函数后（见下面我的回答）星期二（2016 年 8 月 30 日）没有输入参数的简单或 CASEd WHERE 或带有 rangeStart 和 rangeEnd 的简单或 CASEd WHERE：3s 简单或 CASEd WHERE 有 7 个元素 @Locations：0 -1s

将表变量@loclist 迁移到临时表#loclist 后所有测试的 WHEREs/参数：0-1s

在 SQL Server Management Studio (SSMS) 2008 R2 中，我希望将格式不太正确的 CSV 导入到数据库表中。我试图在<database name>-> Tasks->处使用向导Import Data。

与 CSV“标准”的区别

1. 空值由逗号之间的空格表示
2. 一些字段中间有逗号并且没有引号转义
3. 除了标题行（WTF？？），所有行都有一个终端逗号

我最终通过在导入前编辑 CSV 手动解决了这些问题。问题 2 已通过将导致它的 4 个单元格值系列的逗号替换为破折号来解决。第 3 期我通过搜索和替换这些终端逗号来解决。问题 1，这是数字字段的问题，也有搜索和替换, ,-> ,,。

当我的文件最终正确导入时，空值被替换为零，即使我在字段定义中允许空值。

我的基本问题：有没有一种方法可以在 SSMS 导入期间更改CAST/CONVERT操作的代码？我可以访问 SQL 代码的唯一阶段是在创建的表的字段定义中（从Select Source Tables and Views-> Edit Mappings-> Edit SQL- 当且仅当我没有更改Select Source Tables页面上的目标表名称时才可用），但是只是为了CREATE TABLE定义，根本没有解决导入过程。在我看来，我应该能够允许将单字符空格字符串转换为数字时转换为空值（而不是向导失败并声明值超出范围）。此外，空字符串当然应该转换为空值整数，而不是 0。

我可以保存一个 SSIS 包，但我不清楚如何编辑和运行它以实现我想做的事情。有没有我可以编辑的模板文件，以允许将空字符串转换为空整数值（就像这里的东西......）？有没有办法使用部分导入向导来生成 tSQL 代码？我应该使用BULK INSERT语句而不是向导吗？

背景

我正在尝试建立一个“访问”序列，其中如果在基本相同的地方General_Location（ 因此，如果在所有 A(n) 个位置都属于“A”的Location序列中检测到动物，则前 6 次检测将作为访问 1 (@A)，接下来作为访问 2 (@B)，然后作为访问 3 (@D)，然后访问 4 (返回 @A)。
A1, A2, A3, A3, A3, A1, B2, D4, A2
General_Location

由于LAG并且在 SQL Server 2008R2 中不可用（在ing 子句LEAD中也不可用），我正在尝试按照SQL Authority 博客条目中所述的解决方法。UNBOUNDED PRECEDINGPARTITION

我遇到了以下内存问题（更不用说计算时间了）：

WITH s AS (
    SELECT
        RANK() OVER (PARTITION BY det.Technology, det.XmitID ORDER BY DetectDate ASC, ReceiverID ASC) as DetID,
        COALESCE(TA.AnimalID, det.Technology+'-'+cast(da.XmitID AS nvarchar), 'BSVALUE999') as AnimalID,
        det.Technology, det.XmitID, DetectDate, det.location as Location, RL.General_Location as GLocation, ReceiverID
    FROM
        Detections_with_Location as det JOIN
        Receiver_Locations as RL
            ON det.Location=RL.Location LEFT OUTER JOIN
        Tagged_Animal as TA
            ON det.AnimalID=TA.AnimalID
)
INSERT INTO ##ttOrder_det (AnimalID, Technology, XmitID, DD, Location, GLocation, ReceiverID, DetID, PrevDD, BinInc)
    SELECT 
        s1.AnimalID, --was a coalesce
        s1.Technology, s1.XmitID, s1.DetectDate, s1.Location, s1.GLocation, s1.ReceiverID,
        s1.DetID, 
        sLAG.DetectDate,
        CASE WHEN sLAG.DetectDate IS NULL
            THEN 1 
            ELSE CASE WHEN sLAG.GLocation = s1.GLocation
                THEN 0
                ELSE 1
            END
        END AS BinInc
    FROM s as s1
    LEFT OUTER JOIN s AS sLAG ON
        s1.DetID = sLAG.DetID + 1 AND
        s1.AnimalID= sLAG.AnimalID --and s.Technology=sLAG.Technology and s.XmitID=sLAG.XmitID;

正如各种用户（@MartinSmith、@Frisbee）已经提到或暗示的那样，使用AnimalID不是 的完整主键Tagged_Animal，也不是在唯一约束中定义的。但是，表中的行数A.AnimalID=B.AnimalID AND A.TagSN<B.TagSN（当前）为零。为了使这个查询健壮，我必须强制它是唯一的（或者只是从 PK 中删除 TagSN）。

表和索引定义

##ttOrder_det（临时表）

当前在填充表格之前创建了索引；我正在进行测试，在填充临时表后将NONCLUSTERED非索引创建转移到一个位置。UNIQUE

CREATE TABLE ##ttOrder_det (
    AnimalID nvarchar(50) not null,
    Technology varchar(25) not null,
    XmitID int not null,
    DD DateTime not null,
    [Location] [nvarchar](255) NULL,
    [GLocation] nvarchar(255) NULL,
    PrevDD DateTime NULL,
    ReceiverID int not null,
    DetID int NOT NULL,
    BinInc int NULL,
    BinNum int NULL,
CONSTRAINT [PK_ttRDA] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC, [DD] ASC, ReceiverID ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX NIX_F on ##ttOrder_det (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_VTC ON ##ttOrder_det (ReceiverID ASC, AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_TCD ON ##ttOrder_det (AnimalID ASC, DD ASC);
CREATE NONCLUSTERED INDEX NIX_R ON ##ttOrder_det (DetID ASC);
CREATE NONCLUSTERED INDEX NIX_A ON ##ttOrder_det (GLocation ASC);
CREATE NONCLUSTERED INDEX NIX_DD ON ##ttOrder_det (DD, PrevDD);
CREATE UNIQUE INDEX UX_CTR ON ##ttOrder_det (AnimalID ASC, DetID ASC);
CREATE NONCLUSTERED INDEX NIX_Bi ON ##ttOrder_det (BinInc ASC);
CREATE NONCLUSTERED INDEX NIX_CT ON ##ttOrder_det (XmitID ASC, Technology ASC);

标记_动物

CREATE TABLE [dbo].[Tagged_Animal](
    [DateTagged] [datetime] NULL,
    [AnimalID] [nvarchar](50) NOT NULL,
    [TagSN] [nvarchar](50) NOT NULL,
    [XmitID] [int] NULL,
    [Technology] [varchar](25) NULL,
    [Animal_SubType] [nvarchar](50) NULL,
    [Species] [nvarchar](30) NULL,
    [StudyID] [nvarchar](50) NULL,
    [Weight] [float] NULL,
    [Length] [int] NULL,
    [Length_Type] [nvarchar](50) NULL,
    [Date_Released] [datetime] NULL,
    [Release_Location] [nvarchar](50) NULL,
    [Lat] [float] NULL,
    [Lon] [float] NULL,
    [Course_Dist_km] [float] NULL,
    [Sex] [nvarchar](255) NULL,
    [Life_Stage] [nvarchar](255) NULL,
    [Marking_Method] [nvarchar](255) NULL,
    [Tag_Type] [varchar](30) NULL,
    [Notes] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Tagged_Animal] PRIMARY KEY CLUSTERED 
(
    [AnimalID] ASC,
    [TagSN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [I_TF_TagCode] ON [dbo].[Tagged_Animal] 
(
    [XmitID] ASC,
    [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Detections_with_Location

CREATE TABLE [dbo].[Detections_with_Location](
    [AnimalID] [nvarchar](50) NOT NULL,
    [XmitID] [int] NOT NULL,
    [Technology] [varchar](25) NOT NULL,
    [DetectDate] [datetime] NOT NULL,
    [ReceiverID] [int] NOT NULL,
    [Data] [float] NULL,
    [Units] [varchar](50) NULL,
    [Location] [nvarchar](255) NULL,
    [StartD] [datetime] NULL,
    [StopD] [datetime] NULL,
    [fname] [nvarchar](255) NULL,
    [notes] [nvarchar](max) NULL,
 CONSTRAINT [PK_dlwk] PRIMARY KEY CLUSTERED 
(
    [ReceiverID] ASC,
    [Technology] ASC,
    [XmitID] ASC,
    [DetectDate] ASC,
    [AnimalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_VTC] ON [dbo].[Detections_with_Location] 
(
    [ReceiverID] ASC,
    [AnimalID] ASC,
    [XmitID] ASC,
    [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCpi] ON [dbo].[Detections_with_Location] 
(
    [XmitID] ASC,
    [Technology] ASC
)
INCLUDE ( [DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCD] ON [dbo].[Detections_with_Location] 
(
    [AnimalID] ASC,
    [XmitID] ASC,
    [Technology] ASC,
    [DetectDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_F] ON [dbo].[Detections_with_Location] 
(
    [AnimalID] ASC
)
INCLUDE ( [XmitID],
[Technology],
[DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_DSS] ON [dbo].[Detections_with_Location] 
(
    [ReceiverID] ASC,
    [Location] ASC,
    [StartD] ASC,
    [StopD] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Receiver_Locations

CREATE TABLE [dbo].[Receiver_Locations](
    [Region] [nvarchar](50) NULL,
    [Location_Long] [nvarchar](50) NULL,
    [Location] [nvarchar](50) NOT NULL,
    [Lat] [float] NULL,
    [Lon] [float] NULL,
    [Altitude] [float] NULL,
    [Elevation] [float] NULL,
    [RiverKm] [float] NULL,
    [LocationType] [nvarchar](50) NULL,
    [General_Location] [nvarchar](50) NULL,
    [Nearest_Access] [nvarchar](50) NULL,
    [Responsible_Agent] [nvarchar](50) NULL,
    [Agent_Phone] [nvarchar](255) NULL,
    [Agent_Email] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Receiver_Locations] PRIMARY KEY CLUSTERED 
(
    [Location] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

桌子尺寸

Tagged_Animals:
10,000 检测_with_Location: 46+ 百万条目
Receiver_Locations: 800

收到的特定错误

1. 无法为数据库“tempdb”中的对象“dbo.SORT 临时运行存储：140737631617024”分配空间，因为“PRIMARY”文件组已满。通过删除不需要的文件、删除文件组中的对象、向文件组添加其他文件或为文件组中的现有文件设置自动增长来创建磁盘空间。

2. 数据库“tempdb”的事务日志已满。要找出日志中的空间不能被重用的原因，请参阅 sys.databases (tempdb ACTIVE_TRANSACTION) 中的 log_reuse_wait_desc 列

3. 执行批处理时出错。错误消息是：抛出了“System.OutOfMemoryException”类型的异常——（如果s在大约 3300 万条记录后直接选择）。

（估计）初始代码的执行计划摘要

INSERT成本 0%
SEQUENCE成本 0% 但从 9 个不同的子步骤中提取。这些子步骤（具有典型成本）是Index Insert（2%），其次是Sort（8%），然后是Table Spool（2%）。NIX_A有 9% 的成本，Index Insert既没有NIX_TCD也NIX_F没有Sort步骤；Table SpoolforNIX_F是免费的。费用为 10%
。分发流 还有2% 的成本和 1% 的成本。 对于成本，它似乎增加了 95%，其他步骤花费了 13%，所以很明显某处存在一些舍入“错误”，可能主要在 14% 的序列中 。Clustered Index Insert
SortParallelism
SEQUENCEIndex InsertSortTable Spool

注释/参考

基于SQL 权威博客条目
的 LAG/LEAD 实现 另见此 Stackexchange 线程

我的问题

1. 有什么改进的建议吗？

2. 当我加入副本时，我也可以分区s吗？

3. 如果我制作s一个谨慎的临时表并适当地索引它，情况会有所改善吗？

4. 在执行所有插入之后UNIQUE在临时表中创建非索引会更有效吗？我假设必须预先创建（因此）索引以防止违反键约束。UNIQUEPRIMARY KEY

回答我自己的问题之一

4. 是的，是的。进一步优化后
   • 21 分钟用数据填充临时表
   • 1分钟索引

这个过程之前至少进行了 1.5 小时，出错并且没有生成结果表。在我开始摆弄查询逻辑之前，实际上需要 4 个多小时才能出错。

服务器规格：

处理器：Xeon E3-1240 V2 @ 3.4 GHz（4 核/8 线程）
内存：16 GB
分页文件：16 GB on 111 GB SSD（52 GB free）
tempdb + my database on 223 GB SSD（119 GB free）

当前状态

请参阅我发布的解决方案/答案。

第一话

如果您只想破解代码，则可以安全地忽略以下（包括）加入部分：开始。背景和结果仅作为上下文。如果您想查看代码最初的样子，请查看 2015-10-06 之前的编辑历史。

客观的

最终，我想根据表中可用 GPS 数据的日期时间戳计算发射器（X或）的内插 GPS 坐标，这些数据直接位于表中观测值的两侧。XmitSecondTableFirstTable

我实现最终目标的直接目标是弄清楚如何最好地加入FirstTable以SecondTable获得那些侧翼时间点。稍后我可以使用该信息计算中间 GPS 坐标，假设沿等距柱状坐标系进行线性拟合（花言巧语是说我不在乎地球是这个比例的球体）。

问题

1. 有没有更有效的方法来生成最接近的前后时间戳？
   • 由我自己修复，只需抓住“之后”，然后仅获取与“之后”相关的“之前”。
2. 有没有不涉及(A<>B OR A=B)结构的更直观的方式。
   • Byrdzeye提供了基本的替代方案，但是我的“真实世界”体验并不符合他的所有 4 个连接策略都执行相同。但完全归功于他解决了替代连接方式。
3. 您可能有的任何其他想法、技巧和建议。
   • 到目前为止，byrdzeye和Phrancis在这方面都非常有帮助。我发现Phrancis 的建议非常出色，并在关键阶段提供了帮助，所以我会在这里给予他优势。

如果我能就问题 3 获得任何额外帮助，我将不胜感激。 要点反映了我认为在个别问题上对我帮助最大的人。

表定义

半视觉表示

第一桌

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

第二张桌子

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

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

ValidXmitters表

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

SQL小提琴...

...以便您可以使用表定义和代码 这个问题是针对 MSAccess 的，但正如 Phancis 指出的那样，Access 没有 SQL fiddle 样式。所以，您应该可以到这里查看我的表定义和基于Phancis 回答的代码：
http://sqlfiddle.com/#!6/e9942/4（外部链接）

加入：开始

我目前的“内胆”加入策略

首先创建一个 FirstTable_rekeyed 列顺序和复合主键(RecTStamp, ReceivID, XmitID)所有索引/排序ASC。我还在每一列上分别创建了索引。然后像这样填充它。

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;

上面的查询用 153006 条记录填充新表，并在 10 秒左右的时间内返回。

当使用 TOP 1 子查询方法时，当整个方法被包装在“SELECT Count(*) 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_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))

以前的“内胆” JOIN 查询

首先（快......但还不够好）

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)))

第二（较慢）

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; 

背景

我有一个包含不到 100 万个条目的遥测表（别名为 A），其中包含一个基于DateTime标记、发射器 ID 和记录设备 ID 的复合主键。由于无法控制的情况，我的SQL语言是Microsoft Access中的标准Jet DB（用户将使用2007及以后的版本）。由于传输器 ID，这些条目中只有大约 200,000 个与查询相关。

还有第二个遥测表（别名 B），它包含大约 50,000 个条目和一个DateTime主键

对于第一步，我专注于从第二个表中找到最接近第一个表中的时间戳的时间戳。

加入结果

我发现的怪癖......

...在调试过程中

JOIN编写逻辑感觉真的很奇怪FROM FirstTable as A INNER JOIN SecondTable as B ON (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)，就像@byrdzeye在评论中指出的那样（此后消失了）是一种交叉连接形式。请注意，在上面的代码中替换LEFT OUTER JOIN为INNER JOIN似乎对返回的行的数量或标识没有影响。我似乎也不能放弃 ON 子句或 say ON (1=1)。仅使用逗号连接（而不是INNERor LEFT OUTER JOIN）会导致Count(select * from A) * Count(select * from B)此查询中返回行，而不是每个表 A 仅一行，因为 (A<>B OR A=B) 显式JOIN返回。这显然不合适。FIRST在给定复合主键类型的情况下似乎无法使用。

第二种JOIN风格虽然可以说更易读，但速度较慢。JOIN这可能是因为针对较大的表以及CROSS JOIN在两个选项中找到的两个 s需要额外的两个 inner s。

旁白：用/替换该IIF子句似乎会返回相同数量的条目。 适用于“之前”( ) 时间戳，但不直接适用于“之后”( )，如下所示： 因为条件的最小值始终为 0 。此 0 小于任何后纪元（字段是 Access 中的子集，并且此计算将字段转换为）。和/方法 为 AfterXTStamp 值建议的替代方案之所以有效，是因为除以零 ( ) 会生成空值，聚合函数 MIN 和 MAX 会跳过这些空值。MINMAX
MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp)
MAXMIN
MIN(-(B.XTStamp>A.RecTStamp)*B.XTStamp)
FALSEDOUBLEDateTimeIIFMINMAXFALSE

下一步

更进一步，我希望在第二个表中找到直接位于第一个表中时间戳两侧的时间戳，并根据到这些点的时间距离对第二个表中的数据值进行线性插值（即如果时间戳来自第一个表是“之前”和“之后”之间的 25%，我希望计算值的 25% 来自与“之后”点关联的第二个表值数据，而 75% 来自“之前” ). 使用修改后的连接类型作为内部胆量的一部分，并在下面的建议答案之后产生......

    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;

...返回 152928 条记录，符合（至少大约）预期记录的最终数量。在我的 i7-4790、16GB RAM、无 SSD、Win 8.1 Pro 系统上运行时间可能是 5-10 分钟。

参考资料1：MS Access Can Handle Millisecond Time Values--真正和随附的源文件[08080011.txt]