Home > sql server > Query Running Slow after migrating to SQL Server 2008

Query Running Slow after migrating to SQL Server 2008

June 30Hits:1
Advertisement

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.

Answers

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.

Tags:sql server

Related Articles

  • Query Running Slow after migrating to SQL Server 2008June 30

    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 c

  • Ridiculously slow queries on Microsoft SQL Server 2008 R2May 25

    I'm getting really slow queries in MS SQL Server 2008 R2 on my dev machine. This problem has been plaguing me for about a month. Other developers don't have the same problem, but we all run the same code. It seems to be that any query that includes a

  • SQL Server performance is slow when migrated from SQL Server 2012 to SQL Server 2014 March 18

    This question already has an answer here: Query is slow in SQL Server 2014, fast in SQL Server 2012 1 answer We are trying to migrate from SQL Server 2012 to SQL Server 2014 on Windows 2012 R2 OS. After the upgrade, we are doing performance testing a

  • querying a linked PostgreSQL database in SQL Server 2008March 27

    We will be deploying an application with a Postgres back-end and need to provide report writing capabilities in SQL Server. I am exploring options for real-time or near real-time reporting in this environment. I am curious about linking the Postgres

  • Migration from SQL Server 2008 to Postgres 9.2 January 30

    I have a rather large database (2 GB of data, 25 million rows in the largest table) on a shared web hosting. The database engine is Microsoft SQL Server 2008. Due to high fees for the SQL Server web hosting, I would like to migrate my data from SQL S

  • How can I migrate a SQL Server 2008 Express database to SQL Server 2008 Standard?June 27

    SharePoint 2010 automatically installed the database on SQL Server Express 2008 but I need to migrate the database to SQL Server 2008. How can I go about doing this? This is installed on Windows Server 2008 R2 Standard. --------------Solutions-------

  • Database server migration and SQL Server 2008 licensing April 19

    Possible Duplicate: Can you help me with my software licensing issue? We currently have a dedicated server that hosts a number of websites and also has SQL Server 2008 Enterprise running on the same machine housing the databases for those websites. W

  • How can a cached query take 2500 ms on a Sql Server 2008?March 19

    How is it possible that a cached query could take that long. With cache cleared it takes 25000 ms (I know that's just way too long but that's not the issue here). Once it is cached, it takes 2500 ms. What could be the problem here? If it's cached, I

  • Migration From SQL Server 2008 Express to SQL Server WebOctober 2

    I have SQL Server Management Studio (for SQL Server Express R2) on my pc, and I'd like to export a few databases for use by a website that's running on a server with SQL Server Web. However, when I right click one of the databases in management studi

  • Running multiple evaluation instances of SQL Server 2008December 21

    I'm playing around with sql 2008 server. I have already installed an enterprise evaluation instance and i added another one. Installation runs fine, but i cannot connect to the second instance. Server is running for both instances. Is that a restrict

  • Is it possible to run a free version of sql server 2008 to the foundation Server 2008 ServerMarch 14

    I'm planning to run a ms sql server 2008r2 express edition on a windows server 2008R2 foundation edition. Is it possible? what are the possible issues? --------------Solutions------------- You can install SQL Server on 2008 R2 Foundation but you have

  • How to migrate from sql server 2008 to 2012?January 25

    I am facing an issue of migrating from 2008 sql server to 2012 sql server. I am seeing two methods of migration in-house and parallel ones which one is better and how to do? --------------Solutions------------- It is better to migrate to PostgreSQL.:

  • How to store a query result into a variable? SQL Server 2008April 30

    I have two tables and I want to store the result query of my stored procedure into a variable. UserTable: UserID int primary key identity CredentialsID int AccountType nvarchar(15) ModifiedDate date CredentialsTable: Credentials ID int primary key id

  • Oracle 10g to SQL server 2008/ migrationFebruary 11

    We have a reasonably heavy Oracle 10G database (~ 120 GB space occupied). The database consists of tables, packages, materialized views, procedures etc. We are planning to decommission Oracle and migrate to SQL server 2008. Can you provide your exper

  • Seek assistance with: SQL Server 2008 Query that takes values from a column in another table (or from a flat file)March 12

    I am given a list in MS Excel of 554 statistical units (called Primary Sampling Units or PSUs) from another department and have to query all 554 PSUs from a SQL Server 2008 Spatial database table (PSUs_2012) that contains over 6000 PSUs. I want to av

  • Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instanceMay 1

    I have come across three old databases sitting on SQL Server 2000 which I need to move to 2012. I believe the standard approach is to restore into a 2005 or 2008 instance, update, re-export and finally restore into 2012. Fine, except we have no 2005

  • SQL Server 2008 mirroring migration to SQL Server 2012 always onFebruary 21

    Quick question, request your input. I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on. Should I : Restore the SQL Server 2008 R2 backup to SQL Server 2012 primary and mirror (with no recovery), set

  • Rebuilding, truncating etc SQL Server 2008 maintenanceApril 24

    About a month or so ago I inherited this somewhat-new database. I was lead to believe that everything was set up and all maintenance tasks were running without a hitch. SQL Server 2008 R2 I was looking at it today while trying to troubleshoot some sl

  • How to make SQL Server 2008 compatible to SQL Server 2000July 17

    I have to run the backup of SQL Server 2000 in SQL Server 2008. While restoring the database from the .bak file, I got the error specified cast is invalid After doing google I feel there is compatibility issue. Therefore I want to make the database c

  • sql server 2008 full textDecember 29

    just migrated to sql server 2008 for some reason the enable full text checkbox for all my databases are grayed out. any idea why? i do not see full text in services just full text filter daemon launcher --------------Solutions------------- Did you in

Copyright (C) 2018 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.755 s.