sproc tuning..

HI ,
I have tuned a sproc...initially it took 2 minutes ( 12381 rows ) to run but second time it took about 45 seconds ....and on production server to my very surprise it took about
7 minutes but next time it runs in 20 sec....So can we considered it tuned ???
What i mean to say...execution time of sproc when it run first time should be neglected
or not beacause in many cases it run in half the time in second attempt..
but if i run the command DBCC dropcleanbuffers..it again take the same time..
I have used temp tables and table variables heavily in my sproc with clustered index
on temp table...
Regrds,


Rohit Kochar
MCP .Net(Web and SqlServer)
Company : TCS ,Noida

Looks like cached data is improving the response time on the second run. Could you post the (skeleton) sproc?

Hi Adriaan ,
Right sql query analyzer is picking it from cache....But I am confused whether it has to do
something with TempDB or not ?? I m using temporary table and table vaiables in my sproc
heavily which are stored in tempDB and since restatrting the server clean the tempDb as it is
again then copied from Model DB ..itz size is also set to initial one...that means every time I
shut dowm my server all my sprocs using ( temp table and variable) when run first time will take time and in second time they will run fast....
Anyways I am sending u the proc..Please tell me where i need to tuned it again...
**********************---------------------------------*********************************************


alter Procedure [dbo].[sproc_PPM_RPTTotalDepositsDueFromProperties_new]
(
@ip_UrbanEstateCode varchar (5),
@ip_SectorId varchar (20)=null ,
@ip_CategoryCode varchar (5) =null,
@ip_SubCategoryCode varchar (5)=null ,
@ip_PlotNumber varchar(20)=null,
@ip_LocationCode varchar(5)
)

AS
Set nocount on
Declare @PlotId int,
-- @GLdescription varchar(150),
@DueDate varchar(50),
-- @AmountToBePaid decimal(14,2),
@FullName varchar(82),
@FatherFullName varchar(82),
@TotalCost decimal(14,2),
@UrbanEstateName varchar(40),
@SectorId varchar(20),
@CategoryName varchar(40),
@SubCategoryName varchar(40),
@PlotNumber varchar(20),
@DueAmount decimal(14,2),
@PaymentCategory varchar(90),
@AccountCode varchar(5),
@LastPaymentDate datetime,
@TotalInterestPaid decimal(14,2),
@EnhancementInterestAmt decimal(14,2),
@PossessionInterestAmt decimal(14,2),
@TotalAmount decimal(14,2)
SET @TotalAmount = 0


create table #TempTable
(

FullName varchar(82),
FatherFullName varchar(82),
TotalCost decimal(14,2) ,
UrbanEstateName varchar(40),
SectorId varchar(20),
CategoryName varchar(40),
SubCategoryName varchar(40),
PlotNumber varchar(20),
GLDescription varchar(150),
DueAmount decimal(14,2),
DueDate varchar(30),
PlotID int
)



---Print ' B0 : Begins account table insert '+' : '+ convert(varchar,getdate(),109)
declare @temp_tb_AccountCodeMaster table
(
TempAccountCode varchar(5),
TempGL_Code int
primary key (TempAccountCode)
)

insert into @temp_tb_AccountCodeMaster
select AccountCode , GL_Code from tb_AccountCodeMaster

--Print ' B0 : end account table insert '+' : '+ convert(varchar,getdate(),109)

---Print ' B1 : Begins curent ouststtable '+' : '+ convert(varchar,getdate(),109)
declare @Temptb_ppm_CurrentOutstanding TABLE
(
TempCuOustPlotId int,
TempCuOustGLCode int,
TempCuOustCurrentOutstandingAmount decimal(14,2)

)

insert into @Temptb_ppm_CurrentOutstanding
select PlotId ,GLCode ,CurrentOutstandingAmount from
tb_ppm_CurrentOutstanding where CurrentOutstandingAmount > 0
and PartitionColumn = PlotId%8

--Print ' B1 : curent ouststtable insert ends '+' : '+ convert(varchar,getdate(),109)

---Print ' B2 : payment adjustment insert '+' : '+ convert(varchar,getdate(),109)
Declare @Temp_PaymentAdjustment table
(PlotId int , PaymentId int, AdjustedPaymentAmount decimal(14, 2) )

Insert into @Temp_PaymentAdjustment
Select PlotId, PaymentId , AdjustedPaymentAmount
from Tb_PPM_AdjustmentPayment
where GLCode in ((Select TempGL_Code FROM @temp_tb_AccountCodeMaster where
TempAccountCode in ('ENINT','POINT')))
and PartitionColumn = PlotId%8

