MMSQ中关于In的执行效率问题求调优
--组织结构表USE [yc_hw_ems]/****** 对象: Table [dbo].[t_ems_organization] 脚本日期: 06/29/2012 15:38:21 ******/CREATE TABLE [dbo].[t_ems_organization]( [id] [varchar](255) COLLATE Chinese_PRC_CI_AS NOT NULL primary key, [dep_name] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--部门名称 [dep_no] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--部门编号 [remarks] [text] COLLATE Chinese_PRC_CI_AS NULL,--备注 [parent_id] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--上级部门ID [state] [int] NULL,--记录状态 0，1，2 。0为已删除 [indexs] [int] NULL,索引编号）--设备表USE [yc_hw_ems]/****** 对象: Table [dbo].[t_ems_equi_accounting] 脚本日期: 06/29/2012 15:41:16 ******/CREATE TABLE [dbo].[t_ems_equi_accounting]( [id] [varchar](255) COLLATE Chinese_PRC_CI_AS NOT NULL primary key, [code] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--设备编码 [model] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--型号 [brand] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--品牌 [unit] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--数量 [number] [int] NULL, [install_company] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL, [position] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL, [install_date] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [money] [float] NULL, [warranty] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL, [remarks] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL, [dept_id] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--部门id)
DECLARE @Name VARCHAR(800)SET @Name = '运城公司';WITH T AS( SELECT ID , PID , dep_name ,dep_no,indexs FROM dbo.t_ems_organization WHERE dep_name = @Name and state=1 UNION all SELECT A.ID , A.PID , A.dep_name ,A.dep_no,A.indexs FROM dbo.t_ems_organization AS A JOIN T AS B ON B.ID = A.PID and A.state =1)SELECT equi_name,code,brand,model,unit,ISNULL(money,0) AS 'money',install_date,install,remarks,station_num FROM dbo.t_ems_equi_accounting as a inner join dbo.t_ems_equi_accounting a1 on a.id = a1.id inner join T t1 on a1.station_num=t1.dep_no where a.state <> 0 and create_date between '2011-01-11' and dateadd(millisecond, -3, cast('2012-11-11' as datetime)+1)