Select into a temporary table efficiency is faster than direct query-CodePudding

Case:

Query below sql need 10.56 sec, and 236 rows were queried.

select 
    t2.dep,t1.*
from (
    select * from hct_dailywork with (nolock)  where date = '2021-09-28'
) T1
left join mf_mo T2 with (nolock) on T1.mo_no = T2.mo_no

Estimated Execution Plan:
Most cost:

After I try to use select into temporary table then it just need 0.03 sec

select * into #T from (
    select * from hct_dailywork with (nolock)  where date = '2021-09-28'
)T;

select 
    t2.dep,t1.*
from #T T1
left join mf_mo T2 with (nolock) on T1.mo_no = T2.mo_no

Estimated Execution Plan:

Then I've tried add below index, but it still no change.

create index idx_hct_dailywork_mo_no on hct_dailywork (mo_no)
create unique index ak_hct_dailywork_mo_no on hct_dailywork (id,mo_no);

hct_mlots table create sql，total rows 88790

CREATE TABLE [dbo].[Hct_DailyWork](
    [ID] [uniqueidentifier] NOT NULL,
    [emp_no] [nvarchar](200) NOT NULL,
    [mo_no] [nvarchar](200) NOT NULL,
    [date] [nvarchar](200) NOT NULL,
    [shift] [nvarchar](200) NOT NULL,
    [tz_no] [nvarchar](200) NOT NULL,
    [human_worktime] [decimal](20, 5) NULL,
    [eqp_worktime] [decimal](20, 5) NULL,
    [human_num] [decimal](20, 5) NULL,
    [pass_num] [decimal](20, 5) NULL,
    [ng_num] [decimal](20, 5) NULL,
    [ng_reason] [nvarchar](200) NULL,
    [flag] [char](1) NOT NULL,
    [log] [nvarchar](max) NULL,
    [createtime] [datetime] NOT NULL,
    [rem] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Hct_DailyWork] ADD  DEFAULT (newsequentialid()) FOR [ID]
GO

ALTER TABLE [dbo].[Hct_DailyWork] ADD  DEFAULT ('N') FOR [flag]
GO

ALTER TABLE [dbo].[Hct_DailyWork] ADD  DEFAULT (sysdatetime()) FOR [createtime]
GO

create index idx_hct_dailywork_mo_no on hct_dailywork (mo_no)
create unique index ak_hct_dailywork_mo_no on hct_dailywork (id,mo_no); 
GO

mf_mo table create SQL

