0

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.

1
  • So you're doing an inner join, without any indexes being used (because of the function), with 500k rows, and you don't know why it's slow? Honestly, I wouldn't know where to start here. You could rewrite the function, you could try filtering the companyref into a temp table which already has the function applied to each row, then index that, then do the same for the other table ... I don't know ... This is a very inefficient query.
    – user3914
    Jun 30, 2010 at 21:18

1 Answer 1

0

I'm not sure if this is a joke or not, but if you are having slow performance after an upgrade you should try rebuilding the indexes on the table and updating the statistics on it as well.

You must log in to answer this question.