Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database. We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code similar to the following:

CREATE VIEW accounts AS SELECT * FROM OPENQUERY(myLinkedServerName,
'SELECT * FROM PUB.accounts')

CREATE VIEW clients AS SELECT * FROM OPENQUERY(myLinkedServerName,
'SELECT * FROM PUB.clients')

For some reason the queries seem to bring back the whole table and then filter on the SQL side instead of executing the query on the Progress side. I'm a dev and not a DBA so I'm hoping the experts can hook me up with a solution or at least an explanation as to why.

Thanks.

share|improve this question
    
Which provider are you using with the linked server? –  Jon Seigel Sep 19 '12 at 1:06
1  
You may want to check dba.stackexchange.com/questions/6860/… for a good breakdown of why OPENQUERY runs slowly. –  Max Vernon Sep 19 '12 at 4:32
1  
Also, in general you may want to avoid using SELECT * in any query. If the table in question contains more fields than you are actually using, you will be transporting the data in those fields across the network needlessly. –  Max Vernon Sep 19 '12 at 4:33
2  
What filtering are you referring to? You have no filtering applied here. –  Mr.Brownstone Sep 19 '12 at 6:56

2 Answers 2

in your query above, every time some one views your view, an OPENQUERY is executed to your server

why don't you run a job that transfers the data from your progress server to your sql server and create a view pointing to a table in your server locally.

that will be much faster

CREATE PROCEDURE [dbo].[copydatafromProgress]
AS
BEGIN

insert into tbl_accounts
SELECT * FROM OPENQUERY(myLinkedServerName,'SELECT * FROM PUB.accounts')

insert into tbl_clients
SELECT * FROM OPENQUERY(myLinkedServerName,'SELECT * FROM PUB.clients')

End



CREATE VIEW accounts AS SELECT * FROM tbl_accounts
CREATE VIEW clients AS SELECT * FROM tbl_clients

after that create a job that runs the stored procedure once a day or depending on the schedule you need.

share|improve this answer
    
So where's the improvement over the previous statement? Instead of having real time data you'll set for something else. It's a tradeoff that should be presented a bit clearer. If you followed Max's comment (a link to a previous answer) you'll see that if there's no filtering done on the linked server, there's no chance the optimizer will be able to do a good job on the current server. So the solution is not to carry all data, but do the filtering remote. –  Marian Mar 14 '13 at 9:19
    
OPENQUERY executes the sql code on the remote server, i dont see much that you can do about it, other then optimizing the script with a where clause or replacing the * with column names or configuring the remote server for better performance –  AmmarR Mar 14 '13 at 9:24
1  
Well, my opinion is that this is the explanation that he actually needs :-). As long as everything is sent through the network..things can't be done much faster. With the obvious exception of caching data locally, but this comes with some tradeoffs. If you get few million records every time, you can't expect anything to work smoothly. –  Marian Mar 14 '13 at 9:35
up vote 0 down vote accepted

What we did was dump the views all together and added sprocs that perform the openquery with the limited set of return values and a restricted where clause. We also worked with progress and there was some optimizations they recommended on their side as well. Performance still isn't great but it's much better than it was. We are migrating from a Progress backend to a SQL and this was a step to enable Report Designer which it accomplished. We aren't going to start delving into performance much more until after the data is migrated to the SQL Server environment completely.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.