SQL Server Performance

perfomance tuning

Discussion in 'Getting Started' started by dhamu294, May 18, 2007.

  1. dhamu294 New Member

    i have one stored procedure ..in that select statement i have used lot of functions and in where clause i given not in search condition.. this query taking very long time.. can u sugested how to fine tune the query ... i searched many topics sql-server-performance.com

    can u advice me


    DECLARE @client_id smallint

    DECLARE @parent_id smallint



    SET @client_id = 166

    DECLARE @Period_No INT

    SET @Period_No = 220 set @parent_id=166



    select top 10

    add_period = 220,client_id = 166,servicer_id = 166,loan_no = loan_no ,

    legacy_loan_no = loan_no ,

    property_state =dbo.ValidState (prop_state,prop_zip),

    property_zip =dbo.ValidZip (prop_zip,prop_state),

    property_type_id =ISNULL(dbo.LookItUp( @parent_id, @Period_No, 'prop_type', prop_type ),'Z'),

    no_of_units =dbo.into_decimal(units),

    occupancy_id =case

    when occupancy not in ('1','2','3') then 'Z'

    else cast(occupancy as char(1)) end,

    origination_date =dbo.StringToDate(orig_date),

    first_pmt_date =dbo.StringToDate(first_pmt),

    origination_amt =dbo.Into_Money(orig_amt) * .01,

    product_type_id =ISNULL(dbo.LookItUp( @parent_id, @Period_No, 'prod_type', prod_type ),'10'),


    uw_ratio_1 =dbo.into_decimal(NULL),

    [subset_id]= case when @client_id <> dbo.getcid('geb') and loan_no in (

    SELECT loan_no from dbo.ref_geb_gec_loans where client_id=dbo.getCID('GEC'))then cast (1 as char(1)) end

    --INTO Transformations.dbo.trans_wfh220_lps

    from RawData.dbo.raw_wfh220_lps R

    where

    /*loan_no not in (select loan_no from #dupes )

    and */ ltrim(rtrim(branch_cd)) NOT in ('4970', 'DACC')

    and loan_no NOT in (SELECT loan_no from [public].dbo.ref_geb_gec_loans where client_id=dbo.getCID('GEB') AND ISNULL(ORIG_GRADE, SPACE(1))<>'A+')
  2. satya Moderator

    First of all have you checked the execution plan for this SP and recompiled it?
    Also look whether necessary indexes are there for an optimum performance.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page