---Print ' B2 : ends payment adjustment insert '+' : '+ convert(varchar,getdate(),109)

/*create temp table for Tb_PPM_PaymentSchedule aded by rohit 6/6/2006*/
---Print ' B3 : Begins PaymentSchedule '+' : '+ convert(varchar,getdate(),109)
create table #TempTb_PPM_PaymentSchedule
(
TempPPSPlotid int ,
TempPPSGlcode int ,
TempPPSPrincipalDueAmount decimal(14,2),
TempPPSEnhancementNumber varchar(20),
TempPPSDueDate datetime
)

insert into #TempTb_PPM_PaymentSchedule
select Plotid , Glcode , PrincipalDueAmount , EnhancementNumber , DueDate
from Tb_PPM_PaymentSchedule where GLCOde in
(Select TempGL_Code FROM @temp_tb_AccountCodeMaster where
TempAccountCode in ('ENHAN','INSTA') )
and PartitionColumn = Plotid%8

createclustered index IndPPS on #TempTb_PPM_PaymentSchedule
(TempPPSPlotid)

---Print ' B3 : end insert PaymentSchedule '+' : '+ convert(varchar,getdate(),109)


---Print ' B4 : Begins curr outstanding account '+' : '+ convert(varchar,getdate(),109)
create table #TempTb_PPM_CurrentOutstandingAccount
(

TempCOAAsOnDate datetime ,
TempCOAPlotId int,
TempCOAGLCODE int ,
TempCOAOSCalculationFlag varchar(1),
TempCOAOutstandingInterest decimal(14,2)
)

insert into #TempTb_PPM_CurrentOutstandingAccount
select AsOnDate , PlotId , GLCODE , OSCalculationFlag , OutstandingInterest
from Tb_PPM_CurrentOutstandingAccount where OSCalculationFlag <> 'M'
AND PartitionColumn = plotid% 8 and
GLCODE in
(Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode in ('ENINT','POINT')
)


create clustered index IndCurrentOutstandingAccount on #TempTb_PPM_CurrentOutstandingAccount
(TempCOAPlotId )

--Print ' B4 : curr outstanding account insert ends '+' : '+ convert(varchar,getdate(),109)


---temp table for table Tb_PPM_PlotDetail rohit kochar 7/6/2007
---Print ' B6 : insert plot details '+' : '+ convert(varchar,getdate(),109)
create table #TempTb_PPM_PlotDetail
(
Name varchar(40) ,
Surname varchar(40) ,
FatherName varchar(40) ,
FatherSurname varchar(40) ,
IncreasedCost decimal(14,2) ,
LocationCost decimal(14,2) ,
Cost decimal(14,2) ,
PlotId int ,
SectorId varchar(20),
PlotNumber varchar(20),
UrbanEstateCode varchar(5) ,
CategoryCode varchar(5) ,
SubCategoryCode varchar(5)

)


insert into #TempTb_PPM_PlotDetail
select Name ,Surname ,FatherName ,FatherSurname ,IncreasedCost ,
LocationCost ,Cost ,PlotId ,SectorId ,PlotNumber ,UrbanEstateCode ,
CategoryCode ,SubCategoryCode from Tb_PPM_PlotDetail
where UrbanEstateCode = @ip_UrbanEstateCode
and PartitionColumn = plotid%8

create clustered index IndPPD on #TempTb_PPM_PlotDetail
(PlotId)

---Print ' B6 : insert end plot details '+' : '+ convert(varchar,getdate(),109)



---Print ' B5 : enter cursor '+' : '+ convert(varchar,getdate(),109)
declare Curs_1 cursor for
select
upper(substring(PPD.Name,1,1))+lower(substring(PPD.Name,2,len(PPD.Name)))+' '+upper(substring(PPD.Surname,1,1))+lower(substring(PPD.Surname,2,len(PPD.Surname))) AS FullName,
upper(substring(PPD.FatherName,1,1))+lower(substring(PPD.FatherName,2,len(PPD.FatherName)))+' '+upper(substring(PPD.FatherSurname,1,1))+lower(substring(PPD.FatherSurname,2,len(PPD.FatherSurname))) AS FatherFullName,
--TotalCost,
Isnull(PPD.IncreasedCost,0) + Isnull(PPD.LocationCost,0) + isnull(PPD.Cost,0) + (Select ISNull(SUM(TempPPSPrincipalDueAmount),0) FROM #TempTb_PPM_PaymentSchedule
where TempPPSPlotId = PPD.PlotId
--and EnhancementNumber <> '0' commented on 4th may'07
and TempPPSGLCOde in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode = 'ENHAN')) +
(Select ISNULL(SUM(EnhancementCost),0) FROM tb_PPM_PlotMemoDetail
where PlotId = PPD.PlotId
and LetterName = 'DELET'
and EnhancementCost is not null
and EnhancementNumber not in (Select TempPPSEnhancementNumber FROM #TempTb_PPM_PaymentSchedule
where TempPPSPlotId = PPD.PlotId
--and EnhancementNumber = '0' commented on 4th may'07
and TempPPSGLCOde in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode = 'ENHAN')))
AS TotalCost,
b.UrbanEstateName,
PPD.SectorId,
c.CategoryName,
d.SubCategoryName,
PPD.PlotNumber,
PPD.PlotId,
convert(varchar,getdate(),103) As DueDate,
Curr.TempCuOustCurrentOutstandingAmount As DueAmount,
coa.GL_Description AS PaymentCategory ,
AcMas.TempAccountCode
FROM #TempTb_PPM_PlotDetail PPD,
Tb_PPM_UrbanEstateMaster b,
Tb_PPM_PropertyCategoryMaster c,
Tb_PPM_PropertySubCategoryMaster d,
Tb_PPM_EstateOfficeMaster e,
@temp_tb_AccountCodeMaster AcMas,
@Temptb_ppm_CurrentOutstanding Curr,
Tb_FAS_COA_Mast coa

WHERE Curr.TempCuOustGLCode = coa.GL_Code and Curr.TempCuOustCurrentOutstandingAmount > 0
AND PPD.plotid = Curr.TempCuOustPlotId
and AcMas.TempGL_Code = Curr.TempCuOustGLCode
AND PPD.UrbanEstateCode = @ip_UrbanEstateCode
AND PPD.UrbanEstateCode = b.UrbanEstateCode
AND PPD.SectorId = isnull(@ip_SectorId,PPD.SectorId)
AND PPD.CategoryCode = isnull(@ip_CategoryCode,PPD.CategoryCode)
AND PPD.CategoryCode = c.CategoryCode
AND PPD.SubCategoryCode = isnull(@ip_SubCategoryCode,PPD.SubCategoryCode)
AND PPD.SubCategoryCode = d.SubCategoryCode
AND PPD.CategoryCode = d.CategoryCode
AND PPD.PlotNumber = isnull(@ip_PlotNumber,PPD.PlotNumber)
AND e.EstateOfficeCode = b.EstateOfficeCode
and convert(datetime,Convert(varchar,getdate(),102)) BETWEEN convert(datetime,Convert(varchar, b.DateFrom,102)) AND IsNull( convert(datetime,Convert(varchar, b.DateTo ,102)) , convert(datetime,Convert(varchar,getdate(),102)))
and convert(datetime,Convert(varchar,getdate(),102)) BETWEEN convert(datetime,Convert(varchar, e.DateFrom,102)) AND IsNull( convert(datetime,Convert(varchar, e.DateTo ,102)) , convert(datetime,Convert(varchar,getdate(),102)))

AND (@ip_LocationCode = 'HO001' OR e.ZoneCode = @ip_LocationCode OR e.EstateOfficeCode = @ip_LocationCode)

open Curs_1
FETCH NEXT FROM Curs_1 INTO @FullName, @FatherFullName, @TotalCost,@UrbanEstateName,@SectorId,
@CategoryName,@SubCategoryName,@PlotNumber,@PlotId,@DueDate,@DueAmount,@PaymentCategory,@AccountCode
WHILE @@fetch_status=0
BEGIN



if (@AccountCode =('DIINS'))
BEGIN
SELECT @DueAmount = SUM(TempCuOustCurrentOutstandingAmount)
FROM @Temptb_ppm_CurrentOutstanding
WHERE TempCuOustPlotId = @Plotid
AND TempCuOustGLCode IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where
TempAccountCode in ('DIINS','DIPOA','DIIAC'))

END

if (@AccountCode = 'ENINT')
begin
select @LastPaymentDate= IsNull(MAX(TempCOAAsOnDate), '01/01/1800')
from #TempTb_PPM_CurrentOutstandingAccount where TempCOAPlotId = @Plotid

