Vantage 8.03/Progress 10.1B ODBC settings and optimization

Yahoo Archive
1
Some observations - useful i hope, accurate i also hope:

BAQ/Crystal method (which Epicor mostly uses):
1. BAQ has Progress generate an XML as a file system object, maybe filtered or maybe not.
2. Crystal finds & opens that XML file, and maybe apples additional other filters or maybe not.

SQL/Crystal method:
1. Use a "where..." clause in your SQL to get a concise record set, meaning "filtered at the git-go".
2. Hand that recordset directly to Crystal.

Problems i find in the BAQ/Crystal method:
A. Dealing directly with the filesystem is slow.
B. Any filtering left to Crystal means too much data being delivered.
C. Having two realms of filtering just weird for users.
D. Modifying Crystal rpt's is a nightmare when XML structure changes.

The SQL/Crystal method is screamingly fast, and a breeze to modify.
2
I am finding using Crystal with ODBC to be very slow in returning even what I think are simple queries. I am looking to find out what everyone has their ODBC connection properties set to, specifically the Advanced tab that has the "Default Isolation Level", and "Fetch Array Size" variables. What are the best settings for these. My current settings are Default Isolation Level = "READ UNCOMMITTED" and Fetch Array Size=10000. "Enable Timestampe with Time Zone"=Checked. I am querying the LaborDtl table (I know it's a big table), but it's taking several minutes to return just basic information for a few employees/jobs when I run a report. Anything I can do to help this regarding ODBC? Thanks in advance.

Mike..
3
Are you sure it's actually only pulling the info you want? If you config
Crystal incorrectly, it will pull the whole table before it filters it.
I don't go through ODBC if I can avoid it, so this isn't very fresh in my
mind. That said, if this sounds like what's going on let me know and I'll
look up some more info for you.

As a quick test, tell the report to display all records and see if it takes
substantially longer.

On Mon, Jun 4, 2012 at 5:03 PM, flexial_ma <mike.abell@...>wrote:

> **
>
>
> I am finding using Crystal with ODBC to be very slow in returning even
> what I think are simple queries. I am looking to find out what everyone has
> their ODBC connection properties set to, specifically the Advanced tab that
> has the "Default Isolation Level", and "Fetch Array Size" variables. What
> are the best settings for these. My current settings are Default Isolation
> Level = "READ UNCOMMITTED" and Fetch Array Size=10000. "Enable Timestampe
> with Time Zone"=Checked. I am querying the LaborDtl table (I know it's a
> big table), but it's taking several minutes to return just basic
> information for a few employees/jobs when I run a report. Anything I can do
> to help this regarding ODBC? Thanks in advance.
>
> Mike..
>
>
>



--
*John Driggers*
*High End Dev, System Design, Profit Drinking*
*
**:: 904.404.9233*
*:: waffqle@...*
*:: NO FAXES*

*

*


[Non-text portions of this message have been removed]
4
I usually set my fetch array size to 400.
I think I tried boosting it up to a big number like yours & had issues.

--- In vantage@yahoogroups.com, John Driggers <waffqle@...> wrote:
>
> Are you sure it's actually only pulling the info you want? If you config
> Crystal incorrectly, it will pull the whole table before it filters it.
> I don't go through ODBC if I can avoid it, so this isn't very fresh in my
> mind. That said, if this sounds like what's going on let me know and I'll
> look up some more info for you.
>
> As a quick test, tell the report to display all records and see if it takes
> substantially longer.
>
> On Mon, Jun 4, 2012 at 5:03 PM, flexial_ma <mike.abell@...>wrote:
>
> > **
> >
> >
> > I am finding using Crystal with ODBC to be very slow in returning even
> > what I think are simple queries. I am looking to find out what everyone has
> > their ODBC connection properties set to, specifically the Advanced tab that
> > has the "Default Isolation Level", and "Fetch Array Size" variables. What
> > are the best settings for these. My current settings are Default Isolation
> > Level = "READ UNCOMMITTED" and Fetch Array Size=10000. "Enable Timestampe
> > with Time Zone"=Checked. I am querying the LaborDtl table (I know it's a
> > big table), but it's taking several minutes to return just basic
> > information for a few employees/jobs when I run a report. Anything I can do
> > to help this regarding ODBC? Thanks in advance.
> >
> > Mike..
> >
> >
> >
>
>
>
> --
> *John Driggers*
> *High End Dev, System Design, Profit Drinking*
> *
> **:: 904.404.9233*
> *:: waffqle@...*
> *:: NO FAXES*
>
> *
>
> *
>
>
> [Non-text portions of this message have been removed]
>
5
Define "simple query". If you're doing any filtering, for example:



SELECT * FROM LABORDTL WHERE PAYROLLDATE >= TODAY-90



