У меня есть хранимая процедура, которая использует табличную функцию, которая выполняется за 9 секунд. Если я изменю табличную функцию и удалю предложение where, хранимая процедура будет выполнена через 3 секунды. Если я добавлю предложение where обратно, запрос все равно будет выполняться через 3 секунды.
Я взглянул на планы выполнения, и оказалось, что после того, как я удалю предложение where, план выполнения включает параллелизм, а количество сканирований для 2 моих таблиц падает с 50000 и 65000 до 5 и 3. После того, как я добавлю предложение where назад, оптимизированный план выполнения по-прежнему работает, пока я не запущу DBCC FREEPROCCACHE.
Вопросы 1. Почему SQL Server начинает использовать оптимизированный план выполнения для обоих запросов только тогда, когда я сначала удаляю предложение where?
- Есть ли способ заставить SQL Server использовать этот план выполнения?
Кроме того, это параметризованный запрос «все-в-одном», который использует (параметр имеет значение null или параметр) в предложении where, что, как мне кажется, плохо сказывается на производительности.
RETURNS TABLE
AS
RETURN
(
SELECT TOP (@PageNumber * @PageSize)
CASE
WHEN @SortOrder = 'Expensive' THEN ROW_NUMBER() OVER (ORDER BY SellingPrice DESC)
WHEN @SortOrder = 'Inexpensive' THEN ROW_NUMBER() OVER (ORDER BY SellingPrice ASC)
WHEN @SortOrder = 'LowMiles' THEN ROW_NUMBER() OVER (ORDER BY Mileage ASC)
WHEN @SortOrder = 'HighMiles' THEN ROW_NUMBER() OVER (ORDER BY Mileage DESC)
WHEN @SortOrder = 'Closest' THEN ROW_NUMBER() OVER (ORDER BY P1.Distance ASC)
WHEN @SortOrder = 'Newest' THEN ROW_NUMBER() OVER (ORDER BY [Year] DESC)
WHEN @SortOrder = 'Oldest' THEN ROW_NUMBER() OVER (ORDER BY [Year] ASC)
ELSE ROW_NUMBER() OVER (ORDER BY InventoryID ASC)
END as rn,
P1.InventoryID,
P1.SellingPrice,
P1.Distance,
P1.Mileage,
Count(*) OVER () RESULT_COUNT,
dimCarStatus.[year]
FROM (SELECT InventoryID, SellingPrice, Zip.Distance, Mileage, ColorKey, CarStatusKey, CarKey FROM facInventory
JOIN @ZipCodes Zip
ON Zip.DealerKey = facInventory.DealerKey) as P1
JOIN dimColor
ON dimColor.ColorKey = P1.ColorKey
JOIN dimCarStatus
ON dimCarStatus.CarStatusKey = P1.CarStatusKey
JOIN dimCar
ON dimCar.CarKey = P1.CarKey
WHERE
(@ExteriorColor is NULL OR dimColor.ExteriorColor like @ExteriorColor) AND
(@InteriorColor is NULL OR dimColor.InteriorColor like @InteriorColor) AND
(@Condition is NULL OR dimCarStatus.Condition like @Condition) AND
(@Year is NULL OR dimCarStatus.[Year] like @Year) AND
(@Certified is NULL OR dimCarStatus.Certified like @Certified) AND
(@Make is NULL OR dimCar.Make like @Make) AND
(@ModelCategory is NULL OR dimCar.ModelCategory like @ModelCategory) AND
(@Model is NULL OR dimCar.Model like @Model) AND
(@Trim is NULL OR dimCar.Trim like @Trim) AND
(@BodyType is NULL OR dimCar.BodyType like @BodyType) AND
(@VehicleTypeCode is NULL OR dimCar.VehicleTypeCode like @VehicleTypeCode) AND
(@MinPrice is NULL OR P1.SellingPrice >= @MinPrice) AND
(@MaxPrice is NULL OR P1.SellingPrice < @MaxPrice) AND
(@Mileage is NULL OR P1.Mileage < @Mileage)
ORDER BY
CASE
WHEN @SortOrder = 'Expensive' THEN -SellingPrice
WHEN @SortOrder = 'Inexpensive' THEN SellingPrice
WHEN @SortOrder = 'LowMiles' THEN Mileage
WHEN @SortOrder = 'HighMiles' THEN -Mileage
WHEN @SortOrder = 'Closest' THEN P1.Distance
WHEN @SortOrder = 'Newest' THEN -[YEAR]
WHEN @SortOrder = 'Oldest' THEN [YEAR]
ELSE InventoryID
END
)