Which SQL technique is smarter?

[MatthewGrace]

A recent thread made me question which way of doing the same thing is the "better" way. I'd like to know which is least demanding on the processor. I've pasted the queries and the results below, but it's basically an expression field containing a DMax() function or an expression field with a nested Select Max() statement. From behavior, I guess the nested select is more versatile because you can include a WHERE clause, but the DMax() resultset has the advantage of still being able to append new records. That's nice and all, but I'm wondering which executes faster.



Does the processor really have to run every single row against its own DMax() or nested Select? Can't the "brain" of the database first solve for the Max value only one time, and then remember that value for use in each row of the expression field?

[ranman256]

do NOT use Dmax in a query. Its running twice as many functions.
The query IS the Dmax, so just run the query total.

[CJ_London]

fastest way would be to use a second query

SELECT *
FROM Table1 T LEFT JOIN (SELECT Max(reportDate|) as MaxDate FROM Table1) M ON T.ReportDate=M.MaxDate

Other thing that will affect performance is indexing - if table is long, then index reportdate

[MatthewGrace]

Ajax: very interesting - I never thought to do it this way. I entered the SQL you gave me, and in design view it is represented visually but not in the fields below. Nevertheless, it is not complicated I get exactly what you mean.

Ranman: Thank you for your input, but I'm not sure I understand. Are you suggesting DMax should never be used in a function, if so why? Do you agree with Ajax's concept?

Both: After looking at my graphic above, notice the expression field contains either DMax or SELECT... am I correct in assuming that Access needs to run again and again and again that same Dmax or SELECT for every single row in the resulting recordset? If so, I can see why that would be resource demanding.

[CJ_London]

am I correct in assuming that Access needs to run again and again and again that same Dmax or SELECT for every single row in the resulting recordset?

for your two versions - yes, although for the subquery, the dbengine will do some optimising. You might find this link of interest

https://www.experts-exchange.com/art...Functions.html