SQL Server Performance Forum – Threads Archive
perfomance tuning
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 meDECLARE @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+’)
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.
]]>