Блог

У меня есть такая таблица, в которой более 7 миллионов записей:

 CREATE TABLE [dbo].[Test]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UUID] [nvarchar](100) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [AddrLine1] [nvarchar](100) NULL,
    [AddrLine2] [nvarchar](100) NULL,
    [City] [nvarchar](50) NULL,
    [Prov] [nvarchar](10) NULL,
    [Postal] [nvarchar](10) NULL,
    [DateAdded] [datetime] NULL,

    CONSTRAINT [PK_Test] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 

Теперь система ежедневно во второй половине дня выполняет следующий запрос select. Самое забавное, что иногда один и тот же запрос выполняется так медленно, что занимает около 4 минут. Во второй или в другие раз тот же запрос выполняется довольно быстро, что занимает меньше секунды.

Запрос:

 WITH testquery AS 
(
    SELECT TOP 1 
        'Matched' as location,Firstname, LastName, 
        AddrLine1, AddrLine2, City,  Prov, Postal 
    FROM 
        [Test] 
    WHERE 
        UUID = 'BLABLABLABLABLABLABLABLABLA' 
    ORDER BY 
        DateAdded DESC
),
defaults AS 
(
    SELECT
        'Rejected' AS location, NULL AS Firstname, NULL AS LastName, 
        NULL AS AddrLine1, NULL AS AddrLine2, NULL AS City, NULL AS Prov, 
        NULL AS Postal 
)
SELECT * 
FROM testquery

UNION ALL

SELECT * 
FROM defaults
WHERE NOT EXISTS (SELECT * FROM testquery);
 

Кто-нибудь может помочь, пожалуйста?

Примечания:

• У меня есть сервис, который ежедневно по утрам добавляет в таблицу около 1000 новых записей.
• [avg_fragmentation_in_percent] равно 0,01
• UUID может быть продублирован, если у меня один и тот же человек с разными адресами.
• Таблица не используется одновременно где-то еще.
• База данных не занята другими запросами одновременно. Я проверил с помощью «sys.dm_exec_requests»

Вам нужен хороший индекс, чтобы эффективно обслуживать этот запрос.

Вы говорите, что не можете создать его из-за повторяющихся ключевых ошибок: нет необходимости в уникальности индекса.

Таким образом, тот, который вы ищете, будет зависеть от того, какие другие запросы вы выполняете, но для этого запроса будет достаточно следующего:

 CREATE NONCLUSTERED INDEX IX_Test_UuidDate ON
    Test (UUID ASC, DateAdded DESC)
    INCLUDE (Firstname, LastName, AddrLine1, AddrLine2, City, Prov, Postal)

GO
 

Кроме того, нет необходимости запрашивать таблицу дважды.

Начните с VALUES конструктора фиктивной таблицы, чтобы у вас всегда была строка, затем LEFT JOIN таблица и используйте CASE ее для решения проблемы отсутствия строки.

 WITH testquery AS 
(
    SELECT TOP 1 
        *
    FROM 
        [Test] 
    WHERE 
        UUID = 'BLABLABLABLABLABLABLABLABLA' 
    ORDER BY 
        DateAdded DESC
)
SELECT
    CASE WHEN UUID IS NULL 'Rejected' ELSE 'Matched' END as location,
    t.Firstname,
    t.LastName,
    t.AddrLine1,
    t.AddrLine2,
    t.City,
    t.Prov,
    t.Postal 
FROM (VALUES(0)) AS v(dummy)
LEFT JOIN testquery AS t ON 1=1;
 

Обычным объяснением этого является холодный кэш. В вашем случае, я думаю, проблема была бы ORDER BY в первом CTE.

Чтобы устранить эту проблему, вам нужен индекс test(UUID, DateAdded desc) .

Я не уверен, почему это ускорится после первого выполнения. Возможно, кэш сервера работает особенно хорошо.