Re: SQL Server extremely slow

"Rich P" <rpng123@xxxxxxx> wrote in message
news:49b685a2$0$87079$815e3792@xxxxxxxxxxxxxxxxx
How can a disconnected ADO recordset be used as the
source of data displayed in an Access Report resident in
an Access mdb?

The same way you would do it in a .Net application - you pull the data
you need from the server to a local table(s)

Well, ok now you not talking about a disconnected recordset in
terms of what is meant by a dis-connected ado recordset.

One could argue on semantics that a local
table in a mdb file could be considered disconnected from the server
but it would be unfair to say that meaning is wwhat the general
computer industry means as a disconnected ADO record set (that refers to a
in-memory data set). You might not be implying that local tables are
disconnected recordsets, but I do want to be clear as possible here.

The main difference between this method
and a .Net method is that in .Net it all happens in memory - thus
avoiding all the disk I/O.

Well, ok, but keep in mind the disk drive is on sql server! Pulling
10 reocrds from the server via odbc does not produce more (or less)
network traffic than pulling ten records from the server via ADO.

also, if you send one sql update string via a native OLEdb connection
or an odbc update, you still only sending the server one sql
string.
That one sql string might update 1, or 10,000
records...and that don't cause any local or network i/o at that point.

You still not really making the case why ado going to have better
performance here.

But working with data from a local table
within the mdb is way easier than having to go back and forth between
the mdb and the server DB via ODBC - passthrough queries or not.

Its most certainly easier to work with local tables, but it's NOT most
certainly easier write a whole whack of code that first pulls the data from
sql server into local tables (or in memory) and THEN we work with the local
data

Lets keep this perspective here, using a simple pass through query to SQL
server is far less work than writing a bunch of code to pull the data to a
local record set or local tables and then having a report chew on that data.
Furthermore using ADO or that pass through query is not going to yield
**ANY** difference in performance in pulling those records to a report.

ADO is just for pulling the data from the server or pushing data back to
the server, running update queries, stored procedures... you connect to
the server - then you disconnect when the sql statement/proc is
complete.


Right but we're talking about a report here. However, if we "were" updating
data in a table, why would a update query via odbc run slower then a update
query from ado? There are again some "bulk"update options you can use with
an oleDB connection, but that is again not the case here.

I worked with ADPs for a few years and ran into the same problems I had
when using ODBC between an mdb and sql server - the biggest reason is
that the ADP is also ODBC based.

You are 100% WRONG on this. ADP's do not use jet, and ADP projects in access
are 100% native oleDB connections to SQL server. Again: access and adp
projects use an 100% native oleDB connections to sql server. There is no
jet,
and thus reports are really for all considerations native pass through
queries when data is pulled.


Note: there are times when ODBC may be preferred - when the load is low
and you don't have a bunch of users.

There's no difference in performance if you pull twenty records with ODBC,
or pull 20 records with an oleDB connection. As I said the network
connection speed is not changed here, nor do the basic mechanisms used by
SQL server to pull the data out of the table change at all either.

ADO is basically an addition data
access layer.

Well once again let's clarify what's going on here. ADO is most certainly a
data abstractions layer, but the difference in performance to be gained by
using ADO **only** occurs because ADO works with what is called a native
oleDB provider. It is this custom built oleDB provider that's built for the
particular database engine that can yield some improvement in some cases
over ODBC **if** you're actually using the additional features of that
native oleDB provider. In these general cases we are NOT using those
additional features an hence no performance benefit.

So, when not using the special features of that provider for things like
data cubes or special types of processing, you're not in general use going
to gain any kind of performance whatsoever.

Furthmore, you do realize hat you can use an DAO "Direct" conneciton to sql
server and NO jet is involed, nor even loaded.

eg:
Dim strCon As String
Dim rstRecords As DAO.Recordset
Dim wrk1 As DAO.Workspace
Dim MyCon As DAO.Connection

Set wrk1 = DBEngine.CreateWorkspace("TestWorkSpace", "", "", dbUseODBC)

strCon = "ODBC;driver={SQL Server};DSN=;" _
& "SERVER={localhost};" _
& "DATABASE=RidesSql;" _
& "UID=SA;PWD=;OPTION=3;"

Set MyCon = wrk1.OpenConnection("mycon", dbDriverNoPrompt, False, strCon)


Set rstRecords = MyCon.OpenRecordset("select * from tblJunk")

Note I used "dbUseODBC", and thus we are by passing JET directly. Use of
this keyword means that JET is not to be used, nor even loaded!

Keep in mind that this means that DAO is the pre-curosr to ADO and has
"direct" connection featuers for sql server like ADO.

Note that for access 2007, "jet direct" connectons are not suppored anymore.

last but not least:

Please don't take my comments as being harsh or throwing any water on
efforts here. After all your spending time in this community and you trying
to
offer advice and help people here. I never want to discourage anybody
who comes here to try and help people and offer advice. So I respect your
efforts and time here and I also respect that you are tring to help this
person solve perforamcne issues.

There's no question if you make some additional efforts and write some
additional code and use ADO that you can gain some serious performance
increases.

However the issue here is not the fact that ODBC is being used, is the fact
that the designs and current implementation of the persons application is
simply pulling too much data. Implementing a few views and using some pass
through queries for reports allows one to stick to ODBC and with LITTLE
changes to the overall application you get the same performances of ADO at
the end of the day. These performance problem(s) are not due to ADO, ADO, or
odbc.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx



.

Relevant Pages

  • Re: Error -2147168227 Cannot create new transaction because capacity was exceeded.
    ... OLEDB provider for SQL Server. ... > I have been unable to find info in the Knowledge base, ADO or SQL Server ... > ADO driver generates the error. ...
    (microsoft.public.data.ado)
  • Re: Connection from ASP- ODBC vs. SQLOLEDB
    ... The ASP code is already using ADO (adodb.connection, ... The new provider is better than ODBC isn't it, ... SQL Server Client Network Utility- "Enable Shared Memory": ... ASP, ASP.NET as well as Microsoft Content Management Server 2002 SP1a. ...
    (microsoft.public.sqlserver.server)
  • Re: DAO vs ADO
    ... ADO ... > extra overhead retrieving metadata from the server for each query. ... > but I feel DAO is not as unsuitable as is often assumed. ... and ADO when working with SQL Server data. ...
    (microsoft.public.access.conversion)
  • Re: [Access2003, VBA] Use DAO or ADO?
    ... One of the reasons my clients preferred Access / Jet / ODBC was that they ... Autonumber or its server equivalent and could have been shown to the users ... real world of development, Dot Net", ADO has been superceded by ADO.NET ...
    (comp.databases.ms-access)
  • Linked server to ODBC DSN
    ... Server, and want to create a linked server to this same ODBC DSN: ... Provider = "Microsoft OLE DB Provider for ODBC Driver" ... Then i connect to the SQL Server using QA, and try to run my query (that ...
    (microsoft.public.sqlserver.server)