August 2, 2006 at 11:17 am
This proc starts of quickly then starts to slow down.
any ideas?
Declare @PropertyID bigint
Declare @PropertyIDBuffer bigint
Declare @AVMValue float
Declare @Confidence float
Declare @a bigint
Declare @b-2 smallint
Set NoCount On
Set @a = 1
Set @b-2 = 0
While (@a) > 0
Begin
Select top 1 @PropertyIDBuffer = sa_property_id from tabledetails Where replyavm is null
If @PropertyIDBuffer is not null Or @PropertyIDBuffer > 0
Begin
Set @b-2 = 0
DECLARE tabledetails CURSOR FOR
Select top 50 sa_property_id
From tabledetails
Where replyavm is null
OPEN tabledetails
FETCH NEXT FROM tabledetails
INTO @PropertyID
WHILE @@FETCH_STATUS = 0
BEGIN
Set @AVMValue = 0
Set @Confidence = 0
exec usp_ReplyAVMCalculationtabledetails @PropertyID, @AVMValue output, @Confidence output
If @AVMValue is Null
Begin
Set @AVMValue = 0
Set @Confidence = 0
End
Update tabledetails
Set ReplyAVM = @AVMValue,
ReplyScore = @Confidence
Where sa_property_id = @PropertyID
FETCH NEXT FROM tabledetails
INTO @PropertyID
END
CLOSE tabledetails
DEALLOCATE tabledetails
End
Else
Set @a = 0
End
August 2, 2006 at 12:06 pm
Any ideas?? Yes, this can be rewritten without a cursor. Can you post the code for usp_ReplyAVMCalculationtabledetails?
August 2, 2006 at 12:21 pm
*/
CREATE PROCEDURE [dbo].[usp_AVMCalculationPropertyDetails]
@PropertyID bigint,
@AVM as float output,
@Confidence as float output
As
Declare @defAppraisalWeight float
Declare @defRSIWeight float
Declare @defHedonicWeight float
Declare @defAssessedWeight float
Declare @defFiservWeight float
Declare @defSQFTWeight float
Declare @defLotSizeWeight float
Declare @defPctConfWeight float
Declare @defAvgElementWeight float
Declare @AppraisalValue float
Declare @RSIValue float
Declare @HedonicValue float
Declare @AssessedValue float
Declare @FiservValue float
Declare @SQFTValue float
Declare @LotSizeValue float
Declare @PCTValue float
Declare @AvgElementValue float
Declare @TotalMissingWeight as float
Declare @TotalNonMissingWeight as float
Declare @TotalNonMissingElements as smallint
Declare @WeightPerNonMissingElements as float
Declare @TotalSumValue as float
Declare @TotalSumWeight as float
Declare @DQAVM_Value as float
Declare @PropertyZip as varchar(5)
Declare @PropertyType as varchar(10)
Declare @PctDiffWithDQAVM as float --This store percentage different between each final element and dq avmvalue
Declare @PctIncrFor4Models as float
Declare @PctIncr as float
--Weighing for confidence
Declare @defAppraisalConfWeight float
Declare @defRSIConfWeight float
Declare @defHedonicConfWeight float
Declare @defAssessedConfWeight float
Declare @defAvgElementConfWeight float
Declare @defSQFTConfWeight float
Declare @defLotConfWeight float
Declare @defPctConfWeight float
Declare @defFiservConfWeight float
Set @defAppraisalConfWeight = 4
Set @defRSIConfWeight = 1
Set @defHedonicConfWeight = 4
Set @defFiservConfWeight = 3
Set @defAssessedConfWeight = 0.25
Set @defAvgElementConfWeight = .5
Set @defSQFTConfWeight = .5
Set @defLotConfWeight = .5
Set @defPctConfWeight = 2.5
--Reset Element value if it is below DQAVM
Set @PctDiffWithDQAVM = .2
/*Default Weight of the AVM Elements*/
Set @defAppraisalWeight = 39
Set @defRSIWeight = 10
Set @defHedonicWeight = 12
Set @defAssessedWeight = 0
Set @defFiservWeight = 12
Set @defSQFTWeight = 5
Set @defLotSizeWeight = 5
Set @defPctConfWeight = 5
Set @defAvgElementWeight = 12
Set @PctIncr = .15
Set @PctIncrFor4Models = .025
Declare @LastSoldPrice float
Declare @LastSoldMonthYear varchar(20)
Select
@HedonicValue = isNull([HEDONIC_VALUE], 0) + isNull([HEDONIC_VALUE], 0) * @PctIncrFor4Models,
@AssessedValue = isNull([ASSESSED_VALUE], 0) + isNull([ASSESSED_VALUE], 0) * @PctIncrFor4Models,
@RSIValue = isNull(RSI_VALUE, 0) + isNull([RSI_VALUE], 0) * @PctIncrFor4Models,
@AppraisalValue = isNull(APPR_EMUL_VALUE, 0) + isNull([APPR_EMUL_VALUE], 0) * @PctIncrFor4Models,
@SQFTValue = isNull([AVMSQFT], 0) + (isNull([AVMSQFT], 0) * @PctIncr) , @LotSizeValue = isNull([AVMLotSize], 0) + (isNull([AVMLotSize], 0) * @PctIncr) , @PCTValue = avm_value + (avm_value * @PctIncr), @DQAVM_Value = avm_value, @PropertyZip = sa_site_zip, @PropertyType = USE_CODE_STD,
@LastSoldPrice = sa_val_transfer, @LastSoldMonthYear = SA_DATE_TRANSFER
From PropertyDetails
Where sa_property_id = @PropertyID
/*
Select top 1 @LastSoldPrice = sa_val_transfer, @LastSoldMonthYear = SA_DATE_TRANSFER
From PropertySalesData
Where sa_property_id = @PropertyID
And sr_tran_type = 'R'
And isNumeric(SA_DATE_TRANSFER) = 1
And Len(SA_DATE_TRANSFER) = 8
*/
Declare @isAggregateIndex as bit
Declare @IndexType as tinyint
Declare @NumberOfTiers as tinyint
Declare @LowIndex float
Declare @HighIndex float
Declare @MiddleIndex float
Declare @AggregateIndex float
Declare @HighBreak float
Select top 1 @NumberOfTiers = NumberOfTiers, @LowIndex = LowIndex, @MiddleIndex = MiddleIndex, @HighIndex = HighIndex, @AggregateIndex = AggregateIndex, @HighBreak = MidHighBreak
From ZipTrendIndex
Where ZipCode = @PropertyZip
And YearMonth = Left(@LastSoldMonthYear, 6)
Declare @PercBetweenHouseAndBreaks as float
Set @PercBetweenHouseAndBreaks = @LastSoldPrice / (@HighBreak * 1000)
If @NumberOfTiers = 1 Or @PropertyType = 'RCON' --Condo home or NumberOfTiers = 1 we use aggregate index
Begin
Set @isAggregateIndex = 1
Set @IndexType = 4
End
Else
Begin
Set @isAggregateIndex = 0
If @PercBetweenHouseAndBreaks >= .7
Set @IndexType = 3
If @PercBetweenHouseAndBreaks <= .3
Set @IndexType = 1
If @PercBetweenHouseAndBreaks >.3 And @PercBetweenHouseAndBreaks < .7
Set @IndexType = 2
End
Select @FiservValue =isnull(
Case
When @isAggregateIndex = 1 Then @LastSoldPrice / @AggregateIndex * AggregateIndex
When @isAggregateIndex = 0 And @IndexType = 3 Then @LastSoldPrice / @HighIndex * HighIndex
When @isAggregateIndex = 0 And @IndexType = 2 Then @LastSoldPrice / @MiddleIndex * MiddleIndex
When @isAggregateIndex = 0 And @IndexType = 1 Then @LastSoldPrice / @LowIndex * LowIndex
Else
@LastSoldPrice / @AggregateIndex * AggregateIndex
End, 0)
From ZipTrendIndex
Where ZipCode = @PropertyZip
And YearMonth = 200606
--Reset value if the element value is too different with DQ Avm value
If Isnull(@DQAVM_Value, 0) <> 0
Begin
If 1 - (@AppraisalValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @AppraisalValue = 0
If 1 - (@RSIValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @RSIValue = 0
If 1 - (@HedonicValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @HedonicValue = 0
If 1 - (@AssessedValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @AssessedValue = 0
If 1 - (@SQFTValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @SQFTValue = 0
If 1 - (@LotSizeValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @LotSizeValue = 0
If 1 - (@PCTValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @PCTValue = 0
If 1 - (@FiservValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @FiservValue = 0
if 1 - (@AvgElementValue / @DQAVM_Value) >= @PctDiffWithDQAVM
Set @AvgElementValue = 0
End
/*
We need to keeping track of number of elements that dont have avm
value. Sum up the weight of those missing elements and reset each
element's weight to 0
*/
Set @TotalMissingWeight = 0
Set @TotalNonMissingWeight = 0
Set @TotalNonMissingElements = 0
If @AppraisalValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defAppraisalWeight
Set @defAppraisalWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defAppraisalWeight
End
If @RSIValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defRSIWeight
Set @defRSIWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defRSIWeight
End
If @HedonicValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defHedonicWeight
Set @defHedonicWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defHedonicWeight
End
If @AssessedValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defAssessedWeight
Set @defAssessedWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defAssessedWeight
End
If @FiservValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defFiservWeight
Set @defFiservWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defFiservWeight
End
If @SQFTValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defSQFTWeight
Set @defSQFTWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defSQFTWeight
End
If @LotSizeValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defLotSizeWeight
Set @defLotSizeWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defLotSizeWeight
End
If @PCTValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defPctConfWeight
Set @defPctConfWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defPctConfWeight
End
Set @AvgElementValue = (@AppraisalValue + @RSIValue + @HedonicValue + @AssessedValue + @FiservValue + @SQFTValue + @LotSizeValue + @PCTValue)
If @TotalNonMissingElements <> 0
Set @AvgElementValue = @AvgElementValue / @TotalNonMissingElements
Else
Set @AvgElementValue = 0
--select @AvgElementValue, @TotalNonMissingElements
If @AvgElementValue = 0
Begin
Set @TotalMissingWeight = @TotalMissingWeight + @defAvgElementWeight
Set @defAvgElementWeight = 0
End
Else
Begin
Set @TotalNonMissingElements = @TotalNonMissingElements + 1
Set @TotalNonMissingWeight = @TotalNonMissingWeight + @defAvgElementWeight
End
--Are there any missing elements
If @TotalMissingWeight > 0 And @TotalNonMissingWeight > 0
Begin
Set @WeightPerNonMissingElements = @TotalMissingWeight / @TotalNonMissingWeight
End
Else
Begin
Set @WeightPerNonMissingElements = 0
End
--Set @Confidence = @TotalNonMissingWeight * .96
--Assign non-missing elements new weight
If @AvgElementValue > 0
Set @defAvgElementWeight = @defAvgElementWeight + @defAvgElementWeight * @WeightPerNonMissingElements
If @AppraisalValue > 0
Set @defAppraisalWeight = @defAppraisalWeight + @defAppraisalWeight * @WeightPerNonMissingElements
If @RSIValue > 0
Set @defRSIWeight = @defRSIWeight + @defRSIWeight * @WeightPerNonMissingElements
If @HedonicValue > 0
Set @defHedonicWeight = @defHedonicWeight + @defHedonicWeight * @WeightPerNonMissingElements
If @AssessedValue > 0
Set @defAssessedWeight = @defAssessedWeight + @defAssessedWeight * @WeightPerNonMissingElements
If @FiservValue > 0
Set @defFiservWeight = @defFiservWeight + @defFiservWeight * @WeightPerNonMissingElements
If @SQFTValue > 0
Set @defSQFTWeight = @defSQFTWeight + @defSQFTWeight * @WeightPerNonMissingElements
If @LotSizeValue > 0
Set @defLotSizeWeight = @defLotSizeWeight + @defLotSizeWeight * @WeightPerNonMissingElements
If @PCTValue > 0
Set @defPctConfWeight = @defPctConfWeight + @defPctConfWeight * @WeightPerNonMissingElements
Declare @FinalAppraisalValue float
Declare @FinalRSIValue float
Declare @FinalHedonicValue float
Declare @FinalAssessedValue float
Declare @FinalFiservValue float
Declare @FinalSQFTValue float
Declare @FinalLotValue float
Declare @FinalPCTValue float
Declare @FinalAvgElementValue float
Set @FinalAppraisalValue = @defAppraisalWeight * @AppraisalValue
Set @FinalRSIValue = @defRSIWeight * @RSIValue
Set @FinalHedonicValue = @defHedonicWeight * @HedonicValue
Set @FinalAssessedValue = @defAssessedWeight * @AssessedValue
Set @FinalSQFTValue = @defSQFTWeight * @SQFTValue
Set @FinalLotValue = @defLotSizeWeight * @LotSizeValue
Set @FinalPCTValue = @defPctConfWeight * @PCTValue
Set @FinalFiservValue = @defFiservWeight * @FiservValue
Set @FinalAvgElementValue = @defAvgElementWeight * @AvgElementValue
Set @TotalSumValue = 0
Set @TotalSumWeight = @defAppraisalWeight + @defRSIWeight + @defHedonicWeight + @defAssessedWeight + @defFiservWeight + @defSQFTWeight + @defLotSizeWeight + @defPctConfWeight + @defAvgElementWeight
Set @TotalSumValue = @FinalAppraisalValue + @FinalRSIValue + @FinalHedonicValue + @FinalAssessedValue + @FinalFiservValue + @FinalSQFTValue + @FinalLotValue + @FinalPCTValue + @FinalAvgElementValue
Set @Confidence = 0
If @FinalAppraisalValue <> 0
Begin
Set @Confidence = @Confidence + @defAppraisalConfWeight
End
If @FinalRSIValue <> 0
Begin
Set @Confidence = @Confidence + @defRSIConfWeight
End
If @FinalHedonicValue <> 0
Begin
Set @Confidence = @Confidence + @defHedonicConfWeight
End
If @FinalAssessedValue <> 0
Begin
Set @Confidence = @Confidence + @defAssessedConfWeight
End
If @FinalFiservValue <> 0
Begin
Set @Confidence = @Confidence + @defFiservConfWeight
End
If @FinalAvgElementValue <> 0
Begin
Set @Confidence = @Confidence + @defAvgElementConfWeight
End
If @FinalPCTValue <> 0
Begin
Set @Confidence = @Confidence + @defPctConfWeight
End
If @FinalSQFTValue <> 0
Begin
Set @Confidence = @Confidence + @defSQFTConfWeight
End
If @FinalLotValue <> 0
Begin
Set @Confidence = @Confidence + @defLotConfWeight
End
Set @Confidence = @Confidence + 80
--Select @TotalSumValue / @TotalSumWeight as AVM, @DQAVM_Value as DQAVM, @Confidence as Confidence
If @TotalSumWeight <> 0
Set @avm-2 = @TotalSumValue / @TotalSumWeight
Else
Set @avm-2 = 0
August 2, 2006 at 2:05 pm
Well, I was hoping that your stored procedure would be a little easier to get a handle on. I suggest that you try to re-write your main code to not use a cursor. Doing this may require you to rewrite your stored procedure and maybe even make use of a couple of udf's instead. You may get more help if you post the DDL for all of the tables involved along with some test data and an explanation of your expected results.
August 2, 2006 at 8:00 pm
I think your tabledetails is getting bigger and bigger from time to time. Do you have index for replyavm column?
Instead of running this:
Select top 1 @PropertyIDBuffer = sa_property_id from tabledetails Where replyavm is null
Maybe you can change to exists(). For example:
If exists (select 1 from tabledetails Where replyavm is null)
.... begin your cursor here
But, you better remove your cursor and merge this stored proc with usp_ReplyAVMCalculationtabledetails. This should give you much better performance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply