Challenging Query / View

Hi,
We have a tough situation when we migrated one of the view from SQL Server
2000 to SQL Server 2005, we have seen CPU hogging around 100% when we access
this particulat view. It works fine in SQL 2000 but has CPU hogging in SQL
Server 2005. This is the view definition

SELECT CASE WHEN ARC.ContactID IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactID ELSE ARC.ContactID END As ContactID,
CASE WHEN ARC.ContactName IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactName ELSE ARC.ContactName END As ContactName,
CASE WHEN ARC.IsValidEmail IS NULL OR MSC.DataSourceCode = 'USER' THEN
CASE MSC.IsValidEmail WHEN 'Y' THEN MSC.ContactEmail ELSE '' END ELSE
CASE ARC.IsValidEmail WHEN 'Y' THEN ARC.ContactEmail ELSE '' END END As
ContactEmail,
CASE WHEN ARC.ContactType IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactType ELSE ARC.ContactType END As ContactType,
CASE WHEN ARC.DataSourceCode IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.DataSourceCode ELSE ARC.DataSourceCode END As DataSourceCode,
CASE WHEN ARC.IsValidEmail IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.IsValidEmail ELSE ARC.IsValidEmail END As IsValidEmail,
CASE WHEN ARC.IsAllowUpdate IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.IsAllowUpdate ELSE ARC.IsAllowUpdate END As IsAllowUpdate,
CASE WHEN ARC.IsAllowElectronicCommunication IS NULL OR
MSC.DataSourceCode = 'USER' THEN MSC.IsAllowElectronicCommunication ELSE
ARC.IsAllowElectronicCommunication END As IsAllowElectronicCommunication,
CASE WHEN MSC.ContactAddress1 IS NULL THEN ARC.ContactAddress1 ELSE
MSC.ContactAddress1 END As ContactAddress1,
CASE WHEN MSC.ContactAddress2 IS NULL THEN ARC.ContactAddress2 ELSE
MSC.ContactAddress2 END As ContactAddress2,
CASE WHEN MSC.ContactAddress3 IS NULL THEN ARC.ContactAddress3 ELSE
MSC.ContactAddress3 END As ContactAddress3,
CASE WHEN MSC.ContactFaxNbr IS NULL THEN ARC.ContactFaxNbr ELSE
MSC.ContactFaxNbr END As ContactFaxNbr,
CASE WHEN MSC.PhoneNbr1 IS NULL THEN ARC.PhoneNbr1 ELSE MSC.PhoneNbr1
END As PhoneNbr1,
CASE WHEN MSC.PhoneNbr2 IS NULL THEN ARC.PhoneNbr2 ELSE MSC.PhoneNbr2
END As PhoneNbr2,
CASE WHEN MSC.PhoneNbr3 IS NULL THEN ARC.PhoneNbr3 ELSE MSC.PhoneNbr3
END As PhoneNbr3,
CASE WHEN MSC.PhoneNbr4 IS NULL THEN ARC.PhoneNbr4 ELSE MSC.PhoneNbr4
END As PhoneNbr4,
CASE WHEN MSC.Position IS NULL THEN ARC.Position ELSE MSC.Position
END As Position,
CASE WHEN MSC.ContactComments IS NULL THEN ARC.ContactComments ELSE
MSC.ContactComments END As ContactComments,
CASE WHEN MSC.IsActive IS NULL THEN ARC.IsActive ELSE MSC.IsActive
END As IsActive,
CASE WHEN ARC.CreatedBy IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.CreatedBy ELSE ARC.CreatedBy END As CreatedBy,
CASE WHEN ARC.CreatedDate IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.CreatedDate ELSE ARC.CreatedDate END As CreatedDate,
CASE WHEN MSC.UpdatedBy IS NULL THEN ARC.UpdatedBy ELSE MSC.UpdatedBy
END As UpdatedBy,
CASE WHEN MSC.UpdatedDate IS NULL THEN ARC.UpdatedDate ELSE
MSC.UpdatedDate END As UpdatedDate
FROM dbo.dat_Contact MSC
FULL OUTER JOIN ARCommon.dbo.dat_Contact ARC
ON MSC.ContactID = ARC.ContactID

dbo.dat_Contact- This has around 50000 rows and ARCommon.dbo.dat_Contact
has around 40000 rows.

I dont have an option of getting rid of this view as it has been referrend
in more than 50 procs. Is there anyway I can rewrite the logic in this view
so that CPU time comes down in SQL Server 2005. I figured this CASE logic is
the one that is causing CPU hoggging..

Please help me ASAP.

Regards,
Murali

Ses, you are right, the case is consuming the most cpu in here. Did you chance (even for readability) the CASE constructs (Where possible) to ISNULL(someColumn,SomeOtherColumn) ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This construct:

CASE WHEN MSC.PhoneNbr2 IS NULL THEN ARC.PhoneNbr2 ELSE MSC.PhoneNbr2
END As PhoneNbr2,

can be replaced with this:

Code Snippet

PhoneNbr2= coalesce ( MSC.PhoneNbr2, ARC.PhoneNbr2 )

|||

CASE expression and scalar expression operators in general should not consume the most CPU during query execution. This may be the case if you have some complex UDF in the expression for example. Otherwise it is very unlikely. So the problem might be somewhere else - like a parallel execution plan, out of date statistics, missing indexes etc.

Start first by comparing the actual execution plan between SQL Server 2000 (SET STATISTICS PROFILE ON) and SQL Server 2005 (SET XML STATISTICS PROFILE ON). Ensure that the indexes are same between the servers and the statistics are updated. By looking at the execution plan, you can confirm if there is a plan change & if so that is your problem. Once you have determined the change in the plan then it is easy to suggest the workaround.

If the plans are the same then look for any other problem - hardware configuration, disk subsystem etc.

|||This will not help. COALESCE is just short-form for the above CASE expression. It will get resolved to the same form actually. It doesn't help in terms of performance only readability.