November 25, 2008 at 12:35 am
Lynn,
Just wanted to thank you again, apart from a couple of missing "@"' your code worked perfectly. It is a much more elegant process than what I came up with.
Thanks
November 25, 2008 at 7:07 am
Proper etiquette would have you posting your final code for others to see how the problem was solved.
Also, it would be interesting to know if there was a performance improvement over the original code, and if so to a comparision between the two. I'd certainly be interested in this after spending 4 hours writing the code.
November 25, 2008 at 8:57 am
Lynn Pettis (11/24/2008)
On a personal note, this forum thread allowed me to break 3000 forum posts!Edit: And that does NOT count this post!
Woo Hoo! Way to go, Lynn!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 25, 2008 at 10:13 pm
Lynn,
I ending up removing the temporary table for the source data and used the main query directly in the update code you wrote - I only added this additonal temp table because I was incrementally looping through the data and didn't want to call the main query lots of times (with your code this is now unnecessary). I also removed the specified columns for the main source data query because I don't need to specifically align them with the structure of the temp source data table I removed, now just returning all columns. The code is a lot simplier and will be easy to use as a template for the other construction classes. I don't really have any speed difference results as this construction class has very few records in it, but I will be using the same code structure another 9-10 queries some of which have a lot more records in them. Once again thanks for your assistance. Here is the code I ended up with -
ALTER FUNCTION [spatial].[BR_Calcs_Fn]()
-----------------------------------------------------------------
-- This function returns calculated data for Bridges
-----------------------------------------------------------------
RETURNS
-----------------------------------------------------------------
-- Table structure for calculated data to be returned
-----------------------------------------------------------------
@BR_Calcs_Tbl TABLE
(
BR_JOBS_ID_PK_CALC int PRIMARY KEY NOT NULL,
BR_Traffic_Vol_Score decimal(24,10),
BR_Detour_Length_Score decimal(24,10),
Priority_Score decimal(24,10),
Benifit_Maintenance decimal(24,10),
VPD_to_Detour decimal(24,10),
Time_factor_sub decimal(24,10),
Time_Factor decimal(24,10),
Operating_Factor decimal(24,10),
Safety_Factor decimal(24,10),
Detour_Cost_per_Day decimal(24,10),
Benifit_Commercial_Vehical_Detour_Cost decimal(24,10),
Benifit_Floodway_Safety_Cost decimal(24,10),
Benifit_Obstruction_Safety_Cost decimal(24,10),
BENIFIT decimal(24,10),
Upgraded_Bridge_Area decimal(24,10),
Construction_Cost decimal(24,10),
Design_Etc_Cost decimal(24,10),
Total_Cost decimal(24,10),
COST decimal(24,10),
BCR decimal(24,10),
Total_Score decimal(24,10),
Renual_Amount decimal(24,10)
)
AS
BEGIN
-----------------------------------------------------------------
-- Populate return table with BR_JOBS_ID_PK from BR_JOBS
-----------------------------------------------------------------
insert into
@BR_Calcs_Tbl (BR_JOBS_ID_PK_CALC)
select
BR_JOBS_ID_PK
from
spatial.BR_JOBS
order by
BR_JOBS_ID_PK;
-----------------------------------------------------------------
-- Declaration of local variables to store calculated data --
-----------------------------------------------------------------
DECLARE
@BR_Traffic_Vol_Score decimal(24,10),
@BR_Detour_Length_Score decimal(24,10),
@Priority_Score decimal(24,10),
@Benifit_Maintenance decimal(24,10),
@VPD_to_Detour decimal(24,10),
@Time_factor_sub decimal(24,10),
@Time_Factor decimal(24,10),
@Operating_Factor decimal(24,10),
@Safety_Factor decimal(24,10),
@Detour_Cost_per_Day decimal(24,10),
@Benifit_Commercial_Vehical_Detour_Cost decimal(24,10),
@Benifit_Floodway_Safety_Cost decimal(24,10),
@Benifit_Obstruction_Safety_Cost decimal(24,10),
@BENIFIT decimal(24,10),
@Upgraded_Bridge_Area decimal(24,10),
@Construction_Cost decimal(24,10),
@Design_Etc_Cost decimal(24,10),
@Total_Cost decimal(24,10),
@COST decimal(24,10),
@BCR decimal(24,10),
@Total_Score decimal(24,10),
@Renual_Amount decimal(24,10)
-----------------------------------------------------------------
-- Insert calculated local variables into return table
-----------------------------------------------------------------
update @BR_Calcs_Tbl set
-----------------------------------------------------------------
-- Calculate local value of BR_Traffic_Vol_Score
-----------------------------------------------------------------
@BR_Traffic_Vol_Score = BR_Traffic_Vol_Score =
case
when BR_Traffic_Volume_VPD < 50 then 0
when BR_Traffic_Volume_VPD < 200 then 5
when BR_Traffic_Volume_VPD < 500 then 10
when BR_Traffic_Volume_VPD < 2000 then 15
else 20
end,
-----------------------------------------------------------------
-- Calculate local value of BR_Detour_Length_Score
-----------------------------------------------------------------
@BR_Detour_Length_Score = BR_Detour_Length_Score =
case
when BR_Detour_length_required_km < 1 then 0
when BR_Detour_length_required_km < 5 then 5
when BR_Detour_length_required_km < 10 then 10
else 20
end,
-----------------------------------------------------------------
-- Calculate local value of Priority_Score
-----------------------------------------------------------------
@Priority_Score = Priority_Score =
BR_Condition_Score +
BR_Closure_Score +
BR_Flood_Safety_Score +
BR_Load_Limit_Score +
BR_Pot_maintenance_Score +
BR_Width_Score +
@BR_Traffic_Vol_Score +
@BR_Detour_Length_Score,
-----------------------------------------------------------------
-- Calculate local value of Benifit_Maintenance
-----------------------------------------------------------------
@Benifit_Maintenance = Benifit_Maintenance =
BR_Existing_Length_m *
BR_Existing_Width_m *
BR_FACTORS_evaluation_period *
[BR_Condition_maintenance_m2-yr] *
BR_Pot_maintenance_Factor,
-----------------------------------------------------------------
-- Calculate local value of VPD_to_Detour
-----------------------------------------------------------------
@VPD_to_Detour = VPD_to_Detour =
BR_Com_Veh_percentage *
[BR_Load_Limit_%CV_in_load_range],
-----------------------------------------------------------------
-- Calculate local value of Time_Factor_sub
-----------------------------------------------------------------
@Time_factor_sub = Time_factor_sub =
BR_Traffic_Volume_VPD *
@VPD_to_Detour *
23.0000000000 *
(power((1+0.0300000000),8)),
-----------------------------------------------------------------
-- Calculate local value of Time_Factor
-----------------------------------------------------------------
@Time_Factor = Time_Factor =
@Time_factor_sub *
BR_Time_to_take_Detour_mins /
60.0000000000,
-----------------------------------------------------------------
-- Calculate local value of Operating_Factor
-----------------------------------------------------------------
@Operating_Factor = Operating_Factor =
@VPD_to_Detour *
0.8000000000 *
POWER(1 + 0.0300000000, 8) *
BR_Detour_length_required_km,
-----------------------------------------------------------------
-- Calculate local value of Safety_Factor
-----------------------------------------------------------------
@Safety_Factor = Safety_Factor =
BR_FACTORS_Detour_Accident_Cost * 0.850000 *
@VPD_to_Detour *
BR_Traffic_Volume_VPD / 1000000.000000 *
BR_Detour_length_required_km,
-----------------------------------------------------------------
-- Calculate local value of Detour_Cost_per_Day
-----------------------------------------------------------------
@Detour_Cost_per_Day = Detour_Cost_per_Day =
@Time_Factor +
@Operating_Factor +
@Safety_Factor,
-----------------------------------------------------------------
-- Calculate local value of Benefit_Commercial_Vehical_Detour_Cost
-----------------------------------------------------------------
@Benifit_Commercial_Vehical_Detour_Cost = Benifit_Commercial_Vehical_Detour_Cost =
BR_FACTORS_evaluation_period *
365.0000000000 *
@Detour_Cost_per_Day,
-----------------------------------------------------------------
-- Calculate local value of Benifit_Floodway_Safety_Cost
-----------------------------------------------------------------
@Benifit_Floodway_Safety_Cost = Benifit_Floodway_Safety_Cost =
BR_Flood_Events_FLoodways_5yrs *
(BR_FACTORS_evaluation_period / 5.000000) *
BR_Flood_Safety_multiplier *
BR_Traffic_Volume_VPD *
BR_Factors_Floodway_Accident_Cost,
-----------------------------------------------------------------
-- Calculate local value of Benefit_Obstruction_Safety_Cost
-----------------------------------------------------------------
@Benifit_Obstruction_Safety_Cost = Benifit_Obstruction_Safety_Cost =
BR_FACTORS_pot_Obstruction_factor *
BR_Traffic_Volume_VPD *
BR_FACTORS_evaluation_period *
BR_Factors_Floodway_Accident_Cost *
BR_Existing_Length_m *
BR_Obstruction_Dist_multiplier,
-----------------------------------------------------------------
-- Calculate local value of Benefit
-----------------------------------------------------------------
@BENIFIT = BENIFIT =
@Benifit_Obstruction_Safety_Cost +
@Benifit_Floodway_Safety_Cost +
@Benifit_Commercial_Vehical_Detour_Cost +
@Benifit_Maintenance,
-----------------------------------------------------------------
-- Calculate local value of Upgraded_Bridge_Area
-----------------------------------------------------------------
@Upgraded_Bridge_Area = Upgraded_Bridge_Area =
BR_Existing_Length_m *
BR_Existing_Width_m *
BR_FACTORS_Upgrade_Area_Multiplier,
-----------------------------------------------------------------
-- Calculate local value of Construction_Cost
-----------------------------------------------------------------
@Construction_Cost = Construction_Cost =
@Upgraded_Bridge_Area *
BR_FACTORS_Construction_Cost_m2,
-----------------------------------------------------------------
-- Calculate local value of Design_Etc_Cost
-----------------------------------------------------------------
@Design_Etc_Cost = Design_Etc_Cost =
@Construction_Cost *
[BR_FACTORS_Design_etc-Cost],
-----------------------------------------------------------------
-- Calculate local value of Cost
-----------------------------------------------------------------
@Total_Cost = Total_Cost =
case
when BR_Override_Cost > 0 then BR_Override_Cost
else @Construction_Cost + @Design_Etc_Cost
end,
@COST = COST = @Total_Cost,
-----------------------------------------------------------------
-- Calculate local value of BCR
-----------------------------------------------------------------
@BCR = BCR =
@BENIFIT / @Total_Cost,
-----------------------------------------------------------------
-- Calculate local value of Total_Score
-----------------------------------------------------------------
@Total_Score = Total_Score =
@BCR * @Priority_Score,
-----------------------------------------------------------------
-- Calculate local value of Renewal_Amount
-----------------------------------------------------------------
@Renual_Amount = Renual_Amount =
BR_Existing_Length_m *
BR_Existing_Width_m *
BR_FACTORS_Renual_Cost_m2
-----------------------------------------------------------------
-- Query to join return table and source data for above calculations
-----------------------------------------------------------------
from
@BR_Calcs_Tbl AS brct
inner join (
SELECT
*
FROM
(spatial.CAPITAL_WORKS_JOBS AS cwj
LEFT JOIN spatial.[Capital Works type] AS cwt
ON (cwj.Job_Type_FK = cwt.Capital_Works_Type_ID))
RIGHT JOIN (((((((((
spatial.BR_JOBS AS brj
LEFT JOIN spatial.BR_FACTORS AS brf
ON brj.BR_factors_link_FK = brf.BR_FACTORS_ID_PK)
LEFT JOIN spatial.BR_Obstruction_Distance AS brod
ON brj.BR_Obstruction_Dist_FK = brod.BR_Obstruction_Dist_ID_PK)
LEFT JOIN spatial.BR_Condition_Existing AS brce
ON brj.BR_Existing_Manual_Condition_FK = brce.BR_Condition_ID_PK)
LEFT JOIN spatial.BR_Potential_Maintenance AS brpm
ON brj.BR_Potential_Maintenance_Cost_FK = brpm.BR_Pot_Maintenance_ID_PK)
LEFT JOIN spatial.BR_Width_of_Existing AS brwe
ON brj.BR_Width_Existing_FK = brwe.BR_Width_ID_PK)
LEFT JOIN spatial.BR_Load_Limit AS brll
ON brj.BR_Load_Limit_FK = brll.BR_Load_Limit_ID_PK)
LEFT JOIN spatial.BR_Closure AS brcl
ON brj.BR_Closure_Duration_FK = brcl.BR_Closure_ID_PK)
LEFT JOIN spatial.BR_Flood_Safety_Risk AS brfs
ON brj.BR_Flood_Safety_Risk_FK = brfs.BR_Flood_Safety_ID_PK)
LEFT JOIN spatial.BR_Commercial_Vehicals AS brcv
ON brj.BR_Percentage_Commercial_Vehicals_FK = brcv.BR_Com_Veh_ID_PK)
ON cwj.CAPITAL_WORKS_JOBS_ID_PK = brj.BR_JOBS_ID_PK
) AS tsd
on (brct.BR_JOBS_ID_PK_CALC = tsd.BR_JOBS_ID_PK);
RETURN
END
GO
November 25, 2008 at 10:27 pm
Here is the last thing I would with the code. In your select list in the UPDATE statement, I would alias the column names with the table aliases from the FROM clause. This help identify which columns are from which tables. You may know your data, someone coming along after you may not know it as well, and this will help them out. Do it just like it is done in the ON clauses of the JOIN statements.
I'm glad this are working well, and that it will help you with the rest of your code. Again, I'd be interested in hearing about performance differences as you continue reworking the code.
November 27, 2008 at 3:49 am
I also had to move a lot of existing queries into SQL and, at the start, ended up tying myself up in knots. My advice, from experience, would be to stop trying to duplicate what Access is doing and focus on producing the same results with a SQL solution. It may feel like a long way round when a lot of the thought process has already been done for Access but I now know that it's actually the simplest solution
November 27, 2008 at 4:51 am
Just one note. Multi-statement table valued functions do not and will not perform well with larger row sets if you are joining those functions together or joining them to other tables.
The base reason is that the table variable that the functions use to return data have no indexes or statistics. No indexes mean table scans and no statistics means the optimiser's very likely to generate a very bad execution plan.
If you are going to use them, make sure that their performance qualities aren't a problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply