Looking for heavy ODBC user 6.10

Randy,

Thanks for the chops, glad it worked out for you. ODBC is an incredibly
useful tool to work around the limitations of the Vantage program.

On the flip side, you need to pay attention to ODBC security, ODBC's
limitations, have some programming skills and know how to write rational
queries (don't trust the data returned by every query - know your tables
and verify against canned reports!).

have fun,
john




----- Original message -----
From: "Randy" <rweber@...>
To: vantage@yahoogroups.com
Date: Mon, 15 Sep 2008 14:50:59 -0000
Subject: [Vantage] Re: Looking for heavy ODBC user 6.10

I made some changes and have had great results. Thank you all for
your replies. Below are some comments:

John Sage's suggestions did the trick - thanks John.

The join from Company to Company in the query design was most likely
the thing that produced the best results, as well as filtering one
company.
Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber
Randy,



I'm in the same boat as you, but have found a work around to this. Feel
free to give me a call and I'll explain what I've been doing.



Thanks,

Travis Late

ERP Project Manager

M-B Companies, Inc

1200 Park Street

Chilton, WI 53014

Phone: 920-898-1560 Ext.152

Cell: 920-960-0062

Email: tlate@...







From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Randy
Sent: Thursday, September 04, 2008 8:44 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Looking for heavy ODBC user 6.10



Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber





[Non-text portions of this message have been removed]
Please share - I would like to know the work aroudn too....

--- In vantage@yahoogroups.com, "Late, Travis" <tlate@...> wrote:
>
> Randy,
>
>
>
> I'm in the same boat as you, but have found a work around to this.
Feel
> free to give me a call and I'll explain what I've been doing.
>
>
>
> Thanks,
>
> Travis Late
>
> ERP Project Manager
>
> M-B Companies, Inc
>
> 1200 Park Street
>
> Chilton, WI 53014
>
> Phone: 920-898-1560 Ext.152
>
> Cell: 920-960-0062
>
> Email: tlate@...
>
>
>
>
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of Randy
> Sent: Thursday, September 04, 2008 8:44 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Looking for heavy ODBC user 6.10
>
>
>
> Hello,
>
> We use ODBC often in both MS Access and Excel to view live data
from
> the Progress database. When it works, it works great. But, it
seems
> to be causing problems from time to time. At times, it is very
> tasking on the system (we think).
>
> We often have people get booted out of Vantage and extremely slow
> performance. This seems to be happening when others are using ODBC
to
> get at lots of data using complex multi-table queries. I can't
tell
> for sure if it's the ODBC causing the problem because I don't know
of
> any tools to evaluate what is going on.
>
> Sometimes the ODBC report just hangs and hangs. The person asks
for
> help and the same report runs in seconds without changing
anything.
> I'm starting to think that due to the Read-Uncommitted setting,
that
> the ODBC will just hang and hang while someone else might be in
the
> midst of order entry or something. Is this possible?
>
> Are there tools like that - that let me know who or what is
tasking
> the database?
>
> Should we be running ODBC reports at night?
>
> I'd like to find someone who also uses the ODBC connection
> extensively and preferrably speak to him or her rather than type a
> lot here. I have a number of questions about performance and how
they
> use ODBC which would be much easier over the phone.
>
> Thanks in advance,
>
> Randy Weber
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Your complex multi-table queries may be too complex. I've had to break
these down into multiple step queries with one query looking at only 3
to 6 tables. This is true when accessing any SQL database. A query
with many tables will just hang. Although if it hangs it usually will
not run the next time in a few seconds. There can be many other reasons
for performance issues.



I've been doing this with various databases with Access for over 15
years. You can give me a call if you want.



Lon A. Wiksell

R.O.M. Corp.

916-318-8000



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Randy
Sent: Thursday, September 04, 2008 8:44 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Looking for heavy ODBC user 6.10



Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber





[Non-text portions of this message have been removed]
This could be a valuable contribution the group, please post results /
findings to the group. Also, take a look at your ODBC set up on your server
to be sure that you have enough connection set for your data base. Every
ODBC process you run establishes a connection to the database, depending on
how you've configured your query those connection could be held open even
though your query has finished pulling data. If many people are running
queries that stay open you eventually run out of connections allowed. That
being the case, the query could time out waiting for a chance at the
database.

Shirley Graver
Moderator

_____

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
rfc822 Compliance issue From: added by system POTENTIAL SPAM
Sent: Thursday, September 04, 2008 11:47 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Looking for heavy ODBC user 6.10



