Access Performance issues.

[David W. Fenton]

I do wholeheartedly agree
with Mary & Andy that using a RDBMS backend won't be the magic
fixit bullet, but it does not address the advantage that if you
need to upsize, you can quickly do it and tweak the app later.

This is because Jet is so frigging smart.

Jet never gets any respect.

 

[David W. Fenton]

Are talking about the same David... there are two David's in this
thread...

"Let me re-enforce the observation that switching to SQL Server
is not a magic bullet. To make SQL Server run well, you need to
re-write your application."

Also notes, I was not implying that you never have to rewrite but
that it was not necessary to rewrite.

I think the other David's quotation would have been more clear if
he'd said:

You won't need to rewrite your app, but it will likely benefit
enormously from well-chosen revisions here and there, probably in
fewer places than you'd anticipate.

At least, that's always been my experience of upsizing.

On the other hand, I've been designing all my Access apps with
upsizing in mind beginning back around 1998, though I didn't need to
ever upsize one until 2006!

 

[David W. Fenton]

I think Tom thought I was referring to
something David Fenton said which was not the case.

But what I said is very true -- it's much harder to write an Access
app to run over the Internet with a SQL Server than it is to write
an Access app that runs over a LAN with a SQL Server. Much more
performance tuning is going to be required to run over a WAN than
over a LAN.

This is why I prefer WTS/Citrix -- it's just a lot less work.

 

[Tom Wickerath]

Hi Gina,

Are talking about the same David... there are two David's in this thread...

Yes, I think so. I was talking about lowercase "david" (no last name
indicated), not David Fenton. This quotation from "david":

"Let me re-enforce the observation that switching to SQL Server
is not a magic bullet. To make SQL Server run well, you need to
re-write your application."

is essentially the same thing that Andy Baron and Mary Chipman state in the
paragraph that I quoted in my earlier reply.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Tom Wickerath]

Hi Tony,

To me the word rewrite all by itself implies throwing out the Access
app completely and totally redoing it in a different environment.

Then we are working with different definitions of the word "rewrite". To me,
rewriting an application *may* mean doing it in a different environment, but
it may also mean simply cleaning it up a lot in its current state. I
certainly don't reserve the term rewrite to mean a totally different
application.

A hundred hours worth of tweaks on an app that took 2,000 hours to
write is not a rewrite.

It is a rewrite to me. After all, that 100 hours represents 5% of the
initial investment in time. Not a major rewrite, but a rewrite nevertheless.
So, it looks like we simply use the word rewrite to mean different things.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Gina Whipp]

Savide,

I agree!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Tom,

Let's agree to disagree...

Because I don't his statement as being "...essentially the same thing..."

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

David,

Yes it is...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

David,

....and perhpas the way you explained my view was a better way to sate it...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[David W. Fenton]

To me,
rewriting an application *may* mean doing it in a different
environment, but it may also mean simply cleaning it up a lot in
its current state.

To me, one is a rewrite, the other a revision. In my experience,
upsizing requires revision to one degree or another to maximize
performance, but rarely any actual rewriting.

Of course, if you've architected your app with upsizing in mind, you
will have designed your objects and code such that it's fairly
simple to revise the performance bottlenecks.

 

[Larry Linson]

There is, in fact, much to be gained by moving your back-end to a server
database with only minor rewrite to the way you do your application. When
you are using a split Access Jet/ACE database, all (repeat ALL) the data
manipulation is done on the user's machine -- the shared copy of the back
end is used the same as it would be on a local hard drive. That means not
every bit of data travels across the LAN, but depending on your design, much
of it will. When you move to the server, the request is sent across the
LAN, and the final result is transmitted back. Certainly, you can defeat
your purpose if you base forms on a SELECT * or on an entire table -- but I
don't do that even if both the front and back end are on my own individual
PC, unless the database is quite small.

