none
how to enhance this query for good performance ? RRS feed

  • Question

  • I work on sql server 2012 and i need to enhance or make this query have good performance

    this stored procedure work success but i need to know

    when make drop to temp table and cte

    plus how to write it with best practice for performance

    alter Proc ImporterQueue_RunModified
    As
    WITH CTE AS
        (
    	Select Row_Number() Over (Order By GetDate())as rownumber, StoredProcedureName , ImporterQueue.CreateBy , ImporterQueueID,applicationid, dbo.ImporterTemplate.ImporterTemplateID, InputFilePath, OutputFilePath, StoredProcedureName [ImporterTemplate.StoredProcedureName],
                    RN = ROW_NUMBER() OVER (PARTITION BY applicationid ORDER BY ImporterQueueID asc)
    From dbo.ImporterQueue
    Inner Join dbo.ImporterTemplate On dbo.ImporterQueue.ImporterTemplateID = dbo.ImporterTemplate.ImporterTemplateID
    Inner Join Privilages.Module On dbo.ImporterTemplate.ModuleID = Privilages.Module.ModuleID
    Where dbo.ImporterQueue.IsDeleted = 0 And dbo.ImporterQueue.OverAllStatusID = 1 
        )
          SELECT rownumber , RN , ImporterQueueID,CreateBy,StoredProcedureName,InputFilePath,OutputFilePath
     into #results   FROM    CTE
        WHERE   RN = 1;
    If (Select OverAllStatusID From dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID) <> 1 -- Pending
    Return;
    --loop through temp table
    DECLARE @totalRecords INT
    DECLARE @I INT
    
    
    
    
    --Declare @UserID Int = (Select CreateBy From dbo.ImporterQueue Where ImporterQueueID = @ImporterQueueID)
    Declare @ImportingStartDate DateTime = GetDate(), @DurationInSeconds Int
    
    Update dbo.ImporterQueue Set
    ImportingStartDate = @ImportingStartDate,
    OverAllStatusID = 2, -- In Progress
    StatusReason = Null,
    UpdateBy = #results.CreateBy,
    UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID
    
    
    --Begin Transaction Trans
    Begin Try
    SELECT @I = 1
    SELECT @totalRecords = COUNT(ImporterQueueID) FROM #results
    WHILE (@I <= @totalRecords)
    BEGIN
    declare @ProcedureName Nvarchar(200) = (SELECT StoredProcedureName FROM #results WHERE rownumber = @I),
    --@UserIDString Varchar(20) = Convert(Varchar(20), @UserID),
    @ImporterQueueIDString Varchar(20) = (SELECT StoredProcedureName FROM #results WHERE rownumber = @I),
    @InputFilePath Nvarchar(500) = (SELECT InputFilePath FROM #results WHERE rownumber = @I),
    @OutputFilePath Nvarchar(500) = (SELECT OutputFilePath FROM #results WHERE rownumber = @I)
    
    Declare @SQLvalue Nvarchar(1000) = 'EXECUTE ' + @ProcedureName + ' ' + @ImporterQueueIDString + ' , ' + '''' + @InputFilePath + '''' + ' , ' + '''' + @OutputFilePath + '''' + ''
    Exec(@SQLvalue)
        SELECT @I = @I + 1 
    END
    
    
    
    --Commit Transaction Trans
    Update dbo.ImporterQueue Set
    DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),
    OverAllStatusID = 3, -- Done
    StatusReason = Null,
    UpdateBy = #results.CreateBy,
    UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID
    End Try
    
    Begin Catch
    --RollBack Transaction Trans
    Update dbo.ImporterQueue Set
    DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),
    OverAllStatusID = 4, -- Failed
    StatusReason = ERROR_MESSAGE(),
    UpdateBy = #results.CreateBy,
    UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID
    
    End Catch

    Wednesday, November 6, 2019 9:23 AM

All replies

  • can any one help me 

    how to write this query above with best practice 

    Thursday, November 7, 2019 6:18 AM
  • In order to speed up the query you need properly defined indexes , moreover , I strongly recommend you to get rid of loops ans it hurts performance as well.

    You have may queries, which  one performs badly ? 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 7, 2019 6:22 AM
    Answerer
  • Hi engahmedbarbary,

    Per your description , I found that you use insert many times . Is there any index in table ' dbo.ImporterQueue'?

    Could you please share us your execution plan and table structure (CREATE TABLE …) ? 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 7, 2019 8:37 AM
  • thank you for reply 

    importertemplate table is must have file name 

    importerque table have details of importertemplate

    CREATE TABLE [dbo].[ImporterTemplate](
    	[ImporterTemplateID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[ImporterTemplateName] [nvarchar](100) NOT NULL,
    	[InputTemplatePath] [nvarchar](500) NULL,
    	[OutputTemplatePath] [nvarchar](500) NULL,
    	[StoredProcedureName] [nvarchar](100) NULL,
    	[ModuleID] [int] NULL,
    	[IsDeleted] [bit] NOT NULL,
    	[CreateBy] [int] NOT NULL,
    	[CreateDate] [datetime] NOT NULL,
    	[UpdateBy] [int] NULL,
    	[UpdateDate] [datetime] NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[ImporterTemplateID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    UNIQUE NONCLUSTERED 
    (
    	[ImporterTemplateName] 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
    
    ALTER TABLE [dbo].[ImporterTemplate] ADD  DEFAULT ((0)) FOR [IsDeleted]
    GO
    
    ALTER TABLE [dbo].[ImporterTemplate] ADD  DEFAULT (getdate()) FOR [CreateDate]
    GO
    
    ALTER TABLE [dbo].[ImporterTemplate]  WITH CHECK ADD FOREIGN KEY([CreateBy])
    REFERENCES [Privilages].[User] ([UserID])
    GO
    
    ALTER TABLE [dbo].[ImporterTemplate]  WITH CHECK ADD FOREIGN KEY([ModuleID])
    REFERENCES [Privilages].[Module] ([ModuleID])
    GO
    
    ALTER TABLE [dbo].[ImporterTemplate]  WITH CHECK ADD FOREIGN KEY([UpdateBy])
    REFERENCES [Privilages].[User] ([UserID])
    GO
    
    
    ===========================================
    CREATE TABLE [dbo].[ImporterQueue](
    	[ImporterQueueID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[ImporterTemplateID] [int] NULL,
    	[UploadedFileName] [nvarchar](500) NULL,
    	[InputFilePath] [nvarchar](500) NULL,
    	[OutputFilePath] [nvarchar](500) NULL,
    	[DurationInSeconds] [int] NULL,
    	[DurationInMinutes] [int] NULL,
    	[TotalRowsCount] [int] NULL,
    	[ValidRowsCount] [int] NULL,
    	[InValidRowsCount] [int] NULL,
    	[OverAllStatusID] [int] NULL,
    	[StatusReason] [nvarchar](1000) NULL,
    	[ImportingStartDate] [datetime] NULL,
    	[IsDeleted] [bit] NOT NULL,
    	[CreateBy] [int] NOT NULL,
    	[CreateDate] [datetime] NOT NULL,
    	[UpdateBy] [int] NULL,
    	[UpdateDate] [datetime] NULL,
    	[NewRowsCount] [int] NULL,
    	[ExistsRowsCount] [int] NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[ImporterQueueID] 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
    
    ALTER TABLE [dbo].[ImporterQueue] ADD  DEFAULT ((0)) FOR [IsDeleted]
    GO
    
    ALTER TABLE [dbo].[ImporterQueue] ADD  DEFAULT (getdate()) FOR [CreateDate]
    GO
    
    ALTER TABLE [dbo].[ImporterQueue]  WITH CHECK ADD FOREIGN KEY([CreateBy])
    REFERENCES [Privilages].[User] ([UserID])
    GO
    
    ALTER TABLE [dbo].[ImporterQueue]  WITH CHECK ADD FOREIGN KEY([ImporterTemplateID])
    REFERENCES [dbo].[ImporterTemplate] ([ImporterTemplateID])
    GO
    
    ALTER TABLE [dbo].[ImporterQueue]  WITH CHECK ADD FOREIGN KEY([UpdateBy])
    REFERENCES [Privilages].[User] ([UserID])
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 Pending, 2 In Progress, 3 Done, 4 Failed, 5 Canceled' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ImporterQueue', @level2type=N'COLUMN',@level2name=N'OverAllStatusID'
    GO
    
    
    

    Thursday, November 7, 2019 12:34 PM
  • Hi engahmedbarbary,

    Thank you for your detailed reply.

    As you mentioned above , in table 'ImporterQueue' you have three indexes. The more indexes a table has, the slower the execution becomes. The insert statement is the only operation that cannot directly benefit from indexing because it has no where clause.Adding a new row to a table involves several steps. First, the database must find a place to store the row. For a regular heap table—which has no particular row order—the database can take any table block that has enough free space. This is a very simple and quick process, mostly executed in main memory. All the database has to do afterwards is to add the new entry to the respective data block.If there are indexes on the table, the database must make sure the new entry is also found via these indexes. For this reason it has to add the new entry to each and every index on that table. The number of indexes is therefore a multiplier for the cost of an insert statement.

    In your script you use three tables join . If you have many rows , it will cause performance issue . If you have to do, please create some index to reduce its impact. For example, you use 'Where dbo.ImporterQueue.IsDeleted = 0 And dbo.ImporterQueue.OverAllStatusID = 1 ' ,please try to create a index on ImporterQueue (IsDeleted ,OverAllStatusID ).

    Also, in your script , I found that you use 'WHILE'. The natural thought is that it's inefficient, because it's repeated over and over again.

    Hope it will help you.

    Best Regards,

    Rachel 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 8, 2019 8:24 AM
  • according to this point

    Also, in your script , I found that you use 'WHILE'. The natural thought is that it's inefficient, because it's repeated over and over again.

    please what alternative thing can i use  without while loop

    Friday, November 8, 2019 10:10 PM
  • can you please modify script query i write to best practise performance and write here please 
    Friday, November 8, 2019 10:13 PM
  • Hi ,

    Sorry for my late reply.

    Firstly , without your actual , I might not provide you a clear way . Just provide an  idea , and please try your best to change your script . 

    And , I think you might try to use cte recursive. There is an example , please refer to it .

    DECLARE @Source  table (SetID int, ParentName nvarchar(20), ParentGroup nvarchar(30),Usage int,ChildName nvarchar(20), Childgroup nvarchar(30))
    
    
    --source information
    insert into  @Source
    select 1, 'Parent1', 'A', 10 , 'child1', 'B' union all
    select 1, 'Parent1', 'A', 10 , 'child2', 'B' union all
    select 2, 'Parent1', 'A', 15 , 'child1', 'B' union all
    select 2, 'Parent1', 'A', 15 , 'child2', 'B' union all
    select 3, 'Parent2', 'A', 10 ,'child1', 'B' union all
    select 3, 'Parent2', 'A', 10 , 'child2', 'B' union all
    select 4, 'Parent2', 'A', 20 , 'child1', 'B' union all
    select 4, 'Parent2', 'A', 20 , 'child2', 'B' union all
    select 4, 'Parent2', 'A', 20 , 'child3', 'B' 
    
    
     --------starting with SQL Server  2017
    ; with cte as (
    select SetID,ParentName,ParentGroup,Usage,STRING_AGG (ChildName,',') as combinedChildname
    from @Source
    group by  SetID,ParentName,ParentGroup,Usage)
    Select  ParentName, ParentGroup, SUM(Usage) Usage
    from cte
    group by  ParentName, ParentGroup,combinedChildname
    /*
    ParentName           ParentGroup                    Usage
    -------------------- ------------------------------ -----------
    Parent1              A                              25
    Parent2              A                              10
    Parent2              A                              20
    */
    

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 8:35 AM