and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'ENINT')
and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) < convert(datetime,Convert(varchar,getdate(),102))
and ( (convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) in (Select convert(datetime,Convert(varchar,TempPPSDueDate,102)) From
#TempTb_PPM_PaymentSchedule where TempPPSplotid = @Plotid
and TempPPSGLCode IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where
TempAccountCode = 'ENHAN')))
/*OR
(convert(datetime,Convert(varchar,AsONDate,102)) in (Select convert(datetime,Convert(varchar,PaymentDate,102)) from tb_PPM_PaymentReceived
where Plotid = @ip_PlotId and PaymentFlag = 'P' and PaymentId in (Select PaymentId from
Tb_PPM_AdjustmentPayment where PlotId = @ip_PlotId and
GLCode in (Select GL_Code from tb_AccountCodeMaster where AccountCode IN ('ENINT', 'ENHAN','DIENH')))))
OR
(convert(datetime,Convert(varchar,AsOnDate,102)) in (Select convert(datetime,Convert(varchar,ValidStartDate-1,102)) From tb_PPM_InterestRateMaster where InterestType = 'ENHAN')) */
)
---AND TempCOAOSCalculationFlag <> 'M'


select @EnhancementInterestAmt = IsNull(SUM(TempCOAOutstandingInterest),0) from
#TempTb_PPM_CurrentOutstandingAccount
where TempCOAPlotId = @Plotid
and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'ENINT')
and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) =
convert(datetime,Convert(varchar,@LastPaymentDate,102))
---and TempCOAOSCalculationFlag <> 'M'

SELECT @TotalInterestPaid = IsNull(SUM(AdjustedPaymentAmount) ,0)
-- FROM Tb_PPM_AdjustmentPayment
FROM @Temp_PaymentAdjustment
WHERE PlotId = @Plotid
--AND GLCODE in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode in ('ENINT'))
AND PaymentId in (select PaymentId from tb_PPM_PaymentReceived
where plotId = @Plotid
and convert(datetime,Convert(varchar,paymentdate,102)) between convert(datetime,Convert(varchar,@LastPaymentDate,102)) and convert(datetime,Convert(varchar,getdate(),102)) )

SET @DueAmount = @EnhancementInterestAmt - @TotalInterestPaid

IF @DueAmount < 0
SET @DueAmount = 0

end



--SET @TotalAmount = @TotalAmount - @EnhancementInterestAmt

if (@AccountCode = 'POINT')
begin
select @LastPaymentDate= IsNull(MAX(TempCOAAsOnDate), '01/01/1800')
from #TempTb_PPM_CurrentOutstandingAccount where TempCOAPlotId = @Plotid

and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'POINT')
and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) < convert(datetime,Convert(varchar,getdate(),102))
and ( (convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) in (Select convert(datetime,Convert(varchar,TempPPSDueDate,102)) From #TempTb_PPM_PaymentSchedule where TempPPSplotid = @Plotid
and TempPPSGLCode IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'INSTA')))
/*OR
(convert(datetime,Convert(varchar,AsONDate,102)) in (Select convert(datetime,Convert(varchar,PaymentDate,102)) from tb_PPM_PaymentReceived
where Plotid = @ip_PlotId and PaymentFlag = 'P' and PaymentId in (Select PaymentId from
Tb_PPM_AdjustmentPayment where PlotId = @ip_PlotId and
GLCode in (Select GL_Code from tb_AccountCodeMaster where AccountCode IN ('POINT', 'INSTA')))))
OR
(convert(datetime,Convert(varchar,AsOnDate,102)) in (Select convert(datetime,Convert(varchar,ValidStartDate-1,102)) From tb_PPM_InterestRateMaster where InterestType = 'POSES')) */
)
---AND TempCOAOSCalculationFlag <> 'M'



select @PossessionInterestAmt = IsNull(SUM(TempCOAOutstandingInterest),0) from #TempTb_PPM_CurrentOutstandingAccount
where TempCOAPlotId = @Plotid
and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'POINT')
and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) = convert(datetime,Convert(varchar,@LastPaymentDate,102))
--- and TempCOAOSCalculationFlag <> 'M'

SELECT @TotalInterestPaid = IsNull(SUM(AdjustedPaymentAmount) ,0)
FROM @Temp_PaymentAdjustment
WHERE PlotId = @Plotid
-- AND GLCODE in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode in ('POINT'))
and PaymentId in (select PaymentId from tb_PPM_PaymentReceived
where plotId = @Plotid
and convert(datetime,Convert(varchar,paymentdate,102)) between convert(datetime,Convert(varchar,@LastPaymentDate,102)) and convert(datetime,Convert(varchar,getdate(),102)) )

