DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Can somebody give a good tuning for my query

  1. #1
    Join Date
    Feb 2003
    Location
    India
    Posts
    1

    Can somebody give a good tuning for my query

    Hi,

    I have a problem with my sql query, it is taking much time to execute the query, mainly i am facing performance issue.I will be great full if you can helpme by giving a good solution for my query.The following is my query. if I use "Distinct" it is returning the correct result , but taking much time, where as if i wont use "Distinct" it result is fst but returnig some thousands of rows.



    SQL Query :


    Select Distinct(dc.DOC_ID),dc.file_nm,dc.DOC_SZ_VAL,dc.titl_txt,us.FIRST_NM,us.MID_NM,us.LAST_NM,dc.AUTH_U_ ID,dc.UPDT_DT as updt, Kl.KEY_NM ,dc.FILE_DELD_IND,ag.AGCY_NM from JJCFRE.DOC_RCPNT dr,JJCFRE.DOC dc,jjcfre.user_GRP ug,jjcfre.DOC_KEY_LST dk,jjcfre.KEY_LST kl,jjcfre.cfre_user us ,JJCFRE.AGCY ag,JJCFRE.DOC_SECRY_AUTH sa where (dc.AUTH_U_ID=1 and dc.FILE_DELD_IND=0 or dc.DOC_ID=dr.DOC_ID and dr.U_Id=1 or dc.DOC_ID=sa.DOC_ID and sa.SECRY_AUTH_U_ID=1 and sa.SECRY_DOC_STAT_CD!=1) and us.U_ID=dc.AUTH_U_ID and ug.U_ID=us.u_id and ug.AGCY_ID=ag.AGCY_ID and kl.KEY_GRP_ID=4 and dc.doc_id=dk.doc_id and dk.KEY_ID=kl.KEY_ID order by dc.UPDT_DT desc


    Thanks Srinivas

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Srinivas,Ithinkyouwouldgetabetterresponsetoyourpostifyoupaidattentiontothelayout!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    something like this

    SELECT DISTINCT (dc.doc_id)
    ,dc.file_nm
    ,dc.doc_sz_val
    ,dc.titl_txt
    ,us.first_nm
    ,us.mid_nm
    ,us.last_nm
    ,dc.auth_u_id
    ,dc.updt_dt AS updt
    ,kl.key_nm
    ,dc.file_deld_ind
    ,ag.agcy_nm
    FROM jjcfre.doc_rcpnt dr
    ,jjcfre.doc dc
    ,jjcfre.user_grp ug
    ,jjcfre.doc_key_lst dk
    ,jjcfre.key_lst kl
    ,jjcfre.cfre_user us
    ,jjcfre.agcy ag
    ,jjcfre.doc_secry_auth sa
    WHERE ( dc.auth_u_id = 1 AND dc.file_deld_ind = 0
    OR dc.doc_id = dr.doc_id AND dr.u_id = 1
    OR dc.doc_id = sa.doc_id
    AND sa.secry_auth_u_id = 1
    AND sa.secry_doc_stat_cd != 1
    )
    AND us.u_id = dc.auth_u_id
    AND ug.u_id = us.u_id
    AND ug.agcy_id = ag.agcy_id
    AND kl.key_grp_id = 4
    AND dc.doc_id = dk.doc_id
    AND dk.key_id = kl.key_id
    ORDER BY dc.updt_dt DESC
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29
    SELECT DISTINCT
    dc.DOC_ID, dc.file_nm, dc.DOC_SZ_VAL, dc.titl_txt, us.FIRST_NM, us.MID_NM, us.LAST_NM, dc.AUTH_U_ID,
    dc.UPDT_DT as updt, Kl.KEY_NM, dc.FILE_DELD_IND, ag.AGCY_NM
    FROM dr, dc, ug, dk, kl, us, ag, sa
    WHERE ( dc.AUTH_U_ID = 1
    and dc.FILE_DELD_IND = 0
    or dc.DOC_ID = dr.DOC_ID
    and dr.U_Id = 1
    or dc.DOC_ID = sa.DOC_ID
    and sa.SECRY_AUTH_U_ID = 1
    and sa.SECRY_DOC_STAT_CD != 1
    )
    and us.U_ID = dc.AUTH_U_ID
    and ug.U_ID = us.u_id
    and ug.AGCY_ID = ag.AGCY_ID
    and kl.KEY_GRP_ID = 4
    and dc.doc_id = dk.doc_id
    and dk.KEY_ID = kl.KEY_ID
    ORDER BY dc.UPDT_DT desc

    Maybe you should create indexes on fields that you make the condition.

    please collect me if I'm wrong!!!
    Regards,
    P.Peach

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by peach_partner
    please collect me if I'm wrong!!!
    Collected

    why dont you post your explain plan... or the trace generated by tkprof...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29
    Regards,
    P.Peach

  7. #7
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi,

    The reason for the query with the distinct being slow is because the resultset has to be sorted and after that clearing all doubles has to be done.

    Without the distinct keyword the complete resultset can be put to your client session as it is fetched giving a quick response but also containg double reccords.

    Tycho

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width