May 1, 2012 at 9:59 am
I am trying to understand a few things about how data moves through the network with SQL Server and which servers are actually doing the legwork when queries are run via Microsoft Access. Let's pretend we have a setup like this:
1) SQL Server 2008 R2 Enterprise with 24 SAS drives in the 4U enclosure
2) Application server inside the network running Microsoft Access 2012
3) Microsoft Access database on a SAN
Let's pretend a user connects to a 600 million row table in SQL via ODBC in MS Access and groups on 10 columns and sums on 5 columns into a make table query. Can someone explain how the data moves through the network and which servers are processing the data? Here is how I see it going down:
1) The query is set across the network and is received by SQL Server
2) SQL Server processes the query
3) Once the query is complete, the results are transferred across the network to the application server and then through the network again to the SAN where it is saved into a table in MS Access
This is probably over simplifying it, but where am I going wrong?
May 1, 2012 at 10:37 am
Triality (5/1/2012)
I am trying to understand a few things about how data moves through the network with SQL Server and which servers are actually doing the legwork when queries are run via Microsoft Access. Let's pretend we have a setup like this:1) SQL Server 2008 R2 Enterprise with 24 SAS drives in the 4U enclosure
2) Application server inside the network running Microsoft Access 2012
3) Microsoft Access database on a SAN
Let's pretend a user connects to a 600 million row table in SQL via ODBC in MS Access and groups on 10 columns and sums on 5 columns into a make table query. Can someone explain how the data moves through the network and which servers are processing the data? Here is how I see it going down:
1) The query is set across the network and is received by SQL Server
2) SQL Server processes the query
3) Once the query is complete, the results are transferred across the network to the application server and then through the network again to the SAN where it is saved into a table in MS Access
This is probably over simplifying it, but where am I going wrong?
This is a tricky issue. I'm assuming SQL Server tables are linked on MS-Access, is that correct.
If this is the case please note that there are two database engines at work, jet database engine - or whatever is its current name - on the MS-Access side and SQL Server database engine on the SQL Server side. These two have to talk, understand each other, figure out how to run the query and hopefully return the right result set to the user that happily submitted a query on the MS-Access side.
If user gets lucky query gets properly parsed, translated and executed on the SQL Server side then only the result set travels over the network.
If user is not that lucky Jet sends to SQL Server just a piece of the query, usually the FROM clause, gets the whole content of the tables over the network then processes the WHERE clause on the MS-Access side. As you can see, in this case whole tables are traveling over the network.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 1, 2012 at 10:48 am
Thank you very much for your response, Paul. You are correct, I am talking about a linked SQL table in MS Access.
This actually helps quite a bit in explaining why some queries take FOREVER / dont run at all when they are built in MS Access and just a minute or two when run from Management Studio.
Is there a way to tell whether the user "got lucky" and the query was properly parsed vs when they "got unlucky"? In the case they are unlucky, the data is transferred over the network and then into RAM on the application server? Or is it into temporary files on the application server local hard drive? In the case they are lucky, is the result set loaded into RAM or into temporary files on the local hard drive of the application server? Or neither?
May 1, 2012 at 10:52 am
Some of it depends on the query itself in Access. If you are joining a SQL table to an Access table, hope the table in SQL is small as it is all coming over.
May 1, 2012 at 11:02 am
Triality (5/1/2012)
Thank you very much for your response, Paul. You are correct, I am talking about a linked SQL table in MS Access.This actually helps quite a bit in explaining why some queries take FOREVER / dont run at all when they are built in MS Access and just a minute or two when run from Management Studio.
Is there a way to tell whether the user "got lucky" and the query was properly parsed vs when they "got unlucky"? In the case they are unlucky, the data is transferred over the network and then into RAM on the application server? Or is it into temporary files on the application server local hard drive? In the case they are lucky, is the result set loaded into RAM or into temporary files on the local hard drive of the application server? Or neither?
Answering your question, I understand that MS-Access creates temporary tables on the client machine.
The easiest way to avoid this issue is to rely on "pass-through queries". I think you can select this "type" of query when writing a query in MS-Access.
Pass-through queries MUST be written following Transact-SQL syntax; they are sent as they are to the SQL Server database skipping the Jet engine (and the parsing and translating), they process on the SQL Server back end and only return the resulting databaset over the network.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 1, 2012 at 11:18 am
In my experience, unless the queries are pass-through queries, Jet is very fond of pulling all data local and then running the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2012 at 11:18 am
Lynn - very good info to know. Thanks for sharing!!
Paul - If the MS Access database is saved on the SAN, do you know if the temporary tables are created on the local hard drive of the application server, or if they are created on the SAN?
Is it possible to create "pass-through queries" using the Query Designer or is it only possible when you actually write the T-SQL in SQL view?
May 1, 2012 at 11:19 am
GilaMonster (5/1/2012)
In my experience, unless the queries are pass-through queries, Jet is very fond of pulling all data local and then running the query.
Is seems like it completely defeats the purpose of storing the data in SQL and using the enterprise level hardware on the SQL server to process the query. The hardware in the application server is nowhere close to the specs of the SQL box. Frustrating!
May 1, 2012 at 11:21 am
GilaMonster (5/1/2012)
In my experience, unless the queries are pass-through queries, Jet is very fond of pulling all data local and then running the query.
My typical experience as well working with a few power users.
May 1, 2012 at 11:26 am
MS Access is not an enterprise database solution. It's designed for small desktop databases.
You can get Access to just send the data to SQL, use pass-through queries. That said, it's not a great tool to use for large, data heavy enterprise applications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2012 at 11:29 am
The problem is this is what the data analysts have used for 10+ years. The data is far exceeding the 2GB file size limit of MS Access, but this is all they know how to use to query the data and run reports.
Do you have to write the pass-through queries by hand using SQL view or can the Query Design interface be used?
May 1, 2012 at 11:30 am
The power users I was taking about were using Access for analytics and reporting. They were few and I worked with them at times to ease the burden on our server.
May 1, 2012 at 11:32 am
Lynn it sounds very similar to our environment. These power users do amazing things with MS Access -- stuff you aren't supposed to be able to do. However, at times we run into issues with query performance.
May 1, 2012 at 11:44 am
Triality (5/1/2012)
Is it possible to create "pass-through queries" using the Query Designer or is it only possible when you actually write the T-SQL in SQL view?
You do it on Query Designer - just chose the "pass-through" type of query. Just remember that query syntax has to be Transact-SQL compliant, not MS-Access compliant.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply