SQL Server

I'm currently using MS-Access to write reports using a SQL Server db as the 
data source in a client/server arrangement.

The database is sufficiently large (2gb) that data retrieval speed is an 
issue.

The current setup is linked tables to the SQL Server db via ODBC, and then 
the usual queries.

1. Can views and/or stored procedures on the SQL Server side along with 
paramaterized queries provide a significant performance boost?

2. I've read that views and stored procedures can be created w/i Access. Is 
this the same as creating them in SQL Server?

3. Are there other things I should try? 


0
JimP
6/8/2007 6:22:02 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
668 Views

Similar Articles

[PageSpeed] 4

2 Questions

1. Where is the processing done in a pass through query?

2. One of the SQL Server programmers is pushing stored procedures, I guess 
because the processing is done on the server and you're not relying on ODBC 
to carry large amounts of data. Do you think a stored procedure for a 
complex query is likely to have a significant performance boost vs a 
traditional ODBC connection, linked tables and query?

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message 
news:3EB6BC75-9617-47E2-9E89-C2D6EF97D4CE@microsoft.com...
> If you don't need to link to Access tables, I would consider using only
> pass-through queries. If you need to change the criteria of your 
> pass-through
> query, you can use a couple lines of DAO code to change the SQL property 
> of
> the p-t.
> -- 
> Duane Hookom
> Microsoft Access MVP
>
>
> "JimP" wrote:
>
>> I'm currently using MS-Access to write reports using a SQL Server db as 
>> the
>> data source in a client/server arrangement.
>>
>> The database is sufficiently large (2gb) that data retrieval speed is an
>> issue.
>>
>> The current setup is linked tables to the SQL Server db via ODBC, and 
>> then
>> the usual queries.
>>
>> 1. Can views and/or stored procedures on the SQL Server side along with
>> paramaterized queries provide a significant performance boost?
>>
>> 2. I've read that views and stored procedures can be created w/i Access. 
>> Is
>> this the same as creating them in SQL Server?
>>
>> 3. Are there other things I should try?
>>
>>
>> 


0
JimP
6/9/2007 12:12:05 PM
1. The processing is done on the server if you use a pass-through query.

2. Listen to the guy, he obviously knows what he is talking about.  When you 
use stored procedures, the performance gains are huge as the query execution 
plan is stored with the procedure on the server.  This means that the query 
optimiser has to do less work and your server is not bogged down with ad-hoc 
queries.

Stored procedures are deffinitely the way to go.

MH

"JimP" <jpockmire@houston.rr.com> wrote in message 
news:466a98fc$0$16669$4c368faf@roadrunner.com...
>2 Questions
>
> 1. Where is the processing done in a pass through query?
>
> 2. One of the SQL Server programmers is pushing stored procedures, I guess 
> because the processing is done on the server and you're not relying on 
> ODBC to carry large amounts of data. Do you think a stored procedure for a 
> complex query is likely to have a significant performance boost vs a 
> traditional ODBC connection, linked tables and query?
>
> "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message 
> news:3EB6BC75-9617-47E2-9E89-C2D6EF97D4CE@microsoft.com...
>> If you don't need to link to Access tables, I would consider using only
>> pass-through queries. If you need to change the criteria of your 
>> pass-through
>> query, you can use a couple lines of DAO code to change the SQL property 
>> of
>> the p-t.
>> -- 
>> Duane Hookom
>> Microsoft Access MVP
>>
>>
>> "JimP" wrote:
>>
>>> I'm currently using MS-Access to write reports using a SQL Server db as 
>>> the
>>> data source in a client/server arrangement.
>>>
>>> The database is sufficiently large (2gb) that data retrieval speed is an
>>> issue.
>>>
>>> The current setup is linked tables to the SQL Server db via ODBC, and 
>>> then
>>> the usual queries.
>>>
>>> 1. Can views and/or stored procedures on the SQL Server side along with
>>> paramaterized queries provide a significant performance boost?
>>>
>>> 2. I've read that views and stored procedures can be created w/i Access. 
>>> Is
>>> this the same as creating them in SQL Server?
>>>
>>> 3. Are there other things I should try?
>>>
>>>
>>>
>
> 


0
MH
6/9/2007 12:39:06 PM
JimP wrote:
> 2 Questions
>
> 1. Where is the processing done in a pass through query?

By definition a passthrough query is one where a SQL statement is "passed 
through" to the server so ALL processing of the query is done on the server.

> 2. One of the SQL Server programmers is pushing stored procedures, I
> guess because the processing is done on the server and you're not
> relying on ODBC to carry large amounts of data. Do you think a stored
> procedure for a complex query is likely to have a significant
> performance boost vs a traditional ODBC connection, linked tables and
> query?

