Tukukkk

I had been getting data from production and loading it into a staging DWH. And then loading it further into usable format later in another DWH from staging.

Both staging and the final DWH are on the same server. This process wasn't taking long before, but now it's taking ages to load the data from staging. It takes a few minutes to load data from production into staging, but it takes hours to load it further and I am not sure why.

FYI: I had been testing the loads, so I have truncated/deleted the table a few times and reloaded them

Also I had a non clustered index on one of the columns in actual DWH which I removed

CONSTRAINT [PK_EncounterTB_Encounter_id] 

    PRIMARY KEY CLUSTERED ([Encounter_id] ASC),

CONSTRAINT [Uniq_EncounterTB_Encounter_table_id] 

    UNIQUE NONCLUSTERED ([Encounter_Table_id] ASC)

Below is the table structure for staging and I have removed a few of the columns:

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[Stg_Encounter]

(

    [encntr_id] [float] NOT NULL,

    [person_id] [float] NOT NULL,

    [visit_id_stay_number] [varchar](1000) NULL,

    [mrn] [varchar](1000) NULL,

    [encntr_type_cd] [float] NULL,

    [reg_dt_tm] [datetime2](7) NULL,

    [disch_dt_tm] [datetime2](7) NULL,

    [admit_cd] [float] NULL,

    [visit_cd] [float] NULL,

    [source_cd] [float] NULL,

    [sepearation_cd] [float] NULL,

    [medical_service_cd] [float] NULL,

    [reason_problem] [varchar](1000) NULL,

) ON [PRIMARY]

GO

For the actual DWH, the table structure is as below :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[Encounter]

(

    [Encounter_Table_id] [int] NOT NULL,

    [Encounter_id] [int] NOT NULL,

    [Person_id] [int] NOT NULL,

    [Visit_ID] [varchar](1000) NULL,

    [MRN] [varchar](1000) NULL,

    [Encounter_Type] [varchar](1000) NULL,

    [Arrival_Dt_Tm] [datetime2](7) NULL,

    [Departure_Dt_Tm] [datetime2](7) NULL,

    [Mode_of_Arrival] [varchar](1000) NULL,

    [Visit_Type] [varchar](1000) NULL,

    [Admit_Source] [varchar](1000) NULL,

    [Mode_of_Separation] [varchar](1000) NULL,

    [Medical_Service] [varchar](1000) NULL,

    [Presenting_Problem] [varchar](1000) NULL,

    [LOAD_Dt_Tm] [datetime] NOT NULL,

    [Data_Source] [varchar](1000) NOT NULL,



    CONSTRAINT [PK_EncounterTB_Encounter_id] 

        PRIMARY KEY CLUSTERED ([Encounter_id] ASC)

) ON [PRIMARY]

GO

Below insert is being used for inserting data :

INSERT INTO [ACTUAL_DWH].[dbo].[Encounter] 

        (

[Encounter_Table_id]

  ,[Encounter_id]

  ,[Person_id]

  ,[Visit_ID]

  ,[MRN]

  ,[Encounter_Type]

  ,[Arrival_Dt_Tm]

  ,[Departure_Dt_Tm]

  ,[Mode_of_Arrival]

  ,[Visit_Type]

  ,[Admit_Source]

  ,[Mode_of_Separation]

  ,[Medical_Service]

  ,[Presenting_Problem]

  ,[MSAU_LOAD_Dt_Tm]

  ,[Data_Source]    

    )

SELECT

[Encounter_Table_id]= CONVERT(INT,Stg_e.[encntr_id])

    ,   [Encounter_id]                      = CONVERT(INT,Stg_e.[encntr_id])

  ,[Person_id]                          = CONVERT(INT,Stg_e.[person_id])

  ,[Visit_ID]                           = Stg_e.[visit_id_stay_number]

  ,[MRN]                                = Stg_e.[mrn]

  ,[Encounter_Type]                     = [ACTUAL_DWH].[dbo].[emr_get_code_Description](Stg_e.encntr_type_cd)

  ,[Arrival_Dt_Tm]                      = CONVERT(DATETIME,Stg_e.reg_dt_tm)

  ,[Departure_Dt_Tm]                    = CONVERT(DATETIME,Stg_e.disch_dt_tm)

  ,[Mode_of_Arrival]                    = [ACTUAL_DWH].[dbo].[Description](Stg_e.admit_cd)

  ,[Visit_Type]                         = [ACTUAL_DWH].[dbo].[Description](Stg_e.visit_cd)

  ,[Admit_Source]                       = [ACTUAL_DWH].[dbo].[Description](Stg_e.source_cd)

  ,[Mode_of_Separation]                 = [ACTUAL_DWH].[dbo].[Description](Stg_e.sepearation_cd)

  ,[Medical_Service]                    = [ACTUAL_DWH].[dbo].[Description](Stg_e.medical_service_cd)

  ,[Presenting_Problem]                 = Stg_e.reason_problem

  ,[MSAU_LOAD_Dt_Tm]                    = getdate()

  ,[Data_Source]                        = 'SourceName'







    FROM [dbo].Stg_Encounter Stg_e

    where NOT EXISTS ( SELECT 1 FROM [ACTUAL_DWH].[dbo].Encounter e

                            WHERE stg_e.encntr_id = e.encounter_id)

