September 11, 2009 at 1:51 pm
I came across the following at http://technet.microsoft.com/en-us/library/cc966377.aspx.
While researching how trying to find out exactly what would happen when an Access query operating against tables that are located on a SQL Server.
It is in the area towards the bottom of the chapter that discusses the "Query Engine Components", and specifically the Optimizer. (The jet Optimizer sounds remarkable similar to the SQL optimzer.)
"The algorithms that the optimizer uses depend on the accuracy of the statistics provided by the underlying engine. For example, the statistics that some ODBC drivers return may not be accurate. This can cause the optimizer to choose a less-than-optimal execution plan. However, if the whole query is sent to the ODBC server for processing (the usual case), the optimizer's execution plan is irrelevant. " (my italics)
This suggests to me that even though you don't specifically request a pass-thru query Access will pass it to SQL server to execute it anyway whenever possible.
A bit of informal testing on my part suggests that this is true. I created two queries, one a pass-thru, and the other just a standard query. Each query extracted about 1200 records from our item master which is about 30,000 rows long.
Both queries executed and returned the 1200 records in roughly 1 second.
However, I know from past experience that downloading the complete item master into my local machine takes considerably longer, suggesting that in both instances the query was actually executed on the server with only the results passing to my local machine.
Perhaps ODBC, Jet, and Access now integrate with SQL far better than expected?
October 9, 2009 at 11:16 am
Hello Phil,
Interesting article, especially because I, too, had cracked open my ODBC 2 book (QUE 1995) a few weeks ago: been having an intermittant problem connecting an SQL2005 Query Analyser OPENROWSET statement to an Excel. (I'm sure it's due to the this crappy Vista workstation I've been saddled with).
Also intersting was the debate about Access versus SQLServer versus Excel; All are good tools, but not all are the same tool. Hammer, screwdriver, endwrench... all have a purpose - just not the same. Too bad really; there are a lot of good tools: I cringe when I see someone stubornly using the same tool for all situations: just like I'd cringe seeing a sprinter in a 100 yard dash wearing hiking boots
Anyway: cheers and best from Denver
Mark
Just a cog in the wheel.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply