SQL field splitting optimization and SQL field splitting

SQL field splitting optimization and SQL field splitting

Today, a function is used to process the connected SQL statement, which is from a group of netizens. The SQL statement is as follows:

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'

The statement is not complex, but execution is slow. The following information about the SQL statement:

-- 1. SQL executes 203 data records

-- 2. It takes 12 seconds

-- 3. the fid field in the so_order table is a string set,

-- It consists of 1-2 strings separated by commas (,).

SELECT COUNT(*)FROM SO_OrderWHERE so_order.FOrderDate = '2015-09-06'--24SELECT COUNT(*)FROM so_Orderitem--414154

By analyzing the execution plan, it is correct that so_order and so_orderitem take the nested loop. the bottleneck of the query is the index of so_orderitem.

Scan. Therefore, we need to rewrite the SQL statement to achieve the goal of using so_orderitem for index search.

Split the fid field of the so_order table into two fields, union all into a result set, and associate it with so_orderitem, so that the so_orderitem table can be searched by index.

The rewritten SQL statement is as follows:

;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

Below is the feedback from netizens after SQL Rewriting

The SQL Execution Plan after rewriting is as follows: