0
Ladies and gentlemen. (as),
I need help to build an efficient index for an SQL table.
I have a table that is causing me a certain headache, because it is consulted with a certain frequency, besides having constant Inserts in it.
My system stores files from various sources, creating a structure in two tables, being:
1-TB_ARQUIVO - Stores the file that was created
2-TB_ARQUIVO_CONTEUDO_VP - Stores all the contents of the file (here’s my problem)
This table has a ID IDENTITY PK BIGINT and a column ID_ARQUIVO BIGINT, where I store the ID of table TB_ARQUIVO. The content stored in this table reaches 400 thousand lines per file and currently we have about 700 million lines.
Below is the structure of TB_ARQUIVO_CONTEUDO_VP and the index I’m currently using.
CREATE TABLE [dbo].[TB_ARQUIVO_CONTEUDO_VP](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ID_PRODUTO] [bigint] NOT NULL,
[ID_ARQUIVO] [bigint] NOT NULL,
[COD_AGENCIA] [nvarchar](4) NULL,
[COD_TOTAL] [nvarchar](1) NOT NULL,
[COD_REGIAO] [nvarchar](2) NOT NULL,
[VAL_VALOR] [decimal](18, 2) NULL,
[DES_DADOS_EXTRA] [nvarchar](500) NULL,
[DAT_ULT_ATUALIZACAO] [datetime] NULL,
[COD_SEGMENTO] [varchar](4) NULL,
CONSTRAINT [PK_TB_ARQUIVO_CONTEUDO_VP] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_ARQUIVO_CONTEUDO_VP] ON [dbo].[TB_ARQUIVO_CONTEUDO_VP]
(
[ID] ASC,
[ID_ARQUIVO]
)
INCLUDE (
[ID_PRODUTO],
[COD_AGENCIA],
[COD_SEGMENTO],
[DAT_ULT_ATUALIZACAO]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
Complementing: 1 - The SQL Server we use is Microsoft SQL Server Enterprise 2017. 2 - The column ID_ARQUIVO and DAT_ULT_ATUALIZACAO are the most used to query data in the table
If ID is the primary key of TB_ARQUIVO_CONTEUDO_VP did not understand why its index IDX_ARQUIVO_CONTEUDO_VP includes such field in the first place since only it uniquely identifies each row of your table. Would an index only in ID_ARQUIVO not be better used by your applications?
– anonimo
Precisely, I need more accurate knowledge to help me. I am looking for the best way to build this index.
– Luiz Martinez
Think of something like this? CREATE NONCLUSTERED INDEX [IDX_ARQUIVO_CONTEUDO_VP] ON [dbo]. [TB_ARQUIVO_CONTEUDO_VP] ( [ID_ARQUIVO] ) INCLUDE ( [ID_PRODUTO], [COD_AGENCIA], [COD_SEGMENTO], [DAT_ULT_ATUALIZACAO]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALOW_ROW_LOCKS = ON, ALOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
– Luiz Martinez