A question abou evaluation of the WHERE clause

[Squik27]

I have a SQL statement that has a sub-query inside the WHERE clause. The
value of this sub-query changes every week.

Wouldn't it be better to calculate that value only once and then use that
value in the outer query through vb code?

Thank You

 

[Dirk Goldgar]

I have a SQL statement that has a sub-query inside the WHERE clause. The
value of this sub-query changes every week.

Wouldn't it be better to calculate that value only once and then use that
value in the outer query through vb code?

You mean, calculate it once a week and store the value somewhere, then use
that stored value in the outer query, one way or another? The design
tradeoffs on this would be: increased run-time efficiency of the outer
query, vs. the need to implement a reliable weekly process to ensure that
the calculated value is always correct, and the danger that the calculated
value might *not* be up to date. The degree of the performance savings, and
the business impact of each option, would be significant factors in the
decision.

 

[John Spencer MVP]

It depends.

Is the sub-query a correlated sub-query. If not, that usually means the
sub-query runs one time and returns the value(s). If the query is a
correlated sub-query (it refers to a field in the main query) then it probably
runs once for each record in the main query.

Without knowing more about your query and sub-query that is as far as I will
go on commenting on the relative efficiency of varioius methods. Personnally
unless performance is really slow, I would continue to use the sub-query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[sniatecki danielle]

I have a SQL statement that has a sub-query inside the WHERE clause. The
value of this sub-query changes every week.

Wouldn't it be better to calculate that value only once and then use that
value in the outer query through vb code?

Thank You