July 1, 2010 at 1:02 am
Hello,
We have recently migrated one of our database servers from SS2000 to SS 2008. All the functionalities are working fine except one query which is taking excessive time to get executed compared to what it used to take in SS2K.
The function basically formats the given varchar data by removing spaces, special characters etc. and then it is INNER JOINed with master table to get the matching rows.
The query used to take ~5-6 mins in SS2K. In SS2K8 it takes around 45 mins to 1 hour.
The function code looks like given below
CREATE FUNCTION [dbo].[fnMyFunction](@Norma2 varchar(510))
RETURNS varchar(510)
AS
BEGIN
SET @Norma2 = Replace(@Norma2, ' '' ', ' ')
SET @Norma2 = Replace(@Norma2, '(P)', ' ')
SET @Norma2 = Replace(@Norma2, '(I)', ' ')
SET @Norma2 = Replace(@Norma2, '(', ' ')
SET @Norma2 = Replace(@Norma2, ')', ' ')
SET @Norma2 = Replace(@Norma2, '.', ' ')
SET @Norma2 = Replace(@Norma2, 'DOT', ' ')
SET @Norma2 = Replace(@Norma2, '(', ' ')
SET @Norma2 = Replace(@Norma2, 'Private', ' ')
SET @Norma2 = Replace(@Norma2, 'Public', ' ')
SET @Norma2 = Replace(@Norma2, 'Limited', ' ')
SET @Norma2 = Replace(@Norma2, 'Ltd.', ' ')
SET @Norma2 = Replace(@Norma2, 'Ltd', ' ')
SET @Norma2 = Replace(@Norma2, '-', ' ')
SET @Norma2 = Replace(@Norma2,char(128),'')
SET @Norma2 = Replace(@Norma2,char(129),'')
SET @Norma2 = Replace(@Norma2,char(131),'')
SET @Norma2 = Replace(@Norma2,char(132),'')
SET @Norma2 = Replace(@Norma2,char(133),'')
SET @Norma2 = Replace(@Norma2,char(134),'')
SET @Norma2 = Replace(@Norma2,char(135),'')
SET @Norma2 = Replace(@Norma2,char(136),'')
SET @Norma2 = Replace(@Norma2,char(137),'')
SET @Norma2 = Replace(@Norma2,char(138),'')
SET @Norma2 = Replace(@Norma2,char(139),'')
SET @Norma2 = Replace(@Norma2,char(140),'')
SET @Norma2 = Replace(@Norma2,char(141),'')
SET @Norma2 = Replace(@Norma2,char(142),'')
SET @Norma2 = Replace(@Norma2,char(143),'')
SET @Norma2 = Replace(@Norma2,char(144),'')
SET @Norma2 = Replace(@Norma2,char(147),'')
SET @Norma2 = Replace(@Norma2,char(148),'')
SET @Norma2 = Replace(@Norma2,char(149),'')
SET @Norma2 = Replace(@Norma2,char(151),'')
SET @Norma2 = Replace(@Norma2,char(152),'')
SET @Norma2 = Replace(@Norma2,char(154),'')
SET @Norma2 = Replace(@Norma2,char(155),'')
SET @Norma2 = Replace(@Norma2,char(156),'')
SET @Norma2 = Replace(@Norma2,char(157),'')
SET @Norma2 = Replace(@Norma2,char(158),'')
SET @Norma2 = Replace(@Norma2,char(159),'')
SET @Norma2 = Replace(@Norma2,char(160),'')
SET @Norma2 = Replace(@Norma2,char(161),'')
SET @Norma2 = Replace(@Norma2,char(162),'')
SET @Norma2 = Replace(@Norma2,char(163),'')
SET @Norma2 = Replace(@Norma2,char(164),'')
SET @Norma2 = Replace(@Norma2,char(165),'')
SET @Norma2 = Replace(@Norma2,char(166),'')
SET @Norma2 = Replace(@Norma2,char(167),'')
SET @Norma2 = Replace(@Norma2,char(168),'')
SET @Norma2 = Replace(@Norma2,char(170),'')
SET @Norma2 = Replace(@Norma2,char(171),'')
SET @Norma2 = Replace(@Norma2,char(172),'')
SET @Norma2 = Replace(@Norma2,char(173),'')
SET @Norma2 = Replace(@Norma2,char(174),'')
SET @Norma2 = Replace(@Norma2,char(175),'')
SET @Norma2 = Replace(@Norma2,char(176),'')
SET @Norma2 = Replace(@Norma2,char(177),'')
SET @Norma2 = Replace(@Norma2,char(178),'')
SET @Norma2 = Replace(@Norma2,char(179),'')
SET @Norma2 = Replace(@Norma2,char(180),'')
SET @Norma2 = Replace(@Norma2,char(181),'')
SET @Norma2 = Replace(@Norma2,char(182),'')
SET @Norma2 = Replace(@Norma2,char(183),'')
SET @Norma2 = Replace(@Norma2,char(184),'')
SET @Norma2 = Replace(@Norma2,char(185),'')
SET @Norma2 = Replace(@Norma2,char(186),'')
SET @Norma2 = Replace(@Norma2,char(187),'')
SET @Norma2 = Replace(@Norma2,char(188),'')
SET @Norma2 = Replace(@Norma2,char(189),'')
SET @Norma2 = Replace(@Norma2,char(190),'')
SET @Norma2 = Replace(@Norma2,char(191),'')
SET @Norma2 = Replace(@Norma2,char(192),'')
SET @Norma2 = Replace(@Norma2,char(193),'')
SET @Norma2 = Replace(@Norma2,char(194),'')
SET @Norma2 = Replace(@Norma2,char(195),'')
SET @Norma2 = Replace(@Norma2,char(196),'')
SET @Norma2 = Replace(@Norma2,char(197),'')
SET @Norma2 = Replace(@Norma2,char(198),'')
SET @Norma2 = Replace(@Norma2,char(199),'')
SET @Norma2 = Replace(@Norma2,char(200),'')
SET @Norma2 = Replace(@Norma2,char(201),'')
SET @Norma2 = Replace(@Norma2,char(202),'')
SET @Norma2 = Replace(@Norma2,char(203),'')
SET @Norma2 = Replace(@Norma2,char(204),'')
SET @Norma2 = Replace(@Norma2,char(205),'')
SET @Norma2 = Replace(@Norma2,char(206),'')
SET @Norma2 = Replace(@Norma2,char(207),'')
SET @Norma2 = Replace(@Norma2,char(208),'')
SET @Norma2 = Replace(@Norma2,char(209),'')
SET @Norma2 = Replace(@Norma2,char(210),'')
SET @Norma2 = Replace(@Norma2,char(211),'')
SET @Norma2 = Replace(@Norma2,char(212),'')
SET @Norma2 = Replace(@Norma2,char(213),'')
SET @Norma2 = Replace(@Norma2,char(214),'')
SET @Norma2 = Replace(@Norma2,char(215),'')
SET @Norma2 = Replace(@Norma2,char(216),'')
SET @Norma2 = Replace(@Norma2,char(217),'')
SET @Norma2 = Replace(@Norma2,char(218),'')
SET @Norma2 = Replace(@Norma2,char(219),'')
SET @Norma2 = Replace(@Norma2,char(220),'')
SET @Norma2 = Replace(@Norma2,char(221),'')
SET @Norma2 = Replace(@Norma2,char(222),'')
SET @Norma2 = Replace(@Norma2,char(223),'')
SET @Norma2 = Replace(@Norma2,char(224),'')
SET @Norma2 = Replace(@Norma2,char(225),'')
SET @Norma2 = Replace(@Norma2,char(226),'')
SET @Norma2 = Replace(@Norma2,char(227),'')
SET @Norma2 = Replace(@Norma2,char(228),'')
SET @Norma2 = Replace(@Norma2,char(229),'')
SET @Norma2 = Replace(@Norma2,char(230),'')
SET @Norma2 = Replace(@Norma2,char(231),'')
SET @Norma2 = Replace(@Norma2,char(232),'')
SET @Norma2 = Replace(@Norma2,char(233),'')
SET @Norma2 = Replace(@Norma2,char(234),'')
SET @Norma2 = Replace(@Norma2,char(235),'')
SET @Norma2 = Replace(@Norma2,char(236),'')
SET @Norma2 = Replace(@Norma2,char(237),'')
SET @Norma2 = Replace(@Norma2,char(238),'')
SET @Norma2 = Replace(@Norma2,char(239),'')
SET @Norma2 = Replace(@Norma2,char(240),'')
SET @Norma2 = Replace(@Norma2,char(241),'')
SET @Norma2 = Replace(@Norma2,char(242),'')
SET @Norma2 = Replace(@Norma2,char(243),'')
SET @Norma2 = Replace(@Norma2,char(244),'')
SET @Norma2 = Replace(@Norma2,char(245),'')
SET @Norma2 = Replace(@Norma2,char(246),'')
SET @Norma2 = Replace(@Norma2,char(247),'')
SET @Norma2 = Replace(@Norma2,char(248),'')
SET @Norma2 = Replace(@Norma2,char(249),'')
SET @Norma2 = Replace(@Norma2,char(250),'')
SET @Norma2 = Replace(@Norma2,char(251),'')
SET @Norma2 = Replace(@Norma2,char(252),'')
SET @Norma2 = Replace(@Norma2,char(253),'')
SET @Norma2 = Replace(@Norma2,char(254),'')
SET @Norma2 = Replace(@Norma2,char(255),'')
SET @Norma2 = Replace(@Norma2,char(32),'')
SET @Norma2 = Replace(@Norma2,char(33),'')
SET @Norma2 = Replace(@Norma2,char(34),'')
SET @Norma2 = Replace(@Norma2,char(35),'')
SET @Norma2 = Replace(@Norma2,char(36),'')
SET @Norma2 = Replace(@Norma2,char(37),'')
SET @Norma2 = Replace(@Norma2,char(42),'')
SET @Norma2 = Replace(@Norma2,char(43),'')
SET @Norma2 = Replace(@Norma2,char(58),'')
SET @Norma2 = Replace(@Norma2,char(59),'')
SET @Norma2 = Replace(@Norma2,char(60),'')
SET @Norma2 = Replace(@Norma2,char(61),'')
SET @Norma2 = Replace(@Norma2,char(62),'')
SET @Norma2 = Replace(@Norma2,char(63),'')
SET @Norma2 = Replace(@Norma2,char(64),'')
SET @Norma2 = Replace(@Norma2,char(94),'')
SET @Norma2 = Replace(@Norma2,char(123),'')
SET @Norma2 = Replace(@Norma2,char(124),'')
SET @Norma2 = Replace(@Norma2,char(125),'')
SET @Norma2 = Replace(@Norma2,char(126),'')
SET @Norma2 = Replace(@Norma2,char(127),'')
RETURN(@Norma2)
I execute the following query to match the varchar columns from both the tables.
USE DB1
Go
Select Distinct t.MatchValue,M.MASTERname,M.MASTERCODE
from MASTER_TABLE M
INNER JOIN
DB2..BufferTable t where
dbo.fnMyFunction(c.M.MASTERname) = dbo.fnMyFunction(t.MatchValue)
Any idea why the query takes more time in SQL 2K8?
Regards,
Rohit
July 1, 2010 at 1:08 am
Eeeek 🙂
Scrap that entirely.
Scalar UDFs perform very poorly. For Performance reason, here i would probably add a persisted calculated column for the processed (ie cleaned) values.
http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx
July 1, 2010 at 1:43 am
I agree with Dave: get rid of that function if possibile.
If not, try adding WITH SCHEMABINDING to the function definition: it should mark the function as deterministic and avoid a lazy spool in the query plan.
-- Gianluca Sartori
July 1, 2010 at 3:07 am
Thanks for your reply. Having a computed column looks a good option in terms of performance. But I was wondering why SS2K8 should take more time than SS2K?
I also tried to check the execution plan of the same query in 2K as well as in 2K8.
Attach: SQL2K.bmp,SQL2K8.bmp
In SQL 2K8 the inner join cost is 79% whereas in SQL 2K the cost is 15%.
Why is the difference in two execution plans?
Regards,
Rohit
July 1, 2010 at 3:19 am
The biggest change is that the join is now a nested loop rather than a hash match.
You could try
Select Distinct t.MatchValue,M.MASTERname,M.MASTERCODE
from MASTER_TABLE M
INNER HASH JOIN
DB2..BufferTable t where
dbo.fnMyFunction(c.M.MASTERname) = dbo.fnMyFunction(t.MatchValue)
See if that helps , but it is a sticking plaster rather than a fix.
July 1, 2010 at 3:21 am
Over the last 10 years, the SQL Engine has changed a bit along with the way it does its optimisations.
July 1, 2010 at 3:37 am
Update statistics with fullscan on all tables involved. Do that before you force the join hint as Dave suggests, then test and see if the join hint is still necessary. It likely won't be. Poor choice of joins often indicates out of date stats.
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
July 1, 2010 at 3:40 am
Thanks a lot Dave. That worked. The query which was executing within 45 minutes now gets executed in 1.45 minutes !!!
July 1, 2010 at 4:56 am
Glad to of helped.
Though ,
A) Ensure that your statistics are upto date as Gail suggested.
B) Work on Getting rid of the function altogether (inline table functions are cool), i would hazard a guess that this would execute in <20 secs probably less if you did.
July 1, 2010 at 5:03 am
Just out of interest what a the rowcounts of the two table involved ?
July 1, 2010 at 5:06 am
One table (used as a temporary storage place) may contain rows from 100 to 10000 + depending on the amount of data we need to match with our master table.
The master table (production table) has 570K records.
July 1, 2010 at 8:50 am
I took the liberty of forwarding on a simpler example of this to Connor Cunningham.
Not one but two blogs resulted...
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/07/01/conor-vs-join-algorithms.aspx
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/07/01/conor-vs-udfs-in-joins.aspx
July 1, 2010 at 9:02 am
Oooh... So the way to make Conor blog is to send him questions... I'll start the mail flood... 😀
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
July 1, 2010 at 9:21 am
Cant guarantee itll always work 🙂
July 1, 2010 at 10:25 am
I think the idea of a persisted calculated column is a good idea. However, you will still need to use your function for that.
This may be a bit better. It combines the beginning REPLACEs into a nested one, and then utilizes a tally table to break apart what's left of the string, and put it back together skipping all of the ascii characters that you don't want. You would have to test to see if this is better or not - but it does end up removing a LOT of the replace statements, so you could see some improvement in it.
Hope this helps!
CREATE FUNCTION [dbo].[fnMyFunction](@Norma2 varchar(510))
RETURNS varchar(510)
WITH SCHEMABINDING -- this may help out also!
AS
BEGIN
-- combine all of these together to minimize string manipulation.
SET @Norma2 = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(@Norma2, ' '' ', ' '), '(P)', ' '), '(I)', ' '), '(', ' '), ')', ' '), '.', ' '), 'DOT', ' '), '(', ' '), 'Private', ' '), 'Public', ' '), 'Limited', ' '), 'Ltd.', ' '), 'Ltd', ' '), '-', ' ')
-- See Jeff Moden's article
-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.
-- NOTE! A permanent tally table will always be MUCH faster
-- than this inline one. See the above article to create your own!
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Thousands),
CharTable AS
(
-- Rip the string apart into the individual characters.
-- Need to keep the position (N) so we can put it back together.
-- Get the ASCII value of the character at each position also.
SELECT N,
MyChar = SUBSTRING(@Norma2, N, 1),
MyAscii = ASCII(SUBSTRING(@Norma2, N, 1))
FROM Tally
WHERE N <= LEN(@Norma2) -- only get for how long the string is
)
-- Put the string back together, skipping the chars you don't want
SELECT @Norma2 = (SELECT '' + MyChar
FROM CharTable
WHERE MyAscii NOT BETWEEN 32 AND 37
AND MyAscii NOT IN (42,43,94,151,152)
AND MyAscii NOT BETWEEN 58 AND 64
AND MyAscii NOT BETWEEN 123 AND 129
AND MyAscii NOT BETWEEN 131 AND 144
AND MyAscii NOT BETWEEN 147 AND 149
AND MyAscii NOT BETWEEN 154 AND 168
AND MyAscii NOT BETWEEN 170 AND 255
ORDER BY N -- this ensures it goes back together in the proper order
FOR XML PATH(''))
RETURN (@Norma2)
END
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply