Web 缓存可用性缓存网格架构报表服务安全 DBA 数据库开发设计/图形 SQL Server 2008 架构师 XML jQuery 中级开发 SQL Server SQL ASP.NET

T-SQL 报告引擎

Levente Kupás

5.00/5 (6投票s)

2013年6月19日

CPOL

6分钟阅读

27153

如果您有运行缓慢的大型报表，可以尝试使用这个基于 T-SQL 的引擎来加速它们。

介绍

在我之前工作的公司，我们尝试为数据仓库和报表寻找各种创新或非标准化的解决方案。我们的大多数报表都是由 XML 定义构建的，并从中生成即席查询；然而，有些报表接近静态，并且这些报表的逻辑非常复杂，因此它们被实现为存储过程。尽管如此，报表生成时间仍接近 30 秒，而期望是 1 秒。

该报表的特点是返回大约 50,000 行和 50 列。基本上所有列都可以进行过滤和排序；六列是多值列，这意味着最终的 10 行合并为一列逗号分隔的值，此外，任何列都可以从结果中隐藏。

考虑到上述需求，您可能会认为这会导致报表生成缓慢，但某些环境条件缓解了这种情况（数据库用作数据仓库，数据每天只更改一次）。

考虑到各种解决方案，其中一种方案脱颖而出，即：将存储过程的结果存储到一个表中，然后根据给定的元数据对其应用某种过滤，并仅返回包含 10-100 行的请求页面。

让我们看看通过将结果存储到表中（表名是从各种参数计算出的唯一哈希值），可以克服大型报表哪些方面的缺点：

提取 250 万个单元格，需要几秒钟
高网络使用量（20 个并发报表 = 800 MB 数据传输）
Web 服务的高内存使用量，因为 1 个报表占用 200-1000 MB。

背景

我们可以使用哪些逻辑来提高报表性能？

报表由存储过程生成

执行计划重用
复杂逻辑可封装
通过参数可以包含一些动态内容

过程的结果存储在一个名为唯一哈希的表中

通过过期检查，报表不会每天只生成一次（取决于阈值），因此最慢的部分（即生成过程）可以被忽略
可以轻松应用任何类型的过滤、排序

生成报表的存储过程由一个管理器过程调用，该过程能够解析 meta-XML 并验证其内容

生成的 SQL 语句是无注入的 - 通用的，可用于任何由过程构建的报表
可用于验证的元数据表

报表生成步骤

网页需要构建 6 个 XML，其中包含报表的元信息，以及它们的子集。

需要使用这些 XML 执行 SQL-Report-Engine 过程
XML 被保存到临时表中（SQL 可以原生处理）
XML 中定义的步骤和子步骤被解析
在生成 SQL 语句的过程中，不允许注入，通过执行该语句，我们可以获得报表的结果集
并发生成在事务中处理

两个主要步骤

构建报表并将结果存储到表中
在先前构建的表上应用任何类型的过滤/排序/分块/显示

许多子步骤

第一步只有一个子步骤，那就是报表构建
第二步可以包含以下子步骤命令，这些命令必须构建适当的 SQL 语句：

FILT
POSTFILT
CNT
SELECT
DISTCOL
DISTMVCOL

每个子步骤在指定的 XML 中都有其自己的命令
WHERE 子句可以有多个 WHERE 值

在下面的图表中，绿色线条代表报表的验证链。

报表验证和元信息

在报表执行过程中，我们应该能够验证 XML 的内容。

有三个表包含报表的元信息

报表名称（要执行的存储过程）
存储过程参数和类型
报表结果集描述、列名及其类型

扩展示例

扩展报表或创建新报表非常简单，只需遵循以下规则即可

创建/修改返回报表的存储过程，并包含/排除列，最终添加/删除参数
将过程名称添加到 dw_report_type 表中
将参数添加到/从 dw_report_params 表中添加/删除
将列添加到/从 dw_report_result 表中添加/删除

