Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: Sybase Procedure -- Performance Tuning

    Hi
    I have the following procedure created. is there better way or how can the procedure be tuned to get better results. Procedure given below

    Requirement:
    The records for the leads those have the result code (entered in the Filter Criterion) last entered will be retrieved.

    For Example we have lead1, lead2; lead3 in the table will follow result code



    Lead
    Result Code
    DateTime
    Row Version

    Lead 1
    BC
    Feb 6 2004 12:49PM
    0x00010000032757f5

    Lead 1
    BP
    Feb 18 2004 2:52AM
    0x0001000003287f0b

    Lead 1
    CC
    Feb 24 2004 11:26AM
    0x0001000003287d57

    Lead 1
    BT
    Feb 24 2004 11:26AM
    0x0001000003287d64






    Lead 2
    BT
    Feb 19 2004 12:09PM
    0x00010000032757f6

    Lead 2
    CC
    Feb 24 2004 7:52AM
    0x0001000003287f0b

    Lead 2
    BP
    Mar 2 2004 10:12AM
    0x0001000003287d58






    Lead 3
    BC
    Feb 24 2004 7:52AM
    0x0001000003287f1b

    Lead 3
    BT
    Feb 24 2004 11:26AM
    0x0001000003287d60

    Lead 3
    CC
    Mar 2 2004 10:12AM
    0x0001000003287d61




    If the user enters result code CC as the filter Criterion.

    Records for only Lead 3 will be retrieved.

    Lead 1 will not be retrieved even though the date and time are same the last for the result code CC and BT the row version is different ,as per the row version the last record entered is of BT.


    It means the records of all those leads will be retrieved who have the last record entered with result code as 'CC'. for themselves

    Procedure Created

    create proc test_lead_last
    @rslt_cde varchar(10)
    as
    begin
    select lead_id
    from sol_lead
    where lead_id in( select lead_id
    from sol_rsult
    group by lead_id
    having start_dt_tm=max(start_dt_tm)
    and row_vers_stmp=max(row_vers_stmp)
    and sol_rsult_cde = @rslt_cde)
    end
    Regards
    Shalu

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    12
    The following code should also work.

    create proc test_lead_last
    @rslt_cde varchar(10)
    as
    begin
    select lead_id
    from sol_rsult
    where sol_rsult_cde = @rslt_cde
    group by lead_id
    having start_dt_tm=max(start_dt_tm)
    and row_vers_stmp=max(row_vers_stmp)
    end

    Here, I just used your subquery. Since you only need lead_id, you can get it from sol_rsult table also. No need to use sol_lead table.
    Also, the parameter checking sol_rsult_cde = @rslt_cde should not be done in 'having' clause. It should placed in 'where' clause.


    Thanks.
    Pat

Posting Permissions

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