Stored Procedure runs slow at first execution

[E11esar]

Hi there.

I have a stored procedure which I need to make more efficient. When first run the stored procedure can take as long as 34 seconds to return results / complete processing, but then any subsequent runs take around 8 seconds; still a bit long as this is for a web service.

Here is how the SP is coded:

USE [thisTest_DEV]
GO
/****** Object:  StoredProcedure [dbo].[spGetParticipantsByAgreementItem]    Script Date: 08/05/2010 08:13:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetParticipantsByAgreementItem]
@Client                VARCHAR ( 25 ),
@MaxRecords            INT,
@CustomerID            VARCHAR    ( 25 ),            
@AccountID            VARCHAR    ( 25 )    = ' ',    
@SubAccountID            VARCHAR    ( 25 )    = ' ',    
@IsActive            CHAR    ( 1  )    = ' ',    
@LegacyPartNumber        VARCHAR    ( 25 )    = ' ',    
@IDNumber            VARCHAR ( 25 )    = ' ',
@IDType                VARCHAR ( 25 )    = ' ', 
@Description            VARCHAR ( 255)    = ' ',    
@DOB                DATETIME        = ' ',    
@Gender                VARCHAR    ( 25 )    = ' ',
@BadgeType            VARCHAR    ( 25 )    = ' ',
@BadgeLocation            VARCHAR ( 25 )    = ' ',
@AgreementFrequency        VARCHAR ( 25 )    = ' ',    
@CustomerRef            VARCHAR ( 12 )  = ' ',
@MsgId                VARCHAR (25) OUTPUT,
@IsError            BIT OUTPUT    
AS
BEGIN    
    SET NOCOUNT ON;   
 
    DECLARE @SelectSql             NVARCHAR(MAX)
    DECLARE @SYSTEM_PARAM_GENDER VARCHAR(25)
    DECLARE @ErrorMsg             VARCHAR(25)
    DECLARE @ParticipantId         VARCHAR(25)
    -- Fixed Registers
    DECLARE @FR_IDTYPE            VARCHAR(25)
    DECLARE @FR_DOSITYPE        VARCHAR(25)
    DECLARE @FR_FREQUENCY        VARCHAR(25)
    DECLARE @FR_DOSIMETER        VARCHAR(25)
    DECLARE @OrigPartNbr          VARCHAR(25)
    DECLARE @dateOfBirth        VARCHAR(10)
    DECLARE @Status                VARCHAR(1)
    DECLARE @Count                INT    
 
    SET @SelectSql             = ''
    SET @SYSTEM_PARAM_GENDER = 'Q1_GENDER'    
    SET @IsError             = 0    
    SET @dateOfBirth         = CONVERT(VARCHAR(10),@DOB,111)
    SET @FR_IDTYPE             = 'JL31'    
    SET @FR_DOSITYPE         = 'JL65'
    SET @FR_FREQUENCY         = 'DI'
    SET @FR_DOSIMETER         = 'D0'
    SET @OrigPartNbr         = 'ORIGPARTNBR'
    SET @Status                 = 'N' --Default Active 
    SET @Count                 = 0
 
    -- Validations
    SELECT @MsgId = 
    CASE    
        -- Check that the Gender exists in the System Setup Parameter-Q1_GENDER,if provided.
        WHEN (@Gender!= '' AND NOT EXISTS
                (SELECT description1 FROM asysvaluelist WHERE name = @SYSTEM_PARAM_GENDER and description1 = @Gender))
                THEN '29007'
        -- Check for Invalid ID Type.
        WHEN (@IDType!= '' AND NOT EXISTS
                (SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_IDTYPE    AND dim_value = @IDType
                    AND client = @Client AND status = @Status) ) THEN '29002'
        -- Cannot search for ID Number without a valid ID Type
        WHEN (@IDNumber!= '' AND @IDType ='')
                THEN '29008'
        -- Check if Badge Type exists or not.
        WHEN (@BadgeType!= '' AND NOT EXISTS
                (SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_DOSITYPE    AND dim_value = @BadgeType
                    AND client = @Client AND status = @Status) ) THEN '29009'
        -- Check if Badge Location exists or not.
        WHEN (@BadgeLocation!= '' AND NOT EXISTS
                (SELECT zone FROM aq1zone WHERE zone = @BadgeLocation        
                    AND client = @Client AND status = @Status) ) THEN '29010'
        -- Check if Agreement Frequency exists or not.
        WHEN (@AgreementFrequency!= '' AND NOT EXISTS
                (SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_FREQUENCY    AND dim_value = @AgreementFrequency
                    AND client = @Client AND status = @Status) ) THEN '29011'
    ELSE    ''-- No Validation Error
    END
 
    IF(@MsgId != '')
    BEGIN
        SET @IsError = 1
        RETURN;
    END    
 
    -- Temporary tables.
    IF OBJECT_ID(N'tempdb..#Participant') IS NOT NULL
    BEGIN
        DROP TABLE #Participant    
    END
    CREATE TABLE #Participant
    (
         participant_id VARCHAR(25),
         apar_lookup    VARCHAR(25),
         rowid            INT IDENTITY(1,1)
    )        
 
    IF OBJECT_ID(N'tempdb..#ParticipantInfo') IS NOT NULL
    BEGIN
        DROP TABLE #ParticipantInfo    
    END
    CREATE TABLE #ParticipantInfo
    (
        participant_id        VARCHAR(25),
        last_name            VARCHAR(40),
        first_name            VARCHAR(40),
        description            VARCHAR(255),
        gender                VARCHAR(25),
        birth_date            VARCHAR(25),
        legacy_part_id        VARCHAR(25),        
        active                VARCHAR(1),
        classified_begin    DATETIME,
        classified_end        DATETIME,
        inception_date        DATETIME,
        deactivation_date    DATETIME,
        client                VARCHAR(25),
        print_name            VARCHAR(25),
        nrrw_flag            TINYINT,
        occup_category        VARCHAR(25),
        dose_category        VARCHAR(25),
        middle_name            VARCHAR(40),
        participant_grp     VARCHAR(25),
        participant_typ     VARCHAR(25),
        status              CHAR(1),
        rowid                INT IDENTITY(1,1)
    )
 
    IF OBJECT_ID(N'tempdb..#ParticipantIDInfo') IS NOT NULL
    BEGIN
        DROP TABLE #ParticipantIDInfo    
    END
    CREATE TABLE #ParticipantIDInfo
    (
        participant_id VARCHAR(25),
        id_type        VARCHAR(25),
        id_value       VARCHAR(25),
        primary_flag   TINYINT,
        rowid           INT IDENTITY(1,1)
    )
 
    IF OBJECT_ID(N'tempdb..#ParticipantCASInfo') IS NOT NULL
    BEGIN
        DROP TABLE #ParticipantCASInfo    
    END
    CREATE TABLE #ParticipantCASInfo
    (
        participant_id        VARCHAR(25),
        account                VARCHAR(25),
        subaccount            VARCHAR(25),
        series_code            VARCHAR(100), 
        customers_reference VARCHAR(25),
        rowid                INT IDENTITY(1,1)
    )
 
    IF OBJECT_ID(N'tempdb..#ClassifiedPart') IS NOT NULL
    BEGIN
        DROP TABLE #ClassifiedPart    
    END
    CREATE TABLE #ClassifiedPart(client VARCHAR(25),participant_id VARCHAR(25),date_from DATETIME,
    date_to DATETIME,row_id INT IDENTITY(1,1))
 
    SET @SelectSql = 'INSERT INTO #Participant SELECT DISTINCT  a.participant_id,cas.apar_lookup ' + 
                    ' FROM '
 
    ---====================================
    --        Join on the Filtered CAS List
    --=====================================
    SET @SelectSql = @SelectSql + 
                    ' (SELECT client,apar_lookup,cust,account,subaccount,active ' + 
                    ' FROM aq1vipartcasref ' +                     
                    ' WHERE client = ''' + @Client + ''''                    
 
    SET @SelectSql = @SelectSql +                     
                    CASE    
                        -- When SubAccount is provided ignore the Account and Customer.                        
                        WHEN (@SubAccountId != '') THEN ' AND subaccount= ''' + @SubAccountId + ''' '
                        -- When Account is provided ignore Customer.                        
                        WHEN (@AccountId != '') 
                        THEN ' AND account = ''' + @AccountId + ''' '
                        -- If both subaccount and account are not provided, join on customer
                        ELSE ' AND cust = ''' + @CustomerId + ''' '
                    END
 
    SET @SelectSql = @SelectSql + ') cas '
 
 
 
    ---====================================
    -- Join on the Filtered Agreements List
    --=====================================    
 
    SET @SelectSql = @SelectSql + 
                    ' JOIN aq1agreeitem a ' +
                    ' ON cas.client = a.client ' + 
                    ' AND cas.apar_lookup = a.apar_id ' +                    
                    ' AND a.client = ''' + @Client + ''' '
    IF(@IsActive != '')
    BEGIN
        SET @SelectSql = @SelectSql + 
                        ' AND cas.active = ''' + @IsActive + ''' '
    END
 
    --===========================================
    -- CustomerReference is given in the input.
    --===========================================
    IF(LTRIM(RTRIM(@CustomerRef))!='')
    BEGIN
        SET @SelectSql =  @SelectSql + 
                            'JOIN afxcustref FX 
                            ON a.client    = FX.client 
                            AND a.apar_id    = FX.dim_value 
                            AND FX.customers_reference =''' + @CustomerRef + ''' '
    END
 
    SET @SelectSql = @SelectSql + 
                    ' JOIN aq1participant p ON a.client = p.client '+
                    ' AND a.participant_id = p.participant_id ' +
                    ' AND p.client = ''' + @Client + ''' '  +
                    ' AND p.status = ''' + @Status + ''' '
 
    IF(@Description != '')
    BEGIN
        SET @SelectSql = @SelectSql + 
                        ' AND p.description like (''' + @Description + '%'') ' + ' '
    END
 
    IF(@Gender != '')
    BEGIN
        SET @SelectSql = @SelectSql + 
                        ' AND p.gender = ''' + @Gender + ''' '
    END
 
    IF(@dateOfBirth != '' AND @dateOfBirth !='1900/01/01')
    BEGIN
        SET @SelectSql = @SelectSql + 
                        ' AND convert(varchar(10),p.birth_date,111) = ''' + @dateOfBirth + ''' '
    END
 
    IF(@LegacyPartNumber != '')
    BEGIN
        SET @SelectSql = @SelectSql + 
                        ' AND p.legacy_part_id = ''' + @LegacyPartNumber + ''' '
    END
 
    IF(@IDType!= '')
    BEGIN
        SET @SelectSql = @SelectSql + 
            ' JOIN aq1participantno pn ON p.client = pn.client AND p.participant_id = pn.participant_id ' +
            ' AND pn.id_type = ''' + @IDType + ''' '
 
        IF(@IDNumber!= '')
        BEGIN
            -- CR1513:-
            --If ID Type is ORIGPARTNBR then aq1participantno.id_number like “%-ID Number” 
            --(that is ID Number    exactly matches the characters in the id_number after the “-“ character 
            --but there can be any string before those matching characters).
            --else if ID Type is specified and is not ORIGPARTNBR then aq1participantno.id_value = ID Number provided
 
            IF(@IDType = @OrigPartNbr)
            BEGIN
                SET @SelectSql = @SelectSql + ' AND pn.id_value LIKE (''%-'+ @IDNumber + ''') '
            END
            ELSE 
            BEGIN
                SET @SelectSql = @SelectSql + ' AND pn.id_value = ''' + @IDNumber + ''' '
            END 
        END
    END
 
 
    IF(@BadgeLocation != '')
    BEGIN
        SET @SelectSql = @SelectSql + 
                        ' AND a.zone = ''' + @BadgeLocation + ''' '                        
    END
 
    IF(@AgreementFrequency != '' OR @BadgeType != '')
    BEGIN
        SET @SelectSql = @SelectSql + 
                        ' JOIN auiinvdetail inv on a.client = inv.client '+
                        ' AND a.subscription_id = inv.subscription_id ' +
                        ' AND a.sequence_no = inv.sequence_no ' 
 
 
        IF (@AgreementFrequency != '')
        BEGIN
            SET @SelectSql = @SelectSql + 
                            ' AND inv.frequency = ''' + @AgreementFrequency + ''' ' 
        END 
 
        IF (@BadgeType != '')
        BEGIN
            SET @SelectSql = @SelectSql + 
                            ' JOIN aglrelvalue rel on rel.client = inv.client '+
                            ' AND rel.att_value=inv.article ' +
                            ' AND rel.attribute_id=''' + @FR_DOSIMETER + ''''+
                            ' AND rel.rel_attr_id=''' + @FR_DOSITYPE + '''' +
                            ' AND rel.rel_value =''' + @BadgeType + ''''             
        END 
    END
 
    SET @SelectSql = @SelectSql 
                    + ' ORDER BY a.participant_id '
 
    EXECUTE sp_executesql @SelectSql        
 
    -- If there are no validation errors, then get the list of Participants by AgreementItem.    
 
    -- Get the classified participant details.    
    INSERT INTO #ClassifiedPart
    SELECT a.client,a.participant_id,MAX(a.date_from),a.date_to
    FROM aq1classified a
    JOIN 
    (SELECT client,participant_id,MAX(date_to) as dateto
    FROM aq1classified 
    WHERE client = @Client
    GROUP BY client,participant_id) b
    ON 
    a.client = b.client 
    AND a.participant_id = b.participant_id 
    AND a.date_to = b.dateto
    GROUP BY a.client,a.participant_id,a.date_to                
 
    -- Get ParticipantInfo.
    INSERT INTO #ParticipantInfo
    SELECT 
        DISTINCT
        p.participant_id, 
        ISNULL(p.last_name,''),
        ISNULL(p.first_name,''),
        ISNULL(p.description,''), 
        ISNULL(p.gender,''), 
        CONVERT(varchar(10), p.birth_date, 111) AS birth_date, 
        ISNULL(p.legacy_part_id,''),
        ISNULL(v.Active,''), 
        ISNULL(cl.date_from,'1900/01/01'), 
        ISNULL(cl.date_to,'1900/01/01'), 
        ISNULL(p.inception_date,'1900/01/01'), 
        ISNULL(v.date_to,'1900/01/01'),
        p.client,
        ISNULL(p.print_name,''),
        p.nrrw_flag,
        ISNULL(p.occup_category,''),
        ISNULL(p.dose_category,''),
        ISNULL(p.middle_name,''),
        ISNULL(p.participant_grp,''),
        ISNULL(p.participant_typ,''),
        ISNULL(p.status,'') 
 
    FROM aq1participant p
    JOIN #Participant p1 
        ON p.client = @Client AND p.participant_id = p1.participant_id            
    JOIN aq1vipartcasref v 
        ON v.client = p.client AND v.participant_id = p1.participant_id  and p1.apar_lookup =v.apar_lookup
        AND (@IsActive = '' OR v.Active = @IsActive)
    JOIN aq1agreeitem g ON g.client = v.client and g.apar_id = v.apar_lookup and v.participant_id =g.participant_id
    LEFT JOIN #ClassifiedPart cl 
        ON cl.client = p.client AND cl.participant_id = p1.participant_id
 
    SELECT @Count = count(participant_id) FROM #ParticipantInfo
 
    IF(@Count = 0)
    BEGIN
    -- No Matching Participant.    
        SET @MsgId = '29001'
        SET @IsError = 1
        RETURN;
    END
    ELSE IF(@MaxRecords > 0 AND @Count > @MaxRecords)
    BEGIN
    --    MAX Records Reached.        
        SET @MsgId = '29006'
        SET @Count = @MaxRecords
 
    --  Get only the MaxRecords.
        DELETE FROM #ParticipantInfo WHERE rowid > @MaxRecords
    END
 
    -- Get ParticipantIdInfo.
    INSERT INTO #ParticipantIDInfo
    SELECT DISTINCT pn.participant_id, 
           ISNULL(id_type,''), 
           ISNULL(id_value,''), 
           primary_flag 
    FROM aq1participantno pn        
    JOIN #ParticipantInfo p
    ON pn.client =  @Client 
    AND pn.participant_id = p.participant_id
 
    -- Get Participant CAS Info.
    INSERT INTO #ParticipantCASInfo
    SELECT DISTINCT cas.participant_id, 
           ISNULL(cas.account,''),
           ISNULL(cas.subaccount,''),
           ISNULL(cas.series_code,''), 
           ISNULL(ref.customers_reference,'') as customers_reference
    FROM aq1vipartcasref cas
    JOIN #ParticipantInfo p on cas.client = @Client AND cas.participant_id = p.participant_id
    RIGHT JOIN afxcustref ref ON cas.client = ref.client AND cas.apar_lookup = ref.dim_value 
    AND (@CustomerRef = '' OR ref.customers_reference = @CustomerRef)
    WHERE cas.client = @Client 
    AND cas.level IN('2','3')
 
    -- Return the Participant Details.
    SELECT    
        participant_id,
        last_name,
        first_name,
        description,
        gender,
        birth_date,
        legacy_part_id,
        active,
        classified_begin,
        classified_end,
        inception_date,
        deactivation_date,
        print_name,
        nrrw_flag,
        occup_category,
        dose_category,
        middle_name,
        participant_grp,
        participant_typ,
        status 
    FROM #ParticipantInfo ORDER BY participant_id
 
    SELECT    participant_id,
            id_type,
            id_value,
            primary_flag
    FROM #ParticipantIDInfo ORDER BY participant_id
 
    SELECT    participant_id,
            account,
            subaccount,
            series_code,
            customers_reference
    FROM #ParticipantCASInfo ORDER By participant_id
 
    RETURN
END
 

Is there something obvious which I can tune here please? When I display an execution plan, then it is the INSERTs that appear to be hogging all the processing time. The respective tables used with thye INSERTs are indexed.

Hope to hear from somebody soon as I'm a bit in the dark with this one.

Thank you.

M :)

[Oralloy]

I'm going to display my lack of MSSQLServer knowledge here

First off, may I ask why your "temporary" tables are not created as temporary tables? Isn't there a syntax "CREATE TEMPORARY TABLE ..." available? Or am I caught up in Oracle speak here?

If I recall correctly, inserts into perminant, shared tables are generally expensive operations. How much data are you copying?

On the other hand, the select logic you're using to create the tables does some long series of joins. Are all your join fields appropriately indexed? I know that a missing index on a tiny, 1000 record, table can kill a database's query performance.

I observe that your temporary tables aren't indexed. Is there a reason for that? I see that #ParticipantInfo is used in one of your later queries.

Lastly, have you tried profiling your own function by writing trace records with timing information? This will let you isolate the SQL that you're spending wall-clock time on. Wall-clock is what you're worried about right now, later on you can worry about processor and I/O improvement.

Good luck!

[Jerry Winston]

There's a lot of optimizing to do here. This is a really busy piece of code but I think we can trim it down by analyzing our existing data. An analysis which prompts my first question:

Why can't we trust our static table data? There are numerous IFNULL()'s throughout the code that seem to check fields from our static tables. Why can't we trust the data to contain either '' or NULL?

@Oralloy
IN MS SQL temp tables are created with the octothorpe or pound sign single (#) or double (##). Tables created with a single # are only visible/accessible to the current connection. Tables created with ## are visible/accessible to all connections to the server.

[Oralloy]

@b0010100

Thank you for that bit of information about the octothorpe implicitly identifying temporary tables. I learned something (which is why I'm here).

You are right about E11esar's need to analyze/understand his data.

He also needs to be aware of where his performance bottlenecks are. Thus my suggestion that he do some simple profiling. To paraphrase The Great Knuth, 97% of all optimization is wasted.

Anyway, I do apprecaite your feedback. Thank you!