Use Recordset as Form Recordsource (2 Viewers)

[Kevin_S]

You can have an impact on how queries are evaluated by Jet by separating them and nesting them.

If possible could you give an example of seperting and nesting queries so Jet sends only the nested query to the server and not the entire thing (compiling all into on single query)?

Thanks for expanding on querydefs - it was one of those verbage issues where I was pretty sure what you were talking about but not entirely... I must say that while I did quite a bit of reading last night on querydefs and the compilation, loading, and processing differences between stored queries and dynamic SQL your two paragraphs above regarding the use of, and differences between dynamic and querydefs was much easier to read and understand... thank you for taking the time to detail the difference between the two methods of returning recordsets.

In addition, I'm sure someone with your talents is extremely busy but I sincerely hope you are still working on your book as it is apparent you have a gift for making difficult concepts easy to interprit and understand...

Thanks Again,
Kevin

 

[Pat Hartman]

query1:
Select * From query2
Where MyFunction(query1.SomeField) > 10;

query2:
Select * From YourTable
Where YourDate > Date() - 14;

query2 selects rows with dates within the last 2 weeks. Query1 takes that recordset and using a user defined function does some calculation with SomeField and returns a result which is compared to 10. If you combined the two queries as in query3, the entire contents of YourTable would need to be ported to the client for processing. By splitting the criteria, you reduce the number of rows being requested to those with dates within the past 2 weeks and then further refine the selection locally.

query3:
Select * From YourTable
Where YourDate > Date() - 14 AND MyFunction(query1.SomeField) > 10;

Now, it is possible that Jet is smart enough to separate query3 into query1 and query2 but I don't know for sure. Details on how it determines what to send to the server are hard to come by but I am certain that the first example will force Jet to do the right thing.

Did you create a bound form and time it?