Solved

Creating Index on a view based on OpenRowSet

Posted on 2001-06-12
5
673 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

724 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