It might, and it might not.  Each situation is different.  However, the more 
complex the query the more likely it will be that a passthrough or stored 
procedure will be the better way to do it.

Join queries in particular are better done on the server whenever possible.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
6/9/2007 7:10:50 PM
...thank you, all.

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message 
news:UWCai.16963$C96.1100@newssvr23.news.prodigy.net...
> JimP wrote:
>> 2 Questions
>>
>> 1. Where is the processing done in a pass through query?
>
> By definition a passthrough query is one where a SQL statement is "passed 
> through" to the server so ALL processing of the query is done on the 
> server.
>
>> 2. One of the SQL Server programmers is pushing stored procedures, I
>> guess because the processing is done on the server and you're not
>> relying on ODBC to carry large amounts of data. Do you think a stored
>> procedure for a complex query is likely to have a significant
>> performance boost vs a traditional ODBC connection, linked tables and
>> query?
>
> It might, and it might not.  Each situation is different.  However, the 
> more complex the query the more likely it will be that a passthrough or 
> stored procedure will be the better way to do it.
>
> Join queries in particular are better done on the server whenever 
> possible.
>
> -- 
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com
> 


0
JimP
6/11/2007 12:16:20 PM
Reply:

Similar Artilces:

Online Quotes Server
I am running Money 2004 with Windows XP and like so many others lately (since January)I have been experiencing increasing problems with online stock quote downloading. At this point it virtually does not work at all unless you happen to time it right. I find that there is a better probability of having it work after 8 or 9pm EST but not always. Everything else works fine (bank downloads, etc). So I contacted Microsoft and they implied that if you are able to update quotes sometimes but not all the time, then it could be a result of some issues that they are currently experiencing w...

Making all outgoing email goes out through the front-end server?
I have a one back-end and one front-end Ex2k3 server. There is still a 5.5 server connected that needs to be decommissioned as it no longer has active emails or public folders. I have a routing group with all three as members. Currently, I am having delivery problems, especially to AOL. The reverse DNS pointer record goes to the front-end server on a DMZ. BUT when user send email to outside domains it goes out through the default gateway = not the reverse DNS IP address. Any suggestions? I tried adding the smart host to the routing group, but it didn't seem to work. Any suggests wou...

any easy method to change Dynamics SQL server to multilingual
Hi; At the time of setting up SQL server for my GP Dynamics, I just picked the default setting, I did not pick "Binary order, for use with the 850 (Multilingual) character set." What is the procedure and easy way to change my SQL backup to multilingual character set? Thanks! Reinstall SQL and create the company. DTS the data from the old tables/database into the new tables/database. Mark to delete data in destination tables first and unmark Enable Identity Insert. "KANE" <KANE@discussions.microsoft.com> wrote in message news:08AA05E5-CE15-4EEF-9ADD-68FFE4...

Converting SQL database to Access database
I am not too familiar with either of these products. Can someone tell me if an SQL database can be viewed and manipulated through Access? If not how can I convert the SQL database into an Access database. Thanks. Chirvan You can use the Access Table linking feature to view tables from SQL server via ODBC. If you have a unique index on the table in SQL Server, then you can manipulate the data. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Chirvan" <cedross@apos.on.ca> wrote in message news:071301c37de8$09e...

connect exchange server to pop3 server
Currently running pop3 and plan to switch to exchange server. I would like to be able to connect to the pop3 to download emails while switching over. If you use Small Business Server there is an included POP3 connector. Otherwise you will need to purchase a 3rd party connector to retrieve the emails. You will hear this recommendation from others ... if you're going to the trouble to get Exchange, have mail delivered to it directly instead of retrieving POP3. Cheers, Karan Mavai <anonymous@discussions.microsoft.com> wrote in message news:031801c3d615$e9ef0980$a101280a@phx.gbl... ...

Connecting to MS SQL Server 2008 Express
I am using a demo for RMS 2.0 and installed MS SQL Server 2008 Express. Using the Store Operations Administrator Configuration screen I am getting the following listed below when trying to connect. Any clues as to my problem would be greatly appreciated... Error #-2147217843 Login failed for user 'Mike' (Source: Microsoft OLE DB Providor for SQL Server) (SQL State: 42000) (NativeError: 18456) No Help file avaialbe You are tring to log into an SQL server with the SQL Authentication and the username Mike. This error would indicate the user Mike does not exist, does n...

Management reporter Specify Server Connection
Hello, I am installing the client version of Management reporter but do not know how to find the SQL server connection. Could someone point me in the right direction. thanks ...