Your complex multi-table queries may be too complex. I've had to break
these down into multiple step queries with one query looking at only 3
to 6 tables. This is true when accessing any SQL database. A query
with many tables will just hang. Although if it hangs it usually will
not run the next time in a few seconds. There can be many other reasons
for performance issues.

I've been doing this with various databases with Access for over 15
years. You can give me a call if you want.

Lon A. Wiksell

R.O.M. Corp.

916-318-8000

________________________________

From: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
[mailto:vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com] On
Behalf
Of Randy
Sent: Thursday, September 04, 2008 8:44 AM
To: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
Subject: [Vantage] Looking for heavy ODBC user 6.10

Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber

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






[Non-text portions of this message have been removed]
Thanks for your input. Most of my queries are 3 to 6 tables or less, and
it's true that sometimes they run quite fast, and other times they run
very slow. I'm still working on this. Will post anything I find that is
useful.



Randy Weber



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of lonwiksell@...
Sent: Thursday, September 04, 2008 10:47 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Looking for heavy ODBC user 6.10



Your complex multi-table queries may be too complex. I've had to break
these down into multiple step queries with one query looking at only 3
to 6 tables. This is true when accessing any SQL database. A query
with many tables will just hang. Although if it hangs it usually will
not run the next time in a few seconds. There can be many other reasons
for performance issues.

I've been doing this with various databases with Access for over 15
years. You can give me a call if you want.

Lon A. Wiksell

R.O.M. Corp.

916-318-8000

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Randy
Sent: Thursday, September 04, 2008 8:44 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Looking for heavy ODBC user 6.10

Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber

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





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

I'm going to presume that you've set up the SQL connection in Progress
Explorer according to Epicor's guidelines for best performance.

First three rules for all queries (even Report Builder):

1. Company is _always_ your master table

2. First filter is Company.Company = Your Company Code

3. First sort is Company.Company

Regardless of what data you're trying to mine, Company is the smallest
table. Next join should be to another relatively small table (InvcHead,
LaborHed, OrderHed, POHeader, etc). Your first joined field should be
Company.Company to 1stJoinedTable.company. Your third table should also
be joined first as 1stJoinedTable.Company to 2ndJoinedTable.Company.
And so on.

All other joins, filters, and sorts should be on indexed fields (bolded
fields in the Access QBE grid, also look in the Data Dictionary). And
keep your joins to as few as necessary to return unique data. In the
original Report Builder class taught by Mark Pladson, he did an
excellent job of explaining how to tune your queries and reports for
performance.

The biggest reason is you want to trim the size of your results quickly.
Starting with the smallest table and filtering as far upstream as
possible is the way to make this happen. When the server has processed
the query, it dumps the results to your PC and further processing is
done by whatever query engine you're using. Another good tip: never
filter on the outside of an outer join, it will turn your outer join
back in to an inner join and you won't get all the data you think you're
getting.

Keep this in mind too: Progress RDBMS doesn't support a whole lot of
query operators. Avoid things like "in range ..." and try to use simple
operators. When I need something more complex, I'll write an initial
query tuned for smallest possible data set and nest that query into
another query. Access will grab the nested query first, then process
the second query. Unfortunately you cannot do this with the MSQuery
tool that Excel uses. But to get around that, I dump a lot of minimally
filtered data at night into Access tables so that Excel users can make
easy use of it.

have fun,
john




----- Original message -----
From: "Randy" <rweber@...>
To: vantage@yahoogroups.com
Date: Thu, 04 Sep 2008 13:44:14 -0000
Subject: [Vantage] Looking for heavy ODBC user 6.10

Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber
My advice, and take it for what it's worth:

Never let users connect via ODBC to your live Progress 9.x database.

When I was on versions 3, 5 and 6, I used data transformations in SQL Server 7 to pull live Progress data over from key tables (order*, po*, labor*, job* etc.) in the middle of the night.
Any instance where it was acceptable for the data to be yesterday or older, I created Access or VB front ends on that SQL Server data. Things like Labor Analysis, Historical Costing and so on, were perfect for this.
I could create Stored Procedures on the SQL Server that returned the smallest dataset that I needed.

Because you dump the data in the middle of the night, no performance issue there.

My SQL Server for those many years ran on a old Dell box with a pair of Pentium II's (yes, that's a 2) and 768MB or RAM. I could run a monthly labor analysis report on a 30 man department, with about 500 jobs and the report was on-screen in seconds. I don't say these things to brag, like I'm some ace programmer, just that SQL Server's stored procedures are greased lightning next to Vantage's thorazine shuffle.

