Table size for a linked table.

[rer]

I have a query that I am running that combines two linked tables to a
Progress Database.
One table is 40,000 rows and one is table 1,500 rows.
The query could probably be done better as a stored procedure on Progress
but I did not go down that road because of my limited knowledge of Progress
and concern over the clients ablility to maintain the stored procedure.
It worked well until an upgrade from Progress database 9.1 to 10.1. Now
Access runs out of memory and crashes when the query is ran.
Do you think that the size of the tables could be the reason why this is
happening. Perhaps the old driver could manange where the new driver can not.
Is 40,000 joined with 1,500 rows on a linked connection too big for Access?

 

[Dale Fye]

It's not likely to be a problem with the # of records. It most likely has to
do with the your drivers. Can you post the text of the SQL? Have you tried
a real simple query of the new Progress database?

 

[rer]

OK, yes i forgot to mention that opening the linked table still works.

This is the query PUB_job is 40,000 records and PUB_Cust is 1,500 rows.

SELECT [PUB_Job].[Job-ID] AS JobNumber,
[PUB_Job].[Job-Desc] AS JobDescription,
[PUB_Job].[Sales-Rep-ID] AS SalesPerson,
[PUB_cust].[cust-name] AS CustomeName
FROM PUB_Job INNER JOIN PUB_cust
ON [PUB_Job].[Cust-ID-Bill-to]=[PUB_cust].[Cust-code]
WHERE
[PUB_Job].[Job-Open]=1 And
[PUB_Job].[System-ID]="WHA" And
[PUB_cust].[System-ID]='WHA';

This Also runs successfuly.

However I take the results of that Query (Which i named WHAQuery) and joined
it to data in the database.

WHAData is internal data for the application, it is about 200 rows.
Location numbers is about 15 records. Which has the Location name that is
associated with each Location Number.

SELECT * FROM
(WHAData LEFT JOIN WHAQuery ON WHAData.JobNumber=WHAQuery.JobNumber)
LEFT JOIN LocationNames ON LocationNames.LocationNumber=WHAData.Location
ORDER BY Location, ShipDate, Groups, Groups2, WhaData.JobNumber, WhaData.id;

 

[Dale Fye]

So, what is about this query that isn't working?

I think I would be a little more explicit in my Select statement (SELECT
WHATData.*, WHAQuery.*. LocationNames.*).

Have you tried decomposing the query and checking the individual parts

SELECT WHAData.*, WHAQuer.* FROM WHAData LEFT JOIN WHAQuer
ON WHAData.JobNumber = WHAQuery.JobNumber

Dale
--
Email address is not valid.
Please reply to newsgroup only.

OK, yes i forgot to mention that opening the linked table still works.

This is the query PUB_job is 40,000 records and PUB_Cust is 1,500 rows.

SELECT [PUB_Job].[Job-ID] AS JobNumber,
[PUB_Job].[Job-Desc] AS JobDescription,
[PUB_Job].[Sales-Rep-ID] AS SalesPerson,
[PUB_cust].[cust-name] AS CustomeName
FROM PUB_Job INNER JOIN PUB_cust
ON [PUB_Job].[Cust-ID-Bill-to]=[PUB_cust].[Cust-code]
WHERE
[PUB_Job].[Job-Open]=1 And
[PUB_Job].[System-ID]="WHA" And
[PUB_cust].[System-ID]='WHA';

This Also runs successfuly.

However I take the results of that Query (Which i named WHAQuery) and joined
it to data in the database.

WHAData is internal data for the application, it is about 200 rows.
Location numbers is about 15 records. Which has the Location name that is
associated with each Location Number.

SELECT * FROM
(WHAData LEFT JOIN WHAQuery ON WHAData.JobNumber=WHAQuery.JobNumber)
LEFT JOIN LocationNames ON LocationNames.LocationNumber=WHAData.Location
ORDER BY Location, ShipDate, Groups, Groups2, WhaData.JobNumber, WhaData.id;

It's not likely to be a problem with the # of records. It most likely has to
do with the your drivers. Can you post the text of the SQL? Have you tried
a real simple query of the new Progress database?

 

[rer]

The query is valid and was working prior to the upgrade. And in the
production query i do list out all of the columns that are returned (however
there are about 20 so i just simplified that to * for this entry).

For a temporary fix i took out the joins between WHAQuery and Location
Numbers and the order by clause and it worked. However with that the client
can only see the data stored within the application file and they can not see
any of the data that makes the solution usable (sepecifically system data
about the Customer, Job Name/Description and Sales Person)/ On some computers
it will actually run with only the Order By Clause removed.

I believe that I am just barely pushing the limitations of access. 40,000 *
1,500 * 800 * 15. Although I have heard that access should be able to handle
that amonut of data they are remote queries over an ODBC connection with a
brand new ODBC driver.
-Robert

So, what is about this query that isn't working?

I think I would be a little more explicit in my Select statement (SELECT
WHATData.*, WHAQuery.*. LocationNames.*).

Have you tried decomposing the query and checking the individual parts

SELECT WHAData.*, WHAQuer.* FROM WHAData LEFT JOIN WHAQuer
ON WHAData.JobNumber = WHAQuery.JobNumber

Dale
--
Email address is not valid.
Please reply to newsgroup only.

OK, yes i forgot to mention that opening the linked table still works.

This is the query PUB_job is 40,000 records and PUB_Cust is 1,500 rows.

SELECT [PUB_Job].[Job-ID] AS JobNumber,
[PUB_Job].[Job-Desc] AS JobDescription,
[PUB_Job].[Sales-Rep-ID] AS SalesPerson,
[PUB_cust].[cust-name] AS CustomeName
FROM PUB_Job INNER JOIN PUB_cust
ON [PUB_Job].[Cust-ID-Bill-to]=[PUB_cust].[Cust-code]
WHERE
[PUB_Job].[Job-Open]=1 And
[PUB_Job].[System-ID]="WHA" And
[PUB_cust].[System-ID]='WHA';

This Also runs successfuly.

However I take the results of that Query (Which i named WHAQuery) and joined
it to data in the database.

WHAData is internal data for the application, it is about 200 rows.
Location numbers is about 15 records. Which has the Location name that is
associated with each Location Number.

SELECT * FROM
(WHAData LEFT JOIN WHAQuery ON WHAData.JobNumber=WHAQuery.JobNumber)
LEFT JOIN LocationNames ON LocationNames.LocationNumber=WHAData.Location
ORDER BY Location, ShipDate, Groups, Groups2, WhaData.JobNumber, WhaData.id;

It's not likely to be a problem with the # of records. It most likely has to
do with the your drivers. Can you post the text of the SQL? Have you tried
a real simple query of the new Progress database?

--
Email address is not valid.
Please reply to newsgroup only.


:

I have a query that I am running that combines two linked tables to a
Progress Database.
One table is 40,000 rows and one is table 1,500 rows.
The query could probably be done better as a stored procedure on Progress
but I did not go down that road because of my limited knowledge of Progress
and concern over the clients ablility to maintain the stored procedure.
It worked well until an upgrade from Progress database 9.1 to 10.1. Now
Access runs out of memory and crashes when the query is ran.
Do you think that the size of the tables could be the reason why this is
happening. Perhaps the old driver could manange where the new driver can not.
Is 40,000 joined with 1,500 rows on a linked connection too big for Access?

 

[John W. Vinson]

I believe that I am just barely pushing the limitations of access. 40,000 *
1,500 * 800 * 15. Although I have heard that access should be able to handle
that amonut of data they are remote queries over an ODBC connection with a
brand new ODBC driver.

With that size of a query, it may indeed be asking too much to have Access
pull down all the indexes (if there *are* indexes!) and do the joins locally.
Since you're using Access as a client to a server, you may well want to
consider using a Pass-Through query: Access just constructs the SQL of the
query and sends it to the server to be processed, and receives only the
"hits". Does Progress support Views? That's another alternative - create a
view of the Progress data and link to that view rather than having Access do
all the joins.

John W. Vinson [MVP]