Latset ODBC driver

[itsAK]

Hi all - Brand new here and Im finding it most useful so thank you.

I have a few questions.

1) Im running off PROGRESS 9.1d and Im connecting via (ODBC) MERANT 3.60 32-BIT driver..... Is this the latest driver available? If not then what would you suggest?

2) At present Im working with Crystal Reports 10 and connecting via the above driver and Im experiencing performance issues when running large reports. Would it be viable and does anybody currently connect to Progress via SQL server and then run the crystal reports from SQL server?

Either way it looks as though we are going to go the way of SQL server 2005 so I would like to hear you guys input on the above i.e. what the best driver to use etc

Thanks for your time

Al

 

[Casper]

Hi,

Only got a quick answer to the first question:

Im running off PROGRESS 9.1d and Im connecting via (ODBC) MERANT 3.60 32-BIT driver..... Is this the latest driver available? If not then what would you suggest?

If you have client networking installed then you have the driver needed for that version. The driver version also depends on Service Pack level (e.g. 9.1d09 has datadirect 4.10 sql92 driver...)

Regarding your second question:
What kind of performance problem are you experiencing? What makes you sure the driver is the problem and not the query or some other influence?

HTH,

regards,

casper.

 

[itsAK]

Thanks for getting back to me Casper.

The performance issues Im getting is that when I run a certain report or any kind of complex report it is taking hours to run. The report runs fine for our smaller departments but when I run it for our large main department it takes hours.

Im not saying for sure that it’s the ODBC driver it was just a thought!

Here is my query

SELECT Table_A.Field_A, Table_A.Field_B,
Table_A.Field_C,
Table_A.Field_D, Table_A.Field_E ,Table_A.Field_F,
Table_A.Field_G, Table_A.Field_H, Table_B.Field_I, Table_C.Field_J
FROM {oj (PUB.Table_A Table_A
LEFT OUTER JOIN PUB.Table_C Table_C
ON (Table_A.Field_B=Table_C.Field_B) AND (Table_A.Field_G=Table_C.Field_G))
LEFT OUTER JOIN PUB.Table_B Table_B
ON ((Table_A.Field_G=Table_B.Field_G)
AND (Table_C.Field_K=Table_B.Field_K))
AND (Table_C.Field_L=Table_B.Field_L)}
WHERE Table_A.Field_F='R' AND Table_A.Field_G=39
ORDER BY .... etc

I was hoping it would be a simple matter of updating the ODBC driver. But that theory goes as it runs fine for the smaller departments, however as stated the main departments it takes hours and when I say hours Im talking about 5-6 hours!!!!. Can anyone see anything wrong with my query? Please any suggestions welcome.

Will my report work more efficiently if I run it off SQL server? Whats the best way to integrate my Progress DB with SQL server?

Cheers and thanks to all!!!

Al

 

[Lee Curzon]

I don't use SQL or ODBC with Progress, and my general impression is it sucks somewhat, but that may be unfair, I don't know.

There's some links here anyway:

http://www.progresstalk.com/showthread.php?t=97234

 

[Casper]

was hoping it would be a simple matter of updating the ODBC driver. But that theory goes as it runs fine for the smaller departments

That doesn't mean it isn't the odbc driver....

But smaller departments tend to have smaller tables, so it would make sense that the perfomance issue you are experiencing can have to do with the link Lee gave you.

KB P84481
Title: "Performance issue with left-outer join in SQL-92 query."

http://tinyurl.com/kkbyx

(sorry for linking you're link Lee :biggrin: )

What service pack do you have, there are many sql improvements made in SP9 for 9.1D.....

HTH,

Casper.

 

[itsAK]

Thanks guys.... your most kind!!

I will post my findings - If any!!

 

[Kevin Willis]

::: Newest ODBC Driver

DataDirect offers a newer 32-bit ODBC driver for the Progress RDBMS that you may consider trying out - http://www.datadirect.com

Perhaps your query can be simplified somewhat as well - not sure about that, but I know I've run into performance issues like this and
they where (at times) solved by re-writting the SQL query.

Kevin Willis
klwillis.dsl@verizon.net

 

[itsAK]

Re: ::: Newest ODBC Driver

Hi guys....

Im still working on this. It looks like its the LEFT OUTER JOIN issue. When I run the same query using INNER JOIN it runs in roughly 10 mins however obviously it does not return all the data of which I require.....

So thanks to links guys (LEE & Casper) I saw someone had the same problem and that they ran a script to update the stats of the table and that this enabled them to run an efficient query using LEFT OUTER JOINS however.....!!

We currently use out ODBC connection to pull data from progress using SQL so my ODBC setting is that of read only - can someone please send me the steps to connect to progress via ODBC with admin rights?? (I know its probably very simple but Ive tried and tried and run out of ideas)!!

Thanks a milion!!
Al

 

[Casper]

You connect to the database with administrator rights with the login and password of the db creator or create users sysprogress with password in _user adn login as sysprogress.

You can also try select * from sysprogress.sysdbauth to look for the creator of the database.

From KB 20143: http://tinyurl.com/o77l5

In the event that at least one user was created but DBA access rights
are not given, this user can create a SYSPROGRESS user via the 4GL
Data Administration Tool to allow DBA access to the database.
Make sure that password is defined to sysprogress user or connection
will fail.
Listing the Database Authorization Table:
Use the following command to list the Database Authorization Table:
select * from sysprogress.sysdbauth
GRANTEE DBA_ACC RES_ACC
-------------------------------- ------- -------
administrator . y y
SYSPROGRESS y y
The administrator user name corresponds to the user who created the
database. The SYSPROGRESS account is used internally by the SQL
engine. The column DBA_ACC corresponds to the DBA access that a user
can have and RES_ACC corresponds to the resource access, that is,
permission to create objects in the database. If a DBA user is
revoked from any of these permissions, the user name remains in the
sysprogress.sysdbauth table, however, the permission is displayed as
blank.

HTH,

Casper

 

[itsAK]

Thanks again Lee and Casper.....

I created a DBA account ect and ran the above script to update the stats to allow for the LEFT OUTER JOIN to run effieciently.

update table statistics for pub.table_Name
update index statistics for pub.tablex_Name

I even ran it to update all tables in the s

update statistics
update index statistics

All scripts ran and after a few minutes (just when I thought it were doing something) it returned: a '0' for updated objects? So no records were updated or no records were required to be updated.

Is this common or Im I doing something wrong? This would be the first time that any of the tables would have been updated in this way so I would have expected some kind of results??

Any thoughts and thanks again for all input!

 

[Casper]

If you do:

select * from sysprogress.systblstat;

or

select * from sysprogress.sysidxstat

then you will see the statistics are updated....

Regards,

casper.

 

[itsAK]

Hi guys Im back working on this again. Close to tears at this point!

All three tables stats have been updated.
All indexe stats for each table have been updated.

I ran the following scripts as posted by Casper

select * from sysprogress.systblstat (table)

select * from sysprogress.sysidxstat (index)

For this query I can see all 3 tables have indeed been updated.

I ran the same report but still major performance issues (it just wont run)

I have checked my code (JOINS etc) but they apear fine (although Im open to suggestions).

It is worth noting we run off PROGRESS V9.D06 and connect via MERANT 3.60 32-BIT Progress SQL92 v9.1D

Any ideas guys?.... Im running out fast!

Cheers,
Al​

 

[Casper]

Sure about the right joins and sorts? Are there proper indexes on the table to perform the query?
How does the 4GL version of this query run?
Did you monitor the query with promon?

Casper

 

[itsAK]

Hi,

Yes the LEFT OUTER JOIN's are nessasry. And all fields in the query are indexed.

I have not monitored it with Promon no. And im running this query via Crystal Reports (command) so it does not support 4GL.

So bit it the above can u think of anythink Im doing wrong?

 

[itsAK]

Just a thought (back to my orriginal gut feeling) but could it be the ODBC driver??

Im running PROGRESS V9.D06
Connecting ODBC: MERANT 3.60 32-BIT Progress SQL92 v9.1D

Gentlemen I eagerly await your answers!

 

[Casper]

Well, many performance and sql improvements where made between sp6 and sp9 of version 9.1D. So maybe if you have a good testing environment it''s worth a try.

We have some issues with sp6 and are testing sp9, and first tests show huge performance increase on some sql queries.

datadirect 4.10 32-bit progress v9.1D driver comes with sp9.

Casper

 

[itsAK]

Quick update:

I have enquired about setting up SP9 on our test environment (a lot of red tape involved) so if it is thought that it will increase SQL92 performance then it will certainly be interesting to see if it helps with this problematic report

In the meantime I have started to re-write my SQL query so as to minimize the LEFT OUTER JOIN whererver possible.

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT 1 FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
UNION ALL
SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
INNER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39

Im getting a few syntax errors so Im currently working my way though but I thought it be worthwhile to see if anyone can see any PROGRESS SQL92 issues with the above query.

In terms of indexed fields I ensured all the fields involed in the JOIN where indexed but it looks like the field WHERE Table_One.Field_F='R' is not indexed so I will work on that and that should help with the performance also.

Thanks for your help so far guys!
Al

 

[itsAK]

Hi guys,


After reviewing the code my script still wont execute due to syntax errors.

Now taking it that I wrote the script based on my SQL / ORACLE experience Can you PROGRESS guys see anything within the SQL query that wont execute or is compatible within SQL92 PROGRESS DB?

Thanks again

 

[BCM]

itAK -
I successfully obtain data from a Progress database via SQL everyday. One thing I can tell you is that anything other than the most simple SQL query will perform poorly when sent to Progress via ODBC. In fact, simply adding functions to the selected columns (greatest, least, length, substring, cast, etc.) will slow down a simple query.

1) Select the data you want from each table.
Use a single select for each table.
Write the returned data to local temp tables.

2) Do not reference unindexed columns in the where clause...
they can be filtered locally.

3) Perform the joining, complex logic, and data transformation
locally using the temp tables.

I guarantee that the approach outlined, above, will improve the performance. This is most notable when the Progress tables contain over 100,000 records.

 

[itsAK]

Hi and thanks BCM,

I was hoping not to go down that road as the plan was to run this query via Crystal Reports ( which does cant access temp tables)...

However from the looks of things and from what your saying its not looking good!