williambirkin

恭喜发财!

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

我今天测试一条语句。
代码如下:
 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

posted on 2008-06-30 10:49  williambirkin  阅读(1043)  评论(3编辑  收藏  举报