Speed Improvements With SQL Server?

[maxhugen]

I have an Access app (split into FE and BE) running for some years,
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]

Re: Speed Improvements With SQL Server?

I am one of the few proponents of ADPs who posts regularly to this
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.

On Aug 8, 10:09 am, maxhugen <maxhu...@gmail .comwrote:

I have an Access app (split into FE and BE) running for some years,
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

[bcap]

Re: Speed Improvements With SQL Server?

An Access application split over a WAN would normally be virtually unusable.
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" <maxhugen@gmail .comwrote in message
news:652d5424-f7de-49d1-aeb0-2677a46fc498@j7 g2000prm.google groups.com...

>I have an Access app (split into FE and BE) running for some years,
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

[bcap]

Re: Speed Improvements With SQL Server?

Ah, but the WAN, Lyle, the WAN...

"lyle fairfield" <lyle.fairfield @gmail.comwrote in message
news:a9ed4bb6-8545-4d96-b781-b5604e653bd9@12 g2000hsd.google groups.com...
I am one of the few proponents of ADPs who posts regularly to this
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]

Re: Speed Improvements With SQL Server?

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.

"bcap" <bcap@nospam.no wherewrote in message
news:489c5910$0 $2521$da0feed9@ news.zen.co.uk. ..

An Access application split over a WAN would normally be virtually
unusable. 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" <maxhugen@gmail .comwrote in message
news:652d5424-f7de-49d1-aeb0-2677a46fc498@j7 g2000prm.google groups.com...

>>I have an Access app (split into FE and BE) running for some years,
>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

>
>

[(PeteCresswell)]

Re: Speed Improvements With SQL Server?

Per maxhugen:

>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]

Re: Speed Improvements With SQL Server?


"maxhugen" <maxhugen@gmail .comwrote in message
news:652d5424-f7de-49d1-aeb0-2677a46fc498@j7 g2000prm.google groups.com...

>I have an Access app (split into FE and BE) running for some years,
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
pleaseNOOSpamKa llal@msn.com

[lyle fairfield]

Re: Speed Improvements With SQL Server?

This implies that ADP "queries" exist as strings in the Access
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" <PleaseNOOOsPAM mkal...@msn.com >
wrote:

However, to be fair, an adp project is
MORE forgiving when you write bad queries etc since by nature they are all
pass-through.

[Albert D. Kallal]

Re: Speed Improvements With SQL Server?

"lyle fairfield" <lyle.fairfield @gmail.comwrote in message
news:bf78232a-01b7-487a-a838-7fd7ab380cc5@l4 2g2000hsc.googl egroups.com...

>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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Tony Toews [MVP]]

Re: Speed Improvements With SQL Server?

"bcap" <bcap@nospam.no wherewrote:

>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]

Re: Speed Improvements With SQL Server?

Tom Ellison was convinced that using MSDE (stripped down SQL Server) as the
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]" <ttoews@teluspl anet.netwrote in message
news:7mnu94d1ke 8sm4kq50jl90kkb q237nn9c9@4ax.c om...

"bcap" <bcap@nospam.no wherewrote:
>

>>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]

Re: Speed Improvements With SQL Server?


"bcap" <bcap@nospam.no wherewrote in message
news:489c5b05$0 $26090$db0fefd9 @news.zen.co.uk ...

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.

Larry Linson
Microsoft Office Access MVP

[bcap]

Re: Speed Improvements With SQL Server?


"Larry Linson" <bouncer@localh ost.notwrote in message
news:s47ok.652$ mP.360@trnddc03 ...

>
"bcap" <bcap@nospam.no wherewrote in message
news:489c5b05$0 $26090$db0fefd9 @news.zen.co.uk ...

>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.
>
Larry Linson
Microsoft Office Access MVP
>

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)]

Re: Speed Improvements With SQL Server?

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?

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]

Re: Speed Improvements With SQL Server?

(PeteCresswell) wrote:

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?
>
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.

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