Databases
StackOverflow2013
Postsdbo
POSQL SP runs Slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL SP runs Slow
    primarykey
    data
    text
    <p>Hi I am using an SP which takes 7 minutes in a server which has 7336 recrds and 6seconds in another server which has 3500 records.</p> <p>Can anybody help me to know why is it happening?</p> <p>Thanks, -Divya</p> <p>THE SP:</p> <p>SELECT WORKSHEET_ID FROM PERSON PER INNER JOIN PERSON EMPLEE ON EMPLEE.PERSON_ID = PER.PERSON_ID AND dbo.FN_CHECKRPTSECURITY(EMPLEE.PERSON_ID, @p_SEC_ACCOUNT_ID) > 0</p> <pre><code> LEFT JOIN SEARCH_ASSIGNMENT_VW PERSON_ASGN ON PERSON_ASGN.ASSIGNMENT_ID = dbo.FN_GETRPTASSIGNMENTID(EMPLEE.PERSON_ID) LEFT JOIN LOOKUP EMPLEE_ASGN_STAT ON EMPLEE_ASGN_STAT.TYPE_ = 'ASSIGNMMENT_STATUS_CODE' AND EMPLEE_ASGN_STAT.CODE = PERSON_ASGN.ASGN_STAT_CODE INNER JOIN (SELECT w1.ASSIGNMENT_ID, w1.WORKSHEET_ID, w1.EFFECTIVE_DATE, w1.APPROVED_BY, w3.CREATED_BY FROM WORKSHEET_PAYROLL_VW w1 INNER JOIN WORKSHEET w3 ON w3.WORKSHEET_ID = w1.WORKSHEET_ID WHERE w1.EFFECTIVE_DATE = CASE WHEN @p_MOST_RECENT_ONLY = 'Y' THEN (SELECT MAX(w2.EFFECTIVE_DATE) FROM WORKSHEET_PAYROLL_VW w2 WHERE w1.ASSIGNMENT_ID = w2.ASSIGNMENT_ID AND (ISNULL(@p_WKS_EFFECTIVE_DATE,w2.EFFECTIVE_DATE) =w2.EFFECTIVE_DATE)) ELSE ISNULL(@p_WKS_EFFECTIVE_DATE,w1.EFFECTIVE_DATE) END ) PERSON_WKS ON PERSON_WKS.ASSIGNMENT_ID = dbo.FN_GETRPTASSIGNMENTID(EMPLEE.PERSON_ID) INNER JOIN (SELECT ASSIGNMENT_ID, VALUE FROM ASSIGNMENT_HISTORY AH WHERE FIELD_NAME ='HOME PAYROLL GROUP' AND EFFECTIVE_DATE = (SELECT MAX(EFFECTIVE_DATE) FROM ASSIGNMENT_HISTORY WHERE ASSIGNMENT_ID = AH.ASSIGNMENT_ID AND EFFECTIVE_DATE &lt;=getDate() AND FIELD_NAME = 'HOME PAYROLL GROUP') )HOME_PAYROLL ON HOME_PAYROLL.ASSIGNMENT_ID = dbo.FN_GETRPTASSIGNMENTID(EMPLEE.PERSON_ID) WHERE (@p_SELECTED_PERSON_ONLY = 'N' OR EMPLEE.PERSON_ID = @p_PERSON_ID) AND (@p_ASGN_STAT_CODE IS NULL OR PERSON_ASGN.ASGN_STAT_CODE = SUBSTRING(@p_ASGN_STAT_CODE,1,1) OR PERSON_ASGN.ASGN_STAT_CODE = SUBSTRING(@p_ASGN_STAT_CODE,2,1)) AND (@p_POLICY_ID IS NULL OR PERSON_ASGN.PROGRAM_CODE = @p_POLICY_ID) AND (@p_HOME_COUNTRY_ID IS NULL OR PERSON_ASGN.HOMECOUNTRYID=@p_HOME_COUNTRY_ID) AND (@p_HOME_CITY_ID IS NULL OR PERSON_ASGN.HOMECITYID=@p_HOME_CITY_ID ) AND (@p_HOME_COMPANY_ID IS NULL OR PERSON_ASGN.HOMEBUSINESSID=@p_HOME_COMPANY_ID ) AND (@p_HOME_DIVISION_ID IS NULL OR PERSON_ASGN.HOMECOMPONENTID=@p_HOME_DIVISION_ID ) AND (@p_HOST_COUNTRY_ID IS NULL OR PERSON_ASGN.HOSTCOUNTRYID=@p_HOST_COUNTRY_ID ) AND (@p_HOST_CITY_ID IS NULL OR PERSON_ASGN.HOSTCITYID=@p_HOST_CITY_ID ) AND (@p_HOST_COMPANY_ID IS NULL OR PERSON_ASGN.HOSTBUSINESSID=@p_HOST_COMPANY_ID ) AND (@p_HOST_DIVISION_ID IS NULL OR PERSON_ASGN.HOSTCOMPONENTID=@p_HOST_DIVISION_ID ) AND (@p_CREATED_BY IS NULL OR PERSON_WKS.CREATED_BY=@p_CREATED_BY ) AND (@p_APPROVED_BY IS NULL OR PERSON_WKS.APPROVED_BY=@p_APPROVED_BY ) AND (@p_payroll_code IS NULL OR HOME_PAYROLL.VALUE=@p_payroll_code ) </code></pre> <p>ORDER BY PER.LAST_NAME ASC, PER.FIRST_NAME ASC, PERSON_WKS.EFFECTIVE_DATE DESC</p> <hr> <p>The Function in the 5th line is the one which is running slow. rest of the part is running in 4secs</p> <hr> <p>The FUNCTION: BEGIN DECLARE @v_ASGN_COUNT INT, @v_RESULT INT</p> <pre><code>SELECT @v_ASGN_COUNT = COUNT(ASSIGNMENT_ID) --to find out if this employee has any assignment FROM ASSIGNMENT WHERE EXPATRIATE_PERSON_ID = @p_PERSON_ID AND ASGN_STAT_CODE IN ('PD','A','I') IF(@v_ASGN_COUNT &gt; 0) --yes assignment, check against SECURITY_ASSIGNMENT_VW BEGIN SELECT @v_RESULT = COUNT(ASSIGNMENT_ID) FROM SECURITY_ASSIGNMENT_VW WHERE SEC_ACCOUNT_ID = @p_SEC_ACCOUNT_ID AND ASSIGNMENT_ID IN (SELECT ASSIGNMENT_ID FROM ASSIGNMENT WHERE EXPATRIATE_PERSON_ID = @p_PERSON_ID AND ASGN_STAT_CODE IN ('PD','A','I')) END ELSE --no assignment, so check against SECURITY_PERSON_VW BEGIN SELECT @v_RESULT = COUNT(PERSON_ID) FROM SECURITY_PERSON_VW WHERE SEC_ACCOUNT_ID = @p_SEC_ACCOUNT_ID AND PERSON_ID = @p_PERSON_ID END RETURN @v_RESULT </code></pre> <p>END</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDivya
    1. USDivya
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CONot without details, no.
      singulars
      1. POSQL SP runs Slow
      1. USOMG Ponies
    2. COPlease provide the code of the Stored Procedure.
      singulars
      1. POSQL SP runs Slow
      1. USRalph Wiggum
    3. COGoggle: "Parameter Sniffing"
      singulars
      1. POSQL SP runs Slow
      1. USRBarryYoung
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload