SQL communicating with Progress

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-16 : 16:01:11

Does Progress db has necessary index?

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!

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

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"

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

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources