December 15, 2008 at 4:09 pm
We have MSSQL2000 installed on 4processor 16gb DEV box.
On the same box we have MSSQL2005 instance sitting on top.
Both running the same database -- this is the transition to 2000->2005 migration.
There is an INSERT ... SELECT .. dbo.function() FROM ... JOIN JOIN JOIN in both databases.
In 2000 it runs over minutes, whereas in 2005 it takes hours.
Indexing is same, tables are obviously same, function code is same, hardware is same, memory allocation for instances is same. What could be the possible problem here? Or difference?
ps: if you remove function from INSERT .. SELECT.. call in MSSQL2005, the statement is fast. SO obviously the function is the bottleneck
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
December 15, 2008 at 4:21 pm
Not enough information. It would help to see the code, the DDL for the tables including defined indexes, and the execution plans for the queries.
December 15, 2008 at 4:28 pm
Did you update statistics in 2005? Rebuild indexes? Update usage?
And finally, not related to performance - but did you run DBCC CHECKDB db WITH data_purity?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2008 at 4:42 pm
Lynn Pettis (12/15/2008)
Not enough information. It would help to see the code, the DDL for the tables including defined indexes, and the execution plans for the queries.
Interesting fact (before we go any further)
Show Execution Plan for the SELECT ... dbo.function(...) FROM JOIN JOIN JOIN
in MSSQL2000 comes back with a nice plan.
In 2005 it comes back with an error
Only functions and extended stored procedures can be executed from within a function.
Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.
Text of the fnction is
BEGIN
-- declarations
SELECT @hours_spent = Sum(
dbo.fn_util_getworkingseconds_for_the_day (
@start_date,
h21.CALENDAR_WORKING_DAY,
h21.CALENDAR_Business_Date_Start,
h21.CALENDAR_Business_Date_End,
hs.FULL_DAY,
hs.HOURS_FROM,
hs.HOURS_TO,
@end_date
) / 3600
)
FROM dbo.DIM_SYSDATE_S21HOLIDAYS h21
LEFT JOIN dbo.EXT_SITE_HOLIDAY hs ON
h21.CALENDAR_DATE = hs.HOLIDAY_DATE AND
hs.SIT_ID = @site_id
WHERE
h21.CALENDAR_STATE = @state AND
h21.CALENDAR_DATE >= dbo.fn_util_getstartoftheday(@start_date) AND
h21.CALENDAR_DATE < @end_date
-- finalisation and return
---- fn_util_getworkingseconds_for_the_day
CREATE function dbo.fn_util_getworkingseconds_for_the_day
(
@start_date datetime
,@is_working_day bit
,@business_day_start datetime
,@business_day_end datetime
,@siteholiday_fullday bit
,@siteholiday_business_start datetime
,@siteholiday_business_end datetime
,@end_date datetime
)
returns numeric(8,2)
as
begin
if ISNULL(@siteholiday_fullday,0) = 1 or @is_working_day = 0 --PNH - added ISNULL to @siteholiday_fullday
return 0
if (@business_day_start is null and @siteholiday_business_start is null)
or (@business_day_end is null and @siteholiday_business_end is null)
return 0
declare @start datetime
declare @end datetime
set @start = case when @start_date > isnull(@siteholiday_business_start,@business_day_start) then
@start_date
else
isnull(@siteholiday_business_start,@business_day_start)
end
set @end = case when @end_date < isnull(@siteholiday_business_end,@business_day_end) then
@end_date
else
isnull(@siteholiday_business_end,@business_day_end)
end
declare @retval numeric(8,2)
set @retval = datediff(ss,@start,@end)
if @retval < 0
set @retval = 0
return @retval
end
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
December 15, 2008 at 5:03 pm
Jeffrey Williams (12/15/2008)
Did you update statistics in 2005? Rebuild indexes? Update usage?And finally, not related to performance - but did you run DBCC CHECKDB db WITH data_purity?
Will do ... as soon as this b@stard will stop executing ... It usually takes 4-5 hours in the morning on 2005 instance as opposed to some 2-3 minutes in 2000 instance.
ANother finding.. I have EXEC in this function (which I did not quote above). This EXEC logs errors should they occur within this function.
Strange thing is that the SQL statement with the function call WORKS though slowly, but Display Estimated Execution Plan on the same statement returns with an error message "Only functions and extended stored procedures can be executed from within a function.".
So my rough guess is that MSSQL2005 does not allow the situation but magically it still executes the statement, but without prio compilation of the function (for the compilation fails). Hence the whole thing is crazily slow.
What do you think?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
December 15, 2008 at 8:35 pm
Yeah, that's weird. I don't think that you should be able to do an EXEC in a function. You might want to try taking that out or changing it to something else.
[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]
December 16, 2008 at 2:52 pm
RBarryYoung (12/15/2008)
Yeah, that's weird. I don't think that you should be able to do an EXEC in a function. You might want to try taking that out or changing it to something else.
This is exactly what I did. Deduction method has shown that the idiot who built the INSERT ... SELECT.. WHERE... statement decided to use nested function calls in the WHERE clause for the JOIN purposes.
Apparently this WHERE statement was executed many times in order to join tables and it was slowing the whole thing down.
The SQL programmer did the following:
INSERT
...
SELECT
...
dbo.f1(...)
...
FROM
...
JOIN
...
WHERE
--------
f1(...) AS
SELECT sum(...)
FROM
...
JOIN
...
WHERE
field > dbo.f2(...)
------
f2(...) AS
-- scalar calculations, some conversions etc with the usage of dbo.f3(...)
------
f3(...) AS
-- scalar calculations, some conversions etc with the usage of dbo.f4(...)
------
f4(...) AS
-- scalar calculations, some conversions etc
I have taken f2, f3 and f4 and placed them all in f1 as a collective statment.
This apparently helped the SQL optimiser to optimise the "field > dbo.f2(...)" piece in f1 and sped the thing back to it's normal speed.
Now the question -- why 2005 does not do the same thing in this case as 2000?
Is 2000 smarter or what?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
December 16, 2008 at 6:19 pm
90% of the differences between 2000 and 2005 are things that 2005 does better, but there are always some things that will run slower.
In my experience, the SQL 2005 optimizer is a lot better than 2000, there are many problems and bugs in 2000 that are fixed in 2005.
[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]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply