469,317 Members | 1,950 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,317 developers. It's quick & easy.

Stored Procedure runs slow at first execution

132 100+
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:

Expand|Select|Wrap|Line Numbers
  1. USE [thisTest_DEV]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[spGetParticipantsByAgreementItem]    Script Date: 08/05/2010 08:13:31 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE [dbo].[spGetParticipantsByAgreementItem]
  9. @Client                VARCHAR ( 25 ),
  10. @MaxRecords            INT,
  11. @CustomerID            VARCHAR    ( 25 ),            
  12. @AccountID            VARCHAR    ( 25 )    = ' ',    
  13. @SubAccountID            VARCHAR    ( 25 )    = ' ',    
  14. @IsActive            CHAR    ( 1  )    = ' ',    
  15. @LegacyPartNumber        VARCHAR    ( 25 )    = ' ',    
  16. @IDNumber            VARCHAR ( 25 )    = ' ',
  17. @IDType                VARCHAR ( 25 )    = ' ', 
  18. @Description            VARCHAR ( 255)    = ' ',    
  19. @DOB                DATETIME        = ' ',    
  20. @Gender                VARCHAR    ( 25 )    = ' ',
  21. @BadgeType            VARCHAR    ( 25 )    = ' ',
  22. @BadgeLocation            VARCHAR ( 25 )    = ' ',
  23. @AgreementFrequency        VARCHAR ( 25 )    = ' ',    
  24. @CustomerRef            VARCHAR ( 12 )  = ' ',
  25. @MsgId                VARCHAR (25) OUTPUT,
  26. @IsError            BIT OUTPUT    
  27. AS
  28. BEGIN    
  29.     SET NOCOUNT ON;   
  30.  
  31.     DECLARE @SelectSql             NVARCHAR(MAX)
  32.     DECLARE @SYSTEM_PARAM_GENDER VARCHAR(25)
  33.     DECLARE @ErrorMsg             VARCHAR(25)
  34.     DECLARE @ParticipantId         VARCHAR(25)
  35.     -- Fixed Registers
  36.     DECLARE @FR_IDTYPE            VARCHAR(25)
  37.     DECLARE @FR_DOSITYPE        VARCHAR(25)
  38.     DECLARE @FR_FREQUENCY        VARCHAR(25)
  39.     DECLARE @FR_DOSIMETER        VARCHAR(25)
  40.     DECLARE @OrigPartNbr          VARCHAR(25)
  41.     DECLARE @dateOfBirth        VARCHAR(10)
  42.     DECLARE @Status                VARCHAR(1)
  43.     DECLARE @Count                INT    
  44.  
  45.     SET @SelectSql             = ''
  46.     SET @SYSTEM_PARAM_GENDER = 'Q1_GENDER'    
  47.     SET @IsError             = 0    
  48.     SET @dateOfBirth         = CONVERT(VARCHAR(10),@DOB,111)
  49.     SET @FR_IDTYPE             = 'JL31'    
  50.     SET @FR_DOSITYPE         = 'JL65'
  51.     SET @FR_FREQUENCY         = 'DI'
  52.     SET @FR_DOSIMETER         = 'D0'
  53.     SET @OrigPartNbr         = 'ORIGPARTNBR'
  54.     SET @Status                 = 'N' --Default Active 
  55.     SET @Count                 = 0
  56.  
  57.     -- Validations
  58.     SELECT @MsgId = 
  59.     CASE    
  60.         -- Check that the Gender exists in the System Setup Parameter-Q1_GENDER,if provided.
  61.         WHEN (@Gender!= '' AND NOT EXISTS
  62.                 (SELECT description1 FROM asysvaluelist WHERE name = @SYSTEM_PARAM_GENDER and description1 = @Gender))
  63.                 THEN '29007'
  64.         -- Check for Invalid ID Type.
  65.         WHEN (@IDType!= '' AND NOT EXISTS
  66.                 (SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_IDTYPE    AND dim_value = @IDType
  67.                     AND client = @Client AND status = @Status) ) THEN '29002'
  68.         -- Cannot search for ID Number without a valid ID Type
  69.         WHEN (@IDNumber!= '' AND @IDType ='')
  70.                 THEN '29008'
  71.         -- Check if Badge Type exists or not.
  72.         WHEN (@BadgeType!= '' AND NOT EXISTS
  73.                 (SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_DOSITYPE    AND dim_value = @BadgeType
  74.                     AND client = @Client AND status = @Status) ) THEN '29009'
  75.         -- Check if Badge Location exists or not.
  76.         WHEN (@BadgeLocation!= '' AND NOT EXISTS
  77.                 (SELECT zone FROM aq1zone WHERE zone = @BadgeLocation        
  78.                     AND client = @Client AND status = @Status) ) THEN '29010'
  79.         -- Check if Agreement Frequency exists or not.
  80.         WHEN (@AgreementFrequency!= '' AND NOT EXISTS
  81.                 (SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_FREQUENCY    AND dim_value = @AgreementFrequency
  82.                     AND client = @Client AND status = @Status) ) THEN '29011'
  83.     ELSE    ''-- No Validation Error
  84.     END
  85.  
  86.     IF(@MsgId != '')
  87.     BEGIN
  88.         SET @IsError = 1
  89.         RETURN;
  90.     END    
  91.  
  92.     -- Temporary tables.
  93.     IF OBJECT_ID(N'tempdb..#Participant') IS NOT NULL
  94.     BEGIN
  95.         DROP TABLE #Participant    
  96.     END
  97.     CREATE TABLE #Participant
  98.     (
  99.          participant_id VARCHAR(25),
  100.          apar_lookup    VARCHAR(25),
  101.          rowid            INT IDENTITY(1,1)
  102.     )        
  103.  
  104.     IF OBJECT_ID(N'tempdb..#ParticipantInfo') IS NOT NULL
  105.     BEGIN
  106.         DROP TABLE #ParticipantInfo    
  107.     END
  108.     CREATE TABLE #ParticipantInfo
  109.     (
  110.         participant_id        VARCHAR(25),
  111.         last_name            VARCHAR(40),
  112.         first_name            VARCHAR(40),
  113.         description            VARCHAR(255),
  114.         gender                VARCHAR(25),
  115.         birth_date            VARCHAR(25),
  116.         legacy_part_id        VARCHAR(25),        
  117.         active                VARCHAR(1),
  118.         classified_begin    DATETIME,
  119.         classified_end        DATETIME,
  120.         inception_date        DATETIME,
  121.         deactivation_date    DATETIME,
  122.         client                VARCHAR(25),
  123.         print_name            VARCHAR(25),
  124.         nrrw_flag            TINYINT,
  125.         occup_category        VARCHAR(25),
  126.         dose_category        VARCHAR(25),
  127.         middle_name            VARCHAR(40),
  128.         participant_grp     VARCHAR(25),
  129.         participant_typ     VARCHAR(25),
  130.         status              CHAR(1),
  131.         rowid                INT IDENTITY(1,1)
  132.     )
  133.  
  134.     IF OBJECT_ID(N'tempdb..#ParticipantIDInfo') IS NOT NULL
  135.     BEGIN
  136.         DROP TABLE #ParticipantIDInfo    
  137.     END
  138.     CREATE TABLE #ParticipantIDInfo
  139.     (
  140.         participant_id VARCHAR(25),
  141.         id_type        VARCHAR(25),
  142.         id_value       VARCHAR(25),
  143.         primary_flag   TINYINT,
  144.         rowid           INT IDENTITY(1,1)
  145.     )
  146.  
  147.     IF OBJECT_ID(N'tempdb..#ParticipantCASInfo') IS NOT NULL
  148.     BEGIN
  149.         DROP TABLE #ParticipantCASInfo    
  150.     END
  151.     CREATE TABLE #ParticipantCASInfo
  152.     (
  153.         participant_id        VARCHAR(25),
  154.         account                VARCHAR(25),
  155.         subaccount            VARCHAR(25),
  156.         series_code            VARCHAR(100), 
  157.         customers_reference VARCHAR(25),
  158.         rowid                INT IDENTITY(1,1)
  159.     )
  160.  
  161.     IF OBJECT_ID(N'tempdb..#ClassifiedPart') IS NOT NULL
  162.     BEGIN
  163.         DROP TABLE #ClassifiedPart    
  164.     END
  165.     CREATE TABLE #ClassifiedPart(client VARCHAR(25),participant_id VARCHAR(25),date_from DATETIME,
  166.     date_to DATETIME,row_id INT IDENTITY(1,1))
  167.  
  168.     SET @SelectSql = 'INSERT INTO #Participant SELECT DISTINCT  a.participant_id,cas.apar_lookup ' + 
  169.                     ' FROM '
  170.  
  171.     ---====================================
  172.     --        Join on the Filtered CAS List
  173.     --=====================================
  174.     SET @SelectSql = @SelectSql + 
  175.                     ' (SELECT client,apar_lookup,cust,account,subaccount,active ' + 
  176.                     ' FROM aq1vipartcasref ' +                     
  177.                     ' WHERE client = ''' + @Client + ''''                    
  178.  
  179.     SET @SelectSql = @SelectSql +                     
  180.                     CASE    
  181.                         -- When SubAccount is provided ignore the Account and Customer.                        
  182.                         WHEN (@SubAccountId != '') THEN ' AND subaccount= ''' + @SubAccountId + ''' '
  183.                         -- When Account is provided ignore Customer.                        
  184.                         WHEN (@AccountId != '') 
  185.                         THEN ' AND account = ''' + @AccountId + ''' '
  186.                         -- If both subaccount and account are not provided, join on customer
  187.                         ELSE ' AND cust = ''' + @CustomerId + ''' '
  188.                     END
  189.  
  190.     SET @SelectSql = @SelectSql + ') cas '
  191.  
  192.  
  193.  
  194.     ---====================================
  195.     -- Join on the Filtered Agreements List
  196.     --=====================================    
  197.  
  198.     SET @SelectSql = @SelectSql + 
  199.                     ' JOIN aq1agreeitem a ' +
  200.                     ' ON cas.client = a.client ' + 
  201.                     ' AND cas.apar_lookup = a.apar_id ' +                    
  202.                     ' AND a.client = ''' + @Client + ''' '
  203.     IF(@IsActive != '')
  204.     BEGIN
  205.         SET @SelectSql = @SelectSql + 
  206.                         ' AND cas.active = ''' + @IsActive + ''' '
  207.     END
  208.  
  209.     --===========================================
  210.     -- CustomerReference is given in the input.
  211.     --===========================================
  212.     IF(LTRIM(RTRIM(@CustomerRef))!='')
  213.     BEGIN
  214.         SET @SelectSql =  @SelectSql + 
  215.                             'JOIN afxcustref FX 
  216.                             ON a.client    = FX.client 
  217.                             AND a.apar_id    = FX.dim_value 
  218.                             AND FX.customers_reference =''' + @CustomerRef + ''' '
  219.     END
  220.  
  221.     SET @SelectSql = @SelectSql + 
  222.                     ' JOIN aq1participant p ON a.client = p.client '+
  223.                     ' AND a.participant_id = p.participant_id ' +
  224.                     ' AND p.client = ''' + @Client + ''' '  +
  225.                     ' AND p.status = ''' + @Status + ''' '
  226.  
  227.     IF(@Description != '')
  228.     BEGIN
  229.         SET @SelectSql = @SelectSql + 
  230.                         ' AND p.description like (''' + @Description + '%'') ' + ' '
  231.     END
  232.  
  233.     IF(@Gender != '')
  234.     BEGIN
  235.         SET @SelectSql = @SelectSql + 
  236.                         ' AND p.gender = ''' + @Gender + ''' '
  237.     END
  238.  
  239.     IF(@dateOfBirth != '' AND @dateOfBirth !='1900/01/01')
  240.     BEGIN
  241.         SET @SelectSql = @SelectSql + 
  242.                         ' AND convert(varchar(10),p.birth_date,111) = ''' + @dateOfBirth + ''' '
  243.     END
  244.  
  245.     IF(@LegacyPartNumber != '')
  246.     BEGIN
  247.         SET @SelectSql = @SelectSql + 
  248.                         ' AND p.legacy_part_id = ''' + @LegacyPartNumber + ''' '
  249.     END
  250.  
  251.     IF(@IDType!= '')
  252.     BEGIN
  253.         SET @SelectSql = @SelectSql + 
  254.             ' JOIN aq1participantno pn ON p.client = pn.client AND p.participant_id = pn.participant_id ' +
  255.             ' AND pn.id_type = ''' + @IDType + ''' '
  256.  
  257.         IF(@IDNumber!= '')
  258.         BEGIN
  259.             -- CR1513:-
  260.             --If ID Type is ORIGPARTNBR then aq1participantno.id_number like “%-ID Number” 
  261.             --(that is ID Number    exactly matches the characters in the id_number after the “-“ character 
  262.             --but there can be any string before those matching characters).
  263.             --else if ID Type is specified and is not ORIGPARTNBR then aq1participantno.id_value = ID Number provided
  264.  
  265.             IF(@IDType = @OrigPartNbr)
  266.             BEGIN
  267.                 SET @SelectSql = @SelectSql + ' AND pn.id_value LIKE (''%-'+ @IDNumber + ''') '
  268.             END
  269.             ELSE 
  270.             BEGIN
  271.                 SET @SelectSql = @SelectSql + ' AND pn.id_value = ''' + @IDNumber + ''' '
  272.             END 
  273.         END
  274.     END
  275.  
  276.  
  277.     IF(@BadgeLocation != '')
  278.     BEGIN
  279.         SET @SelectSql = @SelectSql + 
  280.                         ' AND a.zone = ''' + @BadgeLocation + ''' '                        
  281.     END
  282.  
  283.     IF(@AgreementFrequency != '' OR @BadgeType != '')
  284.     BEGIN
  285.         SET @SelectSql = @SelectSql + 
  286.                         ' JOIN auiinvdetail inv on a.client = inv.client '+
  287.                         ' AND a.subscription_id = inv.subscription_id ' +
  288.                         ' AND a.sequence_no = inv.sequence_no ' 
  289.  
  290.  
  291.         IF (@AgreementFrequency != '')
  292.         BEGIN
  293.             SET @SelectSql = @SelectSql + 
  294.                             ' AND inv.frequency = ''' + @AgreementFrequency + ''' ' 
  295.         END 
  296.  
  297.         IF (@BadgeType != '')
  298.         BEGIN
  299.             SET @SelectSql = @SelectSql + 
  300.                             ' JOIN aglrelvalue rel on rel.client = inv.client '+
  301.                             ' AND rel.att_value=inv.article ' +
  302.                             ' AND rel.attribute_id=''' + @FR_DOSIMETER + ''''+
  303.                             ' AND rel.rel_attr_id=''' + @FR_DOSITYPE + '''' +
  304.                             ' AND rel.rel_value =''' + @BadgeType + ''''             
  305.         END 
  306.     END
  307.  
  308.     SET @SelectSql = @SelectSql 
  309.                     + ' ORDER BY a.participant_id '
  310.  
  311.     EXECUTE sp_executesql @SelectSql        
  312.  
  313.     -- If there are no validation errors, then get the list of Participants by AgreementItem.    
  314.  
  315.     -- Get the classified participant details.    
  316.     INSERT INTO #ClassifiedPart
  317.     SELECT a.client,a.participant_id,MAX(a.date_from),a.date_to
  318.     FROM aq1classified a
  319.     JOIN 
  320.     (SELECT client,participant_id,MAX(date_to) as dateto
  321.     FROM aq1classified 
  322.     WHERE client = @Client
  323.     GROUP BY client,participant_id) b
  324.     ON 
  325.     a.client = b.client 
  326.     AND a.participant_id = b.participant_id 
  327.     AND a.date_to = b.dateto
  328.     GROUP BY a.client,a.participant_id,a.date_to                
  329.  
  330.     -- Get ParticipantInfo.
  331.     INSERT INTO #ParticipantInfo
  332.     SELECT 
  333.         DISTINCT
  334.         p.participant_id, 
  335.         ISNULL(p.last_name,''),
  336.         ISNULL(p.first_name,''),
  337.         ISNULL(p.description,''), 
  338.         ISNULL(p.gender,''), 
  339.         CONVERT(varchar(10), p.birth_date, 111) AS birth_date, 
  340.         ISNULL(p.legacy_part_id,''),
  341.         ISNULL(v.Active,''), 
  342.         ISNULL(cl.date_from,'1900/01/01'), 
  343.         ISNULL(cl.date_to,'1900/01/01'), 
  344.         ISNULL(p.inception_date,'1900/01/01'), 
  345.         ISNULL(v.date_to,'1900/01/01'),
  346.         p.client,
  347.         ISNULL(p.print_name,''),
  348.         p.nrrw_flag,
  349.         ISNULL(p.occup_category,''),
  350.         ISNULL(p.dose_category,''),
  351.         ISNULL(p.middle_name,''),
  352.         ISNULL(p.participant_grp,''),
  353.         ISNULL(p.participant_typ,''),
  354.         ISNULL(p.status,'') 
  355.  
  356.     FROM aq1participant p
  357.     JOIN #Participant p1 
  358.         ON p.client = @Client AND p.participant_id = p1.participant_id            
  359.     JOIN aq1vipartcasref v 
  360.         ON v.client = p.client AND v.participant_id = p1.participant_id  and p1.apar_lookup =v.apar_lookup
  361.         AND (@IsActive = '' OR v.Active = @IsActive)
  362.     JOIN aq1agreeitem g ON g.client = v.client and g.apar_id = v.apar_lookup and v.participant_id =g.participant_id
  363.     LEFT JOIN #ClassifiedPart cl 
  364.         ON cl.client = p.client AND cl.participant_id = p1.participant_id
  365.  
  366.     SELECT @Count = count(participant_id) FROM #ParticipantInfo
  367.  
  368.     IF(@Count = 0)
  369.     BEGIN
  370.     -- No Matching Participant.    
  371.         SET @MsgId = '29001'
  372.         SET @IsError = 1
  373.         RETURN;
  374.     END
  375.     ELSE IF(@MaxRecords > 0 AND @Count > @MaxRecords)
  376.     BEGIN
  377.     --    MAX Records Reached.        
  378.         SET @MsgId = '29006'
  379.         SET @Count = @MaxRecords
  380.  
  381.     --  Get only the MaxRecords.
  382.         DELETE FROM #ParticipantInfo WHERE rowid > @MaxRecords
  383.     END
  384.  
  385.     -- Get ParticipantIdInfo.
  386.     INSERT INTO #ParticipantIDInfo
  387.     SELECT DISTINCT pn.participant_id, 
  388.            ISNULL(id_type,''), 
  389.            ISNULL(id_value,''), 
  390.            primary_flag 
  391.     FROM aq1participantno pn        
  392.     JOIN #ParticipantInfo p
  393.     ON pn.client =  @Client 
  394.     AND pn.participant_id = p.participant_id
  395.  
  396.     -- Get Participant CAS Info.
  397.     INSERT INTO #ParticipantCASInfo
  398.     SELECT DISTINCT cas.participant_id, 
  399.            ISNULL(cas.account,''),
  400.            ISNULL(cas.subaccount,''),
  401.            ISNULL(cas.series_code,''), 
  402.            ISNULL(ref.customers_reference,'') as customers_reference
  403.     FROM aq1vipartcasref cas
  404.     JOIN #ParticipantInfo p on cas.client = @Client AND cas.participant_id = p.participant_id
  405.     RIGHT JOIN afxcustref ref ON cas.client = ref.client AND cas.apar_lookup = ref.dim_value 
  406.     AND (@CustomerRef = '' OR ref.customers_reference = @CustomerRef)
  407.     WHERE cas.client = @Client 
  408.     AND cas.level IN('2','3')
  409.  
  410.     -- Return the Participant Details.
  411.     SELECT    
  412.         participant_id,
  413.         last_name,
  414.         first_name,
  415.         description,
  416.         gender,
  417.         birth_date,
  418.         legacy_part_id,
  419.         active,
  420.         classified_begin,
  421.         classified_end,
  422.         inception_date,
  423.         deactivation_date,
  424.         print_name,
  425.         nrrw_flag,
  426.         occup_category,
  427.         dose_category,
  428.         middle_name,
  429.         participant_grp,
  430.         participant_typ,
  431.         status 
  432.     FROM #ParticipantInfo ORDER BY participant_id
  433.  
  434.     SELECT    participant_id,
  435.             id_type,
  436.             id_value,
  437.             primary_flag
  438.     FROM #ParticipantIDInfo ORDER BY participant_id
  439.  
  440.     SELECT    participant_id,
  441.             account,
  442.             subaccount,
  443.             series_code,
  444.             customers_reference
  445.     FROM #ParticipantCASInfo ORDER By participant_id
  446.  
  447.     RETURN
  448. END
  449.  
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 :)
Aug 5 '10 #1
3 2387
Oralloy
983 Expert 512MB
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!
Aug 5 '10 #2
Jerry Winston
145 Expert 100+
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.
Aug 10 '10 #3
Oralloy
983 Expert 512MB
@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!
Aug 10 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Bill Smith | last post: by
2 posts views Thread by mike | last post: by
2 posts views Thread by Patrick Finnegan | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.