Speed Improvements With SQL Server?
maxhugen
that is now also being used in a second office, connected by a WAN.
This office has network problems, as it's over-utilized (97% according
to one IT guy!!).
I'm looking into converting the MDB app into an ADP with a SQL Server
backend. To justify the costs involved, I'm trying to get a feel for
what spped improvements might reasonably be expected.
After a lot of searching, I've found a great many sites that say it
will (or should) be faster, but I have yet to find any mention of
anyone doing it, and whether they were actually able to get speed
improvements.
Has any had some actual personal experience in 'upsizing' from Access
to ADP + SQL Server that they could share pls? Or point me to any
URLs?
MTIA
lyle fairfield
newsgroup.
I've converted several applications to ADPs. I don't think they run
noticeably faster than a local MDB solution. I think they are less
secure.
They can be faster if your application requires complex Selects which
MAY be effected more efficiently on a Server-Database.
IMO, there is no speed advantage to be gained by going to ADPs (or
MDBS via ODBC) and SQL Server that could come anywhere close to a
rewriting of an existing application by a qualified, experienced
Access Developer. The Developer accounts for 95% of the efficiency of
any Database application.
bcap
If this is what you are experiencing then it doesn't matter how overloaded
or not the remote LAN is, it's gonna run like a three-legged dog anyway
simply because of the limitations inherent in the architecture of Access.
However, if the back-end were SQL Server, the application would usually be
quite usable over a WAN. Noticeably slower than on a LAN, but nonetheless
usable.
So, what you can expect is to improve performance from "hopeless" to "quite
adequate" - which by any metric is probably an improvement of several orders
of magnitude!
"maxhugen" <maxh...@gmail.com> wrote in message
news:652d5424-f7de-49d1...@j7g2000prm.googlegroups.com...
bcap
"lyle fairfield" <lyle.fa...@gmail.com> wrote in message
news:a9ed4bb6-8545-4d96...@12g2000hsd.googlegroups.com...
bcap
application using Terminal Services may well be cheaper than rewriting it!
And if you are determined to go client/server, upsizing using ODBC linked
tables may well be cheaper than rewriting it as an ADP.
"bcap" <bc...@nospam.nowhere> wrote in message
news:489c5910$0$2521$da0f...@news.zen.co.uk...
(PeteCresswell)
>Has any had some actual personal experience in 'upsizing' from Access
>to ADP + SQL Server that they could share pls? Or point me to any
>URLs?
Ping me in about nine months.
One of my apps had been running front end/user's PC & back
end/LAN server.
Response time (i.e. time it took to load a screen) was pretty
good on day one but has slowly gone down the toilet.
Stopgap fix has been to move the app to a Citrix server, where
multi users get the same (pretty good) performance as I did on my
previous desktop PC.
But we have two processes that are very time consuming and need
tb budded off into some sort of asynchronous processes.
We *could* have a bogus user logged on to the Citrix box all the
time with a special version of the app looking for semaphores
telling it to run said processes, but the users have opted to
migrate the back end to SQL Server.
This works for me bc IT will be doing the migration and then
they'll be one step closer to owning the app when I hit the
lottery and retire to Maui.
Personally, I don't expect a significant improvement in screen
load times.
But maybe I'll be surprised.
Within nine months, I should actually know something...
--
PeteCresswell
Albert D. Kallal
"maxhugen" <maxh...@gmail.com> wrote in message
news:652d5424-f7de-49d1...@j7g2000prm.googlegroups.com...
> that is now also being used in a second office, connected by a WAN.
check my article on using ms-access on a wan here.
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
> This office has network problems, as it's over-utilized (97% according
> to one IT guy!!).
>
> I'm looking into converting the MDB app into an ADP with a SQL Server
Do note that you do NOT necessary have to move to a adp project. You can
keep your application as is now, and simply link the tables to sql server in
place of your back end. This is a recommend approach since then all of your
existing dao reocrdset code will run (very few mods need be made). If you
are staring from scratch, then a ADP is a good choice, but for an existing
applications, is FAR FAR less work to simply go the linked tables via odbc.
For the most part, performance between a odbc linked table and that of an
adp project is not really different. However, to be fair, an adp project is
MORE forgiving when you write bad queries etc since by nature they are all
pass-through.
>I'm trying to get a feel for
> what spped improvements might reasonably be expected.
Actually, we see weekly posts here in that after converting to sql server,
things
run slower. So, moving to sql server is not a magic bullet. Your designs
must
be ones that LIMITS the number of records transferred to the form. While you
can get away opening a form bound to large table with a mdb back end, when
using odbc to sql server, you NEVER want to do this. (bound forms are ok,
but
you REALLY want to open up a form with a "where" clause to restrict the
record.
I talk about searching and brining up records here:
http://www.members.shaw.ca/AlbertKallal/Search/index.html
>
> After a lot of searching, I've found a great many sites that say it
> will (or should) be faster, but I have yet to find any mention of
> anyone doing it, and whether they were actually able to get speed
> improvements.
You only get improvements if your designs are sound in the first place. The
mere "act" of moving to sql server often will not speed things up at all.
As mentioned, since you have an existing application, you likely better to
stick with a mdb front end, and using linked odbc tables. It is FAR less
work. (and, about 99% of your existing code should run).
If you move to adp, then you have to dump all of your dao code in your
application Depending on the size of your application, this usually means
it better to stick with mdb + linked tables to sql server and it not worth
the time + effort to go the adp route.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
lyle fairfield
application and the strings are passed through to the SQL Server. I'm
sure Albert, that you know that except in the case of dynamic query
strings called from code, this is not the case. The "queries" we see
exist on the SQL-Server as Stored Procedures, Views and Functions,
entirely independent of the Access application.
On Aug 8, 9:05 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
Albert D. Kallal
>This implies that ADP "queries" exist as strings in the Access
application and the strings are passed through to the SQL Server.
No, not really what I trying to imply.
>I'm
sure Albert, that you know that except in the case of dynamic query
strings called from code, this is not the case.
Well, there many other places/cases (where clause to forms, where clause
to reports, filters etc.
> The "queries" we see
exist on the SQL-Server as Stored Procedures, Views and Functions,
entirely independent of the Access application.
Sure, but regardless, from "in-line" sql, or existing queries...they all
execute server side 100%....just like a pass-though query does. That is
really the only point I making here...
jet does not always do a table join server side..it can mess them up, and
cause the joins to occur local (both tables come down the pipe). Jet usually
does an ok job, but some joins it does mess up.
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
>Ah, but the WAN, Lyle, the WAN...
FWIW Tom Ellision, former Access MVP, did a lot of work with ADPs. He was able to
get adequate performance over a 56 kpbs dialup connection after a lot of tuning. Not
good performance but adequate.
That said I don't know if spending the time migrating to ADPs and working with
Access's ADP quirks would give you better performance compared to linked tables,
views and such. However I bow to Lyle and others experience in that area.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Larry Linson
back end, usually on the very same machine as the front end, was the key to
adequate performance in the huge, massive, SQL statements that were required
in the business environment that he served (commercial seed business).
IIRC, he said that MSDE was multi-threaded whereas Jet was not. I didn't
remember him using ADP and dialup, though. I stand in awe of Tom's SQL
abilities.
Larry Linson
Microsoft Office Access MVP
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:7mnu94d1ke8sm4kq5...@4ax.com...
Larry Linson
"bcap" <bc...@nospam.nowhere> wrote in message
> Of course, I should also have said that having the remote users run the
> application using Terminal Services may well be cheaper than rewriting it!
>
> And if you are determined to go client/server, upsizing using ODBC linked
> tables may well be cheaper than rewriting it as an ADP.
Not only that, but Access MDB/ACCDB <--> Jet/ACE <--> ODBC <--> serverDB is
the configuration for Access clients currently recommended by the Access
team, not ADP.
bcap
"Larry Linson" <bou...@localhost.not> wrote in message
news:s47ok.652$mP.360@trnddc03...
Hi Larry,
I recall reading a blog entry to that effect when Access 2007 was in
development, but can you point to anything more recent or definitive? I
think there's a lot of puzzled people right now who would like to see some
kind of position statement from Microsoft on ADP's; is there anything you
know of that might help?
(PeteCresswell)
>Not only that, but Access MDB/ACCDB <--> Jet/ACE <--> ODBC <--> serverDB is
>the configuration for Access clients currently recommended by the Access
>team, not ADP.
Can anybody speculate on why?
On the several apps where I was forced to stay with ODBC against
tables migrated to SQL Server, response time went right down the
toilet.
OTOH, on the one app where I got to do it my way from the bottom
up (ADO, stored procedures for *everything*) I was pleasantly
surprised by how quickly it loaded/saved some fairly heinous
screens - and I don't know diddley about SQL Server, the whole
thing was done on a wing and a prayer.... heaven forbid somebody
who knew what they were doing should have done the DB design and
written the SPs.
--
PeteCresswell
Rick Brandt
It should be noted that one can use MDB/ODBC and still use "stored
procedures for everything" if one chooses to do so.
I avoided ADP because of the restrictions. I couldn't care less about small
performance differences one way or the other.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
David W. Fenton
news:co54a4lbfk65rrpkg...@4ax.com:
> Per Larry Linson:
>>Not only that, but Access MDB/ACCDB <--> Jet/ACE <--> ODBC <-->
>>serverDB is the configuration for Access clients currently
>>recommended by the Access team, not ADP.
>
> Can anybody speculate on why?
Microsoft's own words might be sufficient to explain. From
<http://technet2.microsoft.com/Office/en-us/library/1dce641e-ba1c-446
a-8ff2-221769a58ba51033.mspx?mfr=true> :
(get rid of the word wrap, with no spaces)
The key section:
Access Data Projects (ADPs)
An Access Data Project is an OLE document file, like the .xls
or.doc file formats. It contains forms, reports, macros, VBA
modules, and a connection string. All tables and queries are
stored in SQL Server. The ADP architecture was designed to create
client-server applications. Because of this, there is a limit to
the number of records that Access returns in any recordset. This
limit is configurable, but you typically must build enough
filtering into your application so that you do not reach the
limit.
Access uses OLEDB to communicate with SQL Server. To provide the
Jet-like cursor behavior desired for desktop applications, Access
implements the Client Data Manager (CDM) as an additional layer
between Access and OLEDB.
Because of the layers required to get from Access to SQL Server
in the ADP architecture, it is often easier to optimize MDB/ACCDB
file solutions. However, there are some scenarios where a report
might be generated significantly faster in an ADP file. To add
these performance improvements and retain the flexibility of SQL
Server, you can build the majority of the application in an MDB
or ACCDB file and have the file load reports from a referenced
ADP file.
One advantage that ADP files have over files in MDB or ACCDB
format is the ability to make design changes to SQL Server
objects. ADP files include graphical designers for tables, views,
stored procedures, functions, and database diagrams.
It's not clear exactly why they have given up on ADPs, but it seems
that it likely has something to do with the layers between the ADP
and your SQL Server. Supposedly, ADPs were supposed to be "closer to
the metal," but in fact, they have just as many intermediate layers
in between the ADP and the SQL Server as you have with MDB/ODBC. And
many have reported the problems with ADO guessing how to make
non-editable queries updatable (i.e., going around your views, with
appropriate security settings, and attempting to update the tables
directly). People complained about Jet guessing wrong, but ADO
guessed wrong just as often, but ADPs didn't offer as many options
as workarounds, I guess, since everything goes through OLEDB (i.e.,
no option for a passthrough).
I never thought that ADPs really had much of a reason to exist other
than a superstitious avoidance of Jet.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
lyle fairfield
to be set to an ADO recordset. This is very powerful, although those
not so experienced with ADO might find it slow and tedious.
Although I have not examined it extensively the recordset setting
seems to enable:
1. Using any database engine for which an ADO provider exists; these
may include Oracle, JET, Ace, text files, http folder members, and XML
files.
2. Avoiding the Client Data Manager layer.
Why was further development on ADPs stopped after Access 2003? I had
two major concerns about ADPs.
One was security. Create a new ADP and you will see and have
everything available to you that is visible and available in your
application ADP, without benefit of its controls. Application roles
will solve this. Application roles in ADPs are disastrous; they won't
work properly and programming them takes forever.
Two was multiple connections. We may think a hundred users of an SQL
db is very slight usage. But what if each of those hundred users' ADP
has ten connections open? Now we have a thousand connections open to
the same db. This isn't efficient, and in my opinion, must, sooner or
later cause locking troubles.
TTBOMK MS was unwilling to assign the resources necessary to solve
these problems; I think major resources would have been required. So
when ADPs did not hit the top of the popularity charts, it may have
been cost effective just to abandon them.
Of course, using an ADO connection created in code and independent of
the ADPs CurrentProject and CodeProject Cnnections (in fact, don't
create these) solves all or almost all of these problems. But I think
very few developers will want to take the time to effect that
solution.
(I believe the security problems are the same with ODBC, but a new MDB
does not slap you in the face with the potential for bad seed
immediately upon creation.)
On Aug 13, 8:01 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
bcap
I tried to use it, as per the ADH, to create logical transactions spanning
updates to a form and it's subform. Utterly useless: as soon as you try
anything clever with that Recordset property, Access starts crashing right,
left and centre.
lyle fairfield
ADP form recordset updates deletes etc work perfectly for me.