View with a where clause

6 thoughts on “View with a where clause”

1. Interesting! I have always advocated against using views because of possible complexity. It’s just a stored query that adds complexity to the query that uses it. And manipulations of columns can make indexes (and statistics) unusable.
   I can replicate your tests on a SQL2019 box with DB compatibility level 2019. Indeed the view query is slower.

   I did some testing with several executions and then I find comparable average durations. What do you think of the below? I ran it on the heap table without statistics io,time on. I got an average duration of 71ms for the table and 68ms for the view. Apparently when running it more often (with re-use of the exec plan) it performs comparable. Note that the standard deviation of the durations are high. In my case it was about half of the average duration. And weirdly in my case: in almost all cases every 3rd or 4th execution of the query would be slower for both the table and the view.

   drop table if exists dbo.results
   GO
   create table results(id int identity(1,1), mode nvarchar(10), startdate datetime, enddate datetime)
   GO
   DBCC FREEPROCCACHE
   GO
   declare @start datetime
   declare @end datetime
   set @start = getdate()
   SELECT * FROM vw_demo
   WHERE YEAR > 2010
   set @end = getdate()
   insert into results (mode,startdate,enddate) values(‘view’,@start,@end)
   GO 100

   DBCC FREEPROCCACHE
   GO
   declare @start datetime
   declare @end datetime

   set @start = getdate()
   SELECT * FROM DIM.DATE
   WHERE YEAR > 2010
   set @end = getdate()
   insert into results (mode,startdate,enddate) values(‘table’,@start,@end)
   GO 100

   select count(*) as #runs, mode,
   avg(datediff(ms,startdate,enddate)) as avg_duration,
   cast(stdev(datediff(ms,startdate,enddate)) as int) as stdev_duration
   from results
   group by mode

   select *,datediff(ms,startdate,enddate) from results

   LikeLike

   Reply
2. Pingback: Performance Differences on a View with a WHERE Clause – Curated SQL

3. The issue arises because your top-level queries qualify for simple parameterization (hence the @1 parameter markers).

   The value inside the view isn’t parameterized, so you end up performing two separate comparisons in that case – once for the @1 value and once for the literal value 2004. Doing more work takes longer of course.

   There are many ways to avoid simple parameterization where it is counterproductive. A common method is to add a redundant 1 = 1 predicate to the top-level query (not inside the view):

   SELECT *
   FROM vw_demo
   WHERE YEAR > 2010
   AND 1 = 1;

   You could also add any meaningless query hint like OPTION (MAXRECURSION 100) or OPTION (KEEP PLAN). Since you’re clearing the plan cache between runs, OPTION (RECOMPILE) would also be harmless. As I said, any query hint will disable simple parameterization attempts.

   Whichever method you choose, without parameterization the optimizer will collapse the two ‘YEAR’ filters on literal values into one equivalent test. Plans and performance for both approaches will then be the same, with both performing a performing a single test on ‘YEAR > 2010’.

   LikeLiked by 1 person

   Reply

   1. Hi Paul, thanks for the reply!

      I’ve tried it out and, the execution plans are identical now, both with the following seek predicate:

      Seek Keys[1]: Start: [DEMODB].[DIM].[DATE].YEAR > Scalar Operator((2010))

      Funny thing is, the regular query is still faster than the view with a filter in it.

      LikeLike

      Reply

4. May I invite you to reconsider your analysis? You are looking at elapsed time, but not taking into consideration the CPU time. If one query has to do more work than the other, it should show up in CPU time. The difference between CPU and elapsed is huge – what are the waits?

   Also, I recommend looking at the query plans. Actual plans also give timing information nowadays, and are generally superior to SET STATISTICS TIME.

   LikeLike

   Reply

   1. Hi Forrest, I think you’ve just set yourself up with a great introduction for your own blogpost :).

      I did look at both query plans and, both were identical (as I wrote in the blog ;)). Getting waitstats on very short query’s is not impossible but it won’t give you much info. I tried it for you and, after filtering out all the benign ones (as per Paul Randall), the result set is empty.

      LikeLike

      Reply