SQL优化：对报表生成工具的改进

之所以写这篇文章，是因为很多时候，优化不仅仅涉及到自己写的sql，数据库索引、统计信息、分表、防阻塞等。

当一部分sql语句是程序生成的，我也改不了，无能为力了，那么优化就真的遇到了大问题了，这个时候必须要修改程序。

但是吧，程序也不是我说改，架构师就能给改的，我给发了多个邮件，指出了具体方案，但不知道是别人太忙，还是什么其他的原因，根本不鸟我，我算老几。。。

所以，很多时候是不了了之，最后还好客户也对结果基本满意，就勉强过去了。。。

首先，说一下公司的报表生成工具的运转过程：

1、通过在前端web界面中进行选择，最后把选项保存到数据库中。

2、客户选择查询条件后，点击查询，把查询要求提交到应用应用程序，然后，程序根据客户提交的这些选项，动态生成sql语句。

3、应用程序把sql语句发送到数据库，数据库执行sql，返回查询结果。

4、应用按照之前设置保存的的报表样式，进行格式化，把格式化后的网页，发送到客户的浏览器上，最后浏览器显示这个页面。

如果不对报表工具本身进行修改，sql调优会有很大的限制。。。

一、工具所生成的sql语句，受到预先写好的主题sql的限制，这里所谓主题sql，可以简单理解为就是一段纯sql（除sql中的一些可被实际条件值替换的文本标签外，类似于{:business_date}，都是静态的sql），不能是t-sql语句，
可以在一个编辑框中修改、运行这段sql，返回前N行数据作为预览。

主题sql类似于：
SELECT * FROM TB_SALE_DATA


而报表代码类似于这样：

select c.sale_name,
       c.sale_id,
  c.amount
from TB_SALE_DATA c
inner join TB_CUBE_CUSTOMER TCS      --客户维度表
ON C.CUSTOMER_ID = TCS.CUSTOMER_ID
inner join TB_CUBE_ORGANIZATION TCS  --销售维度表
ON C.EMPLOYEE_ID = TCS.EMPLOYEE_ID


二、现在的问题是如何才能让主题sql也可以是t-sql呢？

实际上让主题是t-sql代码是很容易的，没什么难度，关键的问题是，如果把代码改为t-sql之后，报表如何生成sql代码，想了想，就是应用在运行代码的时候，必须要加上select * from #temp这个。

原始的主题代码如下（代码1）：
--cube sql 
declare @t int=1;

select * into #temp
from TB_SALE_DATA
where sale_type = @t {and sale_name = :name} --这里的{and name = :name}在没有传入实际值时，会被替换为空串

在主题编辑框中运行时，应用必须要生成这样的代码，才能运行：
--cube sql 
declare @t int=1;

select * into #temp
from TB_SALE_DATA
where sale_type = @t {and sale_name = :name} --这里的{and name = :name}在没有传入实际值时，会被替换为空串

select * from #temp  --增加了这个sql，不然没办法运行 


三、系统如何来生成上面多表关联的报表代码呢？
要实现这种扩展，可以在主题中增加一个额外的指定取数的设置选项，也就说一个编辑框里可以填写 t-sql代码，另外一个设置项里输入#temp，这样就指明了可以从#temp里取数，相当于指定了数据源，类似于：

select c.sale_name,
       c.sale_id,
  c.amount
from #temp c
inner join TB_CUBE_CUSTOMER TCS      --客户维度表
ON C.CUSTOMER_ID = TCS.CUSTOMER_ID
inner join TB_CUBE_ORGANIZATION TCS  --销售维度表
ON C.EMPLOYEE_ID = TCS.EMPLOYEE_ID

最后，生成的报表的代码就是：
--cube sql start
declare @t int=1;

select * into #temp
from sys.tables
where sale_type = @t and sale_name = 'vc'
--cube sql end


--report sql
select c.sale_name,
       c.sale_id,
  c.amount
from #temp c
inner join TB_CUBE_CUSTOMER TCS      --客户维度表
ON C.CUSTOMER_ID = TCS.CUSTOMER_ID
inner join TB_CUBE_ORGANIZATION TCS  --销售维度表
ON C.EMPLOYEE_ID = TCS.EMPLOYEE_ID