?
Solved

Creating Index on a view based on OpenRowSet

Posted on 2001-06-12
5
Medium Priority
?
676 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 800 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

752 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