sql - 在sql中使用多条语句时分页查询的最佳性能？
问题描述
这是一个有点复杂的查询，但让我详细说明一下，我的程序中有多个过滤器参数，还有一个名为 Vw1 的视图，它认为我在查询中的主要对象大多数过滤器参数都是针对位于另一个视图的这个视图进行过滤的服务器，然后我需要将此视图与另一个表进行比较并检查两侧列是否可能存在冲突，然后检查是否存在任何冲突。你可以在下面看到我的查询
ALTER PROC [dbo].[spGetAyandehVagozariWithConflict]
@Take INT,
@Skip INT,
@FromVagozariDate NVARCHAR(10),
@ToVagozariDate NVARCHAR(10),
@OrderBankCode NVARCHAR(4)=null,
@OrderBrCode NVARCHAR(7)=null,
@BenefBrCode NVARCHAR(4)=null,
@FromAmount NVARCHAR(50)=null,
@ToAmount NVARCHAR(50)=null,
@CheqSerie NVARCHAR(50)=null,
@CheqSerial NVARCHAR(10)=null,
@CheqStatus NVARCHAR(4)=null,
@CreditorNationalCode VARCHAR(20)=NULL,
@CheqType NVARCHAR(4)=null,
@BenefAccn NVARCHAR(13)=null,
@FromMatDate NVARCHAR(10)=null,
@ToMatDate NVARCHAR(10)=null,
@FromTaeenVaziatDate NVARCHAR(10)=null,
@ToTaeenVaziatDate NVARCHAR(10)=NULL,
@HasConflict VARCHAR(4)=null,
@TotalCnt INT OUTPUT,
@SUM BIGINT OUTPUT
AS
BEGIN
DECLARE @Query NVARCHAR(MAX)=''
IF(NULLIF(@FromVagozariDate,'') IS null)
SET @FromVagozariDate='NULL'
IF(NULLIF(@ToVagozariDate,'') IS null)
SET @ToVagozariDate='NULL'
IF(NULLIF(@OrderBrCode,'') IS null)
SET @OrderBrCode='NULL'
IF(NULLIF(@OrderBankCode,'') IS null)
SET @OrderBankCode='NULL'
IF(NULLIF(@BenefBrCode,'') IS null)
SET @BenefBrCode='NULL'
IF(NULLIF(@FromAmount,'') IS null)
SET @FromAmount='NULL'
IF(NULLIF(@ToAmount,'') IS null)
SET @ToAmount='NULL'
IF(NULLIF(@CheqSerie,'') IS null)
SET @CheqSerie='NULL'
IF(NULLIF(@CheqSerial,'') IS null)
SET @CheqSerial='NULL'
IF(NULLIF(@CheqStatus,'') IS null)
SET @CheqStatus='NULL'
IF(NULLIF(@CheqType,'') IS null)
SET @CheqType='NULL'
IF(NULLIF(@BenefAccn,'') IS null)
SET @BenefAccn='NULL'
IF(NULLIF(@ToMatDate,'') IS null)
SET @ToMatDate='NULL'
IF(NULLIF(@FromMatDate,'') IS null)
SET @FromMatDate='NULL'
IF(NULLIF(@FromTaeenVaziatDate,'') IS null)
SET @FromTaeenVaziatDate='NULL'
IF(NULLIF(@ToTaeenVaziatDate,'') IS null)
SET @ToTaeenVaziatDate='NULL'
IF(NULLIF(@HasConflict,'') IS null)
SET @HasConflict='NULL'
IF(NULLIF(@CreditorNationalCode,'') IS null)
SET @CreditorNationalCode='NULL'
IF @FromVagozariDate<>'NULL'
BEGIN
SET @FromVagozariDate=REPLACE(@FromVagozariDate,'/','')
END
IF @ToVagozariDate<>'NULL'
BEGIN
SET @ToVagozariDate=REPLACE(@ToVagozariDate,'/','')
END
IF @FromMatDate<>'NULL'
BEGIN
SET @FromMatDate=REPLACE(@FromMatDate,'/','')
END
IF @ToMatDate<>'NULL'
BEGIN
SET @ToMatDate=REPLACE(@ToMatDate,'/','')
END
IF @FromTaeenVaziatDate<>'NULL'
BEGIN
SET @FromTaeenVaziatDate=REPLACE(@FromTaeenVaziatDate,'/','')
SET @FromTaeenVaziatDate=SUBSTRING(@FromTaeenVaziatDate,3,6)
END
IF @ToTaeenVaziatDate<>'NULL'
BEGIN
SET @ToTaeenVaziatDate=REPLACE(@ToTaeenVaziatDate,'/','')
SET @ToTaeenVaziatDate=SUBSTRING(@ToTaeenVaziatDate,3,6)
END
IF @OrderBankCode<>'NULL'
BEGIN
--SET @OrderBankCode='0'+@OrderBankCode;
SET @OrderBankCode=dbo.LeftPad(@OrderBankCode,'0',3)
END
IF(@BenefBrCode<>'NULL')
BEGIN
SET @BenefBrCode=dbo.LeftPad(@BenefBrCode,'0',4)
END
IF(@OrderBrCode<>'NULL')
BEGIN
SET @OrderBrCode=dbo.LeftPad(@OrderBrCode,'0',4)
END
SET @Query=@Query+N'
SELECT *
INTO #Vw1
FROM [LinkedServer].DbName.dbo.Vw1 vd
where (vd.mat_date BETWEEN '+''''+@FromMatDate+''''+' AND'+''''+ @ToMatDate+''''+' OR ('+''''+@FromMatDate+''''+' =''NULL'' AND '+''''+@ToMatDate+''''+' =''NULL'' )) AND
(dbo.LeftPad(vd.order_bank_code,''0'',3)= '+''''+@OrderBankCode+''''+' OR '+''''+@OrderBankCode+''''+' =''NULL'') AND
(dbo.LeftPad(vd.order_brcode,''0'',4)= '+''''+@OrderBrCode+''''+' OR '+''''+@OrderBrCode+''''+' =''NULL'') AND
(dbo.LeftPad(vd.Benef_br_code,''0'',4)= '+''''+@BenefBrCode+''''+' OR '+''''+@BenefBrCode+''''+' =''NULL'') AND
(substring(vd.amount, patindex(''%[^0]%'',vd.amount), 10) BETWEEN '+''''+@FromAmount+''''+' AND '+''''+@ToAmount+''''+' OR ('+''''+@FromAmount+''''+' =''NULL'' AND '+''''+@ToAmount+''''+' =''NULL'')) AND
(RTRIM(LTRIM(vd.chq_serie))= '+''''+@CheqSerie+''''+' OR '+''''+@CheqSerie+''''+' =''NULL'') AND
(RTRIM(LTRIM(vd.chq_serial))= '+''''+@CheqSerial+''''+' OR '+''''+@CheqSerial+''''+' =''NULL'') AND
(vd.Chq_stat= '+''''+@CheqStatus+''''+' OR '+''''+@CheqStatus+''''+' in (''NULL'',''-1'')) AND
(vd.chq_type= '+''''+@CheqType+''''+' OR '+''''+@CheqType+''''+' =''NULL'') AND
(vd.benef_acno= '+''''+@BenefAccn+''''+' OR '+''''+@BenefAccn+''''+' =''NULL'') AND
((vd.taeen_vaziat_date is null AND ('+''''+@CheqStatus+''''+'=''-1'')) or('+''''+@CheqStatus+''''+'<>''-1'') ) AND
(vd.vagozari_date BETWEEN '+''''+@FromVagozariDate+''''+' AND '+''''+@ToVagozariDate+''''+' OR ('+''''+@FromVagozariDate+''''+' =''NULL'' AND '+''''+@ToVagozariDate+''''+' =''NULL'')) AND
(vd.taeen_vaziat_date BETWEEN '+''''+@FromTaeenVaziatDate+''''+' AND '+''''+@ToTaeenVaziatDate+''''+' OR ('+''''+@ToTaeenVaziatDate+''''+' =''NULL'' AND '+''''+@FromTaeenVaziatDate+''''+' =''NULL''))
order by system_no
select distinct cast(benef_acno as bigint) benef_acno
into #acno
from #Vw1
select ACNO,CUSTNO
into #ACTINFO
from [LinkServer1].[Db1].[dbo].[ACTINFO] af
where accno_id in (select * from #acno)
select [ECONOMIC-CODE],CUSTNO,FIRSTNAME +'' ''+LASTNAME CUSTNAME
into #CUSTINFO
from [LinkServer1].[Db1].[dbo].[CUSTINFO]
where CustNo_Id in (select cast(CUSTNO as bigint) from #ACTINFO)
SELECT
vd.*,
ct.Title CheqTypeTitle,
af.CUSTNO,cf.CUSTNAME,
--'''' as CUSTNO,'''' CUSTNAME,
cs.Status CheqStatusTitle,
av.Id AyandehVagozariId,
p.ParticipantName BenefBankName,
CASE WHEN ISNULL(vd.vagozari_date,'''') <> COALESCE(REPLACE(av.VagozariDate,''/'',''''),vd.vagozari_date,'''') THEN 1 ELSE 0 END VagozariDateHasConflict,
CASE WHEN ISNULL(dbo.LeftPad(vd.order_bank_code,''0'',3),'''') <> COALESCE(av.Order_Bank_Code,dbo.LeftPad(vd.order_bank_code,''0'',3),'''') THEN 1 ELSE 0 END OrderBankCodeHasConflict,
CASE WHEN ISNULL(dbo.LeftPad(vd.order_brcode,''0'',4),'''') <> COALESCE(av.Order_brCode,dbo.LeftPad(vd.order_brcode,''0'',4),'''') THEN 1 ELSE 0 END OrderBrCodeHasConflict,
CASE WHEN ISNULL(dbo.LeftPad(vd.Benef_br_code,''0'',4),'''') <> COALESCE(av.Benef_Br_Code,dbo.LeftPad(vd.Benef_br_code,''0'',4),'''') THEN 1 ELSE 0 END BenefBrCodeHasConflict,
CASE WHEN ISNULL(substring(vd.amount, patindex(''%[^0]%'',vd.amount), 10),'''') <> COALESCE(av.Amount,substring(vd.amount, patindex(''%[^0]%'',vd.amount), 10),'''') THEN 1 ELSE 0 END AmountHasConflict,
CASE WHEN ISNULL(vd.chq_serie,'''') <> COALESCE(av.Chq_serie,vd.chq_serie,'''') THEN 1 ELSE 0 END CheqSerieHasConflict,
CASE WHEN ISNULL(RTRIM(LTRIM(vd.chq_serial)),'''') <> COALESCE(av.Chq_Serial,RTRIM(LTRIM(vd.chq_serial)),'''') THEN 1 ELSE 0 END CheqSerialHasConflict,
CASE WHEN ISNULL(vd.chq_type,'''') <> COALESCE(av.Chq_Type,vd.chq_type,'''') THEN 1 ELSE 0 END CheqTypeHasConflict,
CASE WHEN ISNULL(vd.benef_acno,'''') <> COALESCE(av.Benef_Acno,vd.benef_acno,'''') THEN 1 ELSE 0 END BenefAcnoHasConflict,
CASE WHEN ISNULL(vd.mat_date,'''') <> COALESCE(REPLACE(av.Mat_Date,''/'',''''),vd.mat_date,'''') THEN 1 ELSE 0 END MatDateHasConflict,
CASE WHEN ISNULL(cf.CUSTNO,'''') <> COALESCE(av.CUSTNO,cf.CUSTNO COLLATE DATABASE_DEFAULT,'''') THEN 1 ELSE 0 END CustNoHasConflict,
--CASE WHEN ISNULL(vd.taeen_vaziat_date,'''') <> COALESCE(av.Taeen_Vaziat_Date,vd.taeen_vaziat_date,'''') THEN 1 ELSE 0 END TaeenVaziatDateHasConflict,
0 as TaeenVaziatDateHasConflict,
CASE WHEN ISNULL(vd.Chq_stat,'''') <> COALESCE(av.Chq_Status,vd.Chq_stat,'''') THEN 1 ELSE 0 END CheqStatusHasConflict
--0 as VagozariDateHasConflict,
--0 as OrderBankCodeHasConflict,
--0 as OrderBrCodeHasConflict,
--0 as BenefBrCodeHasConflict,
--0 as AmountHasConflict,
--0 as CheqSerieHasConflict,
--0 as CheqSerialHasConflict,
--0 as CheqTypeHasConflict,
--0 as BenefAcnoHasConflict,
--0 as MatDateHasConflict,
--0 as TaeenVaziatDateHasConflict
INTO #temp
FROM #vw_faranam_vagozari_chakavak vd
LEFT JOIN #ACTINFO af on vd.benef_acno=af.ACNO COLLATE Arabic_CI_AS
LEFT JOIN #CUSTINFO cf on af.CUSTNO=cf.CUSTNO COLLATE Arabic_CI_AS
LEFT JOIN [AyandehVagozari] av ON av.SystemNo=vd.system_no
LEFT JOIN [Cheque_Type_Biha] ct ON ct.Code=vd.chq_type
LEFT JOIN [ChequeStatusForBiha] cs ON cs.Id=vd.Chq_stat
LEFT JOIN [Participant] p ON ''0''+p.ParticipantCode=vd.order_bank_code
WHERE (cf.[ECONOMIC-CODE]= '+''''+@CreditorNationalCode+''''+' OR '+''''+@CreditorNationalCode+''''+' =''NULL'')
order by vd.system_no
DELETE t FROM(
SELECT *,ROW_NUMBER() OVER(partition by system_no order by AyandehVagozariId desc) rn FROM
#temp
)t
where rn>1
SELECT *,
case when (VagozariDateHasConflict=1 or OrderBankCodeHasConflict=1 or OrderBrCodeHasConflict =1 OR BenefBrCodeHasConflict=1
or AmountHasConflict=1 or CheqSerieHasConflict=1 or CheqSerialHasConflict=1 or CustNoHasConflict=1
or CheqStatusHasConflict=1 or CheqTypeHasConflict=1 or BenefAcnoHasConflict=1 or MatDateHasConflict=1 or TaeenVaziatDateHasConflict=1)
then 1 else 0 end HasConflict
INTO #TmpResult
FROM #temp
order by system_no
-这是我过滤最后一个参数的地方，但似乎为时已晚......因为已经涉及很多记录！
SELECT *,ROW_NUMBER() over (ORDER BY system_no) RowNumber
into #finalTemp
FROM
#TmpResult
where (HasConflict=1 and '+''''+@HasConflict+''''+'=''1'')
OR (HasConflict=0 and '+''''+@HasConflict+''''+'=''0'')
OR ('+''''+@HasConflict+''''+'=''NULL'')
order by system_no
SELECT @Total=@@ROWCOUNT
select @Sum=isnull(SUM(cast(amount as bigint)),0) from
#finalTemp
select * from
#finalTemp
WHERE RowNumber >'+CAST(@Skip AS NVARCHAR(10))+' AND RowNumber<=('+CAST(@Skip+@Take AS NVARCHAR(10))+')
'
PRINT @Query
EXEC sp_executesql @Query,N'@Total int output,@Sum bigint output',@TotalCnt OUTPUT,@SUM OUTPUT
END
由于性能问题，我不得不以动态方式编写它，但问题是我无法在此查询中围绕分页进行思考，因为如您所见，我应该将分页行放在查询的末尾，因为@HasConflict参数存在而我不能早点检查一下，这样我的分页似乎毫无意义，因为我正在处理大量数据......
我也不能使用偏移量进行分页，因为我的 sql 版本太旧了......
解决方案
推荐阅读
- python - Legendre多项式的计算，学习
- microsoft-graph-api - 如何从 Microsoft Graph API 资源管理器中区分用户邮箱和共享邮箱
- python-3.x - Paramiko SSH 客户端无法解压 ED25519 密钥
- django - “ManyToManyDescriptor”对象没有“添加”属性
- embedded - 如何覆盖 U-Boot“bootcmd”变量
- c# - 服务器端 .NET Core 中的 Session.SetString() 产生错误“响应开始后无法建立会话”
- google-cloud-platform - 限制对 GCE 实例元数据的访问
- python - 使用元组作为输入初始化一个类
- javascript - 使用不同的参数运行一个突变
- vue.js - 如何在 vue 中触发没有 $refs 的点击事件？