Odd behavior with views

Question

Odd behavior with views

Mike Scalise

SSCrazy Eights

Points: 8145
More actions
November 10, 2017 at 7:29 pm

#341366

Hi all,
My coworker recently told me about an odd situation he encountered with views. He found that when he issued a simple SELECT * query against two views that were joined together, it consistently ran faster than when he specified only the handful of fields he needed in the SELECT statement. Without digging into the query plans just yet, is there a general reason why this might happen? I can't think of a scenario where specifying fields (and fewer fields at that) would take longer than a SELECT *.
Any initial thoughts?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'd ask for a reproducable example with proper timing tests done enough times that you can tell whether the difference is statistically significant.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 2

A couple of "potential" reasons...
1) the separate execution incurred the cost of pulling data from disk into the buffer pool and the joined query simply read from the buffer poll w/o any physical io.
2) the joining of the two views (assuming an inner join) allowed each view to act as a filter predicate for the other.

There's no way to tell for certain without seeing the view definitions, the joined query and the execution plans w/ actual vs estimated rows returned... and the only way to rule out #1 would be to either execute each of them several times (ensuring there was a warm cache for each) or execute DBCC DROPCLEANBUFFERS between each execution (insuring a cold cache for each).

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

It could be tons of things. Limiting the selection list allows the Optimizer to do simplification, eliminating unneeded tables within the views (you haven't specified if the views are of a single table each or are combining lots of tables, which is usually what happens). Without seeing the query, the view definitions and the execution plan, we can only guess.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

I agree with Gail... While I absolutely agree that anecdotal evidence my identify the need, it should not be used to make a decision nor form the basis of some practice. You need to use the scientific method to surely and accurately identify both the cause and the effect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 5

Thank you, all, for the replies. I'm going to see if I can get more information about exactly what's going on here. I know the answer with a lot of SQL Server stuff is, "it depends" but I was hoping there was a clear-cut reason why this would happen--unfortunately it looks like it'll require some deeper investigation.

Again, thanks for your responses.

Mike

Mike Scalise, PMP
https://www.michaelscalise.com