Slooooow Query...

[ChrisM]

Hi, I'm trying to write a query, trouble is it is REALLY slow, in fact, I've
never actually let it run long enough to actually return any records.

The form of the query is this:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

So what I'm trying to do, is input a part number, run it through a query
that returns any alternative part numbers, and return any rows from the
parts table that are equivilent to the specified part number.

PART-XREF is a query, but it runs in under 1 second.

I would expect/want Access/Jet to run the sub-query first which will return
approx 1-5 rows, then simply find rows from the parts table where the part
number matches one of the those returned from the sub-query. What I suspect
it is doing is somehow running the subquery for EVERY row in the parts table
in order to check if it should include it in the result.

Anyone able to confirm or refute my theory, and more importantly, suggest
how I can get Access/Jet to run the query more efficiently??

(Parts table contains about 14,500 records.)

 

[Allen Browne]

Chris, you are probably correct, that Access is runing the subquery for
every row of the main query. That's what it usually does.

It's a bit hard to suggest alternatives when we don't know what's happening
in the PART-XREF query. Alternatives might be a different design that
normalizes the tables, a UNION query that combines the part number and
alternative part number into one long list, some different JOINs, etc.

 

[Jamie Collins]

The form of the query is this:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

Anyone able to ... suggest
how I can get Access/Jet to run the query more efficiently??

It would appear to be logically equivalent to an equi-join, so you
could try:

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

or

SELECT partsTable.*
FROM partsTable, PART-XREF
WHERE partsTable.PART_NO = PART-XREF.PN2
AND PN1 = [RequiredPartNo];

Jamie.

--

 

[ChrisM]

In message u%[email protected],

Hi, I'm trying to write a query, trouble is it is REALLY slow, in
fact, I've never actually let it run long enough to actually return
any records. The form of the query is this:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

So what I'm trying to do, is input a part number, run it through a
query that returns any alternative part numbers, and return any rows
from the parts table that are equivilent to the specified part
number. PART-XREF is a query, but it runs in under 1 second.

I would expect/want Access/Jet to run the sub-query first which will
return approx 1-5 rows, then simply find rows from the parts table
where the part number matches one of the those returned from the
sub-query. What I suspect it is doing is somehow running the
subquery for EVERY row in the parts table in order to check if it
should include it in the result. Anyone able to confirm or refute my
theory, and more importantly,
suggest how I can get Access/Jet to run the query more efficiently??

(Parts table contains about 14,500 records.)

- Top posting corrected - sorry, I just prefer it that way... ;-)

Chris, you are probably correct, that Access is runing the subquery
for every row of the main query. That's what it usually does.

It's a bit hard to suggest alternatives when we don't know what's
happening in the PART-XREF query. Alternatives might be a different
design that normalizes the tables, a UNION query that combines the
part number and alternative part number into one long list, some
different JOINs, etc.

Hi Alan,
Thanks for your reply. I'll guess I'll have to have a think about
alternative ways of constructing the query. To be honest, this seemed like
the easiest option, but obviously not the best...
What I was hoping for was someone to suggest a way I could keep the current
design, but maybe just alter it slightly to force Access into doing the
sub-query first. That would be the sensible way to do it anyway (IMO) as the
sub-query will always return exactly the same rows regardless of which row
of the main query it is currently working on...

Cheers,

Chris.

 

[Douglas J. Steele]

Couldn't he just use

SELECT * FROM partsTable
INNER JOIN [PART-XREF]
ON partsTable.PART_NO = [PART-XREF].PN2
WHERE [PART-XREF].PN1 = [RequiredPartNo]

(Of course, it would be preferable to list the fields wanted from
partsTable, rather than use SELECT *)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Chris, you are probably correct, that Access is runing the subquery for
every row of the main query. That's what it usually does.

It's a bit hard to suggest alternatives when we don't know what's
happening in the PART-XREF query. Alternatives might be a different design
that normalizes the tables, a UNION query that combines the part number
and alternative part number into one long list, some different JOINs, etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I'm trying to write a query, trouble is it is REALLY slow, in fact,
I've never actually let it run long enough to actually return any
records.

The form of the query is this:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

So what I'm trying to do, is input a part number, run it through a query
that returns any alternative part numbers, and return any rows from the
parts table that are equivilent to the specified part number.

PART-XREF is a query, but it runs in under 1 second.

I would expect/want Access/Jet to run the sub-query first which will
return approx 1-5 rows, then simply find rows from the parts table where
the part number matches one of the those returned from the sub-query.
What I suspect it is doing is somehow running the subquery for EVERY row
in the parts table in order to check if it should include it in the
result.

Anyone able to confirm or refute my theory, and more importantly, suggest
how I can get Access/Jet to run the query more efficiently??

(Parts table contains about 14,500 records.)

 

[Jamie Collins]

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

If you are only interested in *performance* (rather than e.g. code
maintenance), something else to try is replacing the Query with the
equivalent derived table e.g.

SELECT partsTable.*
FROM partsTable
INNER JOIN (
<<Query definition goes here>>
WHERE <<some column here>> = [RequiredPartNo]
) AS PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2;

Then you could consider re-writing the derived table in-line e.g.

SELECT partsTable.*
FROM (<<joins used in the Query here>>)
INNER JOIN partsTable ON partsTable.PART_NO = <<some column here>>
WHERE <<some other column here>> = [RequiredPartNo]

As Allen says, it's hard to debug code when you can't see it <g>.

Jamie.

--

 

[ChrisM]

In message (e-mail address removed),

The form of the query is this:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

Anyone able to ... suggest
how I can get Access/Jet to run the query more efficiently??

It would appear to be logically equivalent to an equi-join, so you
could try:

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

or

SELECT partsTable.*
FROM partsTable, PART-XREF
WHERE partsTable.PART_NO = PART-XREF.PN2
AND PN1 = [RequiredPartNo];

Jamie.

Great idea Jamie, and answers my question as asked perfectly, run pretty
quick too The trouble is my question was simplified...
What I'm actually trying to do is more like:

UPDATE partsTable
SET partsTable.MinStock = 99
WHERE partsTable.Part_No IN
(
SELECT .......
)

The options you suggest are 'classified' by Access as non-updatable queries
so don't work for me in this scenario... I suppose I should have been more
specific initially, sorry.

Thanks anyway though and further suggestions most welcome

Cheers,

Chris.

 

[Jamie Collins]

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

It would appear to be logically equivalent to an equi-join, so you
could try:

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

Great idea Jamie, and answers my question as asked perfectly, run pretty
quick too The trouble is my question was simplified...
What I'm actually trying to do is more like:

UPDATE partsTable
SET partsTable.MinStock = 99
WHERE partsTable.Part_No IN
(
SELECT .......
)

What about:

UPDATE partsTable
INNER JOIN [PART-XREF]
ON partsTable.Part_No = [PART-XREF].PN2
SET partsTable.MinStock = 99
WHERE [PART-XREF].PN1 = [RequiredPartNo];

Jamie.

--

 

[ChrisM]

In message (e-mail address removed),

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

It would appear to be logically equivalent to an equi-join, so you
could try:

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

Great idea Jamie, and answers my question as asked perfectly, run
pretty quick too The trouble is my question was simplified...
What I'm actually trying to do is more like:

UPDATE partsTable
SET partsTable.MinStock = 99
WHERE partsTable.Part_No IN
(
SELECT .......
)

What about:

UPDATE partsTable
INNER JOIN [PART-XREF]
ON partsTable.Part_No = [PART-XREF].PN2
SET partsTable.MinStock = 99
WHERE [PART-XREF].PN1 = [RequiredPartNo];

Jamie.

Same problem - 'Operation must use an updatable query'

:-(

 

[Jamie Collins]

What about:

UPDATE partsTable
INNER JOIN [PART-XREF]
ON partsTable.Part_No = [PART-XREF].PN2
SET partsTable.MinStock = 99
WHERE [PART-XREF].PN1 = [RequiredPartNo];

Jamie.

Same problem - 'Operation must use an updatable query'

:-(

Shame.

It worked in my limited test case. FWIW here's the code:

Sub test_cm()
' Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE partsTable (Part_No INT, MinStock" & _
" INT);"
.Execute _
"INSERT INTO partsTable (Part_No, MinStock)" & _
" VALUES (1, 1);"
.Execute _
"CREATE TABLE [PART-X] (" & vbCr & "PN1 INT, PN2 INT);"
.Execute _
"INSERT INTO [PART-X] (PN1, PN2) VALUES (1," & _
" 1);"
.Execute _
"CREATE VIEW [PART-XREF] AS SELECT PN1," & _
" PN2 " & vbCr & "FROM [PART-X];"
.Execute _
"CREATE PROCEDURE TestProc (" & vbCr & "RequiredPartNo" & _
" INTEGER = 1" & vbCr & ") " & vbCr & "AS " & vbCr & "UPDATE
partsTable" & vbCr & "INNER" & _
" JOIN [PART-XREF] " & vbCr & "ON partsTable.Part_No" & _
" = [PART-XREF].PN2" & vbCr & "SET partsTable.MinStock" & _
" = 99" & vbCr & "WHERE [PART-XREF].PN1 = RequiredPartNo;"

Dim rs
Set rs = .Execute( _
"SELECT Part_No, MinStock FROM partsTable;")
MsgBox "Before..." & vbCr & vbCr & rs.GetString

.Execute _
"EXEC TestProc 1;"

rs.Requery
MsgBox "..after" & vbCr & vbCr & rs.GetString

End With
Set .ActiveConnection = Nothing
End With

End Sub

Jamie.

--

 

[Douglas J. Steele]

In message (e-mail address removed),

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

It would appear to be logically equivalent to an equi-join, so you
could try:

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

Great idea Jamie, and answers my question as asked perfectly, run
pretty quick too The trouble is my question was simplified...
What I'm actually trying to do is more like:

UPDATE partsTable
SET partsTable.MinStock = 99
WHERE partsTable.Part_No IN
(
SELECT .......
)

What about:

UPDATE partsTable
INNER JOIN [PART-XREF]
ON partsTable.Part_No = [PART-XREF].PN2
SET partsTable.MinStock = 99
WHERE [PART-XREF].PN1 = [RequiredPartNo];

Jamie.

Same problem - 'Operation must use an updatable query'

What's partsTable, then? Is it a table in a Jet database, or in some other
DBMS? If it's in some other DBMS (or if it's a query), is it updatable when
you use it by itself?

 

[ChrisM]

In message (e-mail address removed),

What about:

UPDATE partsTable
INNER JOIN [PART-XREF]
ON partsTable.Part_No = [PART-XREF].PN2
SET partsTable.MinStock = 99
WHERE [PART-XREF].PN1 = [RequiredPartNo];

Jamie.

Same problem - 'Operation must use an updatable query'

:-(

Shame.

It worked in my limited test case. FWIW here's the code:

Sub test_cm()
' Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE partsTable (Part_No INT, MinStock" & _
" INT);"
.Execute _
"INSERT INTO partsTable (Part_No, MinStock)" & _
" VALUES (1, 1);"
.Execute _
"CREATE TABLE [PART-X] (" & vbCr & "PN1 INT, PN2 INT);"
.Execute _
"INSERT INTO [PART-X] (PN1, PN2) VALUES (1," & _
" 1);"
.Execute _
"CREATE VIEW [PART-XREF] AS SELECT PN1," & _
" PN2 " & vbCr & "FROM [PART-X];"
.Execute _
"CREATE PROCEDURE TestProc (" & vbCr & "RequiredPartNo" & _
" INTEGER = 1" & vbCr & ") " & vbCr & "AS " & vbCr & "UPDATE
partsTable" & vbCr & "INNER" & _
" JOIN [PART-XREF] " & vbCr & "ON partsTable.Part_No" & _
" = [PART-XREF].PN2" & vbCr & "SET partsTable.MinStock" & _
" = 99" & vbCr & "WHERE [PART-XREF].PN1 = RequiredPartNo;"

Dim rs
Set rs = .Execute( _
"SELECT Part_No, MinStock FROM partsTable;")
MsgBox "Before..." & vbCr & vbCr & rs.GetString

.Execute _
"EXEC TestProc 1;"

rs.Requery
MsgBox "..after" & vbCr & vbCr & rs.GetString

End With
Set .ActiveConnection = Nothing
End With

End Sub

Jamie.

Hmmm, only significant difference that I can see on a quick check is that my
Xref query is quite a lot more complicated than your. It actually consists
of 4 distinct subqueries UNIONed together...in fact, hang on...

....Ok, I just went and tested it... If I take use any of the 4 subqueries in
the join then it works(updates) fine. It is only with the UNION of all four
that I have the 'non-updatable' problem...
Oh I so love Access... Never mind, nearly Christmas

 

[BruceM]

Douglas,

Does the statement:
"...it would be preferable to list the fields wanted from partsTable, rather
than use SELECT *"
apply in general to queries, or only in a case where a limited number of
fields are needed?

Couldn't he just use

SELECT * FROM partsTable
INNER JOIN [PART-XREF]
ON partsTable.PART_NO = [PART-XREF].PN2
WHERE [PART-XREF].PN1 = [RequiredPartNo]

(Of course, it would be preferable to list the fields wanted from
partsTable, rather than use SELECT *)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Chris, you are probably correct, that Access is runing the subquery for
every row of the main query. That's what it usually does.

It's a bit hard to suggest alternatives when we don't know what's
happening in the PART-XREF query. Alternatives might be a different
design that normalizes the tables, a UNION query that combines the part
number and alternative part number into one long list, some different
JOINs, etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I'm trying to write a query, trouble is it is REALLY slow, in fact,
I've never actually let it run long enough to actually return any
records.

The form of the query is this:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

So what I'm trying to do, is input a part number, run it through a query
that returns any alternative part numbers, and return any rows from the
parts table that are equivilent to the specified part number.

PART-XREF is a query, but it runs in under 1 second.

I would expect/want Access/Jet to run the sub-query first which will
return approx 1-5 rows, then simply find rows from the parts table where
the part number matches one of the those returned from the sub-query.
What I suspect it is doing is somehow running the subquery for EVERY row
in the parts table in order to check if it should include it in the
result.

Anyone able to confirm or refute my theory, and more importantly,
suggest how I can get Access/Jet to run the query more efficiently??

(Parts table contains about 14,500 records.)

 

[Jamie Collins]

Does the statement:
"...it would be preferable to list the fields wanted from partsTable, rather
than use SELECT *"
apply in general to queries, or only in a case where a limited number of
fields are needed?

Do a google search for the exact phrase, "never use select * in
production code".

Jamie.

--

 

[Jamie Collins]

Do a google search for the exact phrase, "never use select * in
production code".

Just in case that doesn't turn this one up, see:

http://groups.google.com/group/microsoft.public.access/msg/754607e4b53e5c39

Jamie.

--

 

[ChrisM]

In message (e-mail address removed),

Just in case that doesn't turn this one up, see:

http://groups.google.com/group/microsoft.public.access/msg/754607e4b53e5c39

Jamie.

Just for the record, my actual production query DOES have a list of fields.
It was just simpler for the example to say 'SELECT *'

 

[ChrisM]

In message (e-mail address removed),

In message (e-mail address removed),

ChrisM wrote:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

It would appear to be logically equivalent to an equi-join, so you
could try:

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

Great idea Jamie, and answers my question as asked perfectly, run
pretty quick too The trouble is my question was simplified...
What I'm actually trying to do is more like:

UPDATE partsTable
SET partsTable.MinStock = 99
WHERE partsTable.Part_No IN
(
SELECT .......
)

What about:

UPDATE partsTable
INNER JOIN [PART-XREF]
ON partsTable.Part_No = [PART-XREF].PN2
SET partsTable.MinStock = 99
WHERE [PART-XREF].PN1 = [RequiredPartNo];

Jamie.

Same problem - 'Operation must use an updatable query'

What's partsTable, then? Is it a table in a Jet database, or in some
other DBMS? If it's in some other DBMS (or if it's a query), is it
updatable when you use it by itself?

partsTable is just a simple Jet table (~15K rows and about 15 fields)
See my reply to Jamie elsewhere in this thread though, my problems seem to
be that PART-XREF is a fairly complex query - most specifically, it contains
a UNION which seems to upset Access rather when it comes to updatable
queries...

 

[BruceM]

Not surprisingly, I found writings from people who believe that one should
"never use SELECT * in production code". I also know that anything I want
to find on the web to support any point of view about almost anything
whatsoever is available if I use the right search parameters, so I'm not
convinced.
I have browsed around a number of Access-related web sites. "SELECT *"
tends to be identified (including by people for whom I have a high regard
based on their contributions here) only as a way of selecting all fields.

 

[David F Cox]

I would imagine that Access would have no way of optimizing the results from
a union query, i.e. no help from an index, and so would be bound to be as
slow.as it could be.

I would look at that union query and see if there was some way of reducing
the result set from it to only produce pertinent records, i.e. with more
stringent where clauses in each of the four queries.

In message (e-mail address removed),

In message (e-mail address removed),
Jamie Collins <[email protected]> Proclaimed from the tallest
tower:
ChrisM wrote:

SELECT * FROM partsTable
WHERE partsTable.PART_NO IN
(
SELECT PN2 FROM PART-XREF WHERE PN1 = [RequiredPartNo]
)

It would appear to be logically equivalent to an equi-join, so you
could try:

SELECT partsTable.*
FROM partsTable
INNER JOIN PART-XREF
ON partsTable.PART_NO = PART-XREF.PN2
WHERE PN1 = [RequiredPartNo];

Great idea Jamie, and answers my question as asked perfectly, run
pretty quick too The trouble is my question was simplified...
What I'm actually trying to do is more like:

UPDATE partsTable
SET partsTable.MinStock = 99
WHERE partsTable.Part_No IN
(
SELECT .......
)

What about:

UPDATE partsTable
INNER JOIN [PART-XREF]
ON partsTable.Part_No = [PART-XREF].PN2
SET partsTable.MinStock = 99
WHERE [PART-XREF].PN1 = [RequiredPartNo];

Jamie.

Same problem - 'Operation must use an updatable query'

What's partsTable, then? Is it a table in a Jet database, or in some
other DBMS? If it's in some other DBMS (or if it's a query), is it
updatable when you use it by itself?

partsTable is just a simple Jet table (~15K rows and about 15 fields)
See my reply to Jamie elsewhere in this thread though, my problems seem to
be that PART-XREF is a fairly complex query - most specifically, it
contains a UNION which seems to upset Access rather when it comes to
updatable queries...

 

[Jamie Collins]

Not surprisingly, I found writings from people who believe that one should
"never use SELECT * in production code". I also know that anything I want
to find on the web to support any point of view about almost anything
whatsoever is available if I use the right search parameters, so I'm not
convinced.

In my experience it's one of those things you feel you can get away
with until it bites you so bad you resolve to never do it again after
which you try and warn others. If it was just me saying it you may not
give it much credit but it's not just me: SQL authors, SQL Server MVPs,
etc. Sure, you have to choose your authorities with care...

I have browsed around a number of Access-related web sites. "SELECT *"
tends to be identified (including by people for whom I have a high regard
based on their contributions here) only as a way of selecting all fields.

Just be sure you are not making the mistake of seeking solace for
something that falls short of 'best practice' (which IMO is to
enumerate the required column names in VIEWs/PROCEDUREs - Queries - and
review and revise them when base tables are altered). I don't mean to
slight anyone here but I am a firm believer in looking to the *whole*
SQL community.

Jamie.

--