That is a great performance testing ... creating the view in SQlserver was the second solution I got , the first solution was to use ADO conn , over complex query... and I find that ADO was 10 times faster ... Thanks Master Philips...
Philipp does a bang up job of highlighting what Access does in the background when querying SQL Server, I was surprised to learn how Access is sending many queries instead of just one for processing the query. One aspect I’ve always heard in the past is that the ODBC driver is the one interpreting the queries for processing by SQL Server, in other words, is there a way to tell who is breaking up the queries, the Access engine or the ODBC driver? Regardless this video is a must see for anyone working with Access and SQL Server. Well done!
Juan, thank you for the feedback. You can use ODBC tracing to see the actual function calls to the ODBC driver. This shows that the driver gets the broken down queries from Access DBEngine before the driver even starts to process them.
I do all my querying exclusively in SQL Server. So seeing what access is doing is a complete shock to me. This is the kind of thing that makes me wonder why anyone would use access to connect to SQL server at all. The joins you were using are typical. It looks like we are better off creating server side views for every complicated join if Access needs to use SQL server. Compound this with no method of stopping MS Access without Killing it and it makes using it a totally bad idea. If used it should be kept off production servers. Microsoft needs to rethink this approach.