I also think that any query with more than 3 tables needs to be re-evaluated.





----- Original Message ----
From: John Sage <list@...>
To: vantage@yahoogroups.com
Sent: Friday, September 5, 2008 7:47:34 PM
Subject: Re: [Vantage] Looking for heavy ODBC user 6.10


Randy,

I'm going to presume that you've set up the SQL connection in Progress
Explorer according to Epicor's guidelines for best performance.

First three rules for all queries (even Report Builder):

1. Company is _always_ your master table

2. First filter is Company.Company = Your Company Code

3. First sort is Company.Company

Regardless of what data you're trying to mine, Company is the smallest
table. Next join should be to another relatively small table (InvcHead,
LaborHed, OrderHed, POHeader, etc). Your first joined field should be
Company.Company to 1stJoinedTable. company. Your third table should also
be joined first as 1stJoinedTable. Company to 2ndJoinedTable. Company.
And so on.

All other joins, filters, and sorts should be on indexed fields (bolded
fields in the Access QBE grid, also look in the Data Dictionary). And
keep your joins to as few as necessary to return unique data. In the
original Report Builder class taught by Mark Pladson, he did an
excellent job of explaining how to tune your queries and reports for
performance.

The biggest reason is you want to trim the size of your results quickly.
Starting with the smallest table and filtering as far upstream as
possible is the way to make this happen. When the server has processed
the query, it dumps the results to your PC and further processing is
done by whatever query engine you're using. Another good tip: never
filter on the outside of an outer join, it will turn your outer join
back in to an inner join and you won't get all the data you think you're
getting.

Keep this in mind too: Progress RDBMS doesn't support a whole lot of
query operators. Avoid things like "in range ..." and try to use simple
operators. When I need something more complex, I'll write an initial
query tuned for smallest possible data set and nest that query into
another query. Access will grab the nested query first, then process
the second query. Unfortunately you cannot do this with the MSQuery
tool that Excel uses. But to get around that, I dump a lot of minimally
filtered data at night into Access tables so that Excel users can make
easy use of it.

have fun,
john

----- Original message -----
From: "Randy" <rweber@tlcelectroni cs.com>
To: vantage@yahoogroups .com
Date: Thu, 04 Sep 2008 13:44:14 -0000
Subject: [Vantage] Looking for heavy ODBC user 6.10

Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber






[Non-text portions of this message have been removed]
I made some changes and have had great results. Thank you all for
your replies. Below are some comments:

John Sage's suggestions did the trick - thanks John.

The join from Company to Company in the query design was most likely
the thing that produced the best results, as well as filtering one
company. We have two companies, but the second one has almost no
activity, so it had been ingored. This means that most queries had to
look at both companies though only one companies' data was needed.

Shrinking queries down to three tables and creating queries off of
those also helped.

Some queries which were running several minutes, or getting hung up
now take seconds or less than a minute to run. I have more work to
do, but I expect to get many of them down to seconds or else they
will have to be run at night.

Unfortunately due to the nature of our business, we need some of
these complex queries to run during the day. Now I can run many of
them with confidence where before it was a constant problem.

Randy Weber
IT Manager
TLC Electronics





