оптимизированный способ получения количества строк из запроса, содержащего большой объем данных

Я бы сказал посмотреть на индексы, но это, вероятно, не сильно поможет, потому что а) вы, вероятно, уже это сделали, и б) вы не можете получить поиск с таким запросом, только сканирование.

Идея состоит в том, чтобы избавиться от этих OR и позволить оптимизатору создать надежный план.

Есть два варианта.

Не знаю, о какой версии SQL Server идет речь, но если это SQL 2008 SP1 CU5 (10.0.2746) или новее, или SQL 2008 R2 CU1 (10.50.1702) или новее, или что-то новее, добавьте option (recompile) к запросу. Это должно привести к гораздо лучшему плану, используя поиск по соответствующим индексам.

Это, однако, добавит некоторые накладные расходы на повторную компиляцию при каждом выполнении, поэтому, возможно, второй вариант лучше.

Вы можете переписать запрос в динамический и удалить параметры NULL еще до того, как оптимизатор увидит запрос. Я пытался переписать ваш запрос, у меня нет ваших данных, поэтому я не могу его проверить, и в нем могут быть некоторые ошибки, но вы все равно получите мое намерение. И мне пришлось угадывать типы данных. (Кстати, есть ли конкретная причина для SELECT p.policyid?)

Вот:

declare @qry nvarchar(4000), @prms nvarchar(4000);
set @qry = N'
SELECT count(*)
         FROM   resident (nolock) r 
                INNER JOIN resident_policy (nolock) rp 
                        ON r.residentid = rp.residentid 
                INNER JOIN policy (nolock) p 
                        ON p.policyid = rp.policyid 
                INNER JOIN policy_locations (nolock) pl 
                        ON pl.policyid = p.policyid 
                INNER JOIN location (nolock) l 
                        ON pl.locationid = l.locationid 
                INNER JOIN property (nolock) pr 
                        ON pr.propertyid = l.propertyid 
         WHERE  r.primary_resident = 0x1 '
if @ResidentFirstName IS NOT NULL
    set @qry = @qry + ' AND R.firstname LIKE @ResidentFirstName + ''%'''  
if @ResidentLastName IS NOT NULL 
    set @qry = @qry + ' AND R.firstname LIKE @ResidentLastName + ''%'''
if @PropertyAddress IS NOT NULL 
    set @qry = @qry + ' AND pr.address LIKE @PropertyAddress + ''%''' 
if @Policynumber IS NOT NULL 
    set @qry = @qry + ' AND p.policynumber LIKE @Policynumber + ''%''' 
if @LocationAddress IS NOT NULL 
    set @qry = @qry + ' AND l.address2 LIKE @LocationAddress + ''%''' 
if @City IS NOT NULL 
    set @qry = @qry + ' AND pr.city LIKE @City + ''%''' 
if @ZipCode IS NOT NULL 
    set @qry = @qry + ' AND pr.zipcode = @ZipCode'
if @StateId IS NOT NULL 
    set @qry = @qry + ' AND pr.stateid = @StateId'
if @PolicyStatusId IS NOT NULL 
    set @qry = @qry + ' AND p.policystatusid = @PolicyStatusId'


set @prms = N'@PolicyStatusId int, @StateId int, @ZipCode int,
@City varchar(50), @LocationAddress varchar(50), @Policynumber varchar(50), 
@PropertyAddress varchar(50), @ResidentLastName varchar(50), @ResidentFirstName varchar(50)'

exec sp_executesql 
@qry, 
@prms,
@PolicyStatusId = @PolicyStatusId, @StateId = @StateId, @ZipCode = @ZipCode,
@City = @City, @LocationAddress = @LocationAddress, 
@Policynumber = @Policynumber, @PropertyAddress = @PropertyAddress, 
@ResidentLastName = @ResidentLastName, @ResidentFirstName = @ResidentFirstName

Если вы проверите план выполнения, вы увидите поиск по индексу, если у вас есть некластеризованные индексы в столбцах WHERE и JOIN.

При этом план будет кэшироваться, по одному для каждой комбинации параметров.

На этот вопрос сложно ответить, потому что с огромным объемом данных может произойти многое.

С точки зрения соединения, это должно работать хорошо. Если этот запрос предназначен только для выполнения подсчета, то я могу просто предложить вам сделать это напрямую SELECT count('x') без CTE и без (nolock).

SELECT @rowcount = count('x') as rc
FROM   
    resident r 
    INNER JOIN resident_policy rp 
        ON r.residentid = rp.residentid 
    INNER JOIN policy p 
        ON p.policyid = rp.policyid 
    INNER JOIN policy_locations pl 
        ON pl.policyid = p.policyid 
    INNER JOIN location l 
        ON pl.locationid = l.locationid 
    INNER JOIN property pr 
        ON pr.propertyid = l.propertyid 
WHERE  
    r.primary_resident = 0x1 
    AND ( ( @ResidentFirstName IS NULL ) 
        OR R.firstname LIKE @ResidentFirstName + '%' ) 
    AND ( ( @ResidentLastName IS NULL ) 
        OR R.firstname LIKE @ResidentLastName + '%' ) 
    AND ( @PropertyAddress IS NULL 
        OR pr.address LIKE @PropertyAddress + '%' ) 
    AND ( @Policynumber IS NULL 
        OR p.policynumber LIKE @Policynumber + '%' ) 
    AND ( @LocationAddress IS NULL 
        OR l.address2 LIKE @LocationAddress + '%' ) 
    AND ( @City IS NULL 
        OR pr.city LIKE @City + '%' ) 
    AND ( @ZipCode IS NULL 
        OR pr.zipcode = @ZipCode ) 
    AND ( @StateId IS NULL 
        OR pr.stateid = @StateId ) 
    AND ( @PolicyStatusId IS NULL 
        OR p.policystatusid = @PolicyStatusId )

Если этот CTE используется как для подсчета строк, так и для получения данных из CTE, убедитесь, что вы извлекаете только данные для рассматриваемой страницы (только 20 элементов с ROWCOUNT() as RC и RC > 0 AND RC <= 20).

На стороне базы данных вы можете проверить, есть ли у вас индексы для всех ваших предложений соединения. Похоже, что есть только PK, поэтому у них уже есть индексы. Убедитесь, что у вас есть индекс для объединенных столбцов.

Если у вас по-прежнему возникают проблемы, используйте функцию «План выполнения в реальном времени», чтобы увидеть, что, черт возьми, происходит.

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

Есть несколько общих указаний:

• Создайте некластеризованный индекс для всех столбцов с внешним ключом.
• Создать некластеризованный индекс для столбцов Primary_Resident
• Включите фактический план выполнения при выполнении запроса и посмотрите, какая часть тратит время впустую.
• Поместите заявления, которые с большей вероятностью будут false сначала
• Когда вы запустите свой запрос, SQL-сервер предложит вам несколько подсказок, попробуйте их тоже