Solved

Creating Index on a view based on OpenRowSet

Posted on 2001-06-12
5
661 Views
Last Modified: 2012-06-27
Here's my scenario:

I have an Informix DB running my Legacy System.  Nightly, I use DTS packages to transfer some of the Info from Informix to SQL Server 7.0.  I recently found out that I can link the Informix DB to the SQL Server DB and see the objects.  I got that to work then I created views in the SQL Server DB based on the tables in the Informix DB.  The problem then was that queries ran against the views took forever - solution - create index on the views.  SQL Server 7 doesn't allow that but 2000 does so I upgraded.  Now I it's telling me that "'Openrowset' is not allowed on schema-bound objects".  

A typical view looked something like this:

Create view MyView
AS
SELECT DerivedTable1.*
FROM OPENQUERY(Linked_Database, 'SELECT * FROM MyTable')
    DerivedTable1


Ultimately, I want SQL Server to have a live link with the Informix DB so that I can get rid of those nightly transfers and then create views based on the tables in the linked Informix DB and create indices on these views.
0
Comment
Question by:stino
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 6182880
This is a quite complex thing, and should be carefully planned.
First, what is the traffic on the base table(s) in the Informix DB itself by the legacy application?
How much traffic will be there by your SQL view(s) on them?
How much influence can you have on the Informix database?
What degree of real-time do you need for the data?

The thing is, that you might get something working, but not useful regarding all the conditions and the results.
If you issue to many queries using this linked server technique, you will increase network load, so that technically it can work, but in pratice delays will be inacceptable for the users.

Example:
  The legacy application is not issuing much changes on the base table, and you could build a system in the Informix database that extracts the changes on the Informix side. You could read the changes on a timely basis, and apply these changes (even during the whole day) to a SQL table which is a copy of the Informix database. (to ensure consistency over longer periods, you could implement a full-table transfer every week/month or so).
The transfer itself would only take seconds, but you would still have near-to-real-time...

Cheers


0
 
LVL 1

Author Comment

by:stino
ID: 6183284
First, what is the traffic on the base table(s) in the Informix DB itself by the legacy application?

Answer: Certain tables are very heavily trafficed but the ones that are going to be used are not very heavily trafficed.

How much traffic will be there by your SQL view(s) on them?

Answer: Right now, very minimal (seldom more that 5 users at a time, 10-15 max)

How much influence can you have on the Informix database?

Answer : Don't understand this question


What degree of real-time do you need for the data?

Answer: It's for our corporate Intranet and Internet sites. We have a fairly high degree of desire to have the data real time.

The thing is, that you might get something working, but not useful regarding all the conditions and
the results.
If you issue to many queries using this linked server technique, you will increase network load, so
that technically it can work, but in pratice delays will be inacceptable for the users.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6184642
>>How much influence can you have on the Informix database?
Can you add (or let add) tables/procedures/triggers on the Informix database? This would be necessary to implement this kind of replication...
Another option would be to implement the (indexed) views directly in the informix database, and to have the views of SQL go directly to those views (which might still be temporary tables or so)



0
 
LVL 1

Author Comment

by:stino
ID: 6187222
Yes, I can add tables/procedures/triggers on the Informix database.

But to go back to the original question.  Is there anyway to work it the way I had initially wanted.  If not, I'll have to go back to the drawing board all over again.  
0
 
LVL 1

Author Comment

by:stino
ID: 6937760
Thanks for the assistance.  I couldn't get it implemented but the knowledge you've shared has been helpful.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question