Method of simulating views on a SQL Server with read-only access?

Question

I'm trying to do quite a lot of querying against a Microsoft SQL Server that I only have read only access to. My queries are needing to work with the data in very different structure from how the DB architecture is. Since I only have read access, I can't create views, so I'm looking for a solution. What I'm currently doing is using complex queries to return the results as I need them, but this is 4-5 table joins with subqueries. It is rediculously slow and resource intensive. I can see two solutions, but would love to hear about anything I might have missed:

• Use some sort of "proxy" that caches the data, and creates views around it. This would need some sort of method to determine the dirtiness of the data. (is there something like this?)
• run my own SQL server, and mirror the data from the source SQL server every X minutes, and then load views on my SQL server.

Any other ideas? or recommendations on these ideas?

Thanks!

Answer 1

Here are some options for you:

Replication

Set up replication to move the data to your own SQL Server and create any views you need over there. An administrator has to set this up. If you need to see the data as it changes, use Transactional Replication. If not, you can do snapshots.

Read more here: http://technet.microsoft.com/en-us/library/ms151198.aspx

New DB on same instance

Get a new database MyDB on the same server as your ProductionDB with WRITE access for you. Create your views there.

Your view creation can look like this:

USE MyDB
GO
CREATE VIEW DBO.MyView 
AS

SELECT Column1, Column2, Column3, Column4
FROM ProductionDB.dbo.TableName1 t1
    INNER JOIN ProductionDB.dbo.TableName2 t2
        ON t1.ColX = T2.ColX

GO

Same Instance, not same Server + Difference instance: I would suggest to create the MyDB on the same instance of SQL Server as ProductionDB rather than install a new instance. Multiple instances of SQL Server on a single machine is much more expensive in terms of resources than a new DB on the same instance.

Standard Reusable Views

Create a set of standardized views and ask the administrators to put them on the read only server for you and reuse those views in queries

Answer 2

You could ask DBA to create a schema for people like you "Contractors" and allow you to create objects inside that schema only.

Answer 3

you can also use a CTE which can act like a view.

I will go for that if Raj More's #2 suggestion does not work for you...

WITH myusers (userid, username, password)
AS
(
   -- this is where the definition of view would go.
select   userid, username, password from Users 

)


select * from myusers

Answer 4

I would look at the query plan in Management studio and see if you can tell why its not performing well. Maybe you need to rewrite your query. You might also make use of table level variables as temporary tables to store intermediate results if that helps. Just make sure you're not storing a lot of records in them. You can run multiple statements in a batch like this:

DECLARE @tempTable TABLE
(
    col1 int,
    col2 varchar(250)
)

INSERT INTO @tempTable (col1, col2)
SELECT a, b
FROM SomeTable
WHERE a < 100 ... /* some complex query */

SELECT *
FROM OtherTable o
INNER JOIN @tempTable T
ON o.col1 = T.col1
WHERE ...

Answer 5

If you can create a new database on that server you can create the views in the new database. The views can access the data using a three part name. E.g. select * from OtherDB.dbo.Table.

If you have access to another SQL server, the DBA can created a "Linked Server". You can then create views that access the data using a four part name. E.g. select * from OtherServer.OtherDB.dbo.Table

In either case, the data is always "live", so no need to worry about dirty data.

The views will bring you cleaner code and a single location to make changes, and few milliseconds of performance benefit from cached execution plans. However, there shouldn't be in great performance leaps. You mention caching, but as far as I know, the server does not do any particular data caching for ordinary, non-indexed views that it wouldn't do for ad-hoc queries.

If you haven't already done so, you may wish to do experiments to see if the views are actually faster--make a copy of the database and add the views there.

Edit: I did a similar experiment today. I had a stored proc on Server1 that was getting data from Server2 via a Linked Server. It was a complex query, joining many tables on both servers. I created a view on Server2 that got all of the data that I needed from that server, and updated the proc (on Server1) so that it used that view (via a Linked Server) and then joined the view to a bunch of tables that were on Server1. It was noticeably faster after the update. The reason seems to be that Server1 was miss-estimating the number of rows that it would get from Server2, and thus building a bad plan. It did better estimating when using a view. It didn't matter if the view was in the same database as the data it was reading, it just had to be on the same server (I only have on instance, so I don't know how instances would have come into play).

This particular scenario would only come into play if you were already using Linked Servers to get the data, so it may not be relevant to the original question, but I thought it was interesting since we're discussing the performance of views.

Answer 6

By using views, your queries would not perform better. You need to tune those queries, and probably some indexes should be made on those tables, to support your queries.

If you cannot get access to the database, in order to create those indexes, you can "cache" the data in a new database you create, and tune your queries in this new one. And of course, you will have to implement some synchronization, to keep the cached data up to date.

This way you won't see the changes made to the original database immediately (there will be a latency), but you can get your queries perform a lot faster, and you can even create those views, if you wish.