我在SQL中有一个复杂的查询，需要很长时间，即超过1分钟，以产生由于SQL中的Row_Number()的结果。我找不到更好的选择了。下面是我的SQL查询。
Declare @AgreementNumber nvarchar(500)
Declare @SerialNo nvarchar(100)
Declare @EmailId nvarchar(1000)
Declare @CountryId nvarchar(10)
Declare @SaleType tinyint
Declare @CompanyName nvarchar(255)
Declare @PONo numeric(18,0)
Declare @Status tinyint
Declare @POCDeliveryType nvarchar(10)
Declare @FromDate datetime
Declare @ToDate datetime
Declare @CurrentPage numeric
Declare @PageSize numeric
Declare @FileId numeric(18,0)
Declare @PurchaseOrg nvarchar(4)
set @AgreementNumber =''
set @SerialNo =''
set @EmailId =''
set @CountryId ='0014'
set @SaleType=0
set @CompanyName =''
set @PONo= 0
set @Status =0
set @POCDeliveryType =''
set @FromDate =''
set @ToDate =''
set @CurrentPage =1
set @PageSize =100000
set @FileId =0
set @PurchaseOrg =''
----For Paging---------------------------------------------------------------------
Declare @TotalRecord numeric
Declare @StartRow numeric
Declare @EndRow numeric
Set @StartRow=(@PageSize * (@CurrentPage-1)) + 1
Set @EndRow=(@PageSize * (@CurrentPage))
-----------------------------------------------------------------------------------
Select @TotalRecord=Count(*)
From OrderHeader OH inner join OrderLine OL on OH.OrderID=OL.OrderId
Where (OH.CountryId=@CountryId or ISNULL(@CountryId,'')='') and
(OH.PurchaseOrg=@PurchaseOrg or ISNULL(@PurchaseOrg,'')='') and
(SaleType=@SaleType or ISNULL(@SaleType,0)=0) and
(CompanyName like '%'+ isnull(@CompanyName,'') + '%'or isnull(@CompanyName,'')='')and
(PurchaseOrderNo = @PONo or isnull(@PONo,0)=0)and
(OL.Status=@Status or ISNULL(@Status,0)=0) and
(POCDeliveryPreference=@POCDeliveryType or ISNULL(@POCDeliveryType,'')='') and
((CONVERT(VARCHAR(12),OH.Created,112)>=CONVERT(VARCHAR(12),@FromDate,112))or (Isnull(@FromDate,'')=''))and
(CONVERT(VARCHAR(12),OH.Created,112)<=CONVERT(VARCHAR(12),@ToDate,112)or (Isnull(@ToDate,'')='')) and
(OL.SerialNumber like '%'+@SerialNo+'%' or ISNULL(@SerialNo,'') ='') and
(OL.AgreementNumber like '%'+@AgreementNumber+'%' or ISNULL(@AgreementNumber,'') ='') and
(OH.EmailAddress like '%'+@EmailId+'%' or ISNULL(@EmailId,'') ='') and
(OH.FileId=@FileId or isnull(@FileId,0)=0)
Select *, @TotalRecord as TotalRecord From
(
**Select ROW_NUMBER() OVER
(
Order By OH.Created Desc
)as Row**,OH.OrderID,ISNULL(CustomerId,'') as CustomerId,ISNULL(OH.CountryId,'') as CountryId,SaleType,HwPurchaseDate,ISNULL(AddressLine1,'') as
AddressLine1,ISNULL(AddressLine2,'') as AddressLine2,ISNULL(City,'') as City,ISNULL([State],'') as [State],ISNULL(Zip,'') as Zip,
ISNULL(County,'') as County,ISNULL(CompanyName,'') as CompanyName,ISNULL(EmailAddress,'') as EmailId,ISNULL(FirstName,'') as FirstName,
ISNULL(LastName,'') as LastName,ISNULL(PrimaryPhone,'') as Phone,ISNULL(PurchaseOrderNo,0) as POno,ISNULL(OL.[Status],0) as [Status],POCDeliveryPreference,
POCLanguage,CustPOReference,ISNULL(CurrencyCode,'') as CurrencyCode,TransactionId,ISNULL(OH.DeliveryStatus,0) as DeliveryStatus,
isnull(IPAddress,'') as IPAddress, ISNULL(InTouchSessionId,'') as InTouchSessionId, ISNULL(InTouchUserId,'') as InTouchUserId,
ISNULL(SourceSystem,0) as SourceSystem,SalesOrderDate,OH.Created,isnull(OL.SerialNumber,'') as SerialNumber,ISNULL(OL.AgreementNumber,'') as AgreementNumber,
ISNULL(OL.PurchaseOrderNoLineLevel,'') as PurchaseOrderNoLineLevel,ISNULL(OL.ProductName,'') as ProductName,ISNULL(OL.OrderQty,0) as OrderQty,
ISNULL(OL.ManufPartNo,'') as ManufPartNo,isnull(OL.VendorStatus,'') as VendorStatus, isnull(OL.StatusRemark, '') as StatusRemark
From OrderHeader OH inner join OrderLine OL on OH.OrderID=OL.OrderId
Where (OH.CountryId=@CountryId or ISNULL(@CountryId,'')='') and
(OH.PurchaseOrg=@PurchaseOrg or ISNULL(@PurchaseOrg,'')='') and
(SaleType=@SaleType or ISNULL(@SaleType,0)=0) and
(CompanyName like '%'+ isnull(@CompanyName,'') + '%'or isnull(@CompanyName,'')='')and
(PurchaseOrderNo = @PONo or isnull(@PONo,0)=0)and
(OL.Status=@Status or ISNULL(@Status,0)=0) and
(POCDeliveryPreference=@POCDeliveryType or ISNULL(@POCDeliveryType,'')='') and
((CONVERT(VARCHAR(12),OH.Created,112)>=CONVERT(VARCHAR(12),@FromDate,112))or (Isnull(@FromDate,'')=''))and
(CONVERT(VARCHAR(12),OH.Created,112)<=CONVERT(VARCHAR(12),@ToDate,112)or (Isnull(@ToDate,'')='')) and
(OL.SerialNumber like '%'+@SerialNo+'%' or ISNULL(@SerialNo,'') ='') and
(OL.AgreementNumber like '%'+@AgreementNumber+'%' or ISNULL(@AgreementNumber,'') ='') and
(OH.EmailAddress like '%'+@EmailId+'%' or ISNULL(@EmailId,'') ='') and
(OH.FileId=@FileId or isnull(@FileId,0)=0)
)
as RowResults
Where Row between @StartRow AND @EndRow
我已经把花了很长时间的那部分高亮了。
OH.CountryId = @CountryId or ISNULL(@CountryId, '') = '')
where OH.CountryId = @CountryId中? Replace
((CONVERT(VARCHAR(12),OH.Created,112)>=CONVERT(VARCHAR(12),@FromDate,112))
(CONVERT(VARCHAR(12),OH.Created,112)<=CONVERT(VARCHAR(12),@ToDate,112)
by Cast(OH.Created as date)>=Cast(@FromDate as date)
Cast(OH.Created as date)<=Cast(@FromDate as date)
转换创建一个不是最好的计划
查询本身存在很多问题
(1)使用相同的查询两次
(2)试图捕获所有可能产生可怕计划的查询(无效)
(3)使用像OH这样的谓词。EmailAddress like '%'+@EmailId+'%'
(4)分页效率低下
Declare @AgreementNumber nvarchar(500)
Declare @SerialNo nvarchar(100)
Declare @EmailId nvarchar(1000)
Declare @CountryId nvarchar(10)
Declare @SaleType tinyint
Declare @CompanyName nvarchar(255)
Declare @PONo numeric(18,0)
Declare @Status tinyint
Declare @POCDeliveryType nvarchar(10)
Declare @FromDate datetime
Declare @ToDate datetime
Declare @CurrentPage numeric
Declare @PageSize numeric
Declare @FileId numeric(18,0)
Declare @PurchaseOrg nvarchar(4)
set @AgreementNumber =''
set @SerialNo =''
set @EmailId =''
set @CountryId ='0014'
set @SaleType=0
set @CompanyName =''
set @PONo= 0
set @Status =0
set @POCDeliveryType =''
set @FromDate =''
set @ToDate =''
set @CurrentPage =1
set @PageSize =100000
set @FileId =0
set @PurchaseOrg =''
----For Paging---------------------------------------------------------------------
Declare @TotalRecord numeric
Declare @StartRow numeric
Declare @EndRow numeric
Set @StartRow=(@PageSize * (@CurrentPage-1)) + 1
Set @EndRow=(@PageSize * (@CurrentPage))
-----------------------------------------------------------------------------------
--Select @TotalRecord=Count(*)
--From OrderHeader OH inner join OrderLine OL on OH.OrderID=OL.OrderId
--Where (OH.CountryId=@CountryId or ISNULL(@CountryId,'')='') and
--(OH.PurchaseOrg=@PurchaseOrg or ISNULL(@PurchaseOrg,'')='') and
-- (SaleType=@SaleType or ISNULL(@SaleType,0)=0) and
-- (CompanyName like '%'+ isnull(@CompanyName,'') + '%'or isnull(@CompanyName,'')='')and
-- (PurchaseOrderNo = @PONo or isnull(@PONo,0)=0)and
-- (OL.Status=@Status or ISNULL(@Status,0)=0) and
-- (POCDeliveryPreference=@POCDeliveryType or ISNULL(@POCDeliveryType,'')='') and
-- ((CONVERT(VARCHAR(12),OH.Created,112)>=CONVERT(VARCHAR(12),@FromDate,112))or (Isnull(@FromDate,'')=''))and
-- (CONVERT(VARCHAR(12),OH.Created,112)<=CONVERT(VARCHAR(12),@ToDate,112)or (Isnull(@ToDate,'')='')) and
-- (OL.SerialNumber like '%'+@SerialNo+'%' or ISNULL(@SerialNo,'') ='') and
-- (OL.AgreementNumber like '%'+@AgreementNumber+'%' or ISNULL(@AgreementNumber,'') ='') and
-- (OH.EmailAddress like '%'+@EmailId+'%' or ISNULL(@EmailId,'') ='') and
-- (OH.FileId=@FileId or isnull(@FileId,0)=0)
With RowResults as
(
Select ROW_NUMBER() OVER
(
Order By OH.Created Desc
)as Row ,OH.OrderID,ISNULL(CustomerId,'') as CustomerId,ISNULL(OH.CountryId,'') as CountryId,SaleType,HwPurchaseDate,ISNULL(AddressLine1,'') as
AddressLine1,ISNULL(AddressLine2,'') as AddressLine2,ISNULL(City,'') as City,ISNULL([State],'') as [State],ISNULL(Zip,'') as Zip,
ISNULL(County,'') as County,ISNULL(CompanyName,'') as CompanyName,ISNULL(EmailAddress,'') as EmailId,ISNULL(FirstName,'') as FirstName,
ISNULL(LastName,'') as LastName,ISNULL(PrimaryPhone,'') as Phone,ISNULL(PurchaseOrderNo,0) as POno,ISNULL(OL.[Status],0) as [Status],POCDeliveryPreference,
POCLanguage,CustPOReference,ISNULL(CurrencyCode,'') as CurrencyCode,TransactionId,ISNULL(OH.DeliveryStatus,0) as DeliveryStatus,
isnull(IPAddress,'') as IPAddress, ISNULL(InTouchSessionId,'') as InTouchSessionId, ISNULL(InTouchUserId,'') as InTouchUserId,
ISNULL(SourceSystem,0) as SourceSystem,SalesOrderDate,OH.Created,isnull(OL.SerialNumber,'') as SerialNumber,ISNULL(OL.AgreementNumber,'') as AgreementNumber,
ISNULL(OL.PurchaseOrderNoLineLevel,'') as PurchaseOrderNoLineLevel,ISNULL(OL.ProductName,'') as ProductName,ISNULL(OL.OrderQty,0) as OrderQty,
ISNULL(OL.ManufPartNo,'') as ManufPartNo,isnull(OL.VendorStatus,'') as VendorStatus, isnull(OL.StatusRemark, '') as StatusRemark
From OrderHeader OH inner join OrderLine OL on OH.OrderID=OL.OrderId
Where (OH.CountryId=@CountryId or ISNULL(@CountryId,'')='') and
(OH.PurchaseOrg=@PurchaseOrg or ISNULL(@PurchaseOrg,'')='') and
(SaleType=@SaleType or ISNULL(@SaleType,0)=0) and
(CompanyName like '%'+ isnull(@CompanyName,'') + '%'or isnull(@CompanyName,'')='')and
(PurchaseOrderNo = @PONo or isnull(@PONo,0)=0)and
(OL.Status=@Status or ISNULL(@Status,0)=0) and
(POCDeliveryPreference=@POCDeliveryType or ISNULL(@POCDeliveryType,'')='') and
((CONVERT(VARCHAR(12),OH.Created,112)>=CONVERT(VARCHAR(12),@FromDate,112))or (Isnull(@FromDate,'')=''))and
(CONVERT(VARCHAR(12),OH.Created,112)<=CONVERT(VARCHAR(12),@ToDate,112)or (Isnull(@ToDate,'')='')) and
(OL.SerialNumber like '%'+@SerialNo+'%' or ISNULL(@SerialNo,'') ='') and
(OL.AgreementNumber like '%'+@AgreementNumber+'%' or ISNULL(@AgreementNumber,'') ='') and
(OH.EmailAddress like '%'+@EmailId+'%' or ISNULL(@EmailId,'') ='') and
(OH.FileId=@FileId or isnull(@FileId,0)=0)
)
Select max (Row) , * FRom RowResults
Where Row between @StartRow AND @EndRow
option (recompile)
-- quick fix only
-- --if using SQL SERVER 2012
-- OFFSET @StartRow ROWS
--FETCH NEXT @EndRow -@StartRow ROWS ONLY
--if ans still not gaining performance we wold like to see actual exection plan
——(1)使用相同的查询两次DONE
——(2)尝试捕获所有查询(无效)，可以通过选项recompile产生可怕的计划完成(仍然需要理解这个使用动态查询，如果你不暴露于sql注入)
——(3)使用像OH这样的谓词。EmailAddress像'%'+@EmailId+'%'——你需要解决这个
——(4)分页效率低下——获取下一个相当快的row_num .