Also a few months ago - my exchange with Bart/Jose and Jose’s Screenshots of testing =)
Execution Time was 90 MS
Execution Time was 0 MS
“So it appears the assumption I made was correct, joining to the class object causes the “join” to occur in memory post SQL query so it would yield a much slower result… Good stuff!” - Jose
You need to always be aware that joining to an in memory structure is problematic. Sorry I did not review your question to dig it out.
I run into this in internal reviews as well. I still have not looked at your complete scenario but one of the things folks do a lot is push the ttrows (or some subset of columns) into the db as a ‘temp table’ and join against it. Very quick then even with millions of db records in partTran, etc type tables. The push of the data into the temp table and cleanup of records afterwards is many times faster than the ‘join to tt’
– Bart