[dba-SQLServer] SQL Server ODBC optimization

Doug Murphy dw-murphy at cox.net
Fri Feb 6 15:55:01 CST 2009


Hello Dan and others,

I'll ask a little more specific question.  I have an Access FE connected to
SQL server BE via ODBC. The BE tables can hold many records so I was
thinking I could put a stored procedure on the BE to limit the number of
records coming over the network to the FE to just those of interest.  The
problem I have found is that the records are not updateable and I can not
insert new records. I was trying to not use linked tables, but that seem
unavoidable.  I am not in the position of being able to convert the FE to an
ADP so need to keep the forms etc connected to updatable record sources.
How do you folks approach this type of situation.

Thanks.

Doug

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, February 06, 2009 6:35 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SQL Server ODBC optimization

Hi Doug,

A few months ago I went through some conversion activities.  This is what I
learned:

1) To upsize an Access database to SQL Server, use the SQL Server Migration
Assistant (SSMA) for Access.  This is a free MS utility which is more
advanced than using the upsizing wizard, and will upsize queries in Access
to Views in SQL Server.  The manual for this utility has some good
instructions for what needs to be done prior to upsizing.  

2) On a LAN, the data transfer speed is perhaps 10% slower using Access FE /
SQL BE using ODBC table links.  However, on a LAN, no one will notice.  This
is an easy solution because your code can simply refer to the SQL table
links instead of the Access table links as long as the link names are the
same.

3) On a WAN, you don't want to use ODBC table links to SQL Server -
performance would be poor (but perhaps usable).  You'll want to use an OLEDB
Provider to make the connections.  Using an OLEDB Provider means that you'll
be rewriting your code significantly, because you won't have any table links
in the FE that your code can use!  This is a big learning curve - a good
start is 'Microsoft Access Developer's Guide to SQL Server.'  This is an
older book that uses Access 2000, but it's still valid and very helpful to
learn the basics of moving data between the FE and the BE.  Another good
book is 'Access 2002 Enterprise Developer's Handbook'.  Once you get this
running, your data transfer speed will increase by at least 5X, probably
more like 10X.

Good Luck!
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Thursday, February 05, 2009 6:42 PM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] SQL Server ODBC optimization

Folks,

I sent this to the AccessD list with no responses. Possibly some one on this
list can share their insight in how best to use an Access front end
connected to a SQL Server BE via ODBC. What I am trying to learn is how to
do this most efficiently from the SQL Server perspective.  Do you use
queries from Access, move all queries to SQL server and pass parameters
where required, etc.

I am know that there are some design guidelines that should be followed with
designing a system with an Access front end connected to a SQL Server back
end via ODBC to obtain good performance. This is a pretty broad question,
but could any of you folks who do this on a regular basis provide a list of
the techniques and design approaches you follow or point me to some
references? I am not looking for a treatise on the subject just a list of
items to be aware of and good design practices.

Thanks in advance.

Doug

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list