locked
Insert taking longer than 7 min RRS feed

  • Question

  • User-1571110992 posted

    Hi All,

    I have table which having distinct records around 200k( Distinct by internal_id, indicator_id,geo_type,geo_entity and year_id)

    CREATE TABLE [dbo].[indicator_data_summarize]
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[internal_id] [int] NULL,
    	[sort_index] [int] NULL,
    	[year] [varchar](max) NULL,
    	[geo_type] [varchar](max) NULL,
    	[geo_entity] [varchar](max) NULL,
    	[these_data] [varchar](max) NULL,
    	[unreliable_text] [varchar](max) NULL,
    	[location_control1] [varchar](max) NULL,
    	[location_control2] [varchar](max) NULL,
    	[location_control3] [varchar](max) NULL,
    	[location_col] [int] NULL,
    	[location_chk] [int] NULL,
    	[year_control] [varchar](max) NULL,
    	[year_col] [int] NULL,
    	[year_chk] [int] NULL,
    	[ci_data] [varchar](max) NULL,
    	[sort_key1] [int] NULL,
    	[sort_key2] [int] NULL,
    	[end_period] [date] NULL,
    	[measurement_type_id] [int] NULL,
    	[indicator_id] [int] NULL,
    	[year_id] [int] NULL,
     CONSTRAINT [PK_indicator_data_summarize] 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]

    I always delete set of records before inserting data using following code( internal_id =21240)

      DELETE FROM dbo.indicator_data_summarize               
      where internal_id =@internal_id              

    I insert using following code

    inseting set of rows from view indicator_data_geo_entity_year_new to table dbo.indicator_data_summarize

    SET ANSI_WARNINGS OFF                               
     
     SET IDENTITY_INSERT dbo.indicator_data_summarize off     
                           
     INSERT INTO  dbo.indicator_data_summarize ([internal_id]            
                  
          ,[sort_index]            
          ,[year]            
          ,[geo_type]            
          ,[geo_entity]            
          ,[these_data]            
          ,[unreliable_text]          
          ,[location_control1]            
          ,[location_control2]            
          ,[location_control3]            
          ,[location_col]            
          ,[location_chk]            
          ,[year_control]            
          ,[year_col]            
          ,[year_chk]            
          ,[ci_data]            
              
          ,[sort_key1]            
          ,[sort_key2]            
          ,[end_period]            
          ,[measurement_type_id]            
          ,[indicator_id]            
          ,[year_id]  )                                   
     SELECT      
           [internal_id]            
          ,[sort_index]            
          ,[year]            
          ,[geo_type]            
          ,[geo_entity]            
          ,[these_data]            
          ,isnull([unreliable_text],'')            
          ,[location_control1]            
          ,[location_control2]            
          ,[location_control3]            
          ,[location_col]            
          ,[location_chk]            
          ,[year_control]            
          ,[year_col]            
          ,[year_chk]            
          ,[ci_data]            
               
          ,[sort_key1]            
          ,[sort_key2]            
          ,[end_period]            
          ,[measurement_type_id]            
          ,[indicator_id]            
          ,[year_id]            
         -- into #temp            
      FROM [indicator_data_geo_entity_year_new]            
      where internal_id=@internal_id                               
                
      SET IDENTITY_INSERT dbo.indicator_data_summarize on    

    Insert of 288 records taking 7 min. Please suggest how I can immprove this code.

     

    Tuesday, April 3, 2018 9:50 PM

Answers

  • User475983607 posted

    The queries are based on internal_id which is not indexed.  Other than that you'll need to explain the logic/requirements.  The code deletes records which is always bad because deletes makes the log file grow.   Since you already have the data I question moving data from one table to another. It looks like a design bug.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 3, 2018 11:19 PM
  • User-1716253493 posted

    I guess indicator_data_geo_entity_year_new view taking times

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 4, 2018 12:50 AM
  • User753101303 posted

    Hi,

    You have quite a lot of varchar(max) column so beyond a row count I'm not sure which amount of data you are moving here. Also you may have costly indexes on your table? As pointed already trying to update existing data rather than to delete/insert all might be quicker. If not all rows are updated it could be likely further optimized.

    I would also try to see if  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql could give some indication.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 4, 2018 4:24 PM

All replies

  • User475983607 posted

    The queries are based on internal_id which is not indexed.  Other than that you'll need to explain the logic/requirements.  The code deletes records which is always bad because deletes makes the log file grow.   Since you already have the data I question moving data from one table to another. It looks like a design bug.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 3, 2018 11:19 PM
  • User-1716253493 posted

    I guess indicator_data_geo_entity_year_new view taking times

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 4, 2018 12:50 AM
  • User-1571110992 posted

    Thank you for replay. It is taking  .14 sec to retrieve records in first call. After some sometime it is taking longer 

    Wednesday, April 4, 2018 2:52 PM
  • User475983607 posted

    Thank you for replay. It is taking  .14 sec to retrieve records. 

    Glad to hear you fixed the issue.

    Wednesday, April 4, 2018 3:02 PM
  • User-1571110992 posted

    Sorry but  I could not 

    Wednesday, April 4, 2018 3:38 PM
  • User753101303 posted

    Hi,

    You have quite a lot of varchar(max) column so beyond a row count I'm not sure which amount of data you are moving here. Also you may have costly indexes on your table? As pointed already trying to update existing data rather than to delete/insert all might be quicker. If not all rows are updated it could be likely further optimized.

    I would also try to see if  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql could give some indication.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 4, 2018 4:24 PM
  • User-1571110992 posted

    Thank you for help . I reduced no of return field form the view before inserting into table. It reduced time for insertion. 

    Wednesday, April 4, 2018 6:04 PM