Click here to Skip to main content
15,867,568 members
Articles / Security

T-SQL reporting engine

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
20 Jun 2013CPOL6 min read 24.1K   21   8
If you have big reports which run slowly, you may use this T-SQL based engine to speed-up them.

Introduction 

In my formal company we try to find different innovative or not standard solutions for warehousing and reporting. Most of our reports are built by an XML definition from which an ad-hoc query are built; however some of the reports are near static and the logic of these are really complex and therefore implemented in stored procedure. Despite all this the report generation time is reaching 30 sec and the expected is 1 sec.

The characteristics of the report are to return around 50.000 rows and 50 columns. Basically all columns can be filtered and attended in a sorting condition; six columns are a multi-value which means an eventually 10 rows are merged into a single comma separated column, moreover any column can be hidden from the result.

If you think about the above requirements these should lead to a slow reporting, however some environmental conditions ease the situation (the database used like a warehouse in which the data changed only once a day).

By taking in account the solutions one stands out from the many, namely to store the result of the procedure into a table and apply some kind of filtering on it by a given metadata and return just the requested page with 10-100 rows.

Let's see what kind of disadvantages of the big report can overcome by storing the result into a table (the name is a unique hash calculated from various parameters):

  • Fetching out 2.5 million of cells what take some seconds 
  • High network usage (20 concurrent report = 800 MB of data transfer)
  • High memory usage of the web services since 1 report engrosses 200-1000 MB.

Background

What kind of logic we can use to boost the report performance?

  • The report is generated by a stored procedure
    • Reuse of execution plan
    • Complex logic can be encapsulated
    •  By parameters can be included some dynamics 
  • The result of the procedure stored into a unique hash named table
    • By obsolescence check the report does not created just once daily (depends on the threshold) so the slowest part which is the generation can be neglected 
    • Any kind of filtering, ordering can be easily applied 
  • Procedure which generates the report is invoked by a manager procedure which is capable to interpret the meta-XML and validate its content
    • The SQL statement generated is injection free- Generic, can be used for any kind of report built by a procedure
    • Meta tables available for validation

Image 1

Report generation steps 

Webpage has to build up 6 XML which contains Meta information related to the report and eventually for subset of that.

  1. SQL-Report-Engine procedure has to be executed with these XML’s 
  2. XML’s are saved into temp tables (SQL can handle this natively)   
  3. Steps and sub steps  defined in the XML are interpreted 
  4.  During the SQL statement generation the injection is not allowed and by executing this we can get back the result set of the report 
  5. Concurrent generation handled in transaction 

The two main steps:

  • Build the report and store result into a table
  • Apply any kind of FILTERING/ORDERING/CHUNKING/DISPLAYING on the previously built table

Many sub steps:

  • The first step have just one single sub step which is the report building
  • The second step can contains the following sub step commands which have to build the appropriate SQL statements: 
    • FILT
    • POSTFILT
    • CNT
    • SELECT
    • DISTCOL
    • DISTMVCOL
  • Each sub step has his own proper command in the specified XML 
  • A where clause can have multiple where value 

In the following diagram the green lines represents the validation chains of the report.

Image 2

Report validation and meta info

During the report execution we should able to validate the content of the XML.

There are three tables which contain meta information about the report:

  • Report name (stored procedure to execute) 
  • Stored procedure parameters and types
  • Report result-set description, column names and their types

Enlargement policy

Extending the report or creating a new one is very simple just follow the next rules:

  • Create/Modify the stored procedure which return the report and include/exclude columns, eventually add/remove parameters 
  • Add procedure name into dw_report_type table 
  • Add/Remove parameters into/from dw_report_params table 
  • Add/Remove columns into/from dw_report_result table

Report functions   

The report on the website possess with the below functionalities:

  • Paging; return rows requested from the result-set (ex: skip 500, take 50) 
  • Distinct-field filtering; get distinct values of a column and apply IN-kind of filter with selected values
  • Distinct-multi-value filtering; get distinct values of the multi-value column, and apply IN-kind of filtering with selected values 
  • LIKE-filter for any column, return a list or rows which contains the filter value 
  • Any other kind of filtering for a given column:
    =, >, >=, <, <=, <>
    IN, CSV
    LIKE
    IS NULL, IS NOT NULL
    BETWEEN, RANGE BETWEEN 
  • Apply ORDER BY on any or all column 
  • Report can be forced to refresh its content

