January 10, 2014 at 1:36 pm
Not sure if this question has a "general" answer or not.
At work, I ran into a view that is only for a certain type of member (call them "special") and selects from the member table. But there is no WHERE clause filtering for them, such as WHERE MemberType = 'SPECIAL', so the view will return all 3,000,000 records, instead of the 25,000 'special' member records
The selection for "SPECIAL" members is done in the code that references the view.
select * from Table_A
Join V_SpecialMembers on v.member = a.member
where v.MemberType = "SPECIAL".
I am wondering if there is a performance difference if the WHERE MemberType = 'SPECIAL' is in the view, or outside in the code that uses the view:
Intuitively, I would think the WHERE should be in the view to restrict the # of rows it selects, but I know that my intuition and the facts can be far apart !
EDIT: A quick test on the member table with a simple query & view returns the results in the same time for bot, and both show the same execution plan.
January 10, 2014 at 2:45 pm
homebrew01 (1/10/2014)
I am wondering if there is a performance difference if the WHERE MemberType = 'SPECIAL' is in the view, or outside in the code that uses the view:
No.
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 10, 2014 at 3:20 pm
I wouldn't expect to see a difference unless the NOEXPAND hint was used, forcing the query to run without allowing the where clause access to the underlying indexes.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply