3

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

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

1
  • What exactly is the problem? That the query is mostly heavy on a hash does not mean it is too slow. As in: what runtime do you expect, what get, what is your hardwware? btw., 600 million rows is - ah - not exactly very large. We add that daily to a simulation table.
    – TomTom
    Aug 22, 2013 at 8:04

1 Answer 1

2

Some of the statistics statements in the setup script fail with an error. Some people will give up providing an answer if the setup script errors out. Perhaps it would be better to script statistics using the built-in features of SSMS rather than writing your own script?

The join confuses me because it makes the update non-deterministic. If you'd used MERGE to do this change, it would fail with an error: the join doesn't identify a single row in Pin_Mtg for each row of Pin_Mtg_Suppl. Hence the ANY aggregate after the join. Given the multiple matches, the query processor chooses a MtgId value essentially at random.

I would have expected a join on the primary key. Is there some unenforced logic that means only one row will qualify for the update? Otherwise, the query is just flawed at a fundamental level as it stands. You need to resolve that issue before we can talk about optimizing the details of the query.

Not the answer you're looking for? Browse other questions tagged or ask your own question.