Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 SQL communicating with Progress

Author  Topic 

LadyReader
Starting Member

18 Posts

Posted - 2008-05-15 : 15:38:48
Our ERP uses a Progress db but several of our web apps are developed in vb.Net and use a SQL backend. I need to pull some data in from Progress using TSQL queries.

Experimenting a little I find that a simple query, i.e. from 1 Progress table, takes anywhere from 20 seconds to a couple of minutes. However, when I joined on a 2nd table, I had to cancel the query after it ran for over 10 minutes.

SQL server is connected to Progress via a Linked server.

Any advice on improving the query will be appreciated.

I believe the query has to be in the T-SQL syntax; is that right? Where does the translation into Progress syntax occur?


Thank you,
LadyReader

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-15 : 18:28:33
It is impossible for us to help improve a query without seeing it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-16 : 16:01:11
Does Progress db has necessary index?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-05-20 : 05:18:01
If you can get away with it, try mirroring the progress tables on SQL server for your queries. Progress is very procedural (you basically have to specify your selects & joins in the order you want to execute them). If it gets it wrong you are in for a long wait!
Go to Top of Page

LadyReader
Starting Member

18 Posts

Posted - 2008-05-27 : 08:51:54
Thank you all. I have been out of the office, in class, for awhile so I just saw your replies. Yes, the Progress db is indexed. My SQL code, as submitted to Progress, is as follows:


SELECT Item.item, jobroute.wc, Sch.[oper-num], Sch.[run-lbr-hrs], Item.[lot-size], Ux.[uf-jroute-wop]
FROM Syteline..pub.[item] as Item
join Syteline..pub.[jobroute] as Jobroute
on Item.job = Jobroute.job
join Syteline..pub.[jrt-sch] as Sch
on Sch.job = Jobroute.job AND
Sch.suffix = Jobroute.suffix AND
Sch.[oper-num] = Jobroute.[oper-num]
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 substring(Item.Item, 6, 1) = '-'
AND Item.[p-m-t-code] = 'M'
AND substring(Item.[product-code], 1, 3) = 'FG0'
AND Jobroute.Suffix = 1
AND (Jobroute.wc = '30101' or Jobroute.wc = '30101S'
or Jobroute.wc = '30501' or Jobroute.wc = '30501S'
or Jobroute.wc = '30502' or Jobroute.wc = '30502S'
or Jobroute.wc = '30301' or Jobroute.wc = '30301S'
or Jobroute.wc = '31002' or Jobroute.wc = '31001')


Any ideas?
Thanks!

Thank you,
LadyReader
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 09:49:55
The two SUBSTRING functions renders the present indexes over [product-code] and [item] useless.
Try this
SELECT		Item.item,
jobroute.wc,
Sch.[oper-num],
Sch.[run-lbr-hrs],
Item.[lot-size],
Ux.[uf-jroute-wop]
FROM Syteline..pub.[item] as Item
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.Item LIKE '_____-%'
AND Item.[p-m-t-code] = 'M'
AND Item.[product-code] like 'FG0%'
AND Jobroute.Suffix = 1
AND (Jobroute.wc IN ('30101', '30101S', '30501', '30501S', '30502', '30502S', '30301', '30301S', '31002', '31001')


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

LadyReader
Starting Member

18 Posts

Posted - 2008-05-27 : 11:03:10
Thanks for the suggestions! I ran your query 4 times and mine 4 times, alternating between the two; both returned 7469 rows today:

Yours (in minutes and seconds):
Trial
1) 13:50
2) 1:47
3) 2:00
4) 1:57

Mine:
Trial
1) 9:23
2) 2:18
3) 2:25
4) 2:18

I would say that it looks like yours runs a little faster, but not significantly so. Not fast enough to run this real-time during web app execution so I will stick with doing the retrieval once per day, at night, inserting into a SQL table for real-time retrieval during the day, unless you have some more ideas?

Thanks again.

Thank you,
LadyReader
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 12:30:39
The OR part is very resource consuming.
You can verify this by viewing the execution plan.

Try this.

DECLARE @wc TABLE (wc VARCHAR(6) PRIMARY KEY CLUSTERED)

INSERT @wc
SELECT '30101' UNION ALL
SELECT '30101S' UNION ALL
SELECT '30501' UNION ALL
SELECT '30501S' UNION ALL
SELECT '30502' UNION ALL
SELECT '30502S' UNION ALL
SELECT '30301' UNION ALL
SELECT '30301S' UNION ALL
SELECT '31002' UNION ALL
SELECT '31001'

SELECT		Item.item,
jobroute.wc,
Sch.[oper-num],
Sch.[run-lbr-hrs],
Item.[lot-size],
Ux.[uf-jroute-wop]
FROM Syteline..pub.[item] as Item
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]
inner join @wc as y on y.wc = Jobroute.wc
where Item.Item LIKE '_____-%'
AND Item.[p-m-t-code] = 'M'
AND Item.[product-code] like 'FG0%'
AND Jobroute.Suffix = 1


or this one

SELECT		Item.item,
jobroute.wc,
Sch.[oper-num],
Sch.[run-lbr-hrs],
Item.[lot-size],
Ux.[uf-jroute-wop]
FROM Syteline..pub.[item] as Item
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.Item LIKE '_____-%'
AND Item.[p-m-t-code] = 'M'
AND Item.[product-code] like 'FG0%'
AND Jobroute.Suffix = 1
AND EXISTS(SELECT * FROM @wc as y WHERE y.wc = Jobroute.wc)
Also make sure all columns used in WHERE and JOIN binding are indexed.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -