Queries painfully slow, some general pointers please?

That's because SQL Server uses single quotes, not doubles.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


"HM" wrote in message
...
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" wrote in message
...
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

"Graham R Seach" wrote in
message
...
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" wrote in message
...
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];









.