Solved

Creating Index on a view based on OpenRowSet

Posted on 2001-06-12
5
652 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 142

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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now