SQL Server 2008 Stored Proc Performance where Column = NULL

Question

When I execute a certain stored procedure (which selects from a non-indexed view) with a non-null parameter, it's lightning fast at about 10ms. When I execute it with a NULL parameter (resulting in a FKColumn = NULL query) it's much slower at about 1200ms.

I've executed it with the actual execution plan and it appears the most costly portion of the query is a clustered index scan with the predicate IS NULL on the fk column in question - 59%! The index covering this column is (AFAIK) good.

So what can I do to improve the performance here? Change the fk column to NOT NULL and fill the nulls with a default value?

SELECT  top 20 dbo.vwStreamItems.ItemId
            ,dbo.vwStreamItems.ItemType
            ,dbo.vwStreamItems.AuthorId
            ,dbo.vwStreamItems.AuthorPreviewImageURL
            ,dbo.vwStreamItems.AuthorThumbImageURL
            ,dbo.vwStreamItems.AuthorName
            ,dbo.vwStreamItems.AuthorLocation
            ,dbo.vwStreamItems.ItemText
            ,dbo.vwStreamItems.ItemLat
            ,dbo.vwStreamItems.ItemLng
            ,dbo.vwStreamItems.CommentCount
            ,dbo.vwStreamItems.PhotoCount
            ,dbo.vwStreamItems.VideoCount
            ,dbo.vwStreamItems.CreateDate
            ,dbo.vwStreamItems.Language
            ,dbo.vwStreamItems.ProfileIsFriendsOnly
            ,dbo.vwStreamItems.IsActive
            ,dbo.vwStreamItems.LocationIsFriendsOnly
            ,dbo.vwStreamItems.IsFriendsOnly
            ,dbo.vwStreamItems.IsDeleted
            ,dbo.vwStreamItems.StreamId
            ,dbo.vwStreamItems.StreamName
            ,dbo.vwStreamItems.StreamOwnerId
            ,dbo.vwStreamItems.StreamIsDeleted
            ,dbo.vwStreamItems.RecipientId
            ,dbo.vwStreamItems.RecipientName
            ,dbo.vwStreamItems.StreamIsPrivate
             ,dbo.GetUserIsFriend(@RequestingUserId, vwStreamItems.AuthorId) as IsFriend
             ,dbo.GetObjectIsBookmarked(@RequestingUserId, vwStreamItems.ItemId) as IsBookmarked
    from dbo.vwStreamItems WITH (NOLOCK)
    where 1 = 1
    and vwStreamItems.IsActive = 1
    and vwStreamItems.IsDeleted = 0
    and vwStreamItems.StreamIsDeleted = 0
    and (
            StreamId is NULL
            or
            ItemType = 'Stream'
        )


    order by CreateDate desc

Answer 1

The biggest performance gain would be for you to try to loose GetUserIsFriend and GetObjectIsBookmarked functions and use JOIN to make the same functionality. Using functions or stored procedures inside a query is basically the same as using FOR loop - the items are called 1 by 1 to determine the value of a function. If you'd use joining tables instead, all of the items values would be determined together as a group in 1 pass.

Answer 2

When it's not null, do you have

and vwStreamItems.StreamIsDeleted = 0
and (
    StreamId = 'xxx'
    or
    ItemType = 'Stream'
    )

or

and vwStreamItems.StreamIsDeleted = 0
and (
    StreamId = 'xxx'
    )

You have an OR clause there which is most likely the problem, not the IS NULL as such.

The plans will show why: the OR forces a SCAN but it's manageable with StreamId = 'xxx'. When you use IS NULL, you lose selectivity.

I'd suggest changing your index make StreamId the right-most column.

However, a view is simply a macro that expands so the underlying query on the base tables could be complex and not easy to optimise...