![迁移到 SQL Server 2008 后查询运行缓慢](https://linux22.com/image/517733/%E8%BF%81%E7%A7%BB%E5%88%B0%20SQL%20Server%202008%20%E5%90%8E%E6%9F%A5%E8%AF%A2%E8%BF%90%E8%A1%8C%E7%BC%93%E6%85%A2.png)
我们已经将我们的一个系统从 SQL Server 2000 迁移到 2008,但一些查询的性能很慢。我们在 SQL 2008 机器上运行以下查询,这需要花费大量时间。
从 companyref c 内连接 db_support..extn_temp t 中选择不同的 t.cdbcode、c.companyname、c.companycode,在 dbo.fnMakeNorma(c.CompanyName) = dbo.fnMakeNorma(t.cdbcode)
这里 dbo.fnMakeNorma 如下:
创建函数 [dbo].[fnMakeNorma](@Norma2 varchar(510))
返回 varchar(510)
作为
开始
--Declare @SQL varchar(100)
--declare @Norma2 varchar(510)
SET @Norma2 = Replace(@Norma2, ' Co ', ' ')
SET @Norma2 = Replace(@Norma2, ' Company ', ' ')
设置 @Norma2 = Replace(@Norma2, ' Company', ' ')
设置 @Norma2 = Replace(@Norma2, ' Co. ', ' ')
设置 @Norma2 = Replace(@Norma2, ' (Co) ', ' ')
设置 @Norma2 = Replace(@Norma2, 'India', ' ')
设置 @Norma2 = Replace(@Norma2, 'Pvt', ' ')
设置 @Norma2 = Replace(@Norma2, 'Pvt.', ' ')
设置 @Norma2 = Replace(@Norma2, 'P.', ' ')
设置 @Norma2 = Replace(@Norma2, 'L.', ' ')
-- 设置 @Norma2 = Replace(@Norma2, 'Corp.', ' ')
-- 设置 @Norma2 = Replace(@Norma2, 'Corpn.', ' ')
-- 设置 @Norma2 = Replace(@Norma2, 'Corpn', ' ')
-- 设置 @Norma2 = Replace(@Norma2, 'Corporation', ' ')
设置 @Norma2 = Replace(@Norma2, ' '' ', ' ')
设置 @Norma2 = Replace(@Norma2, '(P)', ' ')
设置 @Norma2 = Replace(@Norma2, '(I)', ' ')
设置 @Norma2 = Replace(@Norma2, '(', ' ')
设置 @Norma2 = Replace(@Norma2, ')', ' ')
设置 @Norma2 = Replace(@Norma2, '.', ' ')
设置 @Norma2 = Replace(@Norma2, 'DOT', ' ')
设置 @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, ' 和 ', ' ')
设置 @Norma2 = Replace(@Norma2, ',', ' ')
---于 2008 年 2 月 4 日添加
设置 @Norma2 = Replace(@Norma2, 'M\s', ' ')
设置 @Norma2 = Replace(@Norma2, 'M/s', ' ')
设置 @Norma2 = Replace(@Norma2, 'C\o', ' ')
设置 @Norma2 = Replace(@Norma2, 'C/o', ' ')
-- 设置@Norma2 = Replace(@Norma2, 'engg', ' ')
设置@Norma2 = Replace(@Norma2, ' ', ' ')
设置@Norma2 = Replace(@Norma2,'Corpn.','Corporation')
设置@Norma2 = Replace(@Norma2,'Corpn','Corporation')
设置@Norma2 = Replace(@Norma2,'Devp.','Development')
设置@Norma2 = Replace(@Norma2,'Engg.','Engineering')
设置@Norma2 = Replace(@Norma2,'Indl.','Industrial')
设置@Norma2 = Replace(@Norma2,'Inds.','Industries')
设置@Norma2 = Replace(@Norma2,'Intl.','International')
设置@Norma2 = Replace(@Norma2,'Invsts.','投资')
设置@Norma2 = Replace(@Norma2,'Mfg.','制造')
设置@Norma2 = Replace(@Norma2,'Mfrs.','制造商')
设置@Norma2 = Replace(@Norma2,'Mngt.','管理')
设置@Norma2 = Replace(@Norma2,'Spg.','纺纱')
设置@Norma2 = Replace(@Norma2,'Trdg.','交易')
设置@Norma2 = Replace(@Norma2,'Wvg.','织造')
设置@Norma2 = Replace(@Norma2,'Prtg.','印刷')
设置@Norma2 = Replace(@Norma2,'Dyg.','染色')
设置@Norma2 = 替换(@Norma2,char(128),'')
设置@Norma2 = 替换(@Norma2,char(129),'')
设置@Norma2 = 替换(@Norma2,char(131),'')
设置@Norma2 = 替换(@Norma2,char(132),'')
设置@Norma2 = 替换(@Norma2,char(133),'')
设置@Norma2 = 替换(@Norma2,char(134),'')
设置@Norma2 = 替换(@Norma2,char(135),'')
设置@Norma2 = 替换(@Norma2,char(136),'')
设置@Norma2 = 替换(@Norma2,char(137),'')
设置@Norma2 = 替换(@Norma2,char(138),'')
设置@Norma2 =替换(@Norma2,char(139),'')
设置@Norma2 = 替换(@Norma2,char(140),'')
设置@Norma2 = 替换(@Norma2,char(141),'')
设置@Norma2 = 替换(@Norma2,char(142),'')
设置@Norma2 = 替换(@Norma2,char(143),'')
设置@Norma2 = 替换(@Norma2,char(144),'')
设置@Norma2 = 替换(@Norma2,char(147),'')
设置@Norma2 = 替换(@Norma2,char(148),'')
设置@Norma2 = 替换(@Norma2,char(149),'')
设置@Norma2 = 替换(@Norma2,char(151),'')
设置@Norma2 = 替换(@Norma2,char(152),'')
设置@Norma2 = Replace(@Norma2,char(154),'')
设置 @Norma2 = Replace(@Norma2,char(155),'')
设置 @Norma2 = Replace(@Norma2,char(156),'')
设置 @Norma2 = Replace(@Norma2,char(157),'')
设置 @Norma2 = Replace(@Norma2,char(158),'')
设置 @Norma2 = Replace(@Norma2,char(159),'')
设置 @Norma2 = Replace(@Norma2,char(160),'')
设置 @Norma2 = Replace(@Norma2,char(161),'')
设置 @Norma2 = Replace(@Norma2,char(162),'')
设置 @Norma2 = Replace(@Norma2,char(163),'')
设置 @Norma2 = Replace(@Norma2,char(164),'')
设置@Norma2 = 替换(@Norma2,char(165),'')
设置@Norma2 = 替换(@Norma2,char(166),'')
设置@Norma2 = 替换(@Norma2,char(167),'')
设置@Norma2 = 替换(@Norma2,char(168),'')
设置@Norma2 = 替换(@Norma2,char(170),'')
设置@Norma2 = 替换(@Norma2,char(171),'')
设置@Norma2 = 替换(@Norma2,char(172),'')
设置@Norma2 = 替换(@Norma2,char(173),'')
设置@Norma2 = 替换(@Norma2,char(174),'')
设置@Norma2 = 替换(@Norma2,char(175),'')
设置@Norma2 =替换(@Norma2,char(176),'')
设置@Norma2 = 替换(@Norma2,char(177),'')
设置@Norma2 = 替换(@Norma2,char(178),'')
设置@Norma2 = 替换(@Norma2,char(179),'')
设置@Norma2 = 替换(@Norma2,char(180),'')
设置@Norma2 = Replace(@Norma2,char(181),'')
设置@Norma2 = Replace(@Norma2,char(182),'')
设置@Norma2 = Replace(@Norma2,char(183),'')
设置@Norma2 = Replace(@Norma2,char(184),'')
设置@Norma2 = Replace(@Norma2,char(185),'')
设置@Norma2 = 替换(@Norma2,char(186),'')
设置@Norma2 = 替换(@Norma2,char(187),'')
设置@Norma2 = 替换(@Norma2,char(188),'')
设置@Norma2 = 替换(@Norma2,char(189),'')
设置@Norma2 = 替换(@Norma2,char(190),'')
设置@Norma2 = 替换(@Norma2,char(191),'')
设置@Norma2 = 替换(@Norma2,char(192),'')
设置@Norma2 = 替换(@Norma2,char(193),'')
设置@Norma2 = 替换(@Norma2,char(194),'')
设置@Norma2 = 替换(@Norma2,char(195),'')
设置@Norma2 =替换(@Norma2,char(196),'')
设置@Norma2 = 替换(@Norma2,char(197),'')
设置@Norma2 = 替换(@Norma2,char(198),'')
设置@Norma2 = 替换(@Norma2,char(199),'')
设置@Norma2 = 替换(@Norma2,char(200),'')
设置@Norma2 = 替换(@Norma2,char(201),'')
设置@Norma2 = 替换(@Norma2,char(202),'')
设置@Norma2 = 替换(@Norma2,char(203),'')
设置@Norma2 = 替换(@Norma2,char(204),'')
设置@Norma2 = 替换(@Norma2,char(205),'')
设置@Norma2 = 替换(@Norma2,char(206),'')
设置@Norma2 = Replace(@Norma2,char(207),'')
设置 @Norma2 = Replace(@Norma2,char(208),'')
设置 @Norma2 = Replace(@Norma2,char(209),'')
设置 @Norma2 = Replace(@Norma2,char(210),'')
设置 @Norma2 = Replace(@Norma2,char(211),'')
设置 @Norma2 = Replace(@Norma2,char(212),'')
设置 @Norma2 = Replace(@Norma2,char(213),'')
设置 @Norma2 = Replace(@Norma2,char(214),'')
设置 @Norma2 = Replace(@Norma2,char(215),'')
设置 @Norma2 = Replace(@Norma2,char(216),'')
设置 @Norma2 = Replace(@Norma2,char(217),'')
设置@Norma2 = 替换(@Norma2,char(218),'')
设置@Norma2 = 替换(@Norma2,char(219),'')
设置@Norma2 = 替换(@Norma2,char(220),'')
设置@Norma2 = 替换(@Norma2,char(221),'')
设置@Norma2 = 替换(@Norma2,char(222),'')
设置@Norma2 = 替换(@Norma2,char(223),'')
设置@Norma2 = 替换(@Norma2,char(224),'')
设置@Norma2 = 替换(@Norma2,char(225),'')
设置@Norma2 = 替换(@Norma2,char(226),'')
设置@Norma2 = 替换(@Norma2,char(227),'')
设置@Norma2 =替换(@Norma2,char(228),'')
设置@Norma2 = 替换(@Norma2,char(229),'')
设置@Norma2 = 替换(@Norma2,char(230),'')
设置@Norma2 = 替换(@Norma2,char(231),'')
设置@Norma2 = 替换(@Norma2,char(232),'')
设置@Norma2 = Replace(@Norma2,char(233),'')
设置@Norma2 = Replace(@Norma2,char(234),'')
设置@Norma2 = Replace(@Norma2,char(235),'')
设置@Norma2 = Replace(@Norma2,char(236),'')
设置@Norma2 = Replace(@Norma2,char(237),'')
设置@Norma2 = 替换(@Norma2,char(238),'')
设置@Norma2 = 替换(@Norma2,char(239),'')
设置@Norma2 = 替换(@Norma2,char(240),'')
设置@Norma2 = 替换(@Norma2,char(241),'')
设置@Norma2 = 替换(@Norma2,char(242),'')
设置@Norma2 = 替换(@Norma2,char(243),'')
设置@Norma2 = 替换(@Norma2,char(244),'')
设置@Norma2 = 替换(@Norma2,char(245),'')
设置@Norma2 = 替换(@Norma2,char(246),'')
设置@Norma2 = 替换(@Norma2,char(247),'')
设置@Norma2 =替换(@Norma2,char(248),'')
设置@Norma2 = 替换(@Norma2,char(249),'')
设置@Norma2 = 替换(@Norma2,char(250),'')
设置@Norma2 = 替换(@Norma2,char(251),'')
设置@Norma2 = 替换(@Norma2,char(252),'')
设置@Norma2 = 替换(@Norma2,char(253),'')
设置@Norma2 = 替换(@Norma2,char(254),'')
设置@Norma2 = 替换(@Norma2,char(255),'')
设置@Norma2 = 替换(@Norma2,char(32),'')
设置@Norma2 = 替换(@Norma2,char(33),'')
设置@Norma2 = 替换(@Norma2,char(34),'')
设置@Norma2 =替换(@Norma2,char(35),'')
设置@Norma2 = 替换(@Norma2,char(36),'')
设置@Norma2 = 替换(@Norma2,char(37),'')
设置@Norma2 = 替换(@Norma2,char(42),'')
设置@Norma2 = 替换(@Norma2,char(43),'')
设置@Norma2 = 替换(@Norma2,char(58),'')
设置@Norma2 = 替换(@Norma2,char(59),'')
设置@Norma2 = 替换(@Norma2,char(60),'')
设置@Norma2 = 替换(@Norma2,char(61),'')
设置@Norma2 = 替换(@Norma2,char(62),'')
设置@Norma2 = 替换(@Norma2,char(63),'')
设置@Norma2 =替换(@Norma2,char(64),'')
设置@Norma2 = 替换(@Norma2,char(94),'')
设置@Norma2 = 替换(@Norma2,char(123),'')
设置@Norma2 = 替换(@Norma2,char(124),'')
设置@Norma2 = 替换(@Norma2,char(125),'')
设置@Norma2 = 替换(@Norma2,char(126),'')
设置@Norma2 = 替换(@Norma2,char(127),'')
返回(@Norma2)
结束
companyref 表有 ~500K 行,而 extn_temp 中的记录根据用户请求而变化。相同的查询在 sql 2000 框上运行良好。
您的意见对我们确实有很大帮助。
提前致谢。
问候,Sameer Golam。
答案1
我不确定这是不是个玩笑,但如果升级后性能变慢,您应该尝试重建表上的索引并更新其上的统计信息。