The function used is as per below :

USE [ACTUAL_DWH]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER function [dbo].[Description](@cv int)  

returns varchar(80)  

as begin   



declare @ret varchar(80)  

select @ret = cv.DESCRIPTION

from ACTUAL_DWH.DBO.CODE_VALUE cv   

where cv.code_value = @cv   

    and cv.active_ind = 1  

return isnull(@ret, 0)



end;

I am just confused where I have missed stuff!!! And what can I change. the table has around 6 million rows and it was loading them in a minute.

After the suggestions provided, I got to know that issue is with the function that I am using.I have read about CROSS APPLY but is it a good idea to apply CROSS APPLY on 15 columns?

You can use SQL CREATE INDEX Statement to retrieve data from the database very fast.

    CREATE INDEX IX_Encounter

            ON [ACTUAL_DWH].[dbo].[Encounter](Encounter_Table_id) ON [PRIMARY]



    INSERT INTO [ACTUAL_DWH].[dbo].[Encounter] 

            (

    [Encounter_Table_id]

      ,[Encounter_id]

      ,[Person_id]

      ,[Visit_ID]

      ,[MRN]

      ,[Encounter_Type]

      ,[Arrival_Dt_Tm]

      ,[Departure_Dt_Tm]

      ,[Mode_of_Arrival]

      ,[Visit_Type]

      ,[Admit_Source]

      ,[Mode_of_Separation]

      ,[Medical_Service]

      ,[Presenting_Problem]

      ,[MSAU_LOAD_Dt_Tm]

      ,[Data_Source]    

        )

    SELECT

    [Encounter_Table_id]= CONVERT(INT,Stg_e.[encntr_id])

        ,   [Encounter_id]                      = CONVERT(INT,Stg_e.[encntr_id])

      ,[Person_id]                          = CONVERT(INT,Stg_e.[person_id])

      ,[Visit_ID]                           = Stg_e.[visit_id_stay_number]

      ,[MRN]                                = Stg_e.[mrn]

      ,[Encounter_Type]                     = [ACTUAL_DWH].[dbo].[emr_get_code_Description](Stg_e.encntr_type_cd)

      ,[Arrival_Dt_Tm]                      = CONVERT(DATETIME,Stg_e.reg_dt_tm)

      ,[Departure_Dt_Tm]                    = CONVERT(DATETIME,Stg_e.disch_dt_tm)

      ,[Mode_of_Arrival]                    = [ACTUAL_DWH].[dbo].[Description](Stg_e.admit_cd)

      ,[Visit_Type]                         = [ACTUAL_DWH].[dbo].[Description](Stg_e.visit_cd)

      ,[Admit_Source]                       = [ACTUAL_DWH].[dbo].[Description](Stg_e.source_cd)

      ,[Mode_of_Separation]                 = [ACTUAL_DWH].[dbo].[Description](Stg_e.sepearation_cd)

      ,[Medical_Service]                    = [ACTUAL_DWH].[dbo].[Description](Stg_e.medical_service_cd)

      ,[Presenting_Problem]                 = Stg_e.reason_problem

      ,[MSAU_LOAD_Dt_Tm]                    = getdate()

      ,[Data_Source]                        = 'SourceName'







        FROM [dbo].Stg_Encounter Stg_e

        where NOT EXISTS ( SELECT 1 FROM [ACTUAL_DWH].[dbo].Encounter e

                                WHERE stg_e.encntr_id = e.encounter_id)

You can check more info about index here.INDEX

Just to close this post. As suggested I had tried to break down the query and found that the function was the culprit. I am exploring it further for the resolution.

The function is taking parameter and running a SQL on another table. Which is slowing down the query. If I do the insert without the function it actually takes a few seconds to load 6 million rows.