SQL Server Performance

Query Tuning

Discussion in 'T-SQL Performance Tuning for Developers' started by ramkumar.mu, Jul 3, 2006.

  1. ramkumar.mu New Member

    The below two queries when run together takes 6-8 min for giving out results. Can anyone suggest some ways to tune this query?

    -- Query 1
    SELECT DISTINCT FT.SETID
    ,FT.DEPTID_FILTR
    ,FT.DEPTID
    ,P.EMPLID
    ,N.NAME
    ,A.COUNTRY
    ,A.ADDRESS1
    ,A.ADDRESS2
    ,A.CITY
    ,A.STATE
    ,A.POSTAL
    ,P.PER_STATUS
    FROM PS_PERSON P
    ,PS_JOB J
    ,PS_NAMES N
    ,PS_ADDRESSES A
    ,PS_FAST_SQR_SEC_VW SCRTY
    ,PS_MS_RPT_STO_TBL FT
    ,PS_MS_RUN_CNTL_DPT D
    WHERE J.EMPLID = SCRTY.EMPLID
    AND J.EMPL_STATUS = 'A'
    AND J.EFFDT = (SELECT MAX(J2.EFFDT)
    FROM PS_JOB J2
    WHERE J2.EMPLID = J.EMPLID
    AND J2.EMPL_RCD = J.EMPL_RCD
    AND J2.EFFDT <= getdate())
    AND J.EFFSEQ = (SELECT MAX(J3.EFFSEQ)
    FROM PS_JOB J3
    WHERE J3.EMPLID = J.EMPLID
    AND J3.EMPL_RCD = J.EMPL_RCD
    AND J3.EFFDT = J.EFFDT)
    AND P.EMPLID = J.EMPLID
    AND P.PER_STATUS IN ('E','N')
    AND N.EMPLID = P.EMPLID
    AND N.NAME_TYPE = 'PRI'
    AND N.EFFDT = (SELECT MAX(N2.EFFDT)
    FROM PS_NAMES N2
    WHERE N2.EMPLID = N.EMPLID
    AND N2.NAME_TYPE = N.NAME_TYPE
    AND N2.EFFDT <= getdate())
    AND A.EMPLID = P.EMPLID
    AND A.ADDRESS_TYPE = 'HOME'
    AND A.EFFDT = (SELECT MAX(A2.EFFDT)
    FROM PS_ADDRESSES A2
    WHERE A.EMPLID = A2.EMPLID
    AND A.ADDRESS_TYPE = A2.ADDRESS_TYPE
    AND A.EFFDT <= getdate())
    AND FT.SETID = D.SETID
    AND FT.DEPTID_FILTR = 'STORES'
    AND FT.DEPTID = '0039'
    AND FT.SETID = J.SETID_DEPT
    AND FT.DEPTID = J.DEPTID
    AND D.OPRID = '00014520'
    AND D.RUN_CNTL_ID = 'EMERGENCY_CONTACT'
    ORDER BY FT.SETID
    ,FT.DEPTID_FILTR
    ,FT.DEPTID
    ,N.NAME

    -- Query 2
    SELECT DISTINCT L.SETID
    ,L.LOCATION
    ,P1.EMPLID
    ,N1.NAME
    ,A1.COUNTRY
    ,A1.ADDRESS1
    ,A1.ADDRESS2
    ,A1.CITY
    ,A1.STATE
    ,A1.POSTAL
    ,P1.PER_STATUS
    FROM PS_PERSON P1
    ,PS_JOB J
    ,PS_NAMES N1
    ,PS_ADDRESSES A1
    ,PS_FAST_SQR_SEC_VW SCRTY
    ,PS_LOCATION_TBL L
    ,PS_MS_RUN_CNTL_DPT D
    WHERE J.EMPLID = SCRTY.EMPLID
    AND J.EMPL_STATUS = 'A'
    AND J.EFFDT = (SELECT MAX(J2.EFFDT)
    FROM PS_JOB J2
    WHERE J2.EMPLID = J.EMPLID
    AND J2.EMPL_RCD = J.EMPL_RCD
    AND J2.EFFDT <= getdate())
    AND J.EFFSEQ = (SELECT MAX(J3.EFFSEQ)
    FROM PS_JOB J3
    WHERE J3.EMPLID = J.EMPLID
    AND J3.EMPL_RCD = J.EMPL_RCD
    AND J3.EFFDT = J.EFFDT)
    AND P1.EMPLID = J.EMPLID
    AND P1.PER_STATUS in ('N','L')
    AND N1.EMPLID = P1.EMPLID
    AND N1.NAME_TYPE = 'PRI'
    AND N1.EFFDT = (SELECT MAX(N2.EFFDT)
    FROM PS_NAMES N2
    WHERE N2.EMPLID = N1.EMPLID
    AND N2.NAME_TYPE = N1.NAME_TYPE
    AND N2.EFFDT <= getdate())
    AND A1.EMPLID = P1.EMPLID
    AND A1.ADDRESS_TYPE = 'HOME'
    AND A1.EFFDT = (SELECT MAX(A2.EFFDT)
    FROM PS_ADDRESSES A2
    WHERE A1.EMPLID = A2.EMPLID
    AND A1.ADDRESS_TYPE = A2.ADDRESS_TYPE
    AND A1.EFFDT <= getdate())
    AND L.SETID = D.SETID
    AND L.LOCATION = D.LOCATION
    AND L.SETID = J.SETID_LOCATION
    AND L.LOCATION = J.LOCATION
    AND D.OPRID = '00014520'
    AND D.RUN_CNTL_ID = 'EMERGENCY_CONTACT'
    ORDER BY L.SETID
    ,L.LOCATION
    ,N1.NAME

    The table structure is as given below...

    1) PS_PERSON -- 103351 rows

    index_name index_description index_keys ---------- ------------------ -----------
    PSAPERSON nonclustered located on PRIMARY BIRTHDATE, EMPLID
    PS_PERSON clustered, unique located on PRIMARY EMPLID

    No constraints, No foreign keys, No views

    2) PS_JOB -- 568078 rows

    index_name index_description index_keys
    ---------- -------------------------------- -----------
    PS0JOB nonclustered located on PRIMARY DEPTID, EMPLID, EMPL_RCD, EFFDT, EFFSEQ
    PS1JOB nonclustered located on PRIMARY JOBCODE, EMPLID, EMPL_RCD, EFFDT, EFFSEQ
    PS2JOB nonclustered located on PRIMARY POSITION_NBR, EMPLID, EMPL_RCD, EFFDT, EFFSEQ
    PS3JOB nonclustered located on PRIMARY GVT_TRANS_NBR, EMPLID, EMPL_RCD, EFFDT, EFFSEQ
    PS4JOB nonclustered located on PRIMARY GVT_TRANS_NBR_SEQ, EMPLID, EMPL_RCD, EFFDT, EFFSEQ
    PSAJOB nonclustered located on PRIMARY EMPLID, EMPL_RCD, EFFDT, EFFSEQ, DEPTID
    PSBJOB nonclustered located on PRIMARY BUSINESS_UNIT, EMPLID, EMPL_RCD, EFFDT, EFFSEQ, JOBCODE
    PSCJOB nonclustered located on PRIMARY SUPERVISOR_ID, EMPLID, EMPL_RCD, EFFDT, EFFSEQ
    PS_JOB clustered, unique located on PRIMARY EMPLID, EMPL_RCD, EFFDT, EFFSEQ

    No constraints, No foreign keys, No views

    3) PS_NAMES -- 112981 rows

    index_name index_description index_keys
    ---------- ----------------- -----------
    PS0NAMES nonclustered located on PRIMARY NAME, EMPLID, NAME_TYPE, EFFDT
    PS1NAMES nonclustered located on PRIMARY LAST_NAME_SRCH, EMPLID, NAME_TYPE, EFFDT
    PSANAMES nonclustered located on PRIMARY LAST_NAME_SRCH, FIRST_NAME_SRCH, EMPLID, NAME_TYPE, EFFDT
    PS_NAMES clustered, unique located on PRIMARY EMPLID, NAME_TYPE, EFFDT

    No constraints, No foreign keys, No views

    4) PS_ADDRESSES -- 117997 rows

    index_name index_description index_keys
    ---------- ----------------- -----------
    PS_ADDRESSES clustered, unique located on PRIMARY EMPLID, ADDRESS_TYPE, EFFDT

    No constraints, No foreign keys, No views

    5) PS_FAST_SQR_SEC_VW -- 312723 rows

    No constraints, No foreign keys, No views, No Indexes

    6) PS_LOCATION_TBL -- 40752 rows

    index_name index_description index_keys
    ---------- ----------------- -----------
    PS0LOCATION_TBL nonclustered located on PRIMARY DESCR, SETID, LOCATION, EFFDT
    PS1LOCATION_TBL nonclustered located on PRIMARY SAL_ADMIN_PLAN, SETID, LOCATION, EFFDT
    PS2LOCATION_TBL nonclustered located on PRIMARY GVT_GEOLOC_CD, SETID, LOCATION, EFFDT
    PS_LOCATION_TBL clustered, unique located on PRIMARY SETID, LOCATION, EFFDT

    No constraints, No foreign keys, No views

    7) PS_MS_RUN_CNTL_DPT -- 3154 rows

    No constraints, No foreign keys, No views, No Indexes

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. dineshasanka Moderator

    Did you check the Execution Plan. From that you can identify the processes which takes more resources.



    ----------------------------------------
    http://dineshasanka.blogspot.com/
  3. ramkumar.mu New Member

    The second query is taking 88% of the total time in that two-query-batch. In that second query, two clustered index seek on PS_JOB table and a non clustered index seek on the same table consumes most of the time.

    I checked the fragmentation and all tables are healthy (Scan Density more than 99% for all tables)

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  4. mmarovic Active Member

    Please run next script on production db and let me know the results:


    select count(*) as dCount
    from PS_MS_RUN_CNTL_DPT
    where
    D.OPRID = '00014520' AND
    D.RUN_CNTL_ID = 'EMERGENCY_CONTACT'
    go
    select count(*) as aCount from address where address_type = 'home' and a.effdt <= getDate()
    go
    select count(*) as nCount from PS_NAMES where name_type = 'PRI' and effdt <= getDate()
    go
    select count(*) as pCount from PS_PERSON where per_status in ('N', 'L')
    go
    select count(*) as jCount
    from PS_JOB
    where
    effdt <= getDate() and
    EMPL_STATUS = 'A'

  5. mmarovic Active Member

    It is possible to replace columns l.setID and l.location from the result set with d.setID and d.location from the second query. In that case you probably don't have to join ps_location at all. You have info you need in the PS_MS_RUN_CNTL_DPT unless there are rows in that table not having matching rows in ps_location table.
  6. ramkumar.mu New Member

    quote:Originally posted by mmarovic

    Please run next script on production db and let me know the results:


    select count(*) as dCount
    from PS_MS_RUN_CNTL_DPT
    where
    D.OPRID = '00014520' AND
    D.RUN_CNTL_ID = 'EMERGENCY_CONTACT'
    go
    select count(*) as aCount from address where address_type = 'home' and a.effdt <= getDate()
    go
    select count(*) as nCount from PS_NAMES where name_type = 'PRI' and effdt <= getDate()
    go
    select count(*) as pCount from PS_PERSON where per_status in ('N', 'L')
    go
    select count(*) as jCount
    from PS_JOB
    where
    effdt <= getDate() and
    EMPL_STATUS = 'A'


    56 rows, 103264 rows, 97860 rows, 525 rows, 466220 rows

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  7. mmarovic Active Member

    Please let me know if it is possible to remove PS_LOCATION_TBL from the query 2 the way I described.
  8. ramkumar.mu New Member

    Looking whether it impacts results. please wait...

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  9. mmarovic Active Member

    Also see if join with PS_FAST_SQR_SEC_VW can be removed too.
  10. mmarovic Active Member

    In other words see if:


    select count(distinct p.empID)
    from PS_FAST_SQR_SEC_VW SCRTY
    join PS_PERSON p on p.empID = scrty.empID

    is equal to:



    select count(distinct p.empID)
    from PS_PERSON p

  11. ramkumar.mu New Member

    Removing the view impacts the system a lot.

    1) select count(distinct p.empID) from PS_FAST_SQR_SEC_VW SCRTY join PS_PERSON p on p.empID = scrty.empID

    2) select count(distinct p.empID) from PS_PERSON p

    for eg.. The Queries above returned 96125 and 94381 rows respectively. i.e. some of the emplds are filtered while joining the view.

    But there is a considerable improvement (14 seconds saved) when that table location_tbl is removed. that was a good option indeed.
    Thanks a lot Mirko Marovic...



    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  12. mmarovic Active Member

    You are wellcome.

    What is the type of effseq?
  13. mmarovic Active Member

    Still far from the optimal version, but it should be improvement:


    SELECT DISTINCT D.SETID
    ,D.LOCATION
    ,P1.EMPLID
    ,N1.NAME
    ,A1.COUNTRY
    ,A1.ADDRESS1
    ,A1.ADDRESS2
    ,A1.CITY
    ,A1.STATE
    ,A1.POSTAL
    ,P1.PER_STATUS
    FROM
    (select j.emplID, j.empl_rcd,
    max(convert(varchar(20), j.effdt, 121)
    + '|'
    + replicate('0', 10 - len(cast(j.effseq as varchar(10))))
    + cast(j.effseq as varchar(10))
    ) as effDtSeq
    from PS_PERSON P
    join PS_JOBj on p.emplID = j.emplID
    where
    P.PER_STATUS in ('N','L') and
    j.effdt <= getDate() and
    j.empl_status = 'A'
    group by
    j.emplID,
    j.empl_rcd
    )p1
    join PS_JOB Jon P1.EMPLID = J.EMPLID and
    p1.empl_rcd = j.empl_rcd and
    j.effdt = left(p1.effDtSeq, charindex('|', p1.effDtSeq) - 1)and
    j.effseq = cast(substring(p1.effDtSeq, charindex('|', p1.effDtSeq) + 1, 10) as int)
    join PS_MS_RUN_CNTL_DPT Don J.SETID = D.SETID AND J.LOCATION = D.LOCATION
    join PS_NAMES N1on P1.EMPLID = N1.EMPLID
    join PS_ADDRESSES A1on P1.EMPLID = A1.EMPLID
    join PS_FAST_SQR_SEC_VW SCRTYon P1.EMPLID = SCRTY.EMPLID
    WHERE N1.NAME_TYPE = 'PRI'
    AND N1.EFFDT = (SELECT MAX(N2.EFFDT)
    FROM PS_NAMES N2
    WHERE N2.EMPLID = N1.EMPLID
    AND N2.NAME_TYPE = N1.NAME_TYPE
    AND N2.EFFDT <= getdate())
    AND A1.ADDRESS_TYPE = 'HOME'
    AND A1.EFFDT = (SELECT MAX(A2.EFFDT)
    FROM PS_ADDRESSES A2
    WHERE A1.EMPLID = A2.EMPLID
    AND A1.ADDRESS_TYPE = A2.ADDRESS_TYPE
    AND A1.EFFDT <= getdate())
    AND D.OPRID = '00014520'
    AND D.RUN_CNTL_ID = 'EMERGENCY_CONTACT'
    ORDER BY
    D.SETID
    ,D.LOCATION
    ,N1.NAME

    Please check if the query returns the same data as original one.
  14. ramkumar.mu New Member

    quote:Originally posted by mmarovic

    You are wellcome.

    What is the type of effseq?

    SmallINT

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  15. ramkumar.mu New Member

    quote:Originally posted by mmarovic

    Still far from the optimal version, but it should be improvement:


    SELECT DISTINCT D.SETID
    ,D.LOCATION
    ,P1.EMPLID
    ,N1.NAME
    ,A1.COUNTRY
    ,A1.ADDRESS1
    ,A1.ADDRESS2
    ,A1.CITY
    ,A1.STATE
    ,A1.POSTAL
    ,P1.PER_STATUS
    FROM
    (select j.emplID, j.empl_rcd,
    max(convert(varchar(20), j.effdt, 121)
    + '|'
    + replicate('0', 10 - len(cast(j.effseq as varchar(10))))
    + cast(j.effseq as varchar(10))
    ) as effDtSeq
    from PS_PERSON P
    join PS_JOBj on p.emplID = j.emplID
    where
    P.PER_STATUS in ('N','L') and
    j.effdt <= getDate() and
    j.empl_status = 'A'
    group by
    j.emplID,
    j.empl_rcd
    )p1
    join PS_JOB Jon P1.EMPLID = J.EMPLID and
    p1.empl_rcd = j.empl_rcd and
    j.effdt = left(p1.effDtSeq, charindex('|', p1.effDtSeq) - 1)and
    j.effseq = cast(substring(p1.effDtSeq, charindex('|', p1.effDtSeq) + 1, 10) as int)
    join PS_MS_RUN_CNTL_DPT Don J.SETID = D.SETID AND J.LOCATION = D.LOCATION
    join PS_NAMES N1on P1.EMPLID = N1.EMPLID
    join PS_ADDRESSES A1on P1.EMPLID = A1.EMPLID
    join PS_FAST_SQR_SEC_VW SCRTYon P1.EMPLID = SCRTY.EMPLID
    WHERE N1.NAME_TYPE = 'PRI'
    AND N1.EFFDT = (SELECT MAX(N2.EFFDT)
    FROM PS_NAMES N2
    WHERE N2.EMPLID = N1.EMPLID
    AND N2.NAME_TYPE = N1.NAME_TYPE
    AND N2.EFFDT <= getdate())
    AND A1.ADDRESS_TYPE = 'HOME'
    AND A1.EFFDT = (SELECT MAX(A2.EFFDT)
    FROM PS_ADDRESSES A2
    WHERE A1.EMPLID = A2.EMPLID
    AND A1.ADDRESS_TYPE = A2.ADDRESS_TYPE
    AND A1.EFFDT <= getdate())
    AND D.OPRID = '00014520'
    AND D.RUN_CNTL_ID = 'EMERGENCY_CONTACT'
    ORDER BY
    D.SETID
    ,D.LOCATION
    ,N1.NAME

    Please check if the query returns the same data as original one.

    Mirko,

    P1.PER_STATUS is not selected in the query that is joined, but is in the select list. it throws an error for me.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  16. mmarovic Active Member

    Sorry, I overlooked that column. The solution is to include it in the derived table:

    SELECT DISTINCT D.SETID
    ,D.LOCATION
    ,P1.EMPLID
    ,N1.NAME
    ,A1.COUNTRY
    ,A1.ADDRESS1
    ,A1.ADDRESS2
    ,A1.CITY
    ,A1.STATE
    ,A1.POSTAL
    ,P1.PER_STATUS
    FROM
    (select j.emplID, j.empl_rcd, p.per_status
    max(convert(varchar(20), j.effdt, 121)
    + '|'
    + replicate('0', 6 - len(cast(j.effseq as varchar(6))))
    + cast(j.effseq as varchar(10))
    ) as effDtSeq
    from PS_PERSON P
    join PS_JOBj on p.emplID = j.emplID
    where
    P.PER_STATUS in ('N','L') and
    j.effdt <= getDate() and
    j.empl_status = 'A'
    group by
    j.emplID,
    j.empl_rcd,
    p.per_status
    )p1
    join PS_JOB Jon P1.EMPLID = J.EMPLID and
    p1.empl_rcd = j.empl_rcd and
    j.effdt = left(p1.effDtSeq, charindex('|', p1.effDtSeq) - 1)and
    j.effseq = cast(substring(p1.effDtSeq, charindex('|', p1.effDtSeq) + 1, 6) as smallint)
    join PS_MS_RUN_CNTL_DPT Don J.SETID = D.SETID AND J.LOCATION = D.LOCATION
    join PS_NAMES N1on P1.EMPLID = N1.EMPLID
    join PS_ADDRESSES A1on P1.EMPLID = A1.EMPLID
    join PS_FAST_SQR_SEC_VW SCRTYon P1.EMPLID = SCRTY.EMPLID
    WHERE N1.NAME_TYPE = 'PRI'
    AND N1.EFFDT = (SELECT MAX(N2.EFFDT)
    FROM PS_NAMES N2
    WHERE N2.EMPLID = N1.EMPLID
    AND N2.NAME_TYPE = N1.NAME_TYPE
    AND N2.EFFDT <= getdate())
    AND A1.ADDRESS_TYPE = 'HOME'
    AND A1.EFFDT = (SELECT MAX(A2.EFFDT)
    FROM PS_ADDRESSES A2
    WHERE A1.EMPLID = A2.EMPLID
    AND A1.ADDRESS_TYPE = A2.ADDRESS_TYPE
    AND A1.EFFDT <= getdate())
    AND D.OPRID = '00014520'
    AND D.RUN_CNTL_ID = 'EMERGENCY_CONTACT'
    ORDER BY
    D.SETID
    ,D.LOCATION
    ,N1.NAME

  17. ramkumar.mu New Member

    Sorry Mirko, the above solution didnt give me any results.
    yesterday i was not able to post any reply in SSP.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  18. mmarovic Active Member

    Hi Ram,

    sorry I was on a long vacation. Let me know if you still need to optimize the proc.

    Mirko.
  19. FrankKalis Moderator

    Hi Mirko, hope you enjoyed your holidays. Welcome back! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  20. ramkumar.mu New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br />Hi Ram,<br /><br />sorry I was on a long vacation. Let me know if you still need to optimize the proc.<br /><br />Mirko.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I couldnt find any useful results for th at query and so i modified that into a function separating that max() function into a separate variable. amazingly it ran in less than 3 minutes.<br /><br /> SELECT@MAXDATE = MAX(TD.EFFDT) <br /> FROMPSTREEDEFN TD <br /> WHERETD.SETID = 'STORE'<br /> AND TD.TREE_NAME = 'DEPT_SECURITY' <br /> AND TD.EFFDT &lt;= CONVERT(CHAR(11), @Date)<br /><br /><br />BTW, welcome back... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br /><br /><br /><br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
  21. mmarovic Active Member

    Thank you Frank and Ram. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  22. TherelentlessOne New Member

Share This Page