报表功能

网站上的报表具有以下功能

分页；从结果集中返回请求的行（例如：跳过 500，取 50）
字段唯一过滤；获取列的唯一值并应用 IN 类型过滤器以及选定的值
多值字段唯一过滤；获取多值列的唯一值，并应用 IN 类型过滤器以及选定的值
任何列的 LIKE 过滤器，返回包含筛选值的列表或行
给定列的任何其他类型的过滤
=, >, >=, <, <=, <>
IN, CSV
LIKE
IS NULL, IS NOT NULL
BETWEEN, RANGE BETWEEN
对任何或所有列应用 ORDER BY
可以强制报表刷新其内容

如何使用这个引擎？

首先，我想提一下，下面显示的能够构建 XML 的网页不属于本文档的一部分。如果您已经构建了引擎可以解析的 XML，那么就可以了。

您可以从下面的链接下载一个示例数据库，其中包含引擎过程以及示例和演示报表。

http://www.2shared.com/file/LE86w69K/sample.html

示例报表中的数据由 Red Gate (SQL Data Generator 2) 的工具生成，因此报表没有实际意义，但其架构是真实的。因此，在开始实现网站部分之前，您可以先检查基本思路。

下载并恢复数据库
执行下面的示例或 test_* 过程。
手动修改 XML 以获得想要的结果集

更改跳过/提取的行数
在 @p4 部分包含 order by 列，遵循那里的语法
设置 is_display="0" 对于其中一些列

分页

下面的请求应返回第 61 行到第 120 行，并在单独的结果集中返回总行数
执行下面的示例后，您可以检查 Management Studio 中的消息。

由网站生成的 XML (test_paging 过程)

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
      step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="1" sub_step_type="SELECT" skip_rows="60" take_rows="60"/>
<row step_id="2" sub_step_id="2" sub_step_type="CNT"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="1" field="ClientName" direction="ASC"/>
<row step_id="2" sub_step_id="1" order_id="2" field="Sector" direction="ASC"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
')
declare @p7 xml
set @p7=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1"/>
')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="1" field_id="1" field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="2" field="ClientName" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="3" field="ClientKey" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="4" field="ClientTicker" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="5" field="Sector" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="6" field="Region" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="7" field="Country" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="8" field="IndexCode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="9" field="ParentCoverageCode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="10" field="Client52WeekLowSharePrice" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="11" field="Client52WeekHighSharePrice" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="12" field="ClientMarketCapUSD" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="13" field="PortfolioYN" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="14" field="ECMSinceFullVolumeUSD" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="15" field="ECMSinceDealCount" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="16" field="DealDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="17" field="DealSubType" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="18" field="DealSubTypeDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="19" field="DealValueTotal" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="20" field="BankRole" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="21" field="BankRoleDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="22" field="TranchOfferChangedPercentage" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="23" field="LastDealDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="24" field="DueDateTotal" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="25" field="DueDateECM" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="26" field="DueDateDCM" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="27" field="DueDateLoan" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="28" field="DealsInBacklog" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="29" field="LastShelfFiledDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="30" field="LastShelfDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="31" field="CONVMaturityDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="32" field="CONVMaturityDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="33" field="PutDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="34" field="PutDateDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="35" field="LockupExpiryDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="36" field="LockupExpiryDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="37" field="ECMFeesPaid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="38" field="ECMWalletPercent" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="39" field="ECMWalletRank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="40" field="ECMWalletLeadBank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="41" field="ECMWalletLeadBankDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="42" field="LOANFeesPaid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="43" field="LOANWalletPercent" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="44" field="LOANWalletRank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="45" field="LOANWalletLeadBank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="46" field="LOANWalletLeadBankDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="47" field="MNAFeesPaid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="48" field="MNAWalletPercent" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="49" field="MNAWalletRank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="50" field="MNAWalletLeadBank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="51" field="MNAWalletLeadBankDecode" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,
  @order_by=@p4,@where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

基于上述 XML 生成的 SQL 语句

if exists (select * from tempdb.sys.tables where name = 'EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE')
	drop table tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE

create table tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE ( rowid int not null identity(1,1) 
  primary key clustered, ParentEid int null,Sector varchar(128) null,Region varchar(128) null,
  Country varchar(128) null,IndexCode varchar(128) null,ParentCoverageCode varchar(128) null,
  ClientName nvarchar(512) null,ClientKey nvarchar(64) null,ClientTicker varchar(32) null,
  Client52WeekLowSharePrice float null,Client52WeekHighSharePrice float null,ClientMarketCapUSD float null,
  PortfolioYN varchar(1) null,ECMSinceFullVolumeUSD float null,ECMSinceDealCount int null,
  DealDate date null,DealSubType varchar(10) null,DealSubTypeDecode varchar(100) null,
  DealValueTotal float null,BankRole nvarchar(5) null,BankRoleDecode nvarchar(150) null,
  TranchOfferChangedPercentage float null,LastDealDealNumber bigint null,DueDateTotal float null,
  DueDateECM float null,DueDateDCM float null,DueDateLoan float null,DealsInBacklog int null,
  LastShelfFiledDate date null,LastShelfDealNumber bigint null,CONVMaturityDate date null,
  CONVMaturityDealNumber bigint null,PutDate date null,PutDateDealNumber bigint null,
  LockupExpiryDate date null,LockupExpiryDealNumber bigint null,ECMFeesPaid float null,
  ECMWalletPercent float null,ECMWalletRank int null,ECMWalletLeadBank varchar(1000) null,
  ECMWalletLeadBankDecode nvarchar(4000) null,MNAFeesPaid float null,MNAWalletPercent float null,
  MNAWalletRank int null,MNAWalletLeadBank varchar(1000) null,MNAWalletLeadBankDecode nvarchar(4000) null,
  LOANFeesPaid float null,LOANWalletPercent float null,LOANWalletRank int null,
  LOANWalletLeadBank varchar(1000) null,LOANWalletLeadBankDecode nvarchar(4000) null, )