Explanation of Exchange Server
Help! I am trying to figure out if changing from a peer-to-peer to Microsoft Small Business Server in order to utilize Outlook w/Business Contact Manager is right for my organization. I need an explanation of MAPI enabled vs. non-MAPI enabled Exchange Server. We utilize one mail server externally for our email. We do need to be able to view each other's calendar. Jennifer, Please take a look at http://www.microsoft.com/exchange/evaluation/features/default.asp In order to effectively use the group collaboration features of Exchange you will need to either use MAPI enabled Outloo...

Publishing files to a web server
I am writing an ASP.NET web service to enable publishing of content from a SQL server database to an internal web site. I am new to the .NET world and am seeking advice on the most efficient way to perform this using ASP.NET? In a previous language (Delphi), I achieved this by creating a file on the local disk and then used an FTP component to transfer the file via FTP to the web server. I have looked at the WebClient UploadFile method to upload the file from the local drive to a virtual directory on the web server (but get the message "Method" not allowed). I have also i...

MTA not working on new server after SP4
I have Enterprise 5.5 with (2) NT4 SP6a servers working great. We are not using Active Dir yet, still PDC-BDC. Needed to replace one of the old servers with newer hardware. Set up a new server with Win2K SP4 then loaded Exch 5.5 joining an exisiting site. Moved a couple of test mailboxes and it worked OK.... until I loaded Exchange SP4 on the new server. Now the MTA doesn't pass mail to the new server. In fact I don't even see the old servers listed in my queues on the new server, but they were there before the SP4. One of my old servers is running Exch 5.5 with SP2 an...

Removing old server address from Outlook 2003
I just changed web hosts. When I configured Outlook for the new mail server, I removed the old server address form the e-mail accounts. I'm getting mail OK from the new server, but the old server address still pops up when I log in or access e-mail. I can cancel thru that pop-up; but I want to get it out of there. The old server is no longer showing in the e-mail accounts lists; so where do I go to get it out of there now? Win XP Pro SP3...Dell Precision 360 Firefox (current edition) Old mail host: Yahoo New Mail host: Blue Host This might indicate a corruption in you...

the OWA server rename
Hello, We are running AD 2003, Exchange 2003 on Server 2003 with about 100 mailboxes connected with OWA server. Initially when we configured the OWA and Exchange server we named them as ADEA_MAIL and ADEA_OWA. Now the problem is when emails are delivered from our domain, some of the recipients end checks the Reverse DNS and finds out that our ADEA_OWA is out of the naming convention format and rejects the email. Is there a way to easily resolve this issue? Or is it better for us to change the server name to the standard format? What are the consciences if any? Thanks Sunu...

which client do you need to install on Citrix server to publish?
There is a normal version desktop client and there is a special client microsoft outlookj client for windows terminal server as well. Just wondering which of these version you need to install on Citrix server to publish through Citrix? Cheers Kyaw We use the desktop client. "Kyaw Zay Ya" wrote: > There is a normal version desktop client and there is a special client > microsoft outlookj client for windows terminal server as well. > > Just wondering which of these version you need to install on Citrix server > to publish through Citrix? > > Cheers &g...

Increase Server timeout (Max 5 Minutes)
My Outlook 2000 allows a maximum sever timeout of 5 minutes Im trying to receibve a 9 MB email attachement. Outlook times out before this is fully downloaded. Is there any solution to this Please use Reply to Group - dont reply to me direct as Spam settings will not allow. Thanks Sam Sam, Before sending a 9MB file I would zip it to reduce the size. Not only due to the limits on increasing the timeout for the server, which is mostly likely setup not to go beyond a certain amount because more needed may indicate an issue. Additionally, opening let alone receiving a 9MB file on the o...

Connecting to SQL Server DB in Windows 7
I've got an Access Front end program that connects to a SQL Database back end. Everything works in Windows XP; but on the one computer running Windows 7 I cannot get the Access application to connect to the SQL Database. My ODBC Connection string looks something like this: ODBC;DSN=MyConnection;APP=My App;Database=bedb;UID=sa;PWD=MyPassword;TABLE=dbo.tblFirstTable I've tried going into Admin Tools, and creating the datasource first, it connects without any problem. Is there something that has changed in Windows 7, that requires me to either alter a setting somewhere, ...

Small Business Server Exchange on 2003 Standard Server?
Does anyone know if its possible to install Windows Small Business Server Premium Exchange(Exchange Only) onto a Windows 2003 Server standard edition. Either out of the box or with some kind of work around? Not possible, sorry. You'd need to buy Exchange 2003 separately to install it on another server. Reily411 wrote: > Does anyone know if its possible to install Windows Small Business > Server Premium Exchange(Exchange Only) onto a Windows 2003 Server > standard edition. Either out of the box or with some kind of work > around? ...

