你的位置:首页 > 数据库

[数据库]通过拆分字段优化SQL

数据库环境:SQL SERVER 2008R2

  今天看到一条用函数处理连接的SQL,是群里某位网友的,SQL语句如下:

SELECT SO_Order.fdate ,    SO_Order.fsnFROM  so_order    INNER JOIN so_orderitem ON CHARINDEX(so_Orderitem.fid, SO_Order.fid) >= 1WHERE  so_order.FOrderDate = '2015-09-06'

View Code

  语句不算复杂,只是执行比较慢,下面是关于这SQL语句的一些信息:

--1.SQL执行203条数据
--2.耗时12秒
--3.so_order表的fid字段是字符串集合,--由1-2个字符串组成,用','分隔SELECT COUNT(*)FROM SO_OrderWHERE so_order.FOrderDate = '2015-09-06'--24SELECT COUNT(*)FROM so_Orderitem--414154

  执行计划

  通过分析执行计划,so_order和so_orderitem走嵌套循环是正确的,查询的瓶颈是在so_orderitem的索引

扫描上。因此,我们要通过改写SQL,达到so_orderitem走索引查找的目的。

  将so_order表的fid字段拆分成2个字段,然后union all成一个结果集,再和so_orderitem关联,即可让

so_orderitem表走索引查找。改写的SQL如下:

;WITH  x0     AS ( SELECT  fdate ,            fsn ,            LEFT(fid, CASE WHEN CHARINDEX(',', fid, 1) = 0 THEN 0                    ELSE CHARINDEX(',', fid, 1) - 1                 END) AS fid        FROM   so_order        WHERE  FOrderDate = '2015-09-06'        UNION ALL        SELECT  fdate ,            fsn ,            RIGHT(fid, LEN(fid) - CHARINDEX(',', fid, 1)) AS fid        FROM   so_order        WHERE  FOrderDate = '2015-09-06'       )  SELECT SO_Order.fdate ,      SO_Order.fsn  FROM  x0 so_order      INNER JOIN so_orderitem ON so_Orderitem.fid = SO_Order.fid

View Code

  下面是SQL改写后,网友的反馈截图