我在SQl Server 2008R2数据库中有一个大表(名为ECRM)。约1200万张唱片。我有一个插入临时表的插件,大约需要30分钟。我的插入查询:
CREATE TABLE #TECRM(ID INT IDENTITY(1,1),
ECRITURE_ID_TECH INT,
MONTANT_DEBIT MONEY,
MONTANT_CREDIT MONEY,
ECRM_ID INT,
CG VARCHAR(20),
CAUX VARCHAR(20),
REF_EXTERNE NVARCHAR(100),
MVT_LIB NVARCHAR(255),
PRIMARY KEY (id)
)
INSERT INTO #TECRM WITH (TABLOCK)
SELECT ECRT_ECR_ID_TECH,
MONTANT_DEBIT,
MONTANT_CREDIT,
ECRM_ID,
ECRM_CG,
ECRM_CAUX,
ECRM_REF,
ECRM_MVT_LIB
FROM ECRM
JOIN ECRT
ON ECRM_ECRITURE_ID = ECRT_ECR_ID_TECH
我在ECRT_ECR_ID_TECH上有一个索引,在ECRM_ECRITURE_ID上有另一个索引我在ECRM_ID(表ECRM的主键)上也有一个聚集索引
当我只看到select查询的估计执行计划时,聚集索引扫描花费了92%!
所以我认为我之前必须改进select查询,但我不知道如何避免聚集索引扫描。。。(成本92%)
有什么想法或建议吗?
谢谢你帮我!!
编辑1:
SELECT查询的实际执行计划
编辑2:
CREATE TABLE ECRM(ECRM_ID INT IDENTITY(1,1) NOT NULL,
ECRM_ECRITURE_ID INT,
MONTANT_DEBIT MONEY NULL,
MONTANT_CREDIT MONEY NULL,
ECRM_CG VARCHAR(20),
ECRM_CAUX VARCHAR(20),
ECRM_REF NVARCHAR(100),
ECRM_MVT_LIB VARCHAR(255),
CONSTRAINT [PK_T_ECRM] PRIMARY KEY CLUSTERED
(
ECRM_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]
添加非集群索引
CREATE NONCLUSTERED INDEX [IX_ECRM_ECRITURE_ID] ON [ECRM]
([ECRM_ECRITURE_ID])
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF )ON [PRIMARY]
表ECRT
CREATE TABLE ECRT(
ECRT_ID int IDENTITY(1,1) NOT NULL,
ECRT_ECR_ID_TECH INT,
ECRT_MVT_CONCATENE_TECH nvarchar(max) NULL,
ECRT_DATE_COMPTA datetime NULL,
ECRT_ABONNEMENT_ID_TECH int NULL,
ECRT_A_ANALYSER bit NULL,
ECRT_SC_ID_TECH int NULL,
ECRT_IMPORT_ID int NULL,
ECRT_SC_ID_NON_IDENTIFIE_TECH int NULL,
CONSTRAINT [PK_TSMCG_ECRITURE_TECH] PRIMARY KEY CLUSTERED
(
ECRT_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]
添加非集群索引
CREATE NONCLUSTERED INDEX [IX_ECRT_ECR_ID_TECH] ON [ECRT]
([ECRT_ECR_ID_TECH])
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF )ON [PRIMARY]
GO
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
GO
SELECT
ECRT_ECR_ID_TECH,
MONTANT_DEBIT,
MONTANT_CREDIT,
ECRM_ID,
ECRM_CG,
ECRM_CAUX,
ECRM_REF,
ECRM_MVT_LIB
INTO #temp
FROM ECRM
JOIN ECRT ON ECRM_ECRITURE_ID = ECRT_ECR_ID_TECH
ALTER TABLE #temp ADD ID INT IDENTITY(1,1) PRIMARY KEY
GO