January 2, 2009 at 1:13 pm
Hi All,
I am trying to tune an application which was written in SQL 2000, after upgrading onto SQL 2005, application is refusing to run or rather taking infinity time to run.
I thought Yukon was meant to improve things and not ruin them, please advise.
UPDATE PersonProfile
SET CP_Service = CP.CampaignID,
FROM PersonProfile E, CompanyProfile CP
WHERE CP.CampaignID = SUBSTRING(PersonURN,1,LEN(CP.CampaignID))
AND CP.CampaignID =
(
SELECT MAX(CampaignID)
FROM CompanyProfile
WHERE CampaignID <= PersonURN
AND CampaignID = SUBSTRING(PersonURN,1,LEN(CampaignID))
)
In fact, I have tried to change the functionality to use a stored procedure to return the values for the update, i.e replacing the bold section, but this hasnt made any difference at all.
Any help will be appreciated.
January 2, 2009 at 2:02 pm
It most like has to do with the Substring function in your where clause. This forces the DB Engine to have to scan through everything looking for matches, and cannot use indexing efficiently to do so.
Perhaps see if you can set that up as a computed, persisted, indexed column?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 2, 2009 at 2:06 pm
I would also make sure that all of the tables involved are aliased, and that all column references have a matching table ref on them, so that there is no ambiguity where things are coming from.
The index usage changed somewhat as well, so you may find that they're not being used.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 2, 2009 at 2:13 pm
Thanks guys, I did think about computing the columns instead of using the substring functions as well, but if you look at where the substrings have been used, you will notice that its calculated dynamically by joining onto another table, thereby making pre computing the columns rather tricky and cumbersome.
It would be easy to create a new pre-computed column of say
SUBSTRING(PersonURN,1,10) instead of
SUBSTRING(PersonURN,1,LEN(CP.CampaignID))
Any thoughts..
Thanks guys
January 2, 2009 at 3:23 pm
Can you show us the execution plan?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 3, 2009 at 7:44 am
RBarryYoung (1/2/2009)
Can you show us the execution plan?
Additionally, the schema of the tables and the index definitions would be of great help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2009 at 6:55 am
After migrating it's pretty strongly suggested that you update statistics with a full scan on all the tables in a database. Did you do this?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2009 at 4:36 am
Thanks Guys
I alreadyupdated statistics during the time of migration.
The schema for the object are detailed below.
CREATE TABLE [dbo].[PersonProfile](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PersonURN] [varchar](30) ,
[SourceCode] [varchar](1) ,
[SourceCode_Updated] [varchar](1) NULL,
[CP_Service] [varchar](100)
)
GO
CREATE CLUSTERED INDEX [ix_PersonProfile] ON [dbo].[PersonProfile]
(
[PersonURN] DESC
)
GO
CREATE NONCLUSTERED INDEX [index_PersonProfile_flags] ON [dbo].[PersonProfile]
(
[SourceCode] ASC,
[SourceCode_Updated] ASC
)
CREATE TABLE [dbo].[CompanyProfile](
[CampaignID] [varchar](20) NULL,
[CampaignID_Code] [varchar](100) NULL
)
CREATE CLUSTERED INDEX [IX_CompanyProfile] ON [dbo].[CompanyProfile]
(
[CampaignID] DESC
)
January 6, 2009 at 6:34 am
Can you post the execution plan please. Save as a .sqlplan file, zip that and attach it to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 8:40 am
Whenever I hear about migration from 2000 to 2005 and performance problems with character data, first thing that comes in my mind is collation. If that's the source of problem, you would see it in the execution plan, so please post that.
In short, if you are using one of the old "SQL" collations, you probably should change it to "Windows" collation, since using old type collation often forces table scans when there is a JOIN or WHERE condition working with character data. Old collation names start with "SQL_", Windows collations don't.
January 6, 2009 at 8:50 am
Thanks for the reply, the collation on the tables is as follows:
Latin1_General_CI_AS
The problem is that, I am currently not on site at the moment and do not currently have access to the query plan.
January 6, 2009 at 9:01 am
I also think some sample data for the tables would help as well. If you read the article linked below in my signature block regarding Asking for help, it will show you how to post sample data.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply