January 3, 2008 at 2:13 pm
Any opinions regarding a view in the relational database (no schema binding) vs. a named query in the DSV for SSAS 2005? Any performance degredation using one vs. the other?
Tommy
Follow @sqlscribeJanuary 4, 2008 at 11:38 am
From a quick test, there seems to be no performance difference (as I suspected). I use named queries in the DSV for everything, even if I need the entire table because I can make minor adjustments to names. If you exclude any columns, using a named query or a view is better than using the entire table and unchecking fields yoou do not want to include.
The only reason I would use views is if I intended to re-use them for something else and needed consistency. If that is not the case, I would always lean toward not adding bundles of objects into my database that I do not need.
January 4, 2008 at 11:44 am
Thanks Michael 🙂
Tommy
Follow @sqlscribeJanuary 5, 2008 at 11:14 am
hi
i used named query in 3 BI project of international company
i don't remark any difference between named query and simple query or direct views
January 8, 2008 at 7:50 am
I've not tested this myself but there’s always been a niggle in the back of my mind that using views instead of base tables when building dimensions and cubes might not take advantage of indexes??
Personally I'm a view man all the way for the reasons already mentioned and to be honest I don’t think it would make any difference but I just haven’t had time to scratch that niggle.
If ever I do; and find any evidence to back up not using views I'll post the findings, alternatively if someone else finds the answer please let us know.
HWUK
January 8, 2008 at 7:55 am
🙂 Thanks, that's a good point, one would assume that the execution plan (view vs. named query) would differ quite a bit.
Tommy
Follow @sqlscribeJanuary 8, 2008 at 8:13 am
If your view is "SELECT * FROM MyTable" there is no performance difference using a view vs a table. The optimization engine will still continue to use indexes regardless of using a view or just running a query against a table directly.
If you view is the same query as the named query in your DSV, SQL will generate the same execution plan.
If you use the * in a view, you will not see new columns if you do not run an ALTER on the view after adding them to the table. However, since a named query in a DSV always specifies the column names it will end up behaving the same.
January 9, 2008 at 12:58 pm
My two cents worth:
Using the table name directly works well, in particular if you've added the database information to allow AS to auto discover the various relationships. However, it doesn't allow you to implement more complex considerations such as filtering (see views below). If you're putting together an adhoc, RAD type cube, this can work well.
Named queries as essentually views stored in MSAS (not the database). They allow you to implement filtering and more complex logic. I'd recommend them more as a prototyping tool, in particular where the MSAS developer is separate from the DBA. (And needs the flexibility to change the approach, perhaps many times.)
View (like named queries) force you to draw the relationships yourself in MSAS. At the same time, they'll allow you to implement record filters (only display active records), limit the columns returned (i.e. audit columns are rairely required in BI), and perform data transformations (WHEN 1 THEN YES). For more formal projects, views allow you to allocate more data responsibilities (i.e. maintenace guys) back to the DBAs. Plus, properly designed, there's a greater potential for reuse.
Recommendation: Consider using a named schema when implementing the views. This allows you to switch back and forth to the base tables (i.e. assuming they are under dbo) without a whole lot of effort.
January 9, 2008 at 1:01 pm
Thanks Dave 🙂
Tommy
Follow @sqlscribeDecember 29, 2010 at 8:56 am
My 2 cents.. The performance difference would be realized when you get into large databases, joined table queries, specific columns, where clauses, and other more complex queries. In these cases, a view would be more efficient because it is optimized at creation time to use the most efficient means to locate, gather and return the data for the query.
Thanks,
Harvey..
December 30, 2010 at 3:17 am
View (like named queries) force you to draw the relationships yourself in MSAS. At the same time, they'll allow you to implement record filters (only display active records), limit the columns returned (i.e. audit columns are rairely required in BI), and perform data transformations (WHEN 1 THEN YES). For more formal projects, views allow you to allocate more data responsibilities (i.e. maintenace guys) back to the DBAs. Plus, properly designed, there's a greater potential for reuse.
In addition to the above, I have found views useful on the date dimension base table to create a view that just shows the relevant fields for the month, or the time table to create a 15 interval version.
I also have a subset of inspectors from my list of technicians. Most of the technicians are not inspectors, and at first I maintained a seperate table. By creating the view, I allowed the underlying maintenance to be performed in one place.
Although I'm generally not a big fan of views in an OLTP design, prefering stored procs in most cases, I'm finding more an more that they have interesting possibilities in an OLAP design.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply