在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版本太旧了...
展开
收起
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关问答
问答排行榜
最热
最新
推荐问答
相关文章
相关电子书
更多