SET @DueAmount = @PossessionInterestAmt - @TotalInterestPaid

IF @DueAmount < 0
SET @DueAmount = 0
end
if (@AccountCode not in ('DIPOA','DIIAC') )
insert into #TempTable
(
FullName ,
FatherFullName ,
TotalCost ,
UrbanEstateName ,
SectorId ,
CategoryName ,
SubCategoryName ,
PlotNumber ,
GLDescription ,
DueAmount ,
DueDate ,
PlotId
)
values
(
@FullName,
@FatherFullName,
@TotalCost,
@UrbanEstateName,
@SectorId,
@CategoryName,
@SubCategoryName,
@PlotNumber,
@PaymentCategory,
@DueAmount,
@DueDate,
@PlotId
)

FETCH NEXT FROM Curs_1 INTO @FullName, @FatherFullName, @TotalCost,@UrbanEstateName,@SectorId,
@CategoryName,@SubCategoryName,@PlotNumber,@PlotId,@DueDate,@DueAmount,@PaymentCategory,@AccountCode
end
close Curs_1
deallocate Curs_1
---Print ' B5 : end cursor '+' : '+ convert(varchar,getdate(),109)


---Print ' B6 : drop and select to temptable'+' : '+ convert(varchar,getdate(),109)
drop table #TempTb_PPM_PaymentSchedule
drop table #TempTb_PPM_CurrentOutstandingAccount
drop table #TempTb_PPM_PlotDetail

create clustered index CITempTable on #TempTable (dueamount)

select * from #TempTable where dueamount <> 0
order by UrbanEstateName,
CASE WHEN (ascii(LEFT(SectorId,1)) between 48 and 57) THEN
convert(integer, dbo.translate(SectorId,'-ABCDEFGHIJKLMNOPQRSTUVWXYZ','')) ELSE 999999999 END, sectorid,
CategoryName, convert(integer, dbo.translate(PlotNumber, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',''))



drop table #TempTable
---Print ' B6 : drop and select to temptable ends'+' : '+ convert(varchar,getdate(),109)












Rohit Kochar
MCP .Net(Web and SqlServer)
Company : TCS ,Noida

Caching will affect only data read from permanent tables (and perhaps global temp tables, but you don't have any of those).

Why the mix of temp tables and table variables? I would stick to variables - better chance of things getting handled in memory, not in tempdb (no guarantee, though).

Did you notice that the cursor repeats for the number of rows found in that table variable, but the assignment that you do in the loop is not cumulative, and you are not storing the SUM value anywhere else? So after the loop, the value in @DueAmount is that of the last iteration only.

The subqueries might also be less than ideal.

With this type of processing, I always wonder how much of it can be done in a single query ...

You start by taking the queries that fill the temp tables and table variables, and use them as derived tables in your FROM clause (leave the joins until later). If this involves any aggregate functions, don't worry at this point. Don't forget to include the relevant filter criteria inside the derived tables.

You then add the table that you query in the cursor, and start replacing the parameter(s) by appropriate joins.

You then add any additional tables you need to get the results.

Happy restructuring!

Well , As I read and experienced table variables are faster than temporaray table but
in case where original table you used to populate table variables contains more data
it is better to use temporaray table with clustered index
You can read it here
http://www.sql-server-performance.com/dt_temp_tables_vs_variables.asp

I have used table variable with a primary key where I knew it beforehand that number of
rows is not that much coz with large data table variable doesnt give that good
performance ...I dont have much idea about Derrived tables I will go through it now...
It was a gud analysis Adriaan...

Rohit Kochar
MCP .Net(Web and SqlServer)
Company : TCS ,Noida

If we're talking about table variables that are not silently done as temp tables, then the amount of data is probably not big enough so that you would notice much difference in performance for clustered or nonclustered.<br /><br />The idea behind derived tables is that SQL can work out the best strategy by itself, using the indexes that already exist. If you're telling SQL to move data into a temp table, then it will follow your strategy (which might still be the best one, for all I know [<img src='/community/emoticons/emotion-5.gif' alt='' />]) and you have to make sure that your temp tables have supporting indexes for the operation.

Fine i will go through derived tables ....one non technical qs <img src='/community/emoticons/emotion-1.gif' alt='' />...<br />what does this 5 star below ur name shows????<br /><br />Rohit Kochar<br />MCP .Net(Web and SqlServer)<br />Company : TCS ,Noida

It's related to the number of posts I've made on this forum, nothing else.[<img src='/community/emoticons/emotion-5.gif' alt='' />]