Table Definitions
CREATE TABLE [dbo].[Pin_Mtg] (
[MtgId] bigint NULL,
[CntyCd] char(5) NOT NULL,
[BatchDt] int NOT NULL,
[BatchSeq] int NOT NULL,
[MtgSeq] tinyint NOT NULL,
[PclId] varchar(45) NULL,
[PclSeqNbr] tinyint NULL,
[ChronoNbr] varchar(14) NOT NULL,
[PrimaryCatcd] varchar(2) NOT NULL,
[Deedcattyp] varchar(3) NOT NULL,
[DocTyp] char(2) NULL,
[SaleDt] int NULL,
[RecordingDt] int NULL,
[DocYyDt] smallint NULL,
[DocNbr] varchar(12) NULL,
[RecordingBook] varchar(6) NULL,
[RecordingPage] varchar(6) NULL,
[MtgDt] int NULL,
[MtgrecordingDt] int NULL,
[Mtgamt] numeric(13, 2) NULL,
[MtgDocYyDt] smallint NULL,
[MtgDocNbr] varchar(12) NULL,
[MtgRecordingBook] varchar(6) NULL,
[MtgRecordingPage] varchar(6) NULL)
GO
ALTER TABLE [dbo].[Pin_Mtg]
ADD CONSTRAINT [PK__Pin_Mtg__0BB1A1BC0D1732A2]
PRIMARY KEY CLUSTERED
([CntyCd], [BatchDt], [BatchSeq], [MtgSeq])
GO
CREATE TABLE [dbo].[Pin_Mtg_Suppl] (
[Rowid] bigint IDENTITY NOT NULL,
[MtgId] bigint NULL,
[CntyCd] char(5) NOT NULL,
[BatchDt] int NOT NULL,
[BatchSeq] int NOT NULL,
[MtgSeq] tinyint NOT NULL,
[PclId] varchar(45) NULL,
[PclSeqNbr] tinyint NULL,
[PrimaryCatcd] varchar(2) NULL,
[ChronoNbr] varchar(14) NOT NULL,
[Deedcattyp] varchar(3) NOT NULL,
[DocTyp] char(2) NULL,
[SaleDt] int NULL,
[RecordingDt] int NULL,
[DocYyDt] smallint NULL,
[DocNbr] varchar(12) NULL,
[RecordingBook] varchar(6) NULL,
[RecordingPage] varchar(6) NULL,
[MtgDt] int NULL,
[MtgrecordingDt] int NULL,
[Mtgamt] numeric(13, 2) NULL,
[OrigDocDt] int NULL,
[OrigDocNbr] varchar(12) NULL,
[OrigRecordingBook] varchar(6) NULL,
[OrigRecordingPage] varchar(6) NULL,
[MtgDocYyDt] smallint NULL,
[MtgDocNbr] varchar(12) NULL,
[MtgRecordingBook] varchar(6) NULL,
[MtgRecordingPage] varchar(6) NULL)
GO
ALTER TABLE [dbo].[Pin_Mtg_Suppl]
ADD CONSTRAINT [PK__Pin_Mtg___0BB1A1BCA10F7BC8]
PRIMARY KEY CLUSTERED
([CntyCd], [BatchDt], [BatchSeq], [MtgSeq])
Query
UPDATE s
SET MtgId = m.MtgId
FROM dbo.Pin_Mtg m
INNER JOIN dbo.Pin_Mtg_Suppl s ON s.CntyCd = m.CntyCd
WHERE s.Mtgid IS NULL
--Assignments + Releases + Notice of Defaults
AND (((s.PrimaryCatCd IN ('G', 'H', 'F')
OR s.DocTyp IN ('CD', 'RD'))
AND ((s.MtgRecordingBook <> ''
AND s.MtgRecordingPage <> ''
AND s.MtgRecordingBook = m.MtgRecordingBook
AND s.MtgRecordingPage = m.MtgRecordingPage)
OR (s.MtgDocYYDt <> 0
AND s.MtgDocNbr <> ''
AND s.MtgDocYYDt = m.MtgDocYYDt
AND s.MtgDocNbr = m.MtgDocNbr)
OR (s.OrigDocDt <> 0
AND s.OrigDocNbr <> ''
AND s.OrigDocDt / 10000 = m.MtgDocYYDt
AND s.OrigDocNbr = m.mtgdocnbr)
OR (s.OrigRecordingBook <> ''
AND s.OrigRecordingPage <> ''
AND s.OrigRecordingBook = m.MtgRecordingBook
AND s.OrigRecordingPage = m.MtgRecordingPage)))
--Mechanic liens
OR (s.PrimaryCatCd = 'I'
AND ((s.OrigDocDt <> 0
AND s.OrigDocNbr <> ''
AND s.OrigDocDt / 10000 = m.DocYYDt
AND s.OrigDocNbr = m.docnbr)
OR (s.OrigRecordingBook <> ''
AND s.OrigRecordingPage <> ''
AND s.OrigRecordingBook = m.RecordingBook
AND s.OrigRecordingPage = m.RecordingPage)))
-- Modifications
OR (s.DocTyp IN ('MO', 'CD', 'RD')
AND ((s.OrigDocDt <> 0
AND s.OrigDocNbr <> ''
AND s.OrigDocDt / 10000 = m.MtgDocYYDt
AND s.OrigDocNbr = m.MtgDocNbr)
OR (s.OrigRecordingBook <> ''
AND s.OrigRecordingPage <> ''
AND s.OrigRecordingBook = m.MtgRecordingBook
AND s.OrigRecordingPage = m.MtgRecordingPage))))
OPTION (RECOMPILE)
Execution Plan
I have been thinking on how to efficiently perform this join. Should I add many filtered indexes for every predicate and split the query into many pieces or...? The data in production will have over 600 million records. We can change the whole table design (still working on how to do this efficiently) at this point.
The table statistics and execution plan are available in a zip file (SkyDrive).