Restoring Exchange Server 2003
Hi, I have excchnage mail boxes and public folders backedup on tapes using veritas 9.1 from my exchnage server named "A ".I was just running a test to restore the exchnage database. I did the following: - Installed onother exchnage server named "B" in same domain, same organization, same adminstrative group. - I unmounted tha databae on B server and also configured the database so that it can be overwritten using Exchnage System Admin utility. Server A and B are Windows Server 2003 woth SP1. When I run restore from Veritas the job just habgs up at first mail box ...

Project Server Cube Build Issue
I think I screwed up the install order on the SQL DMO components. According to MSDN: " At this time, do not use the feature packs for SQL Server 2008 to install and download these components, as those files will not allow you to build cubes in SQL Server 2008 Analysis Services with Project Server 2007." However, I didn't read that carefully enough and installed the Microsoft SQL Server 2008 Management Objects Collection instead of the 2005 version. Now I can't install the 2005 version even if I uninstall the 2008 version. The setup fails with the "highe...

Windows mail syncronization with other mail server
I recently configured windows mail and configured it for synconization with t-online mailserver. Standard configuration was that the mails on the other mailserver will be deleted after 5 days. However, all incoming mails on my t-online mailserver have been deleted immediately after they were transfered to windows mail. Questions: 1. who is responsible for that? 2. How can I get the mails back to my t-online mailserver? Is there another computer or device (smartphone?) checking that same mail account? Other than that, I don't know what might cause that. Try unchecking the '...

Public folders lost due to server crash
I have an exchange 5.5 site that has two servers the original and a new server. All the mailboxes have been moved to the new server but the public folders have not. The original server has crashed. The question is can I restore/copy the pub.edb from the original to the new server and be able to access it from the new server ? TIA Darryl No. You'll need to restore the server and then restore the public folder store. Then, you can replicate the folders to the new server. -- Teo Heras MCSE, CCNA Blog: http://teoheras.blogspot.com/ "Darryl Burnette" wrote: > I ha...

How to remove server from admin grps
Hell-o all, I have a server in the administrator grp that I need to remove/delete from the ESM program. Can anyone advise how this is done or direct me to a doc? TIA, Gary "Gary Hornbeck" <hornbeck@siskiyous.edu> wrote: >I have a server in the administrator grp that I need to remove/delete from >the ESM program. Can anyone advise how this is done or direct me to a doc? If you ran the setup program on the server and elected to remve the Exchange software the server shold hav disappeared from the AD and the ESM. If you failed to do that, and the server was an Exchan...

Restore exchange server 2000 to a disaster recovery machine
Hi all I can't quite get my head around this. I want to be able to restore my exchange server onto another machine for disaster recovery purposes. Every day i want to be able to take a tape thats a backup from the live server to the offsite disaster recovery server (drs) I have set up the DRS to have the same domain name etc and user names. What do I need to do now. I cannot mount the stores due to all sorts of error messages.I have looked at Q297289 but thats a scenario when you wish not to use the live server anymore Regards Craig Domain name does not need to be the same. Setup a n...

Posting XML to Server from ASP.NET/C# webapp
Hi All, I am trying to build an class that will POST XML to a merchant Gateway. The XML represents a customer transaction. I have no idea how to go about posting anything other then a collection of name value pairs. Can someone please give me a hand. I have been searching for a few days. I've come up with 1 very vague VB example from 1999. I'm currently developing in .NET 2.0 and 1.1. Thanks Rich Williams wrote: > I am trying to build an class that will POST XML to a merchant Gateway. The > XML represents a customer transaction. I have no idea how to go about posting ...

Cannot see resources in project server
i can see the projects but i cannot see the resources. It helps if you specify what version of Project Server are you on? Also, are you sure there are resources in your project server environment? Are they assigned to tasks in your published projects. If you still cannot see them then either your resource center views are not enabled for your security group and category under Server Settings>Manage Users or Manage Groups or you permissions do not allow you to view resource information. Are you supposed to be the administrator? Jonathan "Alaa Yehia" <Ala...

Requesting data from frontend exchange server
I have 1 front-end server in the DMZ for OWA and 1 back-end server with all mailboxes. When some internal ol2k2 users are creating or updating a meeting and Outlook is checking the free/busy information, they get the "Requesting data..." message. This message references the Netbios name of the front-end server which will fail due to firewall settings. I don't want the Outlook client to use this front-end server, how do I direct it to use only the back-end server? ...