如何提高join子句中使用的标量值函数的性能？
如何提高join子句中使用的标量值函数的性能？
提问于 2018-12-29 13:36:56
我开发了一个查询来处理报表的数据。我只能从所选月份的历史表中获得一条记录。
例如:这些是4辆车。2018年11月对车辆"1“进行了四次改装，日期和价格如下：
1). 05 November 2018 with price 1000
2). 15 November 2018 with price 5000
3). 25 November 2018 with price 8000现在我只想要上一次修改的日期(11月25日)的唯一记录。
我开发了一个数据库标量函数，并在join子句中使用了该函数，该函数可以很好地处理较少的数据。现在我们有一些记录(历史表中的记录不太多，只有100条记录)，并且报告存在速度/性能问题。我创建了索引并删除了子查询，但仍然没有多大帮助。
请看我试用过的脚本：
declare @pIntFranchiseId int = 16179;
declare @pDtFrom date = '2018-12-01';
declare @pDtTo date = '2018-12-28';
SELECT ba.[Id],BKAgrHistory.[BookingStart],BKAgrHistory.[BookingEnd],BKAgrHistory.[StartTime],BKAgrHistory.[EndTime]
,ba.[FranchiseId],BKAgrHistory.[FleetId],[BKRenterId],[IsReportGenerated],[StepsCompleted]
,ba.[BookingId],ba.[ReservationId],ba.[RentalAgreementId],[VehicleUsedFor],[OperatorLicence]
,[ClearanceCode],[ExcessAmount],[ClearanceCodeAdditionalDriv],[ExcessAmountAdditionalDriv],[IsClearanceVerified]
,[BKDiscount],BKAgrHistory.[AgreementStatus],[IsTravelingOutsideUk],[OtherCountries],[RentalType],[RenterType],[RenterCompanyType]
,[IsVehicleDelivered],[IsReturnedOnSameAddress],[PickUpAddress],[ReturnAddress],[OtherReturnAddress],[TariffId]
,[SpecialRateId],[IsChargeShortWeekend],[IsChargeLongWeekend],[ChargeHalfDay],[IsDamageProvided],[IsRenterAgreement]
,[BKIsOwnInsurance],[ExpectedMileage],BKAgrHistory.[TotalHireCharge],[BKFreeMiles],[BKExpensePerMile],[BKCollisionDamage],[BKDamage]
,[BKTheft],[BKOverHeight],[BKBookingNotes],[BKFleetCurrentMileage],[BKMileageOut],[BKMileageIn],[BKDamageOut],[BKDamageIn],[Condition]
,[AppliedRates],[Paid],[OnceInvoiced],[InterimInvoiceCount],[PBNumber],[Excess],[StatusId],[CreatedOn],[ModifiedOn],BKAgrHistory.[BookingDays]
,[VATPercentage],[BkOpeningHrsSurcharge],[DrivenMileage],[ParentRAId],[NetTotal],[VatTotal],[GrossTotal],[GrandInsurableRevenue]
,[GrandTotalWithoutExtra],[GrandInsurableRevWithoutExtra],[TariffNetPrice],[OnceMailed],[LastBookingEnd],[LastEndTime],[BkOpeningHrsSurchargeNet]
,[BkOpeningHrsSurchargeVat],BKAgrHistory.[DailyHireCharge],[BkNetExtraDrivenMilesExcess],[BkExtraDrivenMilesExcessVAT],[BkTotalExtraDrivenMilesExcess],[ExcessMilesDays]
,[BkWebWeekendSurcharge],BKAgrHistory.[InsertedUserId],BKAgrHistory.[UpdateUserId],BKAgrHistory.[InsertedDate],BKAgrHistory.[UpdatedDate],BKAgrHistory.[InsertedUserName],BKAgrHistory.[UpdatedUserName],
[SecurityDeposit],[SecurityDepositRuleId]
FROM [dbo].[BookingAgreements] ba
join BookingAgreementAmountHistory BKAgrHistory on BKAgrHistory.Id = dbo.[GetAgreementAmtHistoryId](@pIntFranchiseId,ba.Id,@pDtFrom,@pDtTo)
where
(
DATEADD(day, DATEDIFF(day, 0,BKAgrHistory.BookingStart), 0) <= DATEADD(day, DATEDIFF(day, 0, @pDtFrom), 0) OR
DATEADD(day, DATEDIFF(day, 0,BKAgrHistory.BookingEnd), 0) <= DATEADD(day, DATEDIFF(day, 0, @pDtFrom), 0)
)
and ba.FranchiseId=@pIntFranchiseId and StatusId=1
and ISNULL(BKAgrHistory.AgreementStatus,0) not in (0,2,6,7)
and BKAgrHistory.FleetId in (
select f.Id from Fleets f
join FleetsHistory fleetHis on fleetHis.Id = dbo.[GetVehicleHistoryById](@pIntFranchiseId,BKAgrHistory.FleetId,@pDtFrom,@pDtTo)
where fleetHis.GhostVehicle = 0 and fleetHis.CoreFleet = 1 and isnull(fleetHis.StatusId,0) in (1,4) and
f.Franchise_Id=@pIntFranchiseId and DATEADD(day, DATEDIFF(day, 0, fleetHis.PurchaseDate), 0) <= DATEADD(day, DATEDIFF(day, 0, @pDtTo), 0)); SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetVehicleHistoryById](@franchiseId int = NULL,@fleetId int = NULL, @fromDate Date = NULL, @toDate Date = NULL)
RETURNS int
AS BEGIN
Declare @returnId int = 0;
Select top 1 @returnId = isnull(Id,0) from FleetsHistory
where TransactionDate =
(Select max(TransactionDate)
from FleetsHistory fh
where fh.Franchise_Id = @franchiseId and fh.Fleet_Id = @fleetId
--group by AgreementId
and (DATEADD(day, DATEDIFF(day, 0, TransactionDate),0)) <= DATEADD(day, DATEDIFF(day, 0, @toDate),0))
order by Id desc;
Return @returnId;
END SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetAgreementAmtHistoryId](@franchiseId int = NULL,@agreementId int = NULL, @fromDate Date = NULL, @toDate Date = NULL)
RETURNS int
AS BEGIN
Declare @returnId int = 0;
Select top 1 @returnId = isnull(Id,0) from BookingAgreementAmountHistory
where TransactionDate =
(Select max(TransactionDate)
from BookingAgreementAmountHistory
where FranchiseId = @franchiseId and AgreementId = @agreementId
--group by AgreementId
and (DATEADD(day, DATEDIFF(day, 0, TransactionDate),0)) <= DATEADD(day, DATEDIFF(day, 0, @toDate),0))
order by Id desc;
Return @returnId;
END回答 1
Stack Overflow用户
回答已采纳
发布于 2018-12-29 13:49:42
不管其他函数可能做什么，它们都不能提高SQL查询的性能。
我建议使用带有正确参数的apply。考虑到问题中查询的激增、样本数据和所需结果的缺乏以及简单问题与实际查询之间的脱节，很难看出您真正想要的是什么。但这样做的目的是：
select ba.*, baah.*
from [dbo].[BookingAgreements] ba outer apply
(select top (1) baah.*
from BookingAgreementAmountHistory baah
where baah.Id = ba.FranchiseId and
baah.AgreementStatus not in (2, 6, 7) and -- this takes care of NULL values
baah.<date> >= @pDtFrom and
baah.<date> <= @pDtTo
order by baah.<date> desc
) baah
where ba.FranchiseId = @pIntFranchiseId;<date>是您在问题开头提到的修改日期。目前还不清楚这实际上指的是哪一天。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53970062
复制相关文章
相似问题
腾讯云开发者
