SQL to Progress query speed

[LadyReader]

We use both Syteline/Progress and SQL databases. For my asp.Net programs I need to use data that's on Progress. I set up stored procedures in TSQL that access Progress.

The TSQL Select queries take so long to run (45 seconds to 10 minutes) that I've resorted to retrieving all the records I could possibly need, inserting them into a SQL table each night and then accessing that SQL table via .Net using a sproc to get the specific row it needs during the day, realtime.

1) Is it possible to access Progress directly from VB.Net? If so, please show me how.

2) Does anyone have suggestions on how I can speed up these queries? The analogous Progress queries only take seconds. I tried using the TSQL syntax WITH (INDEX(abc))
but it errored with message:
"Cannot specify an index or locking hint for a remote data source"

3) If I write a TSQL sproc that accesses Syteline somewhere along the line I assume it gets converted into the Progress syntax. Where does this happen? Is there any way of affecting the outcome?

Example of 1 query:

SELECT Distinct Item.item,
jobroute.wc,
Sch.[oper-num],
Sch.[run-lbr-hrs],
Item.[lot-size],
Ux.[uf-jroute-wop]
FROM Syteline..pub.[job] as Job
inner join Syteline..pub.[item] as Item on Job.job = Item.job
inner join Syteline..pub.[jobroute] as Jobroute on Item.job = Jobroute.job
inner join Syteline..pub.[jrt-sch] as Sch on Sch.job = Jobroute.job
AND Sch.suffix = Jobroute.suffix
AND Sch.[oper-num] = Jobroute.[oper-num]
inner join Syteline..pub.[ux-jobroute] as Ux on Ux.job = Jobroute.job
AND Ux.suffix = Jobroute.suffix
AND Ux.[oper-num] = Jobroute.[oper-num]
where
(Item.[product-code] like 'FG0%' AND Item.Item LIKE '_____-%')
AND Jobroute.Suffix = 1
AND (Jobroute.wc IN ('30101', '30101S', '30501', '30501S', '30502', '30502S', '30301', '30301S', '31002', '31001'))

order by Item.item, Sch.[oper-num]

Thank you for your help.

 

[harebrain]

Try the OPEN QUERY syntax in T-SQL. It's been 4 years since I had to do this, but I think that was the key. And it blazed for us, so you do have a problem.