September 29, 2011 at 5:08 am
Urgent help please
Ways to Improve this function:
CREATE FUNCTION [dbo].[LBS_Quotes_GetEarliestCurrentVariantCode]
(
@p_quote_id INT
)
RETURNS VARCHAR(1)
AS
BEGIN
-- declare temporary variables
DECLARE @Result VARCHAR(1),
@count_old INT,
@count_pre INT
-- count number of old and pre-current vehicles in the quote
SELECT @count_old = SUM(case when v.current_variant = 'O' then 1 else 0 end),
@count_pre = SUM(case when v.current_variant = 'P' then 1 else 0 end)
FROM [quote_vehicles] qv
INNER JOIN [prdvref]..[variants] v ON v.manufacturer_id = qv.manufacturer_id and v.model_id = qv.model_id and v.variant_id = qv.variant_id
WHERE qv.quote_id = @p_quote_id
-- determine return value
IF (@@ERROR = 0)
BEGIN
IF (@count_old > 0) SET @Result = 'O'
ELSE IF (@count_pre > 0) SET @Result = 'P'
ELSE SET @Result = ''
END
RETURN @Result
END
GO
September 29, 2011 at 5:20 am
What's wrong with it (other than slow, which it is because it's a function)?
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
September 29, 2011 at 5:30 am
pespes009 (9/29/2011)
Ways to Improve this function:
CREATE FUNCTION [dbo].[LBS_Quotes_GetEarliestCurrentVariantCode]
(
@p_quote_id INT
)
RETURNS VARCHAR(1)
AS
BEGIN
-- declare temporary variables
DECLARE @Result VARCHAR(1),
@count_old INT,
@count_pre INT
-- count number of old and pre-current vehicles in the quote
SELECT @count_old = SUM(case when v.current_variant = 'O' then 1 else 0 end),
@count_pre = SUM(case when v.current_variant = 'P' then 1 else 0 end)
FROM [quote-0_vehicles] qv
INNER JOIN [prdvref]..[variants] v ON v.manufacturer_id = qv.manufacturer_id and v.model_id = qv.model_id and v.variant_id = qv.variant_id
WHERE qv.quote_id = @p_quote_id
-- determine return value
IF (@@ERROR = 0)
BEGIN
IF (@count_old > 0) SET @Result = 'O'
ELSE IF (@count_pre > 0) SET @Result = 'P'
ELSE SET @Result = ''
END
RETURN @Result
END
GO
[/quote-0]
It seems based off your last statement in the code that you could do something more like this:
if exists(SELECT v.current_variant = 'O'
FROM [quote_vehicles] qv
INNER JOIN [prdvref]..[variants] v ON v.manufacturer_id = qv.manufacturer_id and v.model_id = qv.model_id and v.variant_id = qv.variant_id
WHERE qv.quote_id = @p_quote_id and v.current_variant = 'O' )
begin
set @result = 'O'
end
else if exists(SELECT v.current_variant = 'P'
FROM [quote_vehicles] qv
INNER JOIN [prdvref]..[variants] v ON v.manufacturer_id = qv.manufacturer_id and v.model_id = qv.model_id and v.variant_id = qv.variant_id
WHERE qv.quote_id = @p_quote_id and v.current_variant = 'P')
begin
set @result = 'P'
end
else begin
set @result = ''
end
It appears based off your existing code that you don't actually care about the count, if O exists then it is an O if P exists then P if neither exist then blank.
Ben
September 29, 2011 at 6:38 am
It looks to me like you could use an existence query instead of count() since you aren't really interested in the actual count, just that it is > 0.
"if exists() " can be a lot faster than "select count()..." in actual practice for obvious reasons.
The probability of survival is inversely proportional to the angle of arrival.
September 29, 2011 at 6:44 am
Thanks a lot guys !!!
October 2, 2011 at 11:31 am
pespes009 (9/29/2011)
Thanks a lot guys !!!
So, what did you end up with? Two way street here... Please post the code that you ended up using so that others may learn. 😉
--Jeff Moden
October 2, 2011 at 1:00 pm
How is this function being used; as a column in a SELECT statement, a WHERE clause of a SELECT statement?
There is probably another way to write the function as well.
What would help is the DDL for the tables, some sample data for the tables, and the expected results based on the sample data.
Read the first article I reference below in my signature block to see how you should post this information if you are interested in any other alternatives.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply