|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
MS Access Query vs. SQL 7 Stored Procedure
Hello,
I herd somewhere that queries written in access that query a SQL database are run either partially or completely with the clients memory. I have 6 update queries that are in access that update tables in SQL. Each of these queries takes about 2 or so minuets. In an effort to speed things up a bit I thought to move them to stored procedures. Is this really going to make a bit a difference? -- -Scott Depends on the type of query... If you are doing lots of joins, and the
joins are occurring on the client side, it is likely that you will get a performance increase... in the end, the only sure way is to test... -- Show quoteHide quoteWayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "Scott Elgram" <SElg***@verifpoint.com> wrote in message news:uqtspttZFHA.3356@TK2MSFTNGP15.phx.gbl... > Hello, > I herd somewhere that queries written in access that query a SQL > database are run either partially or completely with the clients memory. > I have 6 update queries that are in access that update tables in SQL. > Each of these queries takes about 2 or so minuets. In an effort to speed > things up a bit I thought to move them to stored procedures. Is this > really > going to make a bit a difference? > > -- > -Scott > > Two minutes sounds too long to me if all the queries are doing is
updating data (and there are no expressions or user-defined functions running). The best way to tell what's going on is to stick a Profiler trace on the application and run the queries. Then you can see the exact calls that Access is passing to SQLS. Another option for update queries is to create pass-through queries, which requires that you use T-SQL, not Access-SQL syntax. Pass-through queries bypass the Jet engine, which is always involved with local Access queries. --Mary On Wed, 1 Jun 2005 11:54:58 -0700, "Scott Elgram" <SElg***@verifpoint.com> wrote: Show quoteHide quote >Hello, > I herd somewhere that queries written in access that query a SQL >database are run either partially or completely with the clients memory. > I have 6 update queries that are in access that update tables in SQL. >Each of these queries takes about 2 or so minuets. In an effort to speed >things up a bit I thought to move them to stored procedures. Is this really >going to make a bit a difference? There are two main bottlenecks to running client-side queries: network
traffic and client-side memory to handle the mass of data required to produce the local processing. If you can offload this activity to the server, you will speed up throughput. Even if you use pass-through queries, you must still submit the entire SQL statement accross the network. This is in contrast with a simple stored procedure name along with any necessary parameters. This is not so insignificant if you have multiple concurrent users attempting to run similar requests or users running similar requests multiple times. Sincerely, Anthony Thomas -- "Scott Elgram" <SElg***@verifpoint.com> wrote in message I herd somewhere that queries written in access that query a SQLnews:uqtspttZFHA.3356@TK2MSFTNGP15.phx.gbl... Hello, database are run either partially or completely with the clients memory. I have 6 update queries that are in access that update tables in SQL. Each of these queries takes about 2 or so minuets. In an effort to speed things up a bit I thought to move them to stored procedures. Is this really going to make a bit a difference? -- -Scott On Sun, 5 Jun 2005 23:11:08 -0500, "Anthony Thomas"
<ALTho***@kc.rr.com> wrote: >Even if you use pass-through queries, you must still submit the entire SQL I think you misunderstand what an Access pass-through query is. All>statement accross the network. This is in contrast with a simple stored >procedure name along with any necessary parameters. This is not so >insignificant if you have multiple concurrent users attempting to run >similar requests or users running similar requests multiple times. client applications must submit the entire command or SQL statement to the server across the network, this is not functionality unique to Access. Pass-through queries can also be used to execute stored procedures or any other T-SQL syntax. The advantage of a pass-through query lies in the fact that it is not parsed until it reaches the server, avoiding processing by the intermediate layers (Jet, OCBC). Once the pass-through query reaches SQL Server, it is processed by the server in the same way as other commands, with the query plan being cached, etc. Mary Yes, but that is kind of my whole point. You can use a pass-through query
in Access to gain control of a SQL Server stored procedure; however, it is the creation of that stored procedure where all the benefit lies. If you just take your Access query against Linked SQL Server tables, convert it to SQL Server T-SQL against the tables that define the Access Linked Tables, and plop all that syntax into pass-through query, you may have gained some index and execution processing benefits by offloading to the server, but not the same as if you had encapsulated that statement within a defined stored procedure. Also, a pass-through query isn't anything special native to Access, you could do the same thing from within SQL Server, using a pass-through query to Oracle instead of a Linked Server to Oracle. You get a similar effect. Sincerely, Anthony Thomas -- "Mary Chipman [MSFT]" <mc***@online.microsoft.com> wrote in message On Sun, 5 Jun 2005 23:11:08 -0500, "Anthony Thomas"news:0tdja113jl75g81abqoo2ajupdgu1gvfgi@4ax.com... <ALTho***@kc.rr.com> wrote: >Even if you use pass-through queries, you must still submit the entire SQL I think you misunderstand what an Access pass-through query is. All>statement accross the network. This is in contrast with a simple stored >procedure name along with any necessary parameters. This is not so >insignificant if you have multiple concurrent users attempting to run >similar requests or users running similar requests multiple times. client applications must submit the entire command or SQL statement to the server across the network, this is not functionality unique to Access. Pass-through queries can also be used to execute stored procedures or any other T-SQL syntax. The advantage of a pass-through query lies in the fact that it is not parsed until it reaches the server, avoiding processing by the intermediate layers (Jet, OCBC). Once the pass-through query reaches SQL Server, it is processed by the server in the same way as other commands, with the query plan being cached, etc. Mary I'm sorry, I'm not familiar with the terminology you are using and do
not understand what you are trying to say when you talk about "gain control of a stored procedure". Pass-through queries are useful for executing stored procedures using the EXECUTE statement, and a read-only result set is returned (if applicable). You cannot "plop" Access SQL into a pass-through query. It must be written using T-SQL syntax only. All client applications, not only Access, benefit from plan caching and efficient indexes in the SQL Server BE database. For more information, see "Execution Plan Caching and Reuse" in SQL Server Books Online. To view the actual SQL being sent and processed, create a Profiler trace. --Mary On Fri, 10 Jun 2005 12:12:46 -0500, "Anthony Thomas" <ALTho***@kc.rr.com> wrote: Show quoteHide quote >Yes, but that is kind of my whole point. You can use a pass-through query >in Access to gain control of a SQL Server stored procedure; however, it is >the creation of that stored procedure where all the benefit lies. > >If you just take your Access query against Linked SQL Server tables, convert >it to SQL Server T-SQL against the tables that define the Access Linked >Tables, and plop all that syntax into pass-through query, you may have >gained some index and execution processing benefits by offloading to the >server, but not the same as if you had encapsulated that statement within a >defined stored procedure. > >Also, a pass-through query isn't anything special native to Access, you >could do the same thing from within SQL Server, using a pass-through query >to Oracle instead of a Linked Server to Oracle. You get a similar effect. > >Sincerely, > > >Anthony Thomas Mary, yes, I know. This is a SQL Server forum. I'm quite aware of how
pass-through queries operate, and am very well aware of how MS Access works. Also, how much damage Access can do to the SQL Server databases it links to, either directly, or the misuse of pass-through queries, which was my only point. However, this post and question probably would have been better suited to the MS Access forum. Thanks for all or your responses. Sincerely, Anthony Thomas -- "Mary Chipman [MSFT]" <mc***@online.microsoft.com> wrote in message I'm sorry, I'm not familiar with the terminology you are using and donews:eanpa1lv6mialv0chda3590ea0m36mkns2@4ax.com... not understand what you are trying to say when you talk about "gain control of a stored procedure". Pass-through queries are useful for executing stored procedures using the EXECUTE statement, and a read-only result set is returned (if applicable). You cannot "plop" Access SQL into a pass-through query. It must be written using T-SQL syntax only. All client applications, not only Access, benefit from plan caching and efficient indexes in the SQL Server BE database. For more information, see "Execution Plan Caching and Reuse" in SQL Server Books Online. To view the actual SQL being sent and processed, create a Profiler trace. --Mary On Fri, 10 Jun 2005 12:12:46 -0500, "Anthony Thomas" <ALTho***@kc.rr.com> wrote: Show quoteHide quote >Yes, but that is kind of my whole point. You can use a pass-through query >in Access to gain control of a SQL Server stored procedure; however, it is >the creation of that stored procedure where all the benefit lies. > >If you just take your Access query against Linked SQL Server tables, convert >it to SQL Server T-SQL against the tables that define the Access Linked >Tables, and plop all that syntax into pass-through query, you may have >gained some index and execution processing benefits by offloading to the >server, but not the same as if you had encapsulated that statement within a >defined stored procedure. > >Also, a pass-through query isn't anything special native to Access, you >could do the same thing from within SQL Server, using a pass-through query >to Oracle instead of a Linked Server to Oracle. You get a similar effect. > >Sincerely, > > >Anthony Thomas Anthony Thomas wrote:
"However, this post and question probably would have been better suited to the MS Access forum." I actually posted this question in both forums. This one and MS Access. The only response I received in the Access forum was this; "Maybe yes, maybe no; it depends on the details of the query. But you could try changing them to pass-through queries, rather than creating stored procedures in the SQL Server database, and see the difference it makes. If your queries don't take parameters, there's probably no reason to make actual stored procedures." based on that and some other concerns my users had about manipulating these queries I left them as is in Access. -Scott Show quoteHide quote "Anthony Thomas" <ALTho***@kc.rr.com> wrote in message news:eDIDq37bFHA.2696@TK2MSFTNGP09.phx.gbl... > Mary, yes, I know. This is a SQL Server forum. I'm quite aware of how > pass-through queries operate, and am very well aware of how MS Access works. > Also, how much damage Access can do to the SQL Server databases it links to, > either directly, or the misuse of pass-through queries, which was my only > point. > > However, this post and question probably would have been better suited to > the MS Access forum. > > Thanks for all or your responses. > > Sincerely, > > > Anthony Thomas > > > -- > > "Mary Chipman [MSFT]" <mc***@online.microsoft.com> wrote in message > news:eanpa1lv6mialv0chda3590ea0m36mkns2@4ax.com... > I'm sorry, I'm not familiar with the terminology you are using and do > not understand what you are trying to say when you talk about "gain > control of a stored procedure". Pass-through queries are useful for > executing stored procedures using the EXECUTE statement, and a > read-only result set is returned (if applicable). You cannot "plop" > Access SQL into a pass-through query. It must be written using T-SQL > syntax only. > > All client applications, not only Access, benefit from plan caching > and efficient indexes in the SQL Server BE database. For more > information, see "Execution Plan Caching and Reuse" in SQL Server > Books Online. To view the actual SQL being sent and processed, create > a Profiler trace. > > --Mary > > On Fri, 10 Jun 2005 12:12:46 -0500, "Anthony Thomas" > <ALTho***@kc.rr.com> wrote: > > >Yes, but that is kind of my whole point. You can use a pass-through query > >in Access to gain control of a SQL Server stored procedure; however, it is > >the creation of that stored procedure where all the benefit lies. > > > >If you just take your Access query against Linked SQL Server tables, > convert > >it to SQL Server T-SQL against the tables that define the Access Linked > >Tables, and plop all that syntax into pass-through query, you may have > >gained some index and execution processing benefits by offloading to the > >server, but not the same as if you had encapsulated that statement within a > >defined stored procedure. > > > >Also, a pass-through query isn't anything special native to Access, you > >could do the same thing from within SQL Server, using a pass-through query > >to Oracle instead of a Linked Server to Oracle. You get a similar effect. > > > >Sincerely, > > > > > >Anthony Thomas > > Let me know how they perform. You will find that Access is really fast on
locally stored data, but when making calls outside, all sorts of bottlenecks pop up. If the amount of data isn't too great, you might not notice...until the data size starts to grow. Sincerely, Anthony Thomas -- "Scott Elgram" <SElg***@verifpoint.com> wrote in message Anthony Thomas wrote:news:ObY9UYDcFHA.2424@TK2MSFTNGP09.phx.gbl... "However, this post and question probably would have been better suited to the MS Access forum." I actually posted this question in both forums. This one and MS Access. The only response I received in the Access forum was this; "Maybe yes, maybe no; it depends on the details of the query. But you could try changing them to pass-through queries, rather than creating stored procedures in the SQL Server database, and see the difference it makes. If your queries don't take parameters, there's probably no reason to make actual stored procedures." based on that and some other concerns my users had about manipulating these queries I left them as is in Access. -Scott Show quoteHide quote "Anthony Thomas" <ALTho***@kc.rr.com> wrote in message news:eDIDq37bFHA.2696@TK2MSFTNGP09.phx.gbl... > Mary, yes, I know. This is a SQL Server forum. I'm quite aware of how > pass-through queries operate, and am very well aware of how MS Access works. > Also, how much damage Access can do to the SQL Server databases it links to, > either directly, or the misuse of pass-through queries, which was my only > point. > > However, this post and question probably would have been better suited to > the MS Access forum. > > Thanks for all or your responses. > > Sincerely, > > > Anthony Thomas > > > -- > > "Mary Chipman [MSFT]" <mc***@online.microsoft.com> wrote in message > news:eanpa1lv6mialv0chda3590ea0m36mkns2@4ax.com... > I'm sorry, I'm not familiar with the terminology you are using and do > not understand what you are trying to say when you talk about "gain > control of a stored procedure". Pass-through queries are useful for > executing stored procedures using the EXECUTE statement, and a > read-only result set is returned (if applicable). You cannot "plop" > Access SQL into a pass-through query. It must be written using T-SQL > syntax only. > > All client applications, not only Access, benefit from plan caching > and efficient indexes in the SQL Server BE database. For more > information, see "Execution Plan Caching and Reuse" in SQL Server > Books Online. To view the actual SQL being sent and processed, create > a Profiler trace. > > --Mary > > On Fri, 10 Jun 2005 12:12:46 -0500, "Anthony Thomas" > <ALTho***@kc.rr.com> wrote: > > >Yes, but that is kind of my whole point. You can use a pass-through query > >in Access to gain control of a SQL Server stored procedure; however, it is > >the creation of that stored procedure where all the benefit lies. > > > >If you just take your Access query against Linked SQL Server tables, > convert > >it to SQL Server T-SQL against the tables that define the Access Linked > >Tables, and plop all that syntax into pass-through query, you may have > >gained some index and execution processing benefits by offloading to the > >server, but not the same as if you had encapsulated that statement within a > >defined stored procedure. > > > >Also, a pass-through query isn't anything special native to Access, you > >could do the same thing from within SQL Server, using a pass-through query > >to Oracle instead of a Linked Server to Oracle. You get a similar effect. > > > >Sincerely, > > > > > >Anthony Thomas > > If you have performance problems with the queries as-is, create a
Profiler trace and take a look at the conversation between Access and your SQL Server. It's possible to bog down your network and server unless you restrict the data being fetched, so adhering to the golden rule of fetching only needed data will help minimize bottlenecks, regardless of how you write your queries in Access. Don't allow queries to link to entire tables, enforce restrictive WHERE clauses, and use a query-by-form interface, and you'll be able to support more users with less load on the server. --Mary On Mon, 13 Jun 2005 09:36:41 -0700, "Scott Elgram" <SElg***@verifpoint.com> wrote: Show quoteHide quote >Anthony Thomas wrote: > "However, this post and question probably would have been better suited >to the MS Access forum." > >I actually posted this question in both forums. This one and MS Access. >The only response I received in the Access forum was this; >"Maybe yes, maybe no; it depends on the details of the query. But you >could try changing them to pass-through queries, rather than creating >stored procedures in the SQL Server database, and see the difference it >makes. If your queries don't take parameters, there's probably no >reason to make actual stored procedures." > >based on that and some other concerns my users had about manipulating these >queries I left them as is in Access. > >-Scott > >"Anthony Thomas" <ALTho***@kc.rr.com> wrote in message >news:eDIDq37bFHA.2696@TK2MSFTNGP09.phx.gbl... >> Mary, yes, I know. This is a SQL Server forum. I'm quite aware of how >> pass-through queries operate, and am very well aware of how MS Access >works. >> Also, how much damage Access can do to the SQL Server databases it links >to, >> either directly, or the misuse of pass-through queries, which was my only >> point. >> >> However, this post and question probably would have been better suited to >> the MS Access forum. >> >> Thanks for all or your responses. >> >> Sincerely, >> >> >> Anthony Thomas >> >> >> -- >> >> "Mary Chipman [MSFT]" <mc***@online.microsoft.com> wrote in message >> news:eanpa1lv6mialv0chda3590ea0m36mkns2@4ax.com... >> I'm sorry, I'm not familiar with the terminology you are using and do >> not understand what you are trying to say when you talk about "gain >> control of a stored procedure". Pass-through queries are useful for >> executing stored procedures using the EXECUTE statement, and a >> read-only result set is returned (if applicable). You cannot "plop" >> Access SQL into a pass-through query. It must be written using T-SQL >> syntax only. >> >> All client applications, not only Access, benefit from plan caching >> and efficient indexes in the SQL Server BE database. For more >> information, see "Execution Plan Caching and Reuse" in SQL Server >> Books Online. To view the actual SQL being sent and processed, create >> a Profiler trace. >> >> --Mary >> >> On Fri, 10 Jun 2005 12:12:46 -0500, "Anthony Thomas" >> <ALTho***@kc.rr.com> wrote: >> >> >Yes, but that is kind of my whole point. You can use a pass-through >query >> >in Access to gain control of a SQL Server stored procedure; however, it >is >> >the creation of that stored procedure where all the benefit lies. >> > >> >If you just take your Access query against Linked SQL Server tables, >> convert >> >it to SQL Server T-SQL against the tables that define the Access Linked >> >Tables, and plop all that syntax into pass-through query, you may have >> >gained some index and execution processing benefits by offloading to the >> >server, but not the same as if you had encapsulated that statement within >a >> >defined stored procedure. >> > >> >Also, a pass-through query isn't anything special native to Access, you >> >could do the same thing from within SQL Server, using a pass-through >query >> >to Oracle instead of a Linked Server to Oracle. You get a similar >effect. >> > >> >Sincerely, >> > >> > >> >Anthony Thomas >> >> > If you do understand how an Access pass-through query works, you have
not communicated that based on your responses to the initial question. I am merely trying to correct the misinformation you are posting in response to that question. If a user has a question about accessing a SQL Server database, then this *is* the correct newsgroup. It is not helpful to allege that misuse of an Access pass-through query can "damage" a SQL Server database. That is just nonsense. If you know of such a scenario, you should be more specific in presenting facts to support your allegations. Any application can be poorly written, that is the fault of lack of knowledge of the developer, not intrinsic in the tool used. --Mary On Sun, 12 Jun 2005 21:13:37 -0500, "Anthony Thomas" <ALTho***@kc.rr.com> wrote: Show quoteHide quote >Mary, yes, I know. This is a SQL Server forum. I'm quite aware of how >pass-through queries operate, and am very well aware of how MS Access works. >Also, how much damage Access can do to the SQL Server databases it links to, >either directly, or the misuse of pass-through queries, which was my only >point. > >However, this post and question probably would have been better suited to >the MS Access forum. > >Thanks for all or your responses. > >Sincerely, > > >Anthony Thomas  
Other interesting topics
MS PSS-What the heck is this?!
Basic question.... on Index.... Problem with osql.exe -L full drives full trans log version 7 alter table drop column and dbcc cleantable SQL7 2 DB files down to one??? SQLServer Agent varchar vs nvarchar stored procedure with different criteria Server: Msg 7391, Level 16, State 1, Procedure <OBJECT>, Line |
|||||||||||||||||||||||
