Queries painfully slow, some general pointers please?

  • Thread starter Thread starter HM
  • Start date Start date
H

HM

Greetings all,

Im relatively new to Access. When I make certain queries consisting of a
say 3 of 4 tables, they run vey fast on their own, just a couple seconds.
However when I combine another query (which consists of one table) into that
same query that previously ran in seconds, it can take up to 30 minutes to
run. Im just looking for some general (or specific) things that I can look
for.

I link to all the tables through ODBC. And the tables are extremely large.
Is there a faq, or some methodology that I should be using that is obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS [Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
 
Make sure you've set up the joins between the tables--are you getting
umpteen bazillion records returned?
Make sure the join fields and any fields you're using to select records are
filtered
Check for "Rushmore" in on-line help, and see if you can bring your query
in-line with those recommendations

If none of that works, try describing what the query is doing, and 1posting
the SQL.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

HM said:
Greetings all,

Im relatively new to Access. When I make certain queries consisting of a
say 3 of 4 tables, they run vey fast on their own, just a couple seconds.
However when I combine another query (which consists of one table) into that
same query that previously ran in seconds, it can take up to 30 minutes to
run. Im just looking for some general (or specific) things that I can look
for.

I link to all the tables through ODBC. And the tables are extremely large.
Is there a faq, or some methodology that I should be using that is obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS [Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...
 
1. Make sure your tables have indexes on the following columns:
dbo_CUST_ORDER_LINE.CUST_ORDER_ID
dbo_CUSTOMER_ORDER.ID
dbo_CUSTOMER_ORDER.CUSTOMER_ID
dbo_CUSTOMER.ID
dbo_CUST_ORDER_LINE.PART_ID
dbo_PART.ID
[qry Resource ID].WORKORDER_BASE_ID
dbo_CUST_ORDER_LINE.CUST_ORDER_ID

...and whatever other columns in [qry Resource ID] are explicitly used
for searching.

2. I would re-write this as a single query, making use of subqueries, rather
than external queries.

3. I would also recommend moving this query to the server. Performance will
go through the roof then. If you can't move the query to the server, turn it
into a Pass-Through query.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

HM said:
Greetings all,

Im relatively new to Access. When I make certain queries consisting of a
say 3 of 4 tables, they run vey fast on their own, just a couple seconds.
However when I combine another query (which consists of one table) into that
same query that previously ran in seconds, it can take up to 30 minutes to
run. Im just looking for some general (or specific) things that I can look
for.

I link to all the tables through ODBC. And the tables are extremely large.
Is there a faq, or some methodology that I should be using that is obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS [Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...
 
Thanks for the tips Rebecca, I actually get the correct records returned,
but it just takes forever. I just don't get it because individially the
queries run fast enough.. but when i link two together with a common
field.. it crawls.

I checked up on Rushmore query optimization, I will keep that in mind for
future queries, however It said that it couldnt be used on ODBC data source
queries..which is what all mine are.

I will describe what im trying to do in my next post....

thanks again.

HM

Rebecca Riordan said:
Make sure you've set up the joins between the tables--are you getting
umpteen bazillion records returned?
Make sure the join fields and any fields you're using to select records are
filtered
Check for "Rushmore" in on-line help, and see if you can bring your query
in-line with those recommendations

If none of that works, try describing what the query is doing, and 1posting
the SQL.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

HM said:
Greetings all,

Im relatively new to Access. When I make certain queries consisting of a
say 3 of 4 tables, they run vey fast on their own, just a couple seconds.
However when I combine another query (which consists of one table) into that
same query that previously ran in seconds, it can take up to 30 minutes to
run. Im just looking for some general (or specific) things that I can look
for.

I link to all the tables through ODBC. And the tables are extremely large.
Is there a faq, or some methodology that I should be using that is obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS [Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...
Click to expand...
 
Thanks a lot for the pointers, that's exactly the kind of info I wanted. I
do have some follow up questions. The tables that I'm accessing are tied to
our companies manufacturing system (Visual Manufacturing by Lilly Software)
I dont think that I can change the Indexes on the tables, but I will find
out.

I would like some more info on pass through queries if you don't mind. I
tried simply changing one of my basic queries to a passthrough query. It
prompted me to select my data source. I assumed that I should treat this the
same way I would when I connect to my linked tables. So i chose the Machine
Data Source that my tables are stored on. And then entered my
username/password.. However I received the following error

ODBC - call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo_OPERATION'(#208)

What information do I need to add to the query (and where do I need to
define it) to make a pass through query work?



Here is the sql for the simple query I was trying this on:

SELECT dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.WORKORDER_BASE_ID,
dbo_OPERATION.WORKORDER_LOT_ID, dbo_OPERATION.RUN,
dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.LOAD_SIZE_QTY,
dbo_OPERATION.SEQUENCE_NO

FROM dbo_OPERATION

WHERE (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));



Thanks again for your help,

HM

Graham R Seach said:
1. Make sure your tables have indexes on the following columns:
dbo_CUST_ORDER_LINE.CUST_ORDER_ID
dbo_CUSTOMER_ORDER.ID
dbo_CUSTOMER_ORDER.CUSTOMER_ID
dbo_CUSTOMER.ID
dbo_CUST_ORDER_LINE.PART_ID
dbo_PART.ID
[qry Resource ID].WORKORDER_BASE_ID
dbo_CUST_ORDER_LINE.CUST_ORDER_ID

...and whatever other columns in [qry Resource ID] are explicitly used
for searching.

2. I would re-write this as a single query, making use of subqueries, rather
than external queries.

3. I would also recommend moving this query to the server. Performance will
go through the roof then. If you can't move the query to the server, turn it
into a Pass-Through query.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

HM said:
Greetings all,

Im relatively new to Access. When I make certain queries consisting of a
say 3 of 4 tables, they run vey fast on their own, just a couple seconds.
However when I combine another query (which consists of one table) into that
same query that previously ran in seconds, it can take up to 30 minutes to
run. Im just looking for some general (or specific) things that I can look
for.

I link to all the tables through ODBC. And the tables are extremely large.
Is there a faq, or some methodology that I should be using that is obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS [Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...
Click to expand...
 
You have tried to join to an aggregate query (with group, count, and
having).
Because it is an aggregate query, the individual records no longer relate
directly to individual records in a table: one record in the recordset may
represent a group of several records in the table.

This means that one record in the recordset no longer relates directly to
an index. So the join can't use any indexes on the aggregate query side.

Then, Jet (Access) is probably making a bad decision and bringing down
ALL of the records, to do local sorting and grouping.

Regardless of if your rewrite the queries to run on the server, or keep
them local, you should rewrite Query C so that it uses only tables, and
is not based on Query A.

(david)


HM said:
Greetings all,

Im relatively new to Access. When I make certain queries consisting of a
say 3 of 4 tables, they run vey fast on their own, just a couple seconds.
However when I combine another query (which consists of one table) into that
same query that previously ran in seconds, it can take up to 30 minutes to
run. Im just looking for some general (or specific) things that I can look
for.

I link to all the tables through ODBC. And the tables are extremely large.
Is there a faq, or some methodology that I should be using that is obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS [Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...
 
The actual table is likely
dbo.OPERATION

Access renames the . in linked tabelnames to avoid a .
which could confuse its sql-processor

if possible, add a database & owner to it
(like mydatabase.owner.tablename)
for ease of comfort I aliased
dbo.OPERATION to dbo_OPERATION (valid sql)
(FROM dbo.OPERATION dbo_OPERATION)

SELECT dbo_OPERATION.WORKORDER_TYPE,
dbo_OPERATION.WORKORDER_BASE_ID,
dbo_OPERATION.WORKORDER_LOT_ID, dbo_OPERATION.RUN,
dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.LOAD_SIZE_QTY,
dbo_OPERATION.SEQUENCE_NO
FROM dbo.OPERATION dbo_OPERATION

WHERE (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));

should work now in your pass-through query

If you have query analyzer from SQL Server client tools,
you could view the query-plan of this query to see if it
uses any indexes.
-----Original Message-----
Thanks a lot for the pointers, that's exactly the kind of info I wanted. I
do have some follow up questions. The tables that I'm accessing are tied to
our companies manufacturing system (Visual Manufacturing by Lilly Software)
I dont think that I can change the Indexes on the tables, but I will find
out.

I would like some more info on pass through queries if you don't mind. I
tried simply changing one of my basic queries to a passthrough query. It
prompted me to select my data source. I assumed that I should treat this the
same way I would when I connect to my linked tables. So i chose the Machine
Data Source that my tables are stored on. And then entered my
username/password.. However I received the following error

ODBC - call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo_OPERATION'(#208)

What information do I need to add to the query (and where do I need to
define it) to make a pass through query work?



Here is the sql for the simple query I was trying this on:

SELECT dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.WORKORDER_BASE_ID,
dbo_OPERATION.WORKORDER_LOT_ID, dbo_OPERATION.RUN,
dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.LOAD_SIZE_QTY,
dbo_OPERATION.SEQUENCE_NO

FROM dbo_OPERATION

WHERE (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));



Thanks again for your help,

HM

1. Make sure your tables have indexes on the following columns:
dbo_CUST_ORDER_LINE.CUST_ORDER_ID
dbo_CUSTOMER_ORDER.ID
dbo_CUSTOMER_ORDER.CUSTOMER_ID
dbo_CUSTOMER.ID
dbo_CUST_ORDER_LINE.PART_ID
dbo_PART.ID
[qry Resource ID].WORKORDER_BASE_ID
dbo_CUST_ORDER_LINE.CUST_ORDER_ID

...and whatever other columns in [qry Resource ID] are explicitly used
for searching.

2. I would re-write this as a single query, making use
Click to expand...
of subqueries,
rather
than external queries.

3. I would also recommend moving this query to the
Click to expand...
server. Performance
will
go through the roof then. If you can't move the query
Click to expand...
to the server, turn
it
into a Pass-Through query.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
queries consisting of
a just a couple
seconds.
of one table) into
that
Click to expand...
take up to 30 minutes
to
things that I can
look tables are extremely
large. using that is
obvious.
Click to expand...
did paste my sql
below dbo_OPERATION.RESOURCE_ID AS
[Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID =
Click to expand...
dbo_CUSTOMER.ID) INNER
JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below
Click to expand...
Click to expand...
code, It will
will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS
Click to expand...
[Pcs/Load], [qry
Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...
Click to expand...


.
Click to expand...
 
gandalf, thanks a lot. That worked out great. However, for some reason I
had to remove the " " double quotes around the W and use single quotes 'W',
and also removed the ( )'s around the table and field names after the WHERE
statement for the query to run. Not sure why it is different in those areas
then a select query.

Thanks again!
HM

gandalf said:
The actual table is likely
dbo.OPERATION

Access renames the . in linked tabelnames to avoid a .
which could confuse its sql-processor

if possible, add a database & owner to it
(like mydatabase.owner.tablename)
for ease of comfort I aliased
dbo.OPERATION to dbo_OPERATION (valid sql)
(FROM dbo.OPERATION dbo_OPERATION)

SELECT dbo_OPERATION.WORKORDER_TYPE,
dbo_OPERATION.WORKORDER_BASE_ID,
dbo_OPERATION.WORKORDER_LOT_ID, dbo_OPERATION.RUN,
dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.LOAD_SIZE_QTY,
dbo_OPERATION.SEQUENCE_NO
FROM dbo.OPERATION dbo_OPERATION

WHERE (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));

should work now in your pass-through query

If you have query analyzer from SQL Server client tools,
you could view the query-plan of this query to see if it
uses any indexes.
-----Original Message-----
Thanks a lot for the pointers, that's exactly the kind of info I wanted. I
do have some follow up questions. The tables that I'm accessing are tied to
our companies manufacturing system (Visual Manufacturing by Lilly Software)
I dont think that I can change the Indexes on the tables, but I will find
out.

I would like some more info on pass through queries if you don't mind. I
tried simply changing one of my basic queries to a passthrough query. It
prompted me to select my data source. I assumed that I should treat this the
same way I would when I connect to my linked tables. So i chose the Machine
Data Source that my tables are stored on. And then entered my
username/password.. However I received the following error

ODBC - call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo_OPERATION'(#208)

What information do I need to add to the query (and where do I need to
define it) to make a pass through query work?



Here is the sql for the simple query I was trying this on:

SELECT dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.WORKORDER_BASE_ID,
dbo_OPERATION.WORKORDER_LOT_ID, dbo_OPERATION.RUN,
dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.LOAD_SIZE_QTY,
dbo_OPERATION.SEQUENCE_NO

FROM dbo_OPERATION

WHERE (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));



Thanks again for your help,

HM

1. Make sure your tables have indexes on the following columns:
dbo_CUST_ORDER_LINE.CUST_ORDER_ID
dbo_CUSTOMER_ORDER.ID
dbo_CUSTOMER_ORDER.CUSTOMER_ID
dbo_CUSTOMER.ID
dbo_CUST_ORDER_LINE.PART_ID
dbo_PART.ID
[qry Resource ID].WORKORDER_BASE_ID
dbo_CUST_ORDER_LINE.CUST_ORDER_ID

...and whatever other columns in [qry Resource ID] are explicitly used
for searching.

2. I would re-write this as a single query, making use
Click to expand...
of subqueries,
rather
than external queries.

3. I would also recommend moving this query to the
Click to expand...
server. Performance
will
go through the roof then. If you can't move the query
Click to expand...
to the server, turn
it
into a Pass-Through query.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Greetings all,

Im relatively new to Access. When I make certain
Click to expand...
queries consisting of
a
say 3 of 4 tables, they run vey fast on their own,
Click to expand...
just a couple
seconds.
However when I combine another query (which consists of one table) into
that
same query that previously ran in seconds, it can
Click to expand...
take up to 30 minutes
to
run. Im just looking for some general (or specific) things that I can
look
for.

I link to all the tables through ODBC. And the tables are extremely
large.
Is there a faq, or some methodology that I should be using that is
obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I
Click to expand...
did paste my sql
below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID,
Click to expand...
dbo_OPERATION.RESOURCE_ID AS
[Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER
JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below
Click to expand...
code, It will
will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry
Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...


.
Click to expand...
Click to expand...
 
Ok, that makes sense. I will try rewriting the final query.

Thank you

HM


david epsom dot com dot au said:
You have tried to join to an aggregate query (with group, count, and
having).
Because it is an aggregate query, the individual records no longer relate
directly to individual records in a table: one record in the recordset may
represent a group of several records in the table.

This means that one record in the recordset no longer relates directly to
an index. So the join can't use any indexes on the aggregate query side.

Then, Jet (Access) is probably making a bad decision and bringing down
ALL of the records, to do local sorting and grouping.

Regardless of if your rewrite the queries to run on the server, or keep
them local, you should rewrite Query C so that it uses only tables, and
is not based on Query A.

(david)


HM said:
Greetings all,

Im relatively new to Access. When I make certain queries consisting of a
say 3 of 4 tables, they run vey fast on their own, just a couple seconds.
However when I combine another query (which consists of one table) into that
same query that previously ran in seconds, it can take up to 30 minutes to
run. Im just looking for some general (or specific) things that I can look
for.

I link to all the tables through ODBC. And the tables are extremely large.
Is there a faq, or some methodology that I should be using that is obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS [Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN (((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];
Click to expand...
Click to expand...
 
That's because SQL Server uses single quotes, not doubles.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


HM said:
gandalf, thanks a lot. That worked out great. However, for some reason I
had to remove the " " double quotes around the W and use single quotes 'W',
and also removed the ( )'s around the table and field names after the WHERE
statement for the query to run. Not sure why it is different in those areas
then a select query.

Thanks again!
HM

gandalf said:
The actual table is likely
dbo.OPERATION

Access renames the . in linked tabelnames to avoid a .
which could confuse its sql-processor

if possible, add a database & owner to it
(like mydatabase.owner.tablename)
for ease of comfort I aliased
dbo.OPERATION to dbo_OPERATION (valid sql)
(FROM dbo.OPERATION dbo_OPERATION)

SELECT dbo_OPERATION.WORKORDER_TYPE,
dbo_OPERATION.WORKORDER_BASE_ID,
dbo_OPERATION.WORKORDER_LOT_ID, dbo_OPERATION.RUN,
dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.LOAD_SIZE_QTY,
dbo_OPERATION.SEQUENCE_NO
FROM dbo.OPERATION dbo_OPERATION

WHERE (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));

should work now in your pass-through query

If you have query analyzer from SQL Server client tools,
you could view the query-plan of this query to see if it
uses any indexes.
-----Original Message-----
Thanks a lot for the pointers, that's exactly the kind of info I wanted. I
do have some follow up questions. The tables that I'm accessing are tied to
our companies manufacturing system (Visual Manufacturing by Lilly Software)
I dont think that I can change the Indexes on the tables, but I will find
out.

I would like some more info on pass through queries if you don't mind. I
tried simply changing one of my basic queries to a passthrough query. It
prompted me to select my data source. I assumed that I should treat this the
same way I would when I connect to my linked tables. So i chose the Machine
Data Source that my tables are stored on. And then entered my
username/password.. However I received the following error

ODBC - call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo_OPERATION'(#208)

What information do I need to add to the query (and where do I need to
define it) to make a pass through query work?



Here is the sql for the simple query I was trying this on:

SELECT dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.WORKORDER_BASE_ID,
dbo_OPERATION.WORKORDER_LOT_ID, dbo_OPERATION.RUN,
dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.LOAD_SIZE_QTY,
dbo_OPERATION.SEQUENCE_NO

FROM dbo_OPERATION

WHERE (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));



Thanks again for your help,

HM

1. Make sure your tables have indexes on the following columns:
dbo_CUST_ORDER_LINE.CUST_ORDER_ID
dbo_CUSTOMER_ORDER.ID
dbo_CUSTOMER_ORDER.CUSTOMER_ID
dbo_CUSTOMER.ID
dbo_CUST_ORDER_LINE.PART_ID
dbo_PART.ID
[qry Resource ID].WORKORDER_BASE_ID
dbo_CUST_ORDER_LINE.CUST_ORDER_ID

...and whatever other columns in [qry Resource ID] are explicitly used
for searching.

2. I would re-write this as a single query, making use of subqueries,
rather
than external queries.

3. I would also recommend moving this query to the server. Performance
will
go through the roof then. If you can't move the query to the server, turn
it
into a Pass-Through query.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Greetings all,

Im relatively new to Access. When I make certain queries consisting of
a
say 3 of 4 tables, they run vey fast on their own, just a couple
seconds.
However when I combine another query (which consists of one table) into
that
same query that previously ran in seconds, it can take up to 30 minutes
to
run. Im just looking for some general (or specific) things that I can
look
for.

I link to all the tables through ODBC. And the tables are extremely
large.
Is there a faq, or some methodology that I should be using that is
obvious.
I can paste my sql code if that would help..but not sure if that would?

Some general tips are all I am expecting, however I did paste my sql
below
for anyone that can decipher that.

Thanks in advanced,


HM


Below query A runs very fast.


SELECT dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID AS
[Line
Number], dbo_OPERATION.WORKORDER_TYPE, dbo_OPERATION.SEQUENCE_NO,
Count(dbo_OPERATION.WORKORDER_BASE_ID) AS CountOfWORKORDER_BASE_ID
FROM dbo_OPERATION
GROUP BY dbo_OPERATION.WORKORDER_BASE_ID, dbo_OPERATION.RESOURCE_ID,
dbo_OPERATION.WORKORDER_TYPE,
Click to expand...
dbo_OPERATION.SEQUENCE_NO
HAVING (((dbo_OPERATION.WORKORDER_TYPE)="W") AND
((dbo_OPERATION.SEQUENCE_NO)=10));


This query (B) also runs relatively fast..


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID,
Click to expand...
dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load]
FROM ((dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
Click to expand...
dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUSTOMER ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER
JOIN
dbo_PART ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID,
Click to expand...
dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9;




But when I insert query A into Query B, via the below code, It will
will
take 30 min to run.

Query C


SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID,
Click to expand...
dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9 AS [Pcs/Load], [qry
Resource
ID].[Line Number]
FROM [qry Resource ID] INNER JOIN
Click to expand...
(((dbo_CUST_ORDER_LINE INNER JOIN
dbo_CUSTOMER_ORDER ON
Click to expand...
dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUSTOMER_ORDER.ID) INNER JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID) INNER JOIN dbo_PART ON
dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON [qry Resource
ID].WORKORDER_BASE_ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUSTOMER.NAME,
dbo_CUST_ORDER_LINE.PART_ID,
Click to expand...
dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_CUST_ORDER_LINE.UNIT_PRICE, dbo_PART.USER_9, [qry Resource ID].[Line
Number];









.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top