• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

Creating Index on a view based on OpenRowSet

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
stino
Asked:
stino
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
stinoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>>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
 
stinoAuthor Commented:
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
 
stinoAuthor Commented:
Thanks for the assistance.  I couldn't get it implemented but the knowledge you've shared has been helpful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now