One of my SSIS components seem to be very slow. I am fairly new to SS so I am probably doing something wrong. My SSIS package downloads files from SFTP server, imports them into SS table, runs SP. After I import the file(s) into the SS table, SSIS runs below code to create index. If there are no indexes then the SP run super slow. So I definitely have to include the below query. Is there a better way to handle how indexes get created/updated?
/****** Object: StoredProcedure [dbo].[CreateOptionsEODIndex] Script Date: 2023-09-23 9:22:59 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[CreateOptionsEODIndex]
CREATE CLUSTERED COLUMNSTORE INDEX [OptionsEODIndex] ON [dbo].[OptionsEOD] WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [OptionsEODIndex2] ON [dbo].[OptionsEOD]
INCLUDE([ROOT_SYMBOL],[BID_1545],[ASK_1545]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]