We have migrated one of our system from SQL server 2000 to 2008 but experiencing slow performance for few of the queries. We are running below query which takes lot of time on SQL 2008 box.
Select Distinct t.cdbcode,c.companyname,c.companycode from companyref c inner join db_support..extn_temp t on dbo.fnMakeNorma(c.CompanyName) = dbo.fnMakeNorma(t.cdbcode)
Here dbo.fnMakeNorma is as below:
CREATE FUNCTION [dbo].[fnMakeNorma](@Norma2 varchar(510))
RETURNS varchar(510)
AS
BEGIN
--Declare @SQL varchar(100)
--declare @Norma2 varchar(510)
SET @Norma2 = Replace(@Norma2, ' Co ', ' ')
SET @Norma2 = Replace(@Norma2, ' Company ', ' ')
SET @Norma2 = Replace(@Norma2, ' Company', ' ')
SET @Norma2 = Replace(@Norma2, ' Co. ', ' ')
SET @Norma2 = Replace(@Norma2, ' (Co) ', ' ')
SET @Norma2 = Replace(@Norma2, 'India', ' ')
SET @Norma2 = Replace(@Norma2, 'Pvt', ' ')
SET @Norma2 = Replace(@Norma2, 'Pvt.', ' ')
SET @Norma2 = Replace(@Norma2, 'P.', ' ')
SET @Norma2 = Replace(@Norma2, 'L.', ' ')
-- SET @Norma2 = Replace(@Norma2, 'Corp.', ' ')
-- SET @Norma2 = Replace(@Norma2, 'Corpn.', ' ')
-- SET @Norma2 = Replace(@Norma2, 'Corpn', ' ')
-- SET @Norma2 = Replace(@Norma2, 'Corporation', ' ')
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, ' The', ' ')
SET @Norma2 = Replace(@Norma2, 'The ', ' ')
SET @Norma2 = Replace(@Norma2, ' & ', ' ')
SET @Norma2 = Replace(@Norma2, ' and ', ' ')
SET @Norma2 = Replace(@Norma2, ',', ' ')
---Added on 4-Feb-08
Set @Norma2 = Replace(@Norma2, 'M\s', ' ')
Set @Norma2 = Replace(@Norma2, 'M/s', ' ')
Set @Norma2 = Replace(@Norma2, 'C\o', ' ')
Set @Norma2 = Replace(@Norma2, 'C/o', ' ')
-- SET @Norma2 = Replace(@Norma2, 'engg', ' ')
SET @Norma2 = Replace(@Norma2, ' ', ' ')
Set @Norma2 = Replace(@Norma2,'Corpn.','Corporation')
Set @Norma2 = Replace(@Norma2,'Corpn','Corporation')
Set @Norma2 = Replace(@Norma2,'Devp.','Development')
Set @Norma2 = Replace(@Norma2,'Engg.','Engineering')
Set @Norma2 = Replace(@Norma2,'Indl.','Industrial')
Set @Norma2 = Replace(@Norma2,'Inds.','Industries')
Set @Norma2 = Replace(@Norma2,'Intl.','International')
Set @Norma2 = Replace(@Norma2,'Invsts.','Investments')
Set @Norma2 = Replace(@Norma2,'Mfg.','Manufacturing')
Set @Norma2 = Replace(@Norma2,'Mfrs.','Manufacturers')
Set @Norma2 = Replace(@Norma2,'Mngt.','Management')
Set @Norma2 = Replace(@Norma2,'Spg.','Spinning')
Set @Norma2 = Replace(@Norma2,'Trdg.','Trading')
Set @Norma2 = Replace(@Norma2,'Wvg.','Weaving')
Set @Norma2 = Replace(@Norma2,'Prtg.','Printing')
Set @Norma2 = Replace(@Norma2,'Dyg.','Dyeing')
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)
END
The companyref table has ~500K rows while records in extn_temp varies as per user's request. The same query was running fine on sql 2000 box.
Your inputs will really help us.
Thanks in advance.
Regards, Sameer Golam.