should this simple query give Access problems?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this hand-written query that takes excessively long to process and I
was wondering what the reason may be. This is the query:

select count(*) from assets where assetno not in (
select assetno from history WHERE TransType = "MF"
AND TranDate >= [Forms]![UnivCriteriaFrm].[txtFrom] And
TranDate<=[Forms]![UnivCriteriaFrm]![txtTo])

As you can see, the query is pretty simple. The assets table has 22,000+
records and history has 10,000+ records. The subselect ("select assetno from
history WHERE TransType = "MF"...") only returns 68 records. So what's
Access doing that takes so long to generate? It consumes so much time that I
get "Not Responding" for the Access window.

Thanks.
 
I have this hand-written query that takes excessively long to process and I
was wondering what the reason may be. This is the query:

select count(*) from assets where assetno not in (
select assetno from history WHERE TransType = "MF"
AND TranDate >= [Forms]![UnivCriteriaFrm].[txtFrom] And
TranDate<=[Forms]![UnivCriteriaFrm]![txtTo])
Click to expand...

Access doesn't optimize NOT IN queries very well. Try using a
"frustrated outer join" instead. Save the subquery as qryExclude and
use

SELECT Count(*)
FROM assets LEFT JOIN qryExclude
ON assets.assetno = qryExclude.assetno
WHERE qryExclude.assetno IS NULL;
 
I have this hand-written query that takes excessively long to process
and I was wondering what the reason may be. This is the query:

select count(*) from assets where assetno not in (
select assetno from history WHERE TransType = "MF"
AND TranDate >= [Forms]![UnivCriteriaFrm].[txtFrom] And
TranDate<=[Forms]![UnivCriteriaFrm]![txtTo])

As you can see, the query is pretty simple. The assets table has
22,000+ records and history has 10,000+ records. The subselect
("select assetno from history WHERE TransType = "MF"...") only
returns 68 records. So what's Access doing that takes so long to
generate? It consumes so much time that I get "Not Responding" for
the Access window.

Thanks.
Click to expand...

I can think of two things to speed up the query: (1) make sure assetno,
TransType, and TranDate are indexed, and (2) rewrite the query to get
rid of the "Not In" construct. I'm told that the Jet database engine
doesn't handle that very well. You could rewrite the same query like
this:

SELECT Count(*)
FROM Assets
LEFT JOIN
(
SELECT AssetNo FROM History
WHERE TransType = "MF"
AND TranDate >= [Forms]![UnivCriteriaFrm].[txtFrom]
AND TranDate<=[Forms]![UnivCriteriaFrm]![txtTo]
) AS T
ON Assets.AssetNo = T.AssetNo
WHERE T.AssetNo Is Null;

Note: if this is to be a saved query, then when you save it Access will
rewrite its SQL slightly, replacing the parentheses around the subquery
with brackets. Annoyingly, if you later edit it, those brackets in
conjunction with the brackets around the form/control parameters will
cause a syntax error. You can get around that by restoring the
parentheses before you try to save the edited query.
 
Thanks.

But why do you guys check if it's null at the end (... IS NULL)?
None of the records have an AssetNo = NULL (they all have valid values).

Thanks,
VM
 
Thanks.

But why do you guys check if it's null at the end (... IS NULL)?
None of the records have an AssetNo = NULL (they all have valid
values).
Click to expand...

What the SQL statement is doing is creating a left join between the
Assets table and a query (a stored query in John's example, a subquery
in mine) that returns all the asset numbers we *don't* want. The left
join on asset nunber gives us every record from the Assets table, but
only the matching records from the subquery. That means that the
corresponding asset number in the joined records will be Null. So the
set of records returned by the join might look like this:

Assets.AssetNo T.AssetNo
---------------- ---------------------
1 1
2 Null
3 Null
4 4
4 4
4 4
5 Null
6 6
6 6

The records with Null in T.AssetNo are those for which there was no
match in the subquery. Those with duplicates are those for which there
were multiple matches in the subquery. We're only interested in
counting the unmatched records, though, so we apply a criterion of

T.AssetNo Is Null

That weeds out all the matched records, leaving only the unmatched ones
to be counted.
 
I had one last question on the Left Join...
Based on the table you drew,
Assets.AssetNo T.AssetNo
---------------- ---------------------
1 1
2 Null
3 Null
4 4
4 4
4 4
5 Null
6 6
6 6

the only two types of records returned are when 1)Assets.AssetNo and
T.AssetNo are the same value, and 2)when Assets.AssetNo has a valid value
and T.AssetNo is Null. Is that what the left join does? It's so I know
exactly how the left join works.
With a left join, these groups qre invalid and will not be generated:

Assets.AssetNo T.AssetNo
---------------- ---------------------
2 1
Null 6
Null Null

Thanks again.
 
I had one last question on the Left Join...
Based on the table you drew,
Assets.AssetNo T.AssetNo
---------------- ---------------------
1 1
2 Null
3 Null
4 4
4 4
4 4
5 Null
6 6
6 6

the only two types of records returned are when 1)Assets.AssetNo and
T.AssetNo are the same value, and 2)when Assets.AssetNo has a valid
value and T.AssetNo is Null. Is that what the left join does? It's
so I know exactly how the left join works.
With a left join, these groups qre invalid and will not be generated:

Assets.AssetNo T.AssetNo
---------------- ---------------------
2 1
Null 6
Null Null
Click to expand...

Right. The "On" clause of the join expression tells us which fields (or
expressions) we're looking for matches on. The join type, in this case
"LEFT", tells us whether to include any records that don't have matches
in the result set, and if so, from which table.

INNER JOIN (the default) = no unmatched records
LEFT JOIN = include unmatched records from the table on the left
RIGHT JOIN = include unmatched records from the table on the right

Both left and right joins are instances of what are called outer joins.
Some versions of SQL support a "full outer join", where unmatched
records from both sides of the join are included, but Jet SQL (the
native SQL version of Access) doesn't.

From your example above:
Assets.AssetNo T.AssetNo
---------------- ---------------------
2 1
Null 6
Null Null
Click to expand...

the result record (2, 1) can't happen, because the tables are joined on
Assets.AssetNo = T.AssetNo. To be included in the result set, a record
from T must have AssetNo = Assets.AssetNo. The result record (Null, 6)
can't happen, because the left join means we aren't taking any unmatched
records from T. The result record (Null, Null) can't happen because
Assets.AssetNo can never be Null; even if it could, and even if there
were also a record in T with AssetNo = Null, this stll couldn't happen
because Null is not equal to anything, not even Null, so the "On"
expression of Assets.AssetNo = T.AssetNo would not be met.
 
Back
Top