Re: SQL Server extremely slow

On Mar 9, 5:26 pm, Rich P <rpng...@xxxxxxx> wrote:
Hello,

People will argue that the performance issue is not caused by ODBC, but
my personal experience with a similar setup as your (Sql Server ODBC
Access front end) has been the same as you.  My fix was to use ADO
against the sql server (ADO was specifically designed for use with sql
server).  This uses a disconnected recordset paradigm where you connect
temporarily to the server - process your data (read/write/edit) and then
the connection is closed.  With ODBC the connection remains open the
entire time the app is open.  If you have several people connected
simultaneously that will put quite a load on the server.  ADO is
connected for only a matter of seconds (or milliseconds).  Also, ADO has
wayyyy more bandwith than ODBC.  You can transfer way more data through
an ADO connection than you can through an ODBC connection.  At least,
that has been my personal experience for over 10 years.

My recommendation would be to step up to ADO.  Don't confuse this with
dropping other data access technologies like DAO.  DAO works best for
mdb to mdb.  ADO works best for mdb to Sql Server.  Although, you can
also use ADO against another mdb.  You can also use ADO against Excel
from an mdb - works very nicely.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Could you explain the steps required to use an ADO connection, ADO
recordset, ADO enabled SQL Statement or ADO whatever as the
recordsource or recordset of a report that resides in an mdb?
.

Relevant Pages

  • Re: Strange Problems with ODBC connection to SQL Server
    ... When accessing Jet data, use DAO. ... This way you are not exposing connection information in clear ... >accesses an SQL Server 2000 repository via ODBC data source. ...
    (microsoft.public.data.odbc)
  • Re: Access and processor usage
    ... and you are using an ODBC connection, that would be your bottle neck. ... bottleneck - need to step up to Sql Server. ...
    (comp.databases.ms-access)
  • Re: ADODB.Connection object not returning correct ConnectonString prop
    ... Opens the connection (successfully) ... I'm not positive but a guess is that ADO is using the MSDASQL Provider to ... provide OLE DB support for ODBC. ... ODBC Driver. ...
    (microsoft.public.data.ado)
  • Re: Sql Server 2000 slow
    ... If I close the connection (moving the Open/Close ... ADO) and the dll gives twice the performance. ... > Are there any known performance issues with .NET accessing Sql Server ...
    (microsoft.public.dotnet.framework.performance)
  • RE: [ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()) er
    ... I understand that you encountered the ODBC error message on your ecommerce ... TCP/IP protocol to your SQL Server instance by calling conn.Open, ... SQL Server instance and see if there is a connection reset when this issue ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.connect)