我今天测试一条语句。
代码如下：
WITH rList(ID,DBID,ITEM_CODE,BCode,TCode,Quantity,OrderDate,DB_Level,OID)
AS
(
SELECT 5872231,PSO.DBID,PSO.ITEM,UpperWS,
HPCode,CONVERT(numeric(18, 0),sum(QTY)),OEDate,
Convert(varchar,'final'),0
FROM PSO
WHERE ID=5872231
OR EXISTS (SELECT NEW_PSO_ID FROM RETURNDETAIL WHERE OLD_PSO_ID=5872231 AND PSO.ID =NEW_PSO_ID)
GROUP BY PSO.DBID,PSO.ITEM ,UpperWS,HPCode,OEDate
UNION ALL
SELECT DB.ID,DB.BDB,DB.ITEM,UpperWS,ToWS,QTY,DBDate,Convert(varchar,DB.DB_LEVEL),rList.OID+1
FROM DB
INNER JOIN rList ON DB.ID=rList.DBID
)
SELECT ID,DBID,rList.ITEM_CODE,ITEM.ITEM_LNAME_ZH AS INAME,CUSTOMER.CUST_NAME AS BName,CUSTOMER_1.CUST_NAME AS TName,Quantity,OrderDate,DB_Level,OID
FROM rList
INNER JOIN CUSTOMER ON rList.BCode =CUSTOMER.CUST_CODE
INNER JOIN CUSTOMER AS CUSTOMER_1 ON rList.TCode =CUSTOMER_1.CUST_CODE
INNER JOIN ITEM ON rList.ITEM_CODE = ITEM.ITEM_CODE
ORder by OID OPTION(MAXRECURSION 10)
其中pso是个有500万行数据的销售数据表。DB也是一个有300万行数据的表。ADJUSTDETAIL 里的数据很少，不超过100行。
刚开始没有exists（红色这行）语句,速度很快不要1秒钟。
而加了exists语句后执行时间增加为16秒。其实exists中子查询出的数据只有几行。如果吧exists语句换成OR ID=xx OR ID=xxx，速度也会很快了（不到一秒）。
后来我也试过用ID IN (子查询) 的写法。但是速度和exists的差不多。
所以问题处在exists这。我觉得问题主要是要将PSO的500万条数据的ID拿到子查询中去查一遍。这个处理消耗了大量的时间，所以很慢。
而OR ID=XX OR ID=XX 的写法是直接使用索引的，所以快很多。
但是我暂时想不到好的写法去解决这个问题。后来做了个无聊的得事情。
先将子查询执行出来，然后用游标循环拼出OR ID=XX OR ID=XXX的语句，然后再执行动态sql语句。
虽然中间增加了些处理，但是整体速度没有什么影响。查询执行时间还是在一秒内。
但是这样写毕竟是不规范的。不知道有没有什么好的写法能解决这个问题。
查询计划
https://files.cnblogs.com/williambirkin/WinLWControl.rar
表结构
/****** Object: Table [dbo].[RETURNDETAIL] Script Date: 06/30/2008 18:44:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RETURNDETAIL](
[RETURN_DETAIL_ID] [int] IDENTITY(1,1) NOT NULL,
[OLD_PSO_ID] [int] NOT NULL,
[NEW_PSO_ID] [int] NOT NULL,
[RETURN_CODE] [varchar](3) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ADD_DATE] [datetime] NOT NULL CONSTRAINT [DF_RETURNDETAIL_ADD_DATE] DEFAULT (getdate()),
[ADD_USER] [varchar](36) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MOD_DATE] [datetime] NULL,
[MOD_USER] [varchar](36) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_RETURNDETAIL] PRIMARY KEY CLUSTERED
(
[RETURN_DETAIL_ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[DB] Script Date: 06/30/2008 18:44:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FrWS] [char](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UpperWS] [char](6) COLLATE Chinese_PRC_CI_AS NULL,
[ToWS] [char](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ITEM] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[QTY] [numeric](18, 0) NOT NULL,
[DBDate] [datetime] NOT NULL,
[InpDate] [datetime] NOT NULL,
[BDB] [int] NULL,
[DEL] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[SQTY] [numeric](18, 0) NULL,
[PRICE] [money] NULL,
[SENDER] [char](3) COLLATE Chinese_PRC_CI_AS NULL,
[LSTUPD] [datetime] NULL,
[EDI] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[TTID] [char](2) COLLATE Chinese_PRC_CI_AS NULL,
[DB_LEVEL] [int] NULL,
[pcdCutOffFlag] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[dbModUser] [char](10) COLLATE Chinese_PRC_CI_AS NULL,
[dbModDate] [datetime] NULL,
[AddUser] [nvarchar](36) COLLATE Chinese_PRC_CI_AS NULL,
[AddDate] [datetime] NULL,
[ModUser] [nvarchar](36) COLLATE Chinese_PRC_CI_AS NULL,
[ModDate] [datetime] NULL,
CONSTRAINT [PK_DB] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[PSO] Script Date: 06/30/2008 18:43:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PSO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DBID] [int] NULL,
[DBTYPE] [varchar](1) COLLATE Chinese_PRC_CI_AS NULL,
[WSCode] [char](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UpperWS] [char](6) COLLATE Chinese_PRC_CI_AS NULL,
[HPCode] [char](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ITEM] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[QTY] [numeric](18, 0) NOT NULL,
[PRICE] [money] NULL,
[SalesID] [char](3) COLLATE Chinese_PRC_CI_AS NOT NULL,
[OEDate] [datetime] NOT NULL,
[CFM] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[ADJ] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[Memo] [char](100) COLLATE Chinese_PRC_CI_AS NULL,
[InpDate] [datetime] NOT NULL,
[EDI] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[TTID] [char](2) COLLATE Chinese_PRC_CI_AS NULL,
[pcdCutOffFlag] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[psoModUser] [char](10) COLLATE Chinese_PRC_CI_AS NULL,
[psoModDate] [datetime] NULL,
[AddUser] [nvarchar](36) COLLATE Chinese_PRC_CI_AS NULL,
[AddDate] [datetime] NULL,
[ModUser] [varchar](36) COLLATE Chinese_PRC_CI_AS NULL,
[ModDate] [datetime] NULL,
CONSTRAINT [PK_PSO] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。