CREATE TABLE [dbo].[MF_MO](
    [MO_NO] [varchar](20) NOT NULL,
    [MO_DD] [datetime] NULL,
    [STA_DD] [datetime] NULL,
    [END_DD] [datetime] NULL,
    [BIL_ID] [varchar](2) NULL,
    [BIL_NO] [varchar](20) NULL,
    [MRP_NO] [varchar](30) NULL,
    [PRD_MARK] [varchar](40) NULL,
    [WH] [varchar](12) NULL,
    [SO_NO] [varchar](20) NULL,
    [UNIT] [varchar](1) NULL,
    [QTY] [numeric](22, 8) NULL,
    [QTY1] [numeric](22, 8) NULL,
    [NEED_DD] [datetime] NULL,
    [DEP] [varchar](8) NULL,
    [CUS_NO] [varchar](12) NULL,
    [CLOSE_ID] [varchar](1) NULL,
    [USR] [varchar](12) NULL,
    [CHK_MAN] [varchar](12) NULL,
    [BAT_NO] [varchar](40) NULL,
    [REM] [text] NULL,
    [PO_OK] [varchar](1) NULL,
    [MO_NO_ADD] [varchar](38) NULL,
    [QTY_FIN] [numeric](22, 8) NULL,
    [QTY_FIN_UNSH] [numeric](22, 8) NULL,
    [TIME_AJ] [numeric](22, 8) NULL,
    [QTY_ML] [numeric](22, 8) NULL,
    [QTY_ML_UNSH] [numeric](22, 8) NULL,
    [BUILD_BIL] [text] NULL,
    [CST_MAKE] [numeric](22, 8) NULL,
    [CST_PRD] [numeric](22, 8) NULL,
    [CST_OUT] [numeric](22, 8) NULL,
    [CST_MAN] [numeric](22, 8) NULL,
    [USED_TIME] [numeric](22, 8) NULL,
    [CST] [numeric](22, 8) NULL,
    [PRT_SW] [varchar](1) NULL,
    [OPN_DD] [datetime] NULL,
    [FIN_DD] [datetime] NULL,
    [BIL_MAK] [varchar](1) NULL,
    [CPY_SW] [varchar](1) NULL,
    [CONTRACT] [varchar](80) NULL,
    [EST_ITM] [int] NULL,
    [ML_OK] [varchar](1) NULL,
    [MD_NO] [varchar](400) NULL,
    [QTY_RK] [numeric](22, 8) NULL,
    [QTY_RK_UNSH] [numeric](22, 8) NULL,
    [CLS_DATE] [datetime] NULL,
    [ID_NO] [varchar](38) NULL,
    [QTY_CHK] [numeric](22, 8) NULL,
    [CONTROL] [varchar](1) NULL,
    [ISNORMAL] [varchar](1) NULL,
    [QC_YN] [varchar](1) NULL,
    [MM_CURML] [varchar](1) NULL,
    [TS_ID] [varchar](1) NULL,
    [BIL_TYPE] [varchar](2) NULL,
    [CNTT_NO] [varchar](40) NULL,
    [MOB_ID] [varchar](2) NULL,
    [LOCK_MAN] [varchar](12) NULL,
    [LOCK_DATE] [datetime] NULL,
    [SEB_NO] [varchar](200) NULL,
    [GRP_NO] [varchar](20) NULL,
    [OUT_DD_MOJ] [datetime] NULL,
    [SYS_DATE] [datetime] NULL,
    [PG_ID] [varchar](1) NULL,
    [SUP_PRD_NO] [varchar](40) NULL,
    [TIME_CNT] [numeric](22, 8) NULL,
    [ML_BY_MM] [varchar](1) NULL,
    [CAS_NO] [varchar](20) NULL,
    [TASK_ID] [int] NULL,
    [OLD_ID] [varchar](1) NULL,
    [CF_ID] [varchar](1) NULL,
    [CUS_OS_NO] [varchar](30) NULL,
    [PRT_USR] [varchar](12) NULL,
    [QTY_QL] [numeric](22, 8) NULL,
    [QTY_QL_UNSH] [numeric](22, 8) NULL,
    [QL_ID] [varchar](1) NULL,
    [Q2_ID] [varchar](1) NULL,
    [Q3_ID] [varchar](1) NULL,
    [ISSVS] [varchar](1) NULL,
    [QTY_DM] [numeric](22, 8) NULL,
    [QTY_DM_UNSH] [numeric](22, 8) NULL,
    [CST_MAN_ML] [numeric](22, 8) NULL,
    [CST_MAK_ML] [numeric](22, 8) NULL,
    [CST_PRD_ML] [numeric](22, 8) NULL,
    [CST_OUT_ML] [numeric](22, 8) NULL,
    [CST_ML] [numeric](22, 8) NULL,
    [LOCK] [varchar](1) NULL,
    [QTY_LOST] [numeric](22, 8) NULL,
    [QTY_LOST_UNSH] [numeric](22, 8) NULL,
    [ISFROMQD] [varchar](1) NULL,
    [ZT_ID] [varchar](1) NULL,
    [ZT_DD] [datetime] NULL,
    [CV_ID] [varchar](1) NULL,
    [CU_NO] [varchar](20) NULL,
    [QTY_CHK_UNSH] [numeric](22, 8) NULL,
    [CANCEL_ID] [varchar](1) NULL,
    [SUP_PRD_MARK] [varchar](40) NULL,
    [PRT_DATE] [datetime] NULL,
    [BJ_NO] [varchar](20) NULL,
    [MODIFY_DD] [datetime] NULL,
    [MODIFY_MAN] [varchar](12) NULL,
    [DEC_UN] [int] NULL,
    [QTY_QS] [numeric](22, 8) NULL,
    [QTY_QS_UNSH] [numeric](22, 8) NULL,
    [BACK_ID] [varchar](2) NULL,
    [QTY_PG] [numeric](22, 8) NULL,
    [QTY_PG_UNSH] [numeric](22, 8) NULL,
    [mlotcount] [int] NULL,
    [pass_mis_mm_before_check] [char](1) NULL,
    [MIS_BAT_NO] [nvarchar](40) NULL,
 CONSTRAINT [PK__MF_MO] PRIMARY KEY CLUSTERED 
(
    [MO_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MF_MO] ADD  CONSTRAINT [DF_MF_MO_PRD_MARK]  DEFAULT (space((0))) FOR [PRD_MARK]
GO

ALTER TABLE [dbo].[MF_MO] ADD  CONSTRAINT [DF_MF_MO_QTY_FIN]  DEFAULT ('0') FOR [QTY_FIN]
GO

ALTER TABLE [dbo].[MF_MO] ADD  CONSTRAINT [DF_MF_MO_QTY_FIN_UNSH]  DEFAULT ('0') FOR [QTY_FIN_UNSH]
GO

ALTER TABLE [dbo].[MF_MO] ADD  CONSTRAINT [DF_MF_MO_QTY_ML]  DEFAULT ('0') FOR [QTY_ML]
GO

ALTER TABLE [dbo].[MF_MO] ADD  CONSTRAINT [DF_MF_MO_QTY_ML_UNSH]  DEFAULT ('0') FOR [QTY_ML_UNSH]
GO

CodePudding user response：

I made an idiot mistake, two column types are different, mf_mo's mo_no is varchar(20) and hct_dailywork is nvarchar(200), so indexs do not work for them.. (I used automatic generation tool for convenience, but not double check)

Updated estimated execution plan