How to use this engine?      

First of all I would like to mention that the web-page which can build XML's displayed below, does not take part of this article. If you have already built the XML that can be interpreted by the engine presented here.

You can download a sample database from the below link which contains the engine-procedures and the samples as well with a demo report.  

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

The data in the sample report was generated by a tool from Red Gate (SQL Data Generator 2), so the report do not have any meanings, however the schema is a real one.  So you can check the basic idea before you start to implement the website part. 

  1. Download and restore the database 
  2. Execute the below samples or test_* procedures. 
  3. Modify the XML manually to get back the result-set what you want
    • change skip/take rows 
    • include order by columns in the @p4 part following the syntax applied there 
    • set is_display="0" for some of the columns  

PAGING  

The below request have to return rows between 61 and 120 and the total row numbers in separate result-set
You may check the messages in the Management Studio after executing the below samples. 

 Image 3

XML generated by the WEBSITE (test_paging procedure) 

SQL
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
  

SQL statement generated based on the above 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 

Distinct fields 

You can get easily the distinct values of any column by the following command, there are some filtering conditions already set

Image 4

XML generated by the Website (test_distinct_values procedure) 

SQL
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  

SQL statement generated based on the above 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

Distinct multi-value fields  

Image 5

Some of the columns contains consolidated rows, however we would like to filter on the dismantled values

XML generated by the WEBSITE (test_distinct_multi_values procedure) 

SQL
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 

SQL statement generated based on the above 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 filter on a column 

We should able to retrieve different rows of a given filtering condition, that means by typing 3 characters we go to database to get all rowids and the matching values of that column, by having the rowid we easily can navigate to that page.

 Image 6

XML generated by the WEBSITE (test_like_search procedure)

SQL
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 

SQL statement generated based on the above 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   

Points of Interest 

In my environment the first generation time took 5 sec. and any other request is between 0.2-0.7 sec.

It was challenging to create such kind of method which allow some dynamic usage within the report, also to be SQL-Injection free and most importantly to be really fast.

I am very curious to see who else can apply this method and engine in their live environment. If you need any additional documentation, help or you have any ideas about how to extend this engine please drop me an email at kladna@hotmail.com.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer
Hungary Hungary
2010-2012 - DB. Architect & Senior DB. Developer - www.livejasmin.com
2008-2010 - Senior DB. Developer - www.logmein.com
2006-2008 - DBA & DB. Developer - www.eurotaxglass.co.uk

Comments and Discussions

 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Jul-13 20:45
professionalȘtefan-Mihai MOGA13-Jul-13 20:45 
GeneralRe: My vote of 5 Pin
Antonio Suarez25-Apr-14 21:17
Antonio Suarez25-Apr-14 21:17 
QuestionIt is really hard ... for me Pin
martin.nedopil20-Jun-13 0:59
martin.nedopil20-Jun-13 0:59 
AnswerRe: It is really hard ... for me Pin
Levente Kupás20-Jun-13 1:38
Levente Kupás20-Jun-13 1:38 
QuestionWhere is source? Pin
martin.nedopil19-Jun-13 1:23
martin.nedopil19-Jun-13 1:23 
AnswerRe: Where is source? Pin
Levente Kupás19-Jun-13 2:43
Levente Kupás19-Jun-13 2:43 
GeneralRe: Where is source? Pin
martin.nedopil19-Jun-13 23:02
martin.nedopil19-Jun-13 23:02 
Sorry, I`m dumb today due to overheating. Can you publish sample page for testing your idea? I`m totally lost in your code and not able to understand way your application works.
Thanks a lot
Martin
GeneralRe: Where is source? Pin
Levente Kupás20-Jun-13 0:25
Levente Kupás20-Jun-13 0:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.