--- In vantage@yahoogroups.com, "Shirley Graver" <shirleyg@...> wrote:
>
> This could be a valuable contribution the group, please post
results /
> findings to the group. Also, take a look at your ODBC set up on
your server
> to be sure that you have enough connection set for your data base.
Every
> ODBC process you run establishes a connection to the database,
depending on
> how you've configured your query those connection could be held
open even
> though your query has finished pulling data. If many people are
running
> queries that stay open you eventually run out of connections
allowed. That
> being the case, the query could time out waiting for a chance at the
> database.
>
> Shirley Graver
> Moderator
>
> _____
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf Of
> rfc822 Compliance issue From: added by system POTENTIAL SPAM
> Sent: Thursday, September 04, 2008 11:47 AM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] Looking for heavy ODBC user 6.10
>
>
>
> Your complex multi-table queries may be too complex. I've had to
break
> these down into multiple step queries with one query looking at
only 3
> to 6 tables. This is true when accessing any SQL database. A query
> with many tables will just hang. Although if it hangs it usually
will
> not run the next time in a few seconds. There can be many other
reasons
> for performance issues.
>
> I've been doing this with various databases with Access for over 15
> years. You can give me a call if you want.
>
> Lon A. Wiksell
>
> R.O.M. Corp.
>
> 916-318-8000
>
> ________________________________
>
> From: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
> [mailto:vantage@yahoogroups <mailto:vantage%
40yahoogroups.com> .com] On
> Behalf
> Of Randy
> Sent: Thursday, September 04, 2008 8:44 AM
> To: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
> Subject: [Vantage] Looking for heavy ODBC user 6.10
>
> Hello,
>
> We use ODBC often in both MS Access and Excel to view live data
from
> the Progress database. When it works, it works great. But, it seems
> to be causing problems from time to time. At times, it is very
> tasking on the system (we think).
>
> We often have people get booted out of Vantage and extremely slow
> performance. This seems to be happening when others are using ODBC
to
> get at lots of data using complex multi-table queries. I can't tell
> for sure if it's the ODBC causing the problem because I don't know
of
> any tools to evaluate what is going on.
>
> Sometimes the ODBC report just hangs and hangs. The person asks for
> help and the same report runs in seconds without changing anything.
> I'm starting to think that due to the Read-Uncommitted setting,
that
> the ODBC will just hang and hang while someone else might be in the
> midst of order entry or something. Is this possible?
>
> Are there tools like that - that let me know who or what is tasking
> the database?
>
> Should we be running ODBC reports at night?
>
> I'd like to find someone who also uses the ODBC connection
> extensively and preferrably speak to him or her rather than type a
> lot here. I have a number of questions about performance and how
they
> use ODBC which would be much easier over the phone.
>
> Thanks in advance,
>
> Randy Weber
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Hi Lon,



Thank you for your reply. I made some changes and posted results in the
group. Breaking down queries to three or less tables helped in some
cases. What helped most was the Join between Company to Company (someone
else's post), which had been overlooked since the second company in our
database rarely gets used for anything.



What still baffles me is that sometimes they ran fast, sometimes slow,
and sometimes they just hang. Because of that, I never questioned the
query design and assumed it was due to heavy activity by other users,
etc.



I'll keep your number handy just in case.



Randy



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of lonwiksell@...
Sent: Thursday, September 04, 2008 10:47 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Looking for heavy ODBC user 6.10



Your complex multi-table queries may be too complex. I've had to break
these down into multiple step queries with one query looking at only 3
to 6 tables. This is true when accessing any SQL database. A query
with many tables will just hang. Although if it hangs it usually will
not run the next time in a few seconds. There can be many other reasons
for performance issues.

I've been doing this with various databases with Access for over 15
years. You can give me a call if you want.

Lon A. Wiksell

R.O.M. Corp.

916-318-8000

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Randy
Sent: Thursday, September 04, 2008 8:44 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Looking for heavy ODBC user 6.10

Hello,

We use ODBC often in both MS Access and Excel to view live data from
the Progress database. When it works, it works great. But, it seems
to be causing problems from time to time. At times, it is very
tasking on the system (we think).

We often have people get booted out of Vantage and extremely slow
performance. This seems to be happening when others are using ODBC to
get at lots of data using complex multi-table queries. I can't tell
for sure if it's the ODBC causing the problem because I don't know of
any tools to evaluate what is going on.

Sometimes the ODBC report just hangs and hangs. The person asks for
help and the same report runs in seconds without changing anything.
I'm starting to think that due to the Read-Uncommitted setting, that
the ODBC will just hang and hang while someone else might be in the
midst of order entry or something. Is this possible?

Are there tools like that - that let me know who or what is tasking
the database?

Should we be running ODBC reports at night?

I'd like to find someone who also uses the ODBC connection
extensively and preferrably speak to him or her rather than type a
lot here. I have a number of questions about performance and how they
use ODBC which would be much easier over the phone.

Thanks in advance,

Randy Weber

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





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

Thanks for the chops, glad it worked for you. Following the basic rules
of thumb make ODBC a very useful tool to get around the limitations of
the Vantage program.

We use ODBC to extract nested BOMs, to grab demand, to create MRP and
even to backflush (yes, it can be done safely!). The flip side is that
a lot of these solutions are fairly complex and involve a lot of
testing, programming and an understanding of ODBC security and ODBC
limitations.


----- Original message -----
From: "Randy" <rweber@...>
To: vantage@yahoogroups.com
Date: Mon, 15 Sep 2008 14:50:59 -0000
Subject: [Vantage] Re: Looking for heavy ODBC user 6.10

I made some changes and have had great results. Thank you all for
your replies. Below are some comments:

John Sage's suggestions did the trick - thanks John.

The join from Company to Company in the query design was most likely
the thing that produced the best results, as well as filtering one
company.