Temporary Disk

[Bruce]

Sylvain,

I did remove the [MM 04 cond] table. I originally used this with the theory
that each record in this table did not have any fields where [Check] =0. The
thought was that if the table is smaller 263 k records versus 1.3 gig in the
main table [MM 05 mst date] ( [MM 04 cond] is a condensed version of [MM 05
mst date].

What I ultimately ended up doing was Duplicating the [MM 05 mst date] table
in my sub query, and creating a temporary table with limited amounts of
records from the main table. This greatly improved the response times and did
not produce any temporary disk errors.


--
Bruce

Glad to hear that your query is not running correctly.

However, if we take a look at your speed problem, have you tried the other
suggestions like removing the [MM 04 cond] table table in the FROM clause of
the main query as suggested by John S. and removing the [MM 05 mst
date].[EquipmentID] condition in the Order By clause of the subquery or
trying to split your append query into two append queries?

 

[Bruce]

Sylvain,

Thanks to your input and ideas that got me to thinking other avenues to
approach this situation.
--
Bruce

Sorry: « Glad to hear that your query is *now* running correctly. ».

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Glad to hear that your query is not running correctly.

However, if we take a look at your speed problem, have you tried the other
suggestions like removing the [MM 04 cond] table table in the FROM clause
of the main query as suggested by John S. and removing the [MM 05 mst
date].[EquipmentID] condition in the Order By clause of the subquery or
trying to split your append query into two append queries?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I was able to get the append query to run with no errors by indexing Check
and NewCheck. Using records selection of 134 k, the query takes about two
hours to run. It is slow but at least it is now running without getting
the
error message.

Thanks to everyone for all of your help, it is most appreciated.

 

[Bruce]

David,

Not sure of what you meant by what I originally posted was like something
you would have posted??

 

[Sylvain Lafontaine]

Sylvain,

Just to clarify one of your statements. I understood you to state that
even
though a condition is not true, (regarding my sub query) Access will still
evaluate the condition. I sort of suspected this but had no way to verify.
Is
it possible to force Access to only evaluate when a condition is true?

Not with an IIF() call: this is a regular function; which means that every
of its parameters must be evaluated before the call to the function is made.
This is my opinion and it's also what I'm seeing when I'm using a linked
table and SQL-Server Profiler but other people like David disagree with it.

This was an append, I misinformed in my original statement, sorry. Did not
really understand the part where you said to make two append one where
check
= 0 and one where check <> 0.

The only way to deal with this would be to split your append query into two
queries; one with the condition check = 0 in its WHERE clause in one case
and the other with check <> 0. This way, you can remove the usage of the
IIF() function:

INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID, [MM 05 mst date].EquipmentID, [MM 05 mst
date].NewDate, [MM 05 mst date].FuelMiles, [MM 05 mst date].WOMiles, [MM 05
mst date].Check, (Select Top 1 [MM 04
cond].[Check] From [MM 04 cond] Where [MM 04 cond].[Check] >0 And [MM 04
cond].[EquipmentID] = [MM 05 mst date].[EquipmentID] And [MM 04
cond].[NewDate] < [MM 05 mst date].[NewDate] Order by [MM 04
cond].[NewDate]
DESC) AS NewCheck
FROM [MM 05 mst date]
WHERE ((([MM 05 mst date].EquipmentID)<="CP042"))
And ([MM 05 mst date].[Check]=0);


INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID, [MM 05 mst date].EquipmentID, [MM 05 mst
date].NewDate, [MM 05 mst date].FuelMiles, [MM 05 mst date].WOMiles, [MM 05
mst date].Check, [MM 05 mst date].[Check] AS NewCheck
FROM [MM 05 mst date]
WHERE ((([MM 05 mst date].EquipmentID)<="CP042"))
And ([MM 05 mst date].[Check]<>0);


If you want to, you can join these two append queries with an UNION ALL
inside a subquery but I don't see why you would really need to do this in
your case.

 

[david]

Well, I've tried it, and my tests show that only one side of the
condition is evaluated, but:

Sylvain says different, and
I haven't tested with 2007, and
As Sylvain pointed out, my test was not using a sub-query. (I'm
mostly concerned about UDF's)

Putting that aside, the way to encourage Access to not evaluate
a subquery is to use a WHERE condition so that it only queried
where the query needs to be evaluated. Almost as good, if you
can use a WHERE condition in the subquery, so that there are no
records in the subquery, the ORDER BY and TOP 1, which are
the slow parts, will be instant.

(david)

 

[Sylvain Lafontaine]

Yes, you're right: I created the following test to make the distinction
between using a subquery or not by first creating a function that will not
only return its argument but also print it:

Public Function msql(a As Variant) As Variant
Debug.Print CStr(a)
msql = "Hello: " & CStr(a)
End Function


Then I created the following two queries; one with a subquery and the other
without one. Table1 is a simple table with only an ID (IdTable), a column A
and two rows with 1 and 2 as the first two IDs:

SELECT IdTable, A, IIf(IdTable=1,msql(a),"Hi!") AS MsgTest
FROM Table1
WITH OWNERACCESS OPTION;


SELECT IdTable, A, IIf(IdTable=1, (Select msql(T2.a) from Table1 T2 where
T2.IdTable = T1.IdTable),"Hi!") AS MsgTest
FROM Table1 AS T1
WITH OWNERACCESS OPTION;


In the first case, the function msql() is not called each time but it is in
the second case, where a subquery is surrounding it. So the function II()
has a little more intelligence then what I expected first but not so much
more.

 

[David W. Fenton]

I found that if I removed the Order by statement, that the top
value is not retrieved by the sub query, so I had to put in back.

I've lost track of the original SQL, but are you saying it uses a
TOP N clause *plus* a GROUP BY? That seems really weird to me off
the bat, but I guess you could be summarizing totals and then
displaying the TOP N of the totals. But in that case, your GROUP BY
would take care of the order so there's no need for an ORDER BY. The
only situation where you'd need an order by on a GROUP BY to get
correct TOP N results would be if you want the *BOTTOM N* results.

 

[David W. Fenton]

Not sure of what you meant by what I originally posted was like
something you would have posted??

I'm very mean.