Indexed view question
-
Hi everybody,
Is it possible to create local indexed view from underlying tables located at another SQL Server?
-
No. It would be a bad thing anyway. SQL's indexed views are dynamic (contrary wise to Oracle materialized views which are depressingly static) and the overhead would be ugly.
Don't know what you are strying to do, but replication is possibly the answer. One way replication is easy to setup and has few constraints since there is no danger of conflicts.
ps: you might even be able to replicate remotely an indexed view. Would be a logical possibility but I have never tried it.
-
Thanks for your answer lakusha.
Please, will you explain me why do you think that SQL Server indexed view will have more overheads than (for example) stored procedure that retrieves data using linked server and several tables located at remote SQL Server (maybe less overheads with OPENQUERY).
I am not sure about that your suggestion, where you propose database replication, is applicable in my case. I do not want to replicate database with several hundreds tables for the purpose of running query that involve several tables.
Regards,
Milovan
-
The indexed view must be kept up to date and this is part of any transaction that modify rows refered to by the view. If each row in the view is an aggregation of N rows in base tables, you end up with a concurrency hot spot. Indexed views are extremely efficient if you read a lot but don't do much writing. You use it in data warehousing, not OLTP.
Now visualise that each transaction must be committed on the second server before it is committed on the primary server. Here goes your overhead (about 30% slower because of network latency and 2 way handshakes). Linked servers and open query have other problems. They won't slow down your writing since you use them only for reading but you may end up spending lots of time tuning and rewriting to overcome other problems. For example, in some cases queries with a linked server can bring the entire table back and do the filtering on the local host
(ugly with 40 million rows ). OPENQUERY has other problems.Each feature address specific needs, so you have to decide which one to use in your case and each one scales differently. A perfect solution for a 1 gig db/ 20 users won't fit a 100gig/300 users system.
Another solution to look at (with SQL2k5) is running reports on a mirror. If you have budget for high availability, you can mirror on a second server (even asynchronously to have better performance if you have the Enterprise edition) and run reports on snapshots of the mirror db.
-
The main purpose of my question was to try to find way of indexed view implementation using underlying tables that resides on another server. This view will not use aggregations and will be implemented from underlying tables that are not heavily updated/inserted. I will use this view as a data source. It is because I think that my indexed view will have advantage in response time over stored procedures that use joins of remote tables or over OPENQUERY used as data source. Both SP and OPENQUERY use linked server. Difference is that OPENQUERY is executed on remote server and has less network traffics. I have several indexed view implementations on local server (with local tables) and all of them speed up daily operation. I do not agree with you that main purpose of view is to be used in DW.
I do not even think of applying mirrored server. I already implemented log shipping on standby server for reporting and BCP purposes and do not want to use reporting server for queries used in production - in my opinion it is danger.
Anyhow thanks for your answers and explanations.
Regards,
Milovan
-
This is just an opinion... hard for me to write this one without sounding a bit Draconian... sorry...
Since views use the indexes from underlying tables, I've never understood the need to schema bind a view to a table and then turn it into an indexed view. And, unless the views are pass through views (SELECT * , kinda like synonyms in other RDBMS's and seems like what you're trying to do), I've found that most folks simply try to do too much in a view and it really kills performance... because a view must be a single query, folks will join 8 or 10 tables, do some of the oddest calcs in the WHERE and ON clauses, return columns for every situation possible, and usually make a view so general pupose that it doesn't do any one thing well.
AND, if you do a self join on a very high rowcount view, you can drive one or more CPU's right into the stops because the view must materialize in memory/tempdb not once, but twice and then create the necessary internal functionality (hash tables, nested loops, etc) to do the join.
Should views be use at all? Sure... if they are well written and perform well. But, typically, I don't use them and I try to discourage my team from using them.
Views of views are normally "death by SQL" when it comes to performance... if you want to do a quick little performance test on very large views, try doing the following...
SELECT TOP 10 *
FROM someview
WHERE somecolumn = somevalue --Do it once each with and without the WHERE clause
... if it takes more than a second to return, the view is probably written incorrectly.
So far as your remote application goes, a "qualified" pass through view is probably the ticket... "qualified" in this case, means return only the columns you know you will use instead of doing a SELECT *. The real performance drain is going to be the linked server (or OpenRowset, whatever), simply because it is interserver.
Like I said... just an opinion...
--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)
Intro to Tally Tables and Functions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply