Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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 '10 at 21:18

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.