Results 1 to 11 of 11

Thread: SP optimization

  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Unanswered: SP optimization

    What's the best way to optimize Stored Proc in sybase over 700GB. SP causing server hung up while trying builiding tempdb. Increasing hd is not an option. I'm trying to optimize the execution time which is currently 15-16 hrs. Any help will be appreciated.
    Last edited by spiderman07; 11-04-07 at 20:02.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Without more information the best I can do is point you to
    Performance and Tuning: Monitoring and Analyzing

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by spiderman07
    What's the best way to optimize Stored Proc in sybase over 700GB. SP causing server hung up while trying builiding tempdb. Increasing hd is not an option. I'm trying to optimize the execution time which is currently 15-16 hrs. Any help will be appreciated.
    Agree with PDreyer.

    From what it looks like, one of the problems in the stored proc is that it is filling up tempdb? If this is the case, there are only two things you can do, re-write your queries so that it uses less tempdb OR increase your tempdb.

  4. #4
    Join Date
    Nov 2007
    Posts
    6
    Ok here is the code for temp table. Any suggestion on this.

    CREATE TABLE #tempnetset (
    NWNW_ID varchar(12),
    NWST_PFX varchar(4),
    PRPR_ID varchar(12),
    relationshipCode varchar(15)
    )

    --I want all the I records
    insert into #tempnetset
    SELECT DISTINCT ns.NWNW_ID, ns.NWST_PFX, np.PRPR_ID, 'relationshipCode' =
    CASE ns.NWST_PR_NW_STS
    WHEN 'I' THEN 'In-network'
    END
    FROM CMC_NWST_NET_SET ns
    LEFT OUTER JOIN CMC_NWPR_RELATION np ON (ns.NWNW_ID = np.NWNW_ID)
    WHERE ns.NWST_PR_NW_STS = 'I'
    and np.PRPR_ID != ''

    --I want all the P's that don't have I's
    insert into #tempnetset
    select DISTINCT ns.NWNW_ID, ns.NWST_PFX, np.PRPR_ID,
    'relationshipCode' =
    CASE ns.NWST_PR_NW_STS
    WHEN 'P' THEN 'Participating'
    END
    from CMC_NWST_NET_SET ns
    LEFT OUTER JOIN CMC_NWPR_RELATION np ON (ns.NWNW_ID = np.NWNW_ID), #tempnetset t2
    WHERE ns.NWST_PFX NOT IN(SELECT NWST_PFX FROM #tempnetset)

    AND ns.NWST_PR_NW_STS = 'P'
    and np.PRPR_ID != ''

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    What does the data look like? Do you really need to use distinct?

    One big problem is the cross join, remove it.

    from CMC_NWST_NET_SET ns
    LEFT OUTER JOIN CMC_NWPR_RELATION np ON (ns.NWNW_ID = np.NWNW_ID), #tempnetset t2

    You don't need a temp table
    ...NOT IN(SELECT NWST_PFX FROM #tempnetset)...
    just select from the thetable
    where ...='P' and thecode not in (select thecode from thetable where ...='I')

  6. #6
    Join Date
    Nov 2007
    Posts
    6
    Thanks. I just realized that I am not even able to run

    select * from CMC_NWPR_RELATION

    "It's saying "Application has run out of memory while excecuting a query." I think CMC_NWPR_RELATION table has way too much data. Is it recommended to create a VIEW to filter older data from CMC_NWPR_RELATION table and reference the VIEW instead of the actual table from the store procedure. Thanks.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by spiderman07
    "It's saying "Application has run out of memory while excecuting a query." I think CMC_NWPR_RELATION table has way too much data.
    Indeed it sounds as if more data is returned to your application than it (or your PC) can handle. To have a look at what the 1st 200 records look like you can do select top 200 * from ...
    Quote Originally Posted by spiderman07
    Is it recommended to create a VIEW to filter older data from CMC_NWPR_RELATION table and reference the VIEW instead of the actual table from the store procedure.
    No, just add a where clause to your select. However if you intend to use this type of select in many places it might be easier to use a view instead of coding the where clause every ware.

  8. #8
    Join Date
    Nov 2007
    Posts
    6
    Thanks. I looked at top 200 rows. Looks fine. I do have some other sprocs that will use these big tables. I am going to create views to reference those from sprocs.

  9. #9
    Join Date
    Nov 2007
    Posts
    6
    Hello, Is there any way I can reference my view "c_amb" from the store procedures without actually doing find/replace table xxxx with c_amb. Thanks

  10. #10
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If it won't affect anything else, rename the table then create a view with the name of the original table that only look at current data.
    Or archive old data to a history table

  11. #11
    Join Date
    Nov 2007
    Posts
    6
    Thank you for your help
    Last edited by spiderman07; 11-09-07 at 15:39.

Posting Permissions

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