Openquery from SQL to Progress 9.1e execution times

[sqljunkie]

Hello,

I have seen several posts about but nothing has quite given the answer that I have been looking for so here I am starting my own thread.

We currently have a database on SQL Server 2005 which is used as our warehouse for a database on a HP-UX machine running progress 9.1e.

If i run an openquery I am getting very poor execution times when returning results.

An example query follows as:

SELECT * FROM OPENQUERY (<server>, 'Select Post_id from pub.post')

It takes in excess of 20 minutes to return 679,000 rows which seems to be extremely slow.

There is an index on that column in the progress database from what I can see called Post_id. Is there a way I can get the query to utilise the indexes so that I can reduce the time it takes to return the results? if so could example syntax please be supplied?

My knowledge of Progress is extremely limited as the product is supplied by a 3rd party. Could the slow execution times be down to certain maintenance not being run?

 

[Cringer]

For starters you should look into upgrading your Progress version as 9.1e is ancient, obsolete and unsupported. In addition the SQL tools you have available are far inferior to later versions.
I'm afraid I can't b of more assistance than that as my knowledge is somewhat sketchy, but I'm sure a helpful forumite will be along shortly to tell you what to do.

 

[Cringer]

One thought is, have you run UPDATE STATISTICS? Search the forum for instructions how if you don't know.

 

[sqljunkie]

Thanks very much, we are looking to go to 10.2b I think over the next 6 months but I was concerned that the queries may still run slow even on a new version if something else was not right.

I will look into the UPDATE STATISTICS as I am not sure that is being run at all.

Thanks again.

 

[TomBascom]

The chances are very good that UPDATE STATISTICS has not been run.

The SQL-92 engine needs those statistics in order to optimize queries -- without them you often get very poor performance.

You want to create a sql script like this for every table in the schema:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB."tableName";
commit work;

Then execute it periodically (weekly is good).

It can be fairly intense from an IO perspective so it is usually run off hours.

 

[sqljunkie]

Thanks very much tom! should keep me busy for a while.

 

[TomBascom]

You may also be suffering from plain old inadequate tuning

 

[tamhas]

Not to mention that SQL performance has undergone some major changes since 9.1.