简单的SQL表很慢，请帮我优化一下 [英] simple SQL table very slow, help me optimize please

问题描述

我创建的这张表< g class =" gr_ gr_35 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace"数据-GR-ID =" 35 QUOT; ID = QUOT 35 QUOT;>具有< / g取代; 1600万行非常慢。

This table I created <g class="gr_ gr_35 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="35" id="35">have</g> 16 million rows and it is very slow.

< g class =" gr_ gr_63 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace"数据-GR-ID =" 63" ID = QUOT; 63">每次< / g取代;我在DB中插入了大约1m的记录我必须重建索引。

<g class="gr_ gr_63 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="63" id="63">Everytime</g> I insert about 1m records into the DB I have to rebuild the index.

任何建议有什么问题？

谢谢，

鱼

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON¥b $ b GO



CREATE TABLE [dbo]。[position_table]（

[data_id] [bigint] NOT NULL，

[report_dt] [date] NOT NULL，

[curr] [char]（3）NOT NULL，

[cc] [char]（3 ）NOT NULL，

[loc_gl] [bigint] NOT NULL，

[prod_id] [int] NOT NULL，

[transit] [int] NOT NULL，

[vers] [smallint] NULL，

[data_source] [nv archar]（100）NOT NULL，

[file_name] [nvarchar]（100）NOT NULL，

[data_gran] [nvarchar]（26）NOT NULL，

[bal] [decimal]（30,5）NOT NULL，

[coup] [decimal]（30） ，10）NOT NULL，

[orig_dt] [int] NOT NULL，

[repr_dt] [int] NULL，

[mat_dt] [int ] NULL，

[repr_prd] [real] NULL，

[amort_dt] [int] NULL，

[pmt_amt] [decimal]（20， 5）NULL，

[file_dt] [date] NULL，

[region] [nvarchar]（100）NOT NULL，

PRIMARY KEY NONCLUSTERED 

（

[data_id] ASC

）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON ）ON [PRIMARY]，

  CONSTRAINT [position_table_test_idx] UNIQUE CLUSTERED 

（

[report_dt] ASC，

[curr] ASC，

[cc] ASC，

[loc_gl] ASC，

[prod_id] ASC，

[transit] ASC，

[vers] ASC，

[data_source] ASC，

[file_name] ASC，

[妙招] ASC，

[bal] ASC ，

[orig_dt] ASC，

[repr_dt] ASC，

[mat_dt] ASC，

[repr_prd] ASC，

[data_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]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[position_table](
[data_id] [bigint] NOT NULL,
[report_dt] [date] NOT NULL,
[curr] [char](3) NOT NULL,
[cc] [char](3) NOT NULL,
[loc_gl] [bigint] NOT NULL,
[prod_id] [int] NOT NULL,
[transit] [int] NOT NULL,
[vers] [smallint] NULL,
[data_source] [nvarchar](100) NOT NULL,
[file_name] [nvarchar](100) NOT NULL,
[data_gran] [nvarchar](26) NOT NULL,
[bal] [decimal](30, 5) NOT NULL,
[coup] [decimal](30, 10) NOT NULL,
[orig_dt] [int] NOT NULL,
[repr_dt] [int] NULL,
[mat_dt] [int] NULL,
[repr_prd] [real] NULL,
[amort_dt] [int] NULL,
[pmt_amt] [decimal](20, 5) NULL,
[file_dt] [date] NULL,
[region] [nvarchar](100) NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
[data_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [position_table_test_idx] UNIQUE CLUSTERED 
(
[report_dt] ASC,
[curr] ASC,
[cc] ASC,
[loc_gl] ASC,
[prod_id] ASC,
[transit] ASC,
[vers] ASC,
[data_source] ASC,
[file_name] ASC,
[coup] ASC,
[bal] ASC,
[orig_dt] ASC,
[repr_dt] ASC,
[mat_dt] ASC,
[repr_prd] ASC,
[data_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]
GO

推荐答案

在插入之前不要创建任何索引。在data_id上插入create clustered index后

Dont create any index before insert. After insert create clustered index on data_id

这篇关于简单的SQL表很慢，请帮我优化一下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！