Are SQL views efficient?

Question

Are SQL views efficient?

Viewing 2 posts - 16 through 16 (of 16 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I've run into that same argument from a dev before. He insisted that building a temp table from a filtered primary table, then updating it from the rest of the tables, was faster than joining tables in a view. He insisted he had proved that absolutely. He was just plain wrong.

There are occassions where the SQL Query Optimizer will pick a poor plan for a query, and needs to be overridden. They are rare, and mainly apply to very poorly designed databases in the first place. (The dev's "proof" was a query on a database that violated 1NF badly, including multiple Name-Value-Pair tables with a nested hierarchy of complex data typing NVP lookups. Infinitely flexible database design, if you don't mind queries that should take milliseconds, instead taking minutes or even hours to run.)

Basically, that method of querying mimics what SQL Server does behind the scenes, with worktables and nested-loop joins. But it bypasses all the optimizations SQL Server has available to it for things like modifying a query plan when stats change. It avoids hash/merge joins where those would be better and sticks with an immitation nested-loops join.

I would guess that the dev in question, like the one I ran into, has too much Comp Sci theory, and too little practical skill in things like reading execution plans.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 2

GilaMonster (4/19/2012)
pwallis (4/19/2012)
The developer is indicating that it is in executing the CREATE VIEW using the SQL statement that takes the time, if we run just the SQL statement without the CREATE VIEW around it then it doesn't take minutes for the SQL statement to be executed.
A Create View takes no time. SQL doesn't execute the query, it just parses it, then saves the view in the system catalog. The only way that could be slow is via blocking, then you'd have to find out what's blocking it and why
A SELECT * FROM <view> is exactly the same as running the query that defines the view. All a view is is a saved select statement.
Will the order of the clauses in the WHERE clause say result in all records from the primary table being read and then the joins occur and then the records filtered back, instead of subsetting the primary records 1st and then applying the joins? (This goes back the prior question about the use of partitions).
No. The optimiser is smarter than that. Order that the where clause predicates or joins are specified in the query does not determine the order they are executed in.
It sounds to me like the dev is trying to feed you a lot of manure (or the dev is clueless, can't tell)

As mentioned before the create view should not take the time. The generation of the view statement from the front end is probably the culprit in this case, as the OP / dev is using a process to generate the select statement based upon multiple primary tables (in effect fact tables) and multiple lookup tables (dimension tables), for source, joins and where clauses.

Fitz