Case Statement Causes Slowness

[OrWolf]

Hi All,
I have a SQL view that contains a CASE statement to identify is a given value is not null in which case the result shown is a simple asterisk, otherwise nothing. This is used to identify a primary contact that is linked to an organization. The problem is that while helping the users of my .NET application it slows down the view by 75%. Does any one have any idea that will give me the same result but improve performance?

View Syntax:
SELECT Person.PersonID, Person.FirstName, Person.LastName, CASE WHEN Organization.OrganizationID IS NULL THEN '' ELSE '*' END AS PrimaryContactFlag
FROM Person
LEFT OUTER JOIN Organization ON Person.PersonID = Organization.PrimaryContactID

Thank you

 

[gmmastros]

Do you have an index on the PrimaryContactId column of the Organization table?

Try running this....

sp_helpindex 'organization'

This will show you the indexes that exist for the Organization table. I suspect that you do NOT have an index on the column. Furthermore, if you do index it, I suspect that your query performance will improve.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrWolf]

Thanks for looking at this. I did not have an index on this table. I added it but the performance improvement wasn't noticeable. Then I found a solution that really worked. I broke the view into two, with the second select statement just being the organization table where the PrimaryContactID wasn't null. This worked like I had actually removed the case statement.

New view:
SELECT Person.PersonID, Person.FirstName, Person.LastName, PCF.PrimaryContactFlag
FROM Person
LEFT OUTER JOIN (SELECT PrimaryContactID, '*' AS PrimaryContactFlag FROM Organization WHERE PrimaryContactID Is Not Null) PCF ON PCF.PrimaryContactFlag = PersonID