July 20, 2006 at 10:34 am
Hi All.
Has anyone found this issue before?
I have a T-SQL sproc from hell which I am trying to optimise by replacing two nested SELECT statements in the return-column-list with a single UDF (called twice), passing slightly different values in the parameters for each execution. The UDFs are called within CASE statements which are doing ISNUMERIC and CHARINDEX checks. Performance is great, but I am getting strange results.
The second execution of the UDF is returning the SAME value as the first execution of the UDF, for the same row of data - even though the parameters passed should make the two UDF's return different data. When I remove the CASE, the second UDF works fine.
Unfortunately, I can't duplicate this using a simple example written from scratch, but we have tested this two ways from Sunday. If I create a second UDF with the SAME CODE and a different NAME, the query returns the correct results. If I swap them around, correct results. Calling the same-named UDF twice - incorrect results.
I know this sounds totally mad, but is seems as if SQL is caching the result or the parameters for the first UDF, and using that for the second UDF.
Has anyone seen this before? Btw, I am running SP4 - I will try this on SP3 tomorrow.
I'm going home now to have a stiff drink. I look forward to your responses tomorrow. Please cheer me up 🙂
Wayne
July 20, 2006 at 10:47 am
Could you post the code?
July 20, 2006 at 10:50 am
Your problem is probably that the charindex() and isnumeric() checks are not working in the way you expect. Can you post the code?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 21, 2006 at 4:32 am
Hi all. Here is the code - it is pretty ugly. I'm still busy optimising it. It won't run, as my boss asked me to change the table and field names. This was written by an outside company, and the system was originally designed for much less data.
Notice my seat is 86. The UDF is supposed to return 85 and 87 if there are seats booked next to my seat (previous and next), else it will return NULL if no seat is booked. Instead, both calls of the UDF returns 85 for previous_seat and next_seat. next_seat is supposed to be NULL, which is what I get if I remove the CASE around the second UDF. I tried to grab a subset of the data by running the inner select into another table to email to you guys, but it then decided to work correctly when I ran my code on new table. The DB is 6 GB.
Yes, I know this can be written better, but I'm curious as to why I am getting this error.
-- Run under sql login - default language = 'English'
-- I didn't write this code !!!! 🙂
SELECT * FROM
(
SELECTtop 100001
SeatLocation_section,SeatLocation_row,SeatLocation_seat,performance_id,custno,contact_first_name,contact_last_name,performance_description,performance_start_date,contact_greeting_name,contact_phone_number1,custref,addr1,addr2,
addr3,addr4,city,state,zip,delm_name,order_number,order_id,price_type_name,price_zone,audit_time,userrole_name,previous_seat,next_seat
from (select
casewhen(ISNUMERIC(SeatLocation_seat) = 1
AND CHARINDEX(SeatLocation_seat,'.') = 0
AND CHARINDEX(SeatLocation_seat,',') = 0
AND CHARINDEX(SeatLocation_seat,'$') = 0)
thendbo.SeatLocation_fn (t1.order_number, t1.performance_id, t1.SeatLocation_section,
t1.SeatLocation_row, t1.SeatLocation_seat - 1, t1.price_type_name,
t1.price_zone,
'F40B86E3-317C-4AE7-B69C-C32B14B24602',
'2006-07-16 00:00:00',
'2006-07-17 00:00:00'
)
else NULL
end as previous_seat,
casewhen(ISNUMERIC(SeatLocation_seat) = 1
AND CHARINDEX(SeatLocation_seat,'.') = 0
AND CHARINDEX(SeatLocation_seat,',') = 0
AND CHARINDEX(SeatLocation_seat,'$') = 0)
then
dbo.SeatLocation_fn (t1.order_number, t1.performance_id, t1.SeatLocation_section,
t1.SeatLocation_row, t1.SeatLocation_seat + 1, t1.price_type_name,
t1.price_zone,
'F40B86E3-317C-4AE7-B69C-C32B14B24602',
'2006-07-16 00:00:00',
'2006-07-17 00:00:00'
)
else NULL
end
as next_seat
,*
from (selectSeatLocation_section,
SeatLocation_row,
SeatLocation_seat,
performance_id,
custno,
contact_first_name,
contact_last_name,
performance_description,
performance_start_date,
contact_greeting_name,
contact_phone_number1,
custref,
case
when charindex(char(10),addr) > 0
then substring(addr,0,charindex(char(10),addr))
else addr
end as addr1,
case
when charindex(char(10),addr) > 0
then substring(addr,charindex(char(10),addr)+1, LEN(addr))
else ''
end as addr2,
'' as addr3,
'' as addr4,
city,
state,
zip,
delm_name,
order_number,
order_id,
price_type_name,
"price_zone" =
CASE WHEN stvd_value_id = value_legend_id
THEN stvd_description
ELSE value_legend_description
END,
audit_time,
userrole_name
from TBL_seat_location
inner join TBL_admission WITH (NOLOCK) on admission_seat_locid = seat_locid
inner join TBL_order_admission WITH (NOLOCK)
on orderadmission_admissid = admissid
inner join TBL_order WITH (NOLOCK) on order_id = orderadmission_order_id
inner join TBL_address WITH (NOLOCK) on order_address_id = address_id
inner join TBL_performance WITH (NOLOCK) on performance_id = admission_performance_id
inner join TBL_customer WITH (NOLOCK) on customer_id = order_customer_id
left join TBL_order_marketing_data WITH (NOLOCK) on ordermarketingdata_order_id = order_id
inner join TBL_contact WITH (NOLOCK) on customer_default_contact_id = contact_id
inner join TBL_price_type WITH (NOLOCK) on price_type_id = admission_price_type_id
inner join TBL_audit WITH (NOLOCK) on orderadmission_create_audit_id = audit_id
inner join TBL_delivery_method WITH (NOLOCK) on order_deliverymethod_id = deliverymethod_id
inner join TBL_user_role WITH (NOLOCK) on user_role_id = userrole_id
LEFT JOIN TBL_seat_template_seat price_STS WITH (NOLOCK) ON admission_price_template_id = price_STS.seattempseat_template_id
AND admission_seat_id = price_STS.seattempseat_seat_id
LEFT JOIN TBL_value_legend price_value WITH (NOLOCK)
ON price_value.value_legend_id =
CASE
WHEN admission_price_value_id IS NOT NULL
THEN admission_price_value_id
ELSE price_STS.seattempseat_template_value_id
END
left join TBL_seat_template_value_desc WITH (NOLOCK)
ON stvd_value_id = price_value.value_legend_id
AND stvd_seat_template_id = price_STS.seattempseat_template_id
whereperformance_id = '12345689-56E0-4443-BC36-2E542C4CC00E'
AND ( user_role_id = '95683727-317C-4AE7-B69C-C32B14B24602')
AND audit_time >= '2006-07-16 00:00:00'
AND audit_time <= '2006-07-17 00:00:00'
AND ((ordermarketingdata_date1 is NULL)
or (ordermarketingdata_date1 = @audit_time_start
AND audit_time <= @audit_time_end
GROUP BY seatloc_seat
HAVING (ISNUMERIC(seatloc_seat) = 1
AND CHARINDEX(seatloc_seat,'.') = 0
AND CHARINDEX(seatloc_seat,',') = 0
AND CHARINDEX(seatloc_seat,'$') = 0)
RETURN @seatloc_seat_return
END
GO
July 21, 2006 at 4:43 am
If I move both CASE statements around the UDF into the UDF itself as a IF, I also get incorrect results.
If I try the following I get the expected, correct results:
previous_seat = CASEWHEN1 = 1
THENdbo.SeatLocation_fn (t1.order_number, t1.performance_id, t1.seatloc_section,
t1.seatloc_row, t1.seatloc_seat - 1, t1.price_type_name,
t1.price_zone,
'F40B86E3-317C-4AE7-B69C-C32B14B24602',
'2006-07-16 00:00:00',
'2006-07-17 00:00:00'
)
ELSENULL
END,
next_seat =CASEWHEN2 = 2
THENdbo.SeatLocation_fn (t1.order_number, t1.performance_id, t1.seatloc_section,
t1.seatloc_row, t1.seatloc_seat + 1, t1.price_type_name,
t1.price_zone,
'F40B86E3-317C-4AE7-B69C-C32B14B24602',
'2006-07-16 00:00:00',
'2006-07-17 00:00:00'
)
ELSENULL
END
July 21, 2006 at 6:18 am
When calling the function does explicit casting work?
eg Instead of t1.seatloc_seat + 1 try cast(cast(t1.seatloc_seat as int) + 1 as nvarchar(50))
July 21, 2006 at 6:36 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply