none
Long running stored procedure

    Question

  • Hi

    I'm fairly new to this so please be patient.

    I have the following stored procedure that takes best part of 10 hours to run!!!

    Table Motor.MTMIEARN has 16012916 records

    Table Motor.MTMIMTRF has 376358 records

    Table lookup.AGENT has 2000 records

    Is there anything obvious that would spped this up

    Many Thanks

    USE

    [SolvencyIIdwh]


    GO



    /****** Object:  StoredProcedure [GENERIC].[usp_MIEARNData]    Script Date: 08/02/2013 11:02:01 ******/


    SET

    ANSI_NULLS ON

    GO

    SET

    QUOTED_IDENTIFIER ON

    GO


    /*********************************************************************************************************/

    /***  Create output PRIVATE CAR - MIEARN                                                                 */

                                                                                                      


    /***                                                                                                     */

    /***  Date Author Reason                                                                       */

    /***  20130408 MR New                                                                          */

    /***                                                                                                     */

    /*********************************************************************************************************/



    CREATE

    PROCEDURE [GENERIC].[usp_MIEARNData]


    AS

    INSERT

    INTO SolvencyIIdwh.MOTOR.MIEARNData(

    IncMonth

    ,[Cover]

       

    ,[Stnd]

       

    ,[EarnedPrem]

       

    ,[EarnedPol])



     

    /**************MIEARN/MIMTRF output file*************/

     

    SELECT

    SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)) ,3,2) + SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)) ,6,2) AS 'Month'    

    ,CASE t.MMCOVR

    WHEN '01' THEN 'C'


    ELSE 'N'


    END  AS 'Cover' 

    ,t.MMSTND AS 'Standard'


    ,SUM(e.TOTEARNPRM) AS 'Earned Premium'


    ,SUM(CASE WHEN dotcode = '23536P'THEN (TOTEARNPOL * .40)

    ELSE TOTEARNPOL END) AS 'Earned Policies'


     

    FROM SolvencyIIdwh.MOTOR.MTMIMTRF t

     

    JOIN SolvencyIIdwh.MOTOR.MTMIEARN e

     

    ON t.MMPOLN = e.MEPOLN

     

    AND t.MMAPSQ = e.MEAPSQ

     

    AND t.MMAPDT = e.MEAPDT

     

    JOIN SolvencyIIdwh.LOOKUP.Agent a

     

    ON t.MMAGNT = a.AgentNo

     

    WHERE t.MMSCDT >= '01 Jan 1993'


     

    GROUP BY e.MEACPD,t.MMCOVR,t.MMSTND



    GO

    Friday, August 02, 2013 10:13 AM

Answers

  • Has the table got necessary indexes. if possible post the execution plan

    You can also use Database Engine turning adviser to analysis the code and it will suggest all the required index's and stats to improve the speed 

    vt

     

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, August 02, 2013 10:18 AM

All replies

  • Has the table got necessary indexes. if possible post the execution plan

    You can also use Database Engine turning adviser to analysis the code and it will suggest all the required index's and stats to improve the speed 

    vt

     

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, August 02, 2013 10:18 AM
  • Lack of index in table might be a problem.create necessary index and system resource also.

    Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

    Friday, August 02, 2013 10:40 AM
  • Hi

    I'm fairly new to this so please be patient.

    I have the following stored procedure that takes best part of 10 hours to run!!!

    Table Motor.MTMIEARN has 16012916 records

    Table Motor.MTMIMTRF has 376358 records

    Table lookup.AGENT has 2000 records

    Is there anything obvious that would spped this up

    Many Thanks

    USE

    [SolvencyIIdwh]


    GO



    /****** Object:  StoredProcedure [GENERIC].[usp_MIEARNData]    Script Date: 08/02/2013 11:02:01 ******/


    SET

    ANSI_NULLS ON

    GO

    SET

    QUOTED_IDENTIFIER ON

    GO


    /*********************************************************************************************************/

    /***  Create output PRIVATE CAR - MIEARN                                                                 */

                                                                                                      


    /***                                                                                                     */

    /***  Date Author Reason                                                                       */

    /***  20130408 MR New                                                                          */

    /***                                                                                                     */

    /*********************************************************************************************************/



    CREATE

    PROCEDURE [GENERIC].[usp_MIEARNData]


    AS

    INSERT

    INTO SolvencyIIdwh.MOTOR.MIEARNData(

    IncMonth

    ,[Cover]

       

    ,[Stnd]

       

    ,[EarnedPrem]

       

    ,[EarnedPol])



     

    /**************MIEARN/MIMTRF output file*************/

     

    SELECT

    SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)) ,3,2) + SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)) ,6,2) AS 'Month'    

    ,CASE t.MMCOVR

    WHEN '01' THEN 'C'


    ELSE 'N'


    END  AS 'Cover' 

    ,t.MMSTND AS 'Standard'


    ,SUM(e.TOTEARNPRM) AS 'Earned Premium'


    ,SUM(CASE WHEN dotcode = '23536P'THEN (TOTEARNPOL * .40)

    ELSE TOTEARNPOL END) AS 'Earned Policies'


     

    FROM SolvencyIIdwh.MOTOR.MTMIMTRF t

     

    JOIN SolvencyIIdwh.MOTOR.MTMIEARN e

     

    ON t.MMPOLN = e.MEPOLN

     

    AND t.MMAPSQ = e.MEAPSQ

     

    AND t.MMAPDT = e.MEAPDT

     

    JOIN SolvencyIIdwh.LOOKUP.Agent a

     

    ON t.MMAGNT = a.AgentNo

     

    WHERE t.MMSCDT >= '01 Jan 1993'


     

    GROUP BY e.MEACPD,t.MMCOVR,t.MMSTND



    GO

    Can you post Here :

    (1) Indexes on your table used in the query

    (2) Execution plan of the query 

    (3) statistic io 


    Friday, August 02, 2013 10:51 AM
  • 1 You have to check if proper indexes are applied or not.

    2 You can also use

    with (nolock)

    on the tables.

    3 Check what all constraints are present in the destination table


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Friday, August 02, 2013 11:19 AM
  • We need DDL mock to check where you are doing wrong

    ideally if you are comparing date and strings then SQL server is not very good at it. 

    TRy to join on INT columns and less functions in columns in select

    USE [SolvencyIIdwh]
    GO
    
    /****** Object:  StoredProcedure [GENERIC].[usp_MIEARNData]    Script Date: 08/02/2013 11:02:01 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    /*********************************************************************************************************/
    /***  Create output PRIVATE CAR - MIEARN                                                                 */
    /***                                                                                                     */
    /***  Date Author Reason                                                                       */
    /***  20130408 MR New                                                                          */
    /***                                                                                                     */
    /*********************************************************************************************************/
    CREATE PROCEDURE [GENERIC].[usp_MIEARNData]
    AS
    INSERT INTO SolvencyIIdwh.MOTOR.MIEARNData (
    	IncMonth,
    	[Cover],
    	[Stnd],
    	[EarnedPrem],
    	[EarnedPol]
    	)
    /**************MIEARN/MIMTRF output file*************/
    SELECT SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)), 3, 2) + SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)), 6, 2) AS 'Month',
    	CASE t.MMCOVR
    		WHEN '01'
    			THEN 'C'
    		ELSE 'N'
    		END AS 'Cover',
    	t.MMSTND AS 'Standard',
    	SUM(e.TOTEARNPRM) AS 'Earned Premium',
    	SUM(CASE 
    			WHEN dotcode = '23536P'
    				THEN (TOTEARNPOL * .40)
    			ELSE TOTEARNPOL
    			END) AS 'Earned Policies'
    FROM SolvencyIIdwh.MOTOR.MTMIMTRF t
    INNER JOIN SolvencyIIdwh.MOTOR.MTMIEARN e
    	ON t.MMPOLN = e.MEPOLN
    		AND t.MMAPSQ = e.MEAPSQ
    		AND t.MMAPDT = e.MEAPDT
    INNER JOIN SolvencyIIdwh.LOOKUP.Agent a
    	ON t.MMAGNT = a.AgentNo
    WHERE t.MMSCDT >= '01 Jan 1993'
    GROUP BY e.MEACPD,
    	t.MMCOVR,
    	t.MMSTND
    GO
    
    


    • Edited by Prajesh Friday, August 02, 2013 12:55 PM better
    Friday, August 02, 2013 12:54 PM
  • As per my understanding do below:

    1) Ckeck the execution plan if there is any scan is going on then create appropriate index on that. 

    2) None cluster index on which you use in where clause.

    If already above index is there then check fregmantation of underlying table if fragmanted then defrag it and after this update statistics of underlying tables.

    Also check the unnecessory index if there then drop that.

    If you still face the issue then post the execution plan. 

    Saturday, August 03, 2013 3:45 PM
  • Your query looks fine.

    For the number of rows you are handling, it shouldn't take anywhere near 10 hours.

    At the very least, you should check the following:

    • Double check that MTMIMTRF.MMSCDT is a datetime (or date/datetime2/smalldatetime)
    • If instead you have a nonclustered index on MTMIMTRF.MMSCDT, then make sure your statistics are up to date. When in doubt, run UPDATE STATISTICS. If there is no index on this column, then create one
    • Your query shows the literal '01 Jan 1993'. Know that using literals is very different from using parameters. Without any additional query hints, literals work better. So let us know if you are actually using a parameter instead of literal
    • The optimizer has several options for the access path. Because of that, you'd want to have an index on both MTMIMTRF(MMPOLN, MMAPSQ, MMAPDT) and on MTMIEARN(MEPOLN, MEAPSQ, MEAPDT). Preferably, the index on MTMIEARN is clustered
    • For the same reason, you'd want to have an index on Agent(AgentNo) and on MTMIMTRF(MMAGNT). For this specific query, a covering index on MTMIMTRF with the leading column MMSCDT and MMAGNT might be ideal
    • Make sure that you have matching data types for the columns you are joining


    Gert-Jan

    Sunday, August 04, 2013 8:35 AM
  • I would suggest to try the Iif taking over the case statment:

    SELECT

    SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)) ,3,2) + SUBSTRING(Cast(e.MEACPD AS VARCHAR(11)),6,2) AS 'Month'    

    ,IIf ( t.MMCOVR='01','C','N')  AS 'Cover' 

    I noticed that sometimes Iif give you a better performance especially in case like this with only two value. Maybe you can pass from ten hour to eight hour...


    Sunday, August 04, 2013 9:28 AM
  • These following are the suggestions I would like to add in order to improve the sql performance-

    1. Insure indexes are built on right columns

    2. Table MTMIEARN has huge number of records. Try to archive data/purge data if possible.

    3. Use horizontal partitioning to reduce number of records in the ref tables

    4. Try to find bottleneck using Execution plan for the SQL

    5. Add read uncommitted in sp and nolock in SQL

    Sunday, August 04, 2013 11:23 AM
  • ideally if you are comparing date and strings then SQL server is not very good at it. 

    TRy to join on INT columns and less functions in columns in select

    This is a common myth.  The data type choice itself is typically not the long pole in the tent.  On modern hardware, the main thing from a performance perspective is that data types of columns in the JOIN and WHERE clauses match, appropriate indexes exist on the underlying columns and the expressions are sargable (e.g. no functions applied to columns).  I suggest that one design databases with data types most appropriate for the domain of data stored.

    The collation choice can affect performance of searching text (e.g. SomeColumn LIKE '%SomeValue%') but that is not an issue here.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, August 04, 2013 11:43 AM
  • I'm fairly new to this so please be patient.

    Thanks for the code.  But so that we can better help you, please also post the table DDL (CREATE TABLE statements, with indexes and constraints).  This is just as important as the query itself, especially when it comes to performance issues.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, August 04, 2013 11:47 AM