Regardless of the fact if you have your FAR set to 400, ODBC will bring back
ALL 72,581 records from your LaborDtl table and then apply the WHERE clause
above.



I know the folks that know me on here will laugh, but again, this is where
SQL on the back-end greatly helps (vs Progress). When this occurs in a SQL
world, you can use SQL Expressions within Crystal to properly limit the
dataset being pulled across the wire.



I'm assuming you're Progress, so Install SQuirreL (search the group for
install instructions; if you cannot find, email me directly and I will fwd
them to you). Install Squirrel and test your theory about ODBC and perhaps
you can tune your query.



This is another reason why BAQs aren't so bad. They, too, also limit the
dataset at the server and only deliver what you asked for. With ODBC, it's
like ordering a Pepperoni pizza from Dominos and when Dominos shows up at
your door they have one of every pizza they sell and then you choose your
Pepperoni Pizza. BAQs and SQL Expressions is when *only* the pepperoni
pizza is delivered.







From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
flexial_ma
Sent: Monday, June 04, 2012 5:04 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Vantage 8.03/Progress 10.1B ODBC settings and
optimization





I am finding using Crystal with ODBC to be very slow in returning even what
I think are simple queries. I am looking to find out what everyone has their
ODBC connection properties set to, specifically the Advanced tab that has
the "Default Isolation Level", and "Fetch Array Size" variables. What are
the best settings for these. My current settings are Default Isolation Level
= "READ UNCOMMITTED" and Fetch Array Size=10000. "Enable Timestampe with
Time Zone"=Checked. I am querying the LaborDtl table (I know it's a big
table), but it's taking several minutes to return just basic information for
a few employees/jobs when I run a report. Anything I can do to help this
regarding ODBC? Thanks in advance.

Mike..



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2178 / Virus Database: 2425/5044 - Release Date: 06/04/12



[Non-text portions of this message have been removed]
6
I think that if you are using ODBC then you are using SQL even if the database is in Progress. In the developer just select Database / show sql query to see the calls made on the database.
Reports / Performance information in the crystal developer will show the number of records and timing for pulling the data.
Â
What SQL Expressions within Crystal can you do with a SQL database that you can not do with a progress database.
I would be very interested if I could do multiple queries inside a report that are based on the results from the previous query. I have done mixed BAQ and ODBC to combine quieries.
Â
For complicated reports I use Excel and SQL queries and it is so very much faster than Crystal ever could be.
Â
For example a report with item sales and purchases hits many tables. With Kits and manufactured items it gets where crystal has a hard time getting it all on one report.


________________________________
From: Vic Drecchio <vic.drecchio@...>
To: vantage@yahoogroups.com
Sent: Tuesday, June 5, 2012 7:30 AM
Subject: RE: [Vantage] Vantage 8.03/Progress 10.1B ODBC settings and optimization


Â
Define "simple query". If you're doing any filtering, for example:

SELECT * FROM LABORDTL WHERE PAYROLLDATE >= TODAY-90

Regardless of the fact if you have your FAR set to 400, ODBC will bring back
ALL 72,581 records from your LaborDtl table and then apply the WHERE clause
above.

I know the folks that know me on here will laugh, but again, this is where
SQL on the back-end greatly helps (vs Progress). When this occurs in a SQL
world, you can use SQL Expressions within Crystal to properly limit the
dataset being pulled across the wire.

I'm assuming you're Progress, so Install SQuirreL (search the group for
install instructions; if you cannot find, email me directly and I will fwd
them to you). Install Squirrel and test your theory about ODBC and perhaps
you can tune your query.

This is another reason why BAQs aren't so bad. They, too, also limit the
dataset at the server and only deliver what you asked for. With ODBC, it's
like ordering a Pepperoni pizza from Dominos and when Dominos shows up at
your door they have one of every pizza they sell and then you choose your
Pepperoni Pizza. BAQs and SQL Expressions is when *only* the pepperoni
pizza is delivered.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
flexial_ma
Sent: Monday, June 04, 2012 5:04 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Vantage 8.03/Progress 10.1B ODBC settings and
optimization

I am finding using Crystal with ODBC to be very slow in returning even what
I think are simple queries. I am looking to find out what everyone has their
ODBC connection properties set to, specifically the Advanced tab that has
the "Default Isolation Level", and "Fetch Array Size" variables. What are
the best settings for these. My current settings are Default Isolation Level
= "READ UNCOMMITTED" and Fetch Array Size=10000. "Enable Timestampe with
Time Zone"=Checked. I am querying the LaborDtl table (I know it's a big
table), but it's taking several minutes to return just basic information for
a few employees/jobs when I run a report. Anything I can do to help this
regarding ODBC? Thanks in advance.

Mike..

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2178 / Virus Database: 2425/5044 - Release Date: 06/04/12

[Non-text portions of this message have been removed]




[Non-text portions of this message have been removed]