Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Stored Procedure very slow.. take a lot more time than normal to complete

Expand Messages
  • Charles Carroll
    Basically show us the SP. Inserts are slow depending on whether you are over indexed or have clustered indexes and on which field. I use a stored prodecure
    Message 1 of 3 , Mar 13, 2013
    • 0 Attachment
      Basically show us the SP. Inserts are slow depending on whether you are
      "over indexed" or have clustered indexes and on which field.

      I use a stored prodecure to store the time of tasks ProgressReport is
      spname, here is an example:

      USE [AutoStore_LogExt]
      GO
      /****** Object: StoredProcedure
      [dbo].[Agg_CRUD_SummarizeLogRawMessages] Script Date: 03/22/2012
      15:27:50 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      -- =============================================
      -- Author: Charles Carroll
      -- Create date: 3/2011
      -- Description: AR_Transient_Raw_Msgs: create table or append to it if
      exists
      -- Agg_AR_LogRawMsgSummary: create table or update it, if it exists
      -- Raw_Log: shuttle records to AR_Transient_Raw_Msgs and
      summary, then delete
      -- =============================================
      ALTER PROCEDURE [dbo].[Agg_CRUD_SummarizeLogRawMessages]
      AS
      BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      DECLARE @TaskDescription NVARCHAR(100);

      SET @TaskDescription='Sproc [AG_CRUD_SummarizeLogRawMessages]';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='start'

      SET NOCOUNT ON;

      DECLARE @MaxID AS bigint;

      SET @TaskDescription = 'Determing if any LogValues exist at all';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      if not exists (select Raw_Log_ID from Raw_Log where LogValue is not null)
      BEGIN
      SET @TaskDescription = 'No LogValues exist to analyze';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='end'
      RETURN
      END


      SET @TaskDescription = 'Determing How Many Raw_Log_ID entries to process';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      select @MaxID=MAX(Raw_Log_ID)
      FROM Raw_Log

      SET @TaskDescription='Processing Raw_LogID >=' + CAST(@MaxID AS
      nvarchar(12));
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      /* make transient table in case someone needs each message */
      IF OBJECT_ID('AR_Transient_RawMsgs','U') is null
      BEGIN
      SET @TaskDescription = 'Copying Data to NEWLY CREATED Table
      AR_Transient_RawMsgs';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      Select LogValue,Raw_Log_ID
      into AR_Transient_RawMsgs
      from raw_Log
      where LogValue is not null AND [Raw_Log_ID]<=@MaxID
      END
      else
      BEGIN
      SET @TaskDescription = 'Copying Data to EXISTING Table
      AR_Transient_RawMsgs';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      SET IDENTITY_INSERT AR_Transient_RawMsgs ON

      INSERT INTO AR_Transient_RawMsgs (LogValue,Raw_Log_ID)
      Select LogValue,Raw_Log_ID
      from raw_Log
      where LogValue is not null AND [Raw_Log_ID]<=@MaxID

      SET IDENTITY_INSERT AR_Transient_RawMsgs OFF
      END

      /* create or update agreggate table */

      -- remove in production!
      --IF OBJECT_ID('Agg_AR_LogRawMsgSummary','U') is not null
      -- DROP TABLE Agg_AR_LogRawMsgSummary

      IF OBJECT_ID('Agg_AR_LogRawMsgSummary','U') is null
      BEGIN

      SET @TaskDescription = 'Summarizing Data and transferring and
      Creating Agg_AR_LogRawMsgSummary';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      select
      LogValue, count(*) as LogValueCount,
      MIN(OccurredWhen) AS LogValueFirst,
      MAX(OccurredWhen) AS LogValueLast,
      MAX(Raw_Log_ID) AS LogValueLastRawID

      Into Agg_AR_LogRawMsgSummary

      from AutoStore_LogExt.dbo.Raw_Log
      where LogValue is not null AND [Raw_Log_ID]<=@MaxID
      --group by isnull(LogValue,Convert(nvarchar(max),XmlFile))
      group by LogValue
      having COUNT(*) >1

      order by LogValueCount desc
      END
      else
      BEGIN
      SET @TaskDescription = 'Summarizing Data and updating existing
      Agg_AR_LogRawMsgSummary';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      --temp work table
      --this will hold the values for all the LogValueNames
      DECLARE @tWork TABLE(
      LogValue NVARCHAR(400),
      LogValueCount INT,
      LogValueFirst DATETIME,
      LOGValueLast DATETIME,
      LogValueLastRawID INT)

      SET @TaskDescription = 'Fill the temporary table';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      INSERT INTO @tWork
      SELECT LogValue AS LogValue,
      COUNT(*) As LogValueCount,
      MIN(OccurredWhen) AS LogValueFirst,
      MAX(OccurredWhen) AS LogValueLast,
      MAX(Raw_Log_ID) As LogValueLastRawID
      FROM AutoStore_LogExt.dbo.Raw_Log
      GROUP BY LogValue

      SET @TaskDescription = 'updating the Agg_AR_LogRawMsgSummary from
      temp table';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      --now update the log summary table for each logvalue
      --first update all existing records
      UPDATE Agg_AR_LogRawMsgSummary
      SET
      LogValue = tw.LogValue,
      LogValueCount = lst.LogValueCount + tw.LogValueCount,
      LogValueFirst = CASE WHEN lst.LogValueFirst < tw.LogValueFirst
      THEN lst.LogValueFirst ELSE tw.LogValueFirst END,
      LogValueLast = CASE WHEN tw.LogValueLast > lst.LogValueLast THEN
      tw.LogValueLast ELSE tw.LogValueLast END,
      LogValueLastRawID = CASE WHEN tw.LogValueLastRawID >
      lst.LogValueLastRawID THEN tw.LogValueLastRawID ELSE
      lst.LogValueLastRawID END
      FROM Agg_AR_LogRawMsgSummary lst
      JOIN @tWork tw
      ON lst.LogValue = tw.LogValue

      SET @TaskDescription = 'inserting new records into
      Agg_AR_LogRawMsgSummary from temp table';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      INSERT INTO Agg_AR_LogRawMsgSummary
      SELECT
      tw.LogValue,
      tw.LogValueCount,
      tw.LogValueFirst,
      tw.LogValueLast,
      tw.LogValueLastRawID

      FROM @tWork tw

      LEFT JOIN Agg_AR_LogRawMsgSummary lst

      ON tw.LogValue = lst.LogValue

      WHERE lst.LogValue IS NULL
      END

      SET @TaskDescription = 'deleting aggregated LogValues from Raw_Log
      where Raw_Log_ID>=' + CAST(@MaxID AS nvarchar(12));
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      delete from Raw_Log
      where LogValue is not null AND Raw_Log_ID<=@MaxID

      SET @TaskDescription = 'CLEANTABLE running';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      DBCC CLEANTABLE('AutoStore_LogExt', 'dbo.Raw_Log')


      SET @TaskDescription = '%s Sproc [AG_CRUD_SummarizeLogRawMessages] END!!!';
      EXEC [dbo].[ProgressReport] @Msg = @TaskDescription, @Event='progress'

      END


      USE [AutoStore_LogExt]
      GO
      /****** Object: StoredProcedure [dbo].[Lib_ProgressReport] Script
      Date: 03/22/2012 15:28:30 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      -- =============================================
      -- Author: Charles Carroll
      -- Create date: 10/11/2011
      -- Description: Task Progress/Debugging
      -- =============================================
      ALTER PROCEDURE [dbo].[Lib_ProgressReport]
      @Msg AS NVARCHAR(100),
      @Event AS NVARCHAR(20)
      AS
      BEGIN
      SET NOCOUNT ON;
      DECLARE @timeofexec AS NVARCHAR(40);
      DECLARE @EventGood as VARCHAR(1)='N';

      IF lower(@Event)='start' OR LOWER(@Event)='end' OR LOWER(@Event)='progress'
      BEGIN
      SET @EventGood='Y';
      END

      If @EventGood='Y'
      BEGIN
      SET @timeofexec = Convert(nvarchar(40), GETDATE(),8);
      SET @Msg = '%s ' + @Msg + ' ' + @Event + '!!!';
      RAISERROR(@Msg,0,1,@timeofexec) WITH NOWAIT
      END
      else
      BEGIN
      DECLARE @MsgErr AS nvarchar(128);
      SET @MsgErr='Bad @TaskName parameter=' +
      Convert(nvarchar(128),@Event) + ' valid parameters:
      start/end/progress';
      RAISERROR(@MsgErr,0,1,@timeofexec) WITH NOWAIT
      END





      On Wed, Mar 13, 2013 at 12:13 PM, MBDEV - DB <databaselists@...>wrote:

      > **
      >
      >
      > Hi,
      >
      > I have a stored procedure that updates tables in multiple databases. It
      > used to run in minutes and now the client says it is taking 40 minutes to
      > complete. It does have one linked server that is involved so that might be
      > it I don�t know but it used to run way faster even with that.
      >
      > What are some steps I can use to troubleshoot the problem area?
      >
      > Is there a way to time each DB for how long it takes to update in the
      > stored procedure?
      >
      > This stored procedure updates a few databases so if there was a way to
      > programmatically time each one I could find the problem.
      >
      > Any suggestions will help.
      >
      > Thanks,
      >
      > Mike
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >


      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.