Of course, "to get the most" out of SQL Server, you may need to write stored
procedures. But, the vast majority of my clients since 1993 found it easier
and less expensive to hire Access people than to hire SQL Server (or
Informix, or Sybase) developers and did not want us to, would not allow us
to, write stored procedures. Fact is, unless you are dealing with
**humungous** amounts of data, you won't need "to get the most" out of SQL
Server. You are likely to be able to get such improvements with minor tweaks
that you'll never need to even THINK about stored procedures. It's
surprising just how many times in "normal business database processing"
you'll realize you need to retrieve only one single record (if it exists) or
an indication that it is not there (if it doesn't). And, not even the index
will have to be brought across the LAN to make that determination.

Another fact is, if you are an SQL Server "expert", you may well have been
so indoctrinated to its application to humungous, distributed, enterprise
applications that "normal business database applications" of lesser extent,
just upsized to use SQL Server as a back-end (maybe SQL Server Express,
even) are just "below your radar". You'll think in terms of server farms,
and clusters, and such... and you might even give advice that would scare
off potential users who could benefit from moving to a "lesser SQL Server
installation". So, where will they end up? Maybe with MySQL, PostgreSQL,
or others where the "experts" aren't all "enterprise application
oriented"... and they might be no better, or not as well off, as they would
have been with SQL Server Express. Fortunately, most of those are ODBC
compliant, so the frightened Access developer does have "someplace to run".

I have been involved in Access projects with a number of "enterprise
clients", but none of those projects were "enterprise applications"... they
were all specialized applications that were far less vast than the huge,
distributed, enterprise applications... ERP, CRM, etc., none of which
involved Access, not in the companies with whom I contracted. The corporate
real estate database application for a Fortune 100 company, for example, had
far less than 100,000 properties -- the only stored procedures necessary
were to retrieve the next "unique id" and to execute a couple of passthrough
queries for complex reports.

Larry Linson
Microsoft Office Access MVP

 

[Tom Wickerath]

Okay, let's agree to disagree on this one.
I really do believe the two statements are essentially the same, but I won't
argue it further if you prefer not to.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Gina Whipp]

Tom,

I was not arguing... Did you hear my West Indian accent? <G> I was
engaged in a vigorous discussion about a subtle difference in wording. I
was actually enjoying the exchange but not sure this would be the form for
it!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[David W. Fenton]

Of course, "to get the most" out of SQL Server, you may need to
write stored procedures. But, the vast majority of my clients
since 1993 found it easier and less expensive to hire Access
people than to hire SQL Server (or Informix, or Sybase) developers
and did not want us to, would not allow us to, write stored
procedures. Fact is, unless you are dealing with **humungous**
amounts of data, you won't need "to get the most" out of SQL
Server. You are likely to be able to get such improvements with
minor tweaks that you'll never need to even THINK about stored
procedures.

While I wholeheartedly agree with the basic thrust of your message,
and think you overstate the case here. There are a number of
operations, particularly, mass updates and mass inserts (even small
ones) that are much slower when upsized to SQL Server than they
would be with your Jet/ACE back end. This is because with Jet/ACE,
it's done in a single transaction and committed as a batch.

But Jet/ACE tries to be a good citizen as a user of the database and
breaks down your mass update/insert into a series of
updates/inserts, one row for each. This is massively slower, but it
does mean the server can interleave your requests with those of
other users so that the server is not tied up dealing with your
update/insert before it can take care of others' requests.

In that case, you really do have to move some of the logic
server-side.

Now, that said, I've never written any Access apps that do any
significant numbers of such updates/inserts. Mass insert of records
is something that happens in my apps only when they are in the early
stages, or when a major import of data happens. The latter is very
rare in most apps, and even in an app of mine that does need to
import regularly, it still only happens once or twice a year.

Mass updates are, perhaps, more common, but still not all the much
so. I tend to do lots of them massaging and tweaking and cleaning up
data, which is an ongoing process in most apps (e.g., discovering
after an app has been in use that a certain field should have been
restricted to a certain limited number of values, and needs to be
cleaned up so that a stricter editing interface can be put in
place). But as part of everyday workflow, not so much.

I think that any app where large groups of records need to be mass
updated on a regular basis is probably designed improperly -- it
sounds like something that ought to be in a transaction table,
rather than done as a mass update. Naturally, this is a blanket
statement that won't apply in particular situations, but I think it
applies in most.

Therefore, in general, my point here about need to move mass
inserts/updates server-side is something that shouldn't actually
happen very often in most well-designed apps. And because of that,
Larry's point is basically correct.

But it's important to realize that there can be perfectly valid
reasons for needing to do these mass operations, and optimizing them
for SQL Server is probably going to require server-side components.