declare @param1 varchar(10) = ( select top 1 value from 
  #where_value where step_id = 1 and sub_step_id = 1 and condition_id = 1 )
declare @param2 varchar(10) = ( select top 1 value from 
  #where_value where step_id = 1 and sub_step_id = 1 and condition_id = 2 )
declare @param3 date = ( select top 1 value from #where_value 
  where step_id = 1 and sub_step_id = 1 and condition_id = 3 )
declare @param4 date = ( select top 1 value from #where_value 
  where step_id = 1 and sub_step_id = 1 and condition_id = 4 )


insert into tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
exec usp_REP_sample @param1 = @param1,@param2 = @param2,@param3 = @param3,@param4 = @param4


select cast(RowId as int) as RowId, [ParentEid], [Sector], [Region], [Country], [IndexCode], 
  [ParentCoverageCode], [ClientName], [ClientKey], [ClientTicker], [Client52WeekLowSharePrice],
   [Client52WeekHighSharePrice], [ClientMarketCapUSD], [PortfolioYN], [ECMSinceFullVolumeUSD], 
   [ECMSinceDealCount], [DealDate], [DealSubType], [DealSubTypeDecode], [DealValueTotal], [BankRole], 
   [BankRoleDecode], [TranchOfferChangedPercentage], [LastDealDealNumber], [DueDateTotal], [DueDateECM], 
   [DueDateDCM], [DueDateLoan], [DealsInBacklog], [LastShelfFiledDate], [LastShelfDealNumber], 
   [CONVMaturityDate], [CONVMaturityDealNumber], [PutDate], [PutDateDealNumber], [LockupExpiryDate], 
   [LockupExpiryDealNumber], [ECMFeesPaid], [ECMWalletPercent], [ECMWalletRank], [ECMWalletLeadBank], 
   [ECMWalletLeadBankDecode], [MNAFeesPaid], [MNAWalletPercent], [MNAWalletRank], [MNAWalletLeadBank], 
   [MNAWalletLeadBankDecode], [LOANFeesPaid], [LOANWalletPercent], 
   [LOANWalletRank], [LOANWalletLeadBank], [LOANWalletLeadBankDecode]
from ( select rowid = row_number() OVER(order by [ClientName] ASC, [Sector] ASC), [ParentEid], 
  [Sector], [Region], [Country], [IndexCode], [ParentCoverageCode], [ClientName], [ClientKey], 
  [ClientTicker], [Client52WeekLowSharePrice], [Client52WeekHighSharePrice], [ClientMarketCapUSD], 
  [PortfolioYN], [ECMSinceFullVolumeUSD], [ECMSinceDealCount], [DealDate], [DealSubType], 
  [DealSubTypeDecode], [DealValueTotal], [BankRole], [BankRoleDecode], [TranchOfferChangedPercentage], 
  [LastDealDealNumber], [DueDateTotal], [DueDateECM], [DueDateDCM], [DueDateLoan], [DealsInBacklog], 
  [LastShelfFiledDate], [LastShelfDealNumber], [CONVMaturityDate], [CONVMaturityDealNumber], [PutDate], 
  [PutDateDealNumber], [LockupExpiryDate], [LockupExpiryDealNumber], [ECMFeesPaid], [ECMWalletPercent], 
  [ECMWalletRank], [ECMWalletLeadBank], [ECMWalletLeadBankDecode], [MNAFeesPaid], [MNAWalletPercent], 
  [MNAWalletRank], [MNAWalletLeadBank], [MNAWalletLeadBankDecode], [LOANFeesPaid], [LOANWalletPercent], 
  [LOANWalletRank], [LOANWalletLeadBank], [LOANWalletLeadBankDecode]
	   from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
	  ) res 

where RowId between 61 and 120
select count(*) as ROW_COUNT
from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE

字段唯一值

您可以通过以下命令轻松获取任何列的唯一值，其中已设置了一些过滤条件

由网站生成的 XML (test_distinct_values 过程)

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
        step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="1" sub_step_type="FILT"/>
<row step_id="2" sub_step_id="3" sub_step_type="DISTCOL"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="0" field="Region" direction="Asc"/>
<row step_id="2" sub_step_id="3" order_id="1" field="Country" direction="Asc"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
<row step_id="2" sub_step_id="1" condition_id="1" 
  field="TranchOfferChangedPercentage" operator="GREQUAL" condition="AND" bracket="0"/>
<row step_id="2" sub_step_id="1" condition_id="2" 
  field="LOANWalletLeadBank" operator="CSV" condition="AND" bracket="0"/>
<row step_id="2" sub_step_id="1" condition_id="3" 
  field="Region" operator="IN" condition="AND" bracket="0"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
<row step_id="2" sub_step_id="1" condition_id="1" value="60"/>
<row step_id="2" sub_step_id="1" condition_id="2" value="GS,CS"/>
<row step_id="2" sub_step_id="1" condition_id="3" value="Confections"/>
<row step_id="2" sub_step_id="1" condition_id="3" value="Seafood"/>')
declare @p7 xml
set @p7=convert(xml,N'<row id="1" step_id="1" sub_step_id="1"/>')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="2" field_id="1" 
  field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="3" field_id="1" 
  field="Country" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,
   @order_by=@p4,@where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

基于上述 XML 生成的 SQL 语句

declare @param_2_1_1 float = ( select top 1 value from 
  #where_value where step_id = 2 and sub_step_id = 1 and condition_id = 1 )


create table #t_REP_filtered ( RowId int not null primary key clustered, 
  ParentEid int null,Country varchar(128) null, )
create table #t_FILT_params_2_1_2 (LOANWalletLeadBank varchar(1000))


insert into #t_FILT_params_2_1_2 (LOANWalletLeadBank)
select distinct lst.Item from #where_value wv  cross apply [dbo].[clr_split2string] 
  (wv.value, ',') lst where wv.step_id = 2 and sub_step_id = 1 and wv.condition_id = 2


insert into #t_REP_filtered
select cast(RowId as int) as RowId, [ParentEid], [Country]
from ( select rowid = row_number() OVER(order by [Region] ASC), [ParentEid], [Country]
	   from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
       where [TranchOfferChangedPercentage] >= @param_2_1_1
AND [LOANWalletLeadBank] is not null and exists (select * from [dbo].[clr_split2string] 
  (LOANWalletLeadBank, ',') split where exists ( select * from #t_FILT_params_2_1_2 
  as tmp_2_1_2 where split.item = tmp_2_1_2.LOANWalletLeadBank))
AND [Region] IN (select value from #where_value where step_id = 2 and sub_step_id = 1 and condition_id = 3)
	  ) res 

select distinct [Country]
from #t_REP_filtered
order by [Country] ASC

多值字段唯一值

其中一些列包含合并的行，但我们希望根据拆分的值进行过滤

由网站生成的 XML (test_distinct_multi_values 过程)

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
  step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="2" sub_step_type="DISTMVCOL"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="1" field="ClientName" direction="ASC"/>
<row step_id="2" sub_step_id="1" order_id="2" field="Sector" direction="ASC"/>
<row step_id="2" sub_step_id="2" order_id="1" field="ECMWalletLeadBank" direction="Asc"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
')
declare @p7 xml
set @p7=convert(xml,N'<row id="1" step_id="1" sub_step_id="1"/>')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="1" field_id="1" 
  field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="2" field_id="1" 
  field="ECMWalletLeadBank" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,
  @order_by=@p4,@where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

基于上述 XML 生成的 SQL 语句

select distinct Item as [ECMWalletLeadBank]
from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
cross apply [dbo].[clr_split2string] (isnull([ECMWalletLeadBank],''),',') 
order by [ECMWalletLeadBank] ASC

列上的 LIKE 过滤器

我们应该能够根据给定的过滤条件检索不同的行，这意味着通过输入 3 个字符，我们转到数据库以获取所有 rowid 和该列的匹配值，通过拥有 rowid，我们可以轻松导航到该页面。

由网站生成的 XML (test_like_search 过程)

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
  step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="1" sub_step_type="POSTFILT"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="1" field="ClientName" direction="ASC"/>
<row step_id="2" sub_step_id="1" order_id="2" field="Sector" direction="ASC"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
<row step_id="2" sub_step_id="1" condition_id="1" field="ClientName" 
  operator="LIKE" condition="AND" bracket="0"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
<row step_id="2" sub_step_id="1" condition_id="1" value="tru"/>
')
declare @p7 xml
set @p7=convert(xml,N'<row id="1" step_id="1" sub_step_id="1"/>')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="1" field_id="1" 
  field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="2" 
  field="ClientName" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,@order_by=@p4,
  @where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

基于上述 XML 生成的 SQL 语句

declare @param_2_1_1 nvarchar(512) = ( select top 1 value from 
  #where_value where step_id = 2 and sub_step_id = 1 and condition_id = 1 )


select RowId = min(RowId), [ParentEid], [ClientName]
from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
where [ClientName] LIKE '%' + @param_2_1_1 + '%'
group by [ParentEid], [ClientName]
order by [ClientName] ASC

兴趣点

在我的环境中，第一次生成耗时 5 秒，任何其他请求的耗时在 0.2-0.7 秒之间。

创建一个允许在报表中进行一些动态使用的此类方法，同时又要避免 SQL 注入，最重要的是要非常快，这是一项挑战。

我非常好奇地想看看还有谁能在他们的实际环境中应用这种方法和引擎。如果您需要任何额外的文档、帮助，或者对如何扩展此引擎有任何想法，请发送电子邮件至 kladna@hotmail.com。