SQL Server OpenQuery takes forever to run using linked server to a Progress Database

[mramey64]

I have read many posts here about Updating Statistics on the Progress Database but what can I do if I do NOT have permissions to do anything BUT read tables and import data? I am running a query...

SELECT
* FROM OPENQUERY([HAGEN], 'SELECT "Job-ID", "Entry-Number", "Sub-Job-ID", "Form-ID", "Employee-ID", "Batch-Number", "Work-Type-ID", "Work-Center-ID", "Work-Center-Complete", "Activity-ID", "Entry-Type", "Date-Change", "Date-Actual", "Time-Actual", "Start-Status", "Start-Time", "Stop-Time", "Update-By", "Update-Date", "Update-Time", "Task-Complete"
FROM PUB.ShopFloorEntry
WHERE "Job-ID" = ''28999''')
The Progress database was created by an outside vendor and I cannot do anything but import data to my SQL Server Database and I do so using a linked Server connected by ODBC.

So far I can't even count the records on this Progress table. It's runs forever. I'm thinking there are a couple of million records and I realize its probably having to scan all the records. Is there a way to add an index (temporary) to my query so I can complete this query?

Thanks!
Mike

 

[TomBascom]

Re: SQL Server OpenQuery takes forever to run using linked server to a Progress Datab

Sharpen your resume.

 

[mramey64]

Re: SQL Server OpenQuery takes forever to run using linked server to a Progress Datab

Really Tom? Really? That's the best you can do? That's the sum total of your wit? Tom, come on you can do better than that? Sharpen my resume? I'm going to use that one Tom. I am better for reading it. I marvel at your intelligent response Tom Bascom. I didn't realize you're on here to judge and get personal as well as offer advice. What a man you are Tom Bascom.

 

[TomBascom]

Re: SQL Server OpenQuery takes forever to run using linked server to a Progress Datab

Yes, really. If you aren't going to be allowed to do the simple, obvious and standard things to perform your job then your best course of action is to start searching for a new one.

Your issue isn't technical. Your issue is that someone has given you impossible conditions.

There is nothing personal or judgmental about that. Frankly I think it is pretty darned good advice. You can take it or not, that's up to you.

 

[Cringer]

Re: SQL Server OpenQuery takes forever to run using linked server to a Progress Datab

I agree with Tom really. You're between a rock and a hard place there. There's not really anything you can do other than sit and wait. Whilst you're waiting you could either sharpen your resume , or you could get in contact with the vendor and have a go at them for giving you nothing to work with...

 

[TomBascom]

Re: SQL Server OpenQuery takes forever to run using linked server to a Progress Datab

Assuming that there is an appropriate index on "Job-Id", something like this may, or may not help:

OpenEdge 10 supports index hints for SQL. For each table in the FROM clause of a SELECT query, the user can optionally specify an index. The syntax for specifying an index hint is as follows:

SELECT column_list
FROM table_name [ [ AS ] table_alias ]
[ WITH (INDEX ( index_val ))] , ...
WHERE ...

For example:

SELECT *
FROM pub.Customer WITH (INDEX (CountryPost))
WHERE Country = 'USA';

When the OpenEdge SQL Engine optimizes a query, it will generate an internal "candidate list" of indexes that can be chosen to execute the query. If the index hint provided by the client is on the candidate list, then the optimizer will use it. Otherwise, the hint will be ignored. The optimizer will still try to honor index hints for other tables.