SQL Rules

[Tom Ellison]

Dear friends:

I am a SQL developer known personally to most of the MVPs ( I have been one
for 5 years, and hope to be again soon). They know I am a proponent of the
superiority of SQL Server to Jet. This has been no secret for those 5
years.

My recent experience has trumped that to an incredible degree.

A client brought me an existing application that searched a large Jet table
(3 million rows) using an average of 80 queries. I obtained an improvement
in performance using Jet of 8:1 by adding an index.

I have just converted the database to SQL Express and made the same index a
clustered index. 4 hours of processing is now 64 seconds. I had brought it
down to half an hour with the Jet index, but Express is 30 times as fast.

These are all very simple queries, filtering on a set of rows by 10 or so
columns.

To me, the most incredible feat is that Express creates a clustered index
(effectively sorting all the rows into physical order on that column) in
less than 4 minutes. That was 2 gigabytes just of unindexed data in Jet,
being 3 million rows. This 4 minutes of one-time overhead results in
changing 20 hours of searching daily to about 5 minutes. I had reduced that
searching to about 2-1/2 hours by indexing in Jet.

My clients have been repeatedly pleased with what I have been enabled to do
using MSDE, and now Express. I recommend these skills to all my expert
friends.

I can take only the smallest proportion of credit for this. It multiplies
my skills hugely to have this tool in my box. Sometimes I think my biggest
skill is just the phrase, "let's convert that to SQL Server."

I have heard repeatedly from my MVP friends that Jet is the equal if not
superior to SQL Server in performance for simple queries. In this case, for
very simple queries, Express beat Jet 15:1. Because of memory cache, if I
had a gigabyte more memory in the system, the ratio would have been much
greater. If this had been my server, it would have had that memory, and
more.

Thanks to Andrew Kelly in the SQL performance forum for reminding me that a
clustered index is your best key to performance. It was phenomenal.

The remaining project is just to change what is done in the Access front end
to be pass-thru queries.

Tom Ellison

 

[Brendan Reynolds]

<quote>
That was 2 gigabytes just of unindexed data in Jet, being 3 million rows.
</quote>

I think you'll have a hard time, Tom, finding any Access MVP, or for that
matter any experienced Access developer, who would have recommended JET
under those circumstances.

JET and SQL Server are both great products. Either one may be the more
appropriate solution depending on specific circumstances and requirements.
The question facing Access developers today, in my opinion, is how the new
Access database engine (discussed in Eric Rucker's blog at
http://blogs.msdn.com/access/ ) is going to pan out. I personally won't be
making any long-term decisions about which database engine(s) I'm going to
be using until I see where this new database engine is going.

That said, I am of course in complete agreement that SQL Server skills are a
very useful addition to an Access developer's toolkit.

 

[Tom Ellison]

Dear Brandan:

You will perhaps excuse some of my exuberance. Express saved this project,
and dramatically. It makes me look good.

I'm not sure at what scale Jet begins to be faster than Express. Would that
happen at 300,000 rows of data, 1/10 of what I have? How about at 30,000
rows. How about 3,000 rows?

Now that I have this all set up on my system, I could easily run such a test
and find out. I would wager that Jet is slower at 3000 rows.

In my past experience, a properly designed SQL Server back end beats Jet by
a wide margin even at very modest scale. I've heard from my friends in the
MVP community that this is not the case, but it has never seemed to agree
with my results. Perhaps their tests didn't use such superior tools in SQL
Server as clustered indexes, which is what fixed this for me. SQL Server
was about on a par with Jet until I added that.

I do not agree that the scale of this project is the primary reason for the
disparity in performance. I have several applications installed which are
running with less than 10 megabytes of data, and are vastly faster than with
Jet. That isn't to say that the designer doesn't need to know how to
exploit the features of SQL Server that produce this kind of advantage. But
Jet lacks such fetures, and this is why it cannot catch up.

"Either one may be the more appropriate solution depending on specific
circumstances and requirements."

If the specific circumstances and requirments include performance and the
flexibility to perform well with new requirments that may not be initially
apparent, I just would not consider Jet. The original Access developers who
built this one were very wrong to do so. I'm sure you would not have made
those same mistakes, but there are some who do.

I expect we are mostly in agreement. The fact that you are looking toward
future solutions shows we have the same spirit in that regard. When MSDE
first arrived with Access, I was doing that same thing.

Tom Ellison

 

[Mark Shultz Jr]

Brendan,

I can't ever see any reason to use JET when MSDE and SQL Server Express are
provided free of charge. Why would anyone want to potentially hinder a
project's scalability with a data engine that has definite capacity/user
limits. Even for the smallest projects, it's not a big deal to run MSDE on a
workstation, especially since it will not cost the user anything more and
the upgrade path to full SQL Server is very simple and would not require any
re-coding. I don't know about your customers, but mine have a habit of
greatly downplaying their future requirements. It's up to us as developers
to save our clients from themselves! They will be thanking us when they run
out of capacity and the solution takes only a couple hours to implement.

Just my humble opinion.

Mark Shultz
Procurement Data Specialist
Interstate All Battery Centers

 

[Tom Ellison]

Dear Mark:

Your fundamentals are correct as to the reasons to choose SQL Server
products.

There is a significant sector of the market where Jet is a good tool because
the amount of time and effort and the expertise to learn it are much lower.
For limited applications written by novice to intermediate level
programmers, there's a definite niche for Jet.

For professionals who have the skill to exploit a product like Express, I
think it will probably serve poorly to use Jet in most, but not all cases.
To a large degree, this is what Brendan said as well (at least as I
interpret his words.)

It was beyond being "professionally satisfying" to have an improvement in
performance such as I experienced. It was actually exciting. With a bit of
fine tuning (for example, defragging the HD) I know have an application that
is 250 times as fast as what we started with. I do not think the client
will believe me until this is installed and he sees it himself. Would you?
Probably not, unless you had experienced similar improvements yourself in
the past. From the point of view of an ordinary Earthling, this is from
outer space. I just shake my head and marvel.

Tom Ellison

 

[Brendan Reynolds]

For professionals who have the skill to exploit a product like Express, I
think it will probably serve poorly to use Jet in most, but not all cases.
To a large degree, this is what Brendan said as well (at least as I
interpret his words.)

I did not say anything like that, Tom, and I am surprised that you think
there was any need to 'interpret' my words. I thought they were quite clear
and unambigeous.

I am not going to spend any more time on the subject of JET vs SQL Server
because a) it has all been said before and b) I believe the more important
and interesting question today is the future of the new Access database
engine.

 

[Robert Morley]

Of course, you also have to factor in whether or not the Jet/SQL database is
local or remote. In the case of a remote database, I suspect SQL will be
faster even for a small handful of rows...perhaps even for extracting a
single byte/tinyint from a single row table. The overhead for Jet in a
remote scenario is just too high.

But even taking them both as local copies of the data, there are just so
many factors that go into which one is "better". Ignoring, for the moment,
considerations of knowledge base, ease of use, etc., and just looking at
speed-related issues, we have issues like:

- Is SQL using Shared Memory?
- How are the databases partitioned in terms of drives? A small
database will probably do quite well in Jet on a single drive; a large
database will probably perform better being partitioned onto multiple drives
or a RAID drive.
- As you discovered, clustered indexes are a big thing. Jet supports
clustered indexes after a fashion, but doesn't make it obvious (as I
remember it, the Primary Key is automatically clustered, but only when you
Compact the database); SQL, on the other hand, makes it very obvious, and
you generally don't have to worry about manually initiating anything, as a
good maintenance plan will probably take care of that for you behind the
scenes.
- Related to the above, SQL allows you to "save room" for new records,
allowing slightly-out-of-tune clustered indexes to still perform quite well
when new records are added (to a point...it probably won't work well if you
start bulk adding records).

There are probably a gazillion other factors that come into play as well;
I've just mentioned a few that came to mind off the top of my head. Point
being, whether you use Access or SQL Server is really something that should
be decided on a project-by-project basis, and even if there's a clear
winner, you may have the issue of where your data's starting out to begin
with. In the project I'm working on now, we had our data in Access/Jet
since before I got there in 1999. It performed quite well, but was clearly
more suited to SQL Server in later years. Finally, in 2005, we converted to
SQL Server with ADP front-end's, and with the exception of cross-tab
queries, I'm really happy that we did!



Later,
Rob

 

[Robert Morley]

I see Tom's responded to this, though I haven't read it yet, but I can think
of one VERY good reason, and several lesser, more debatable ones to use Jet.

Let's say I'm leaving the office for the weekend, and want to take the
database home with me to work on over the weekend. With Jet (assuming it's
a small project), I just copy one file, and I'm good to go. With MSDE and
the like, there are not only multiple files to copy, but in order to do so,
you have to stop the service and/or dismount the database, etc.

Similar to the above, there's the simple fact that it's self-contained.
Your GUI, your tables, your VBA code, they're all in the same place.
Moreover, while it's generally a bad idea, if you really feel the need, you
can integrate VBA code into your queries.

And, of course, there's the learning curve. Because of how powerful
MSDE/SQL Server are, you often need to know a great deal more than you would
were you using Access alone. (Not to mention the freakin' cross-tab queries
I mentioned in my previous message...what a nightmare to imitate those in
SQL Server!)

Anyway, those are just some thoughts. I agree, there's a lot to be said for
SQL Server and variants, but to suggest that it is THE solution in all cases
is a little extreme, I think.



Rob

 

[Tom Ellison]

Dear Robert:

With SQL Server, you just copy one file, a backup. I program that on a
command button. Should take a couple of seconds to create for one of the
small databases, less than 2 GB. Then, compressing it, I have found these
to be much smaller than a compressed MDB with Jet data. That makes it
easier to take home, or email ahead.

If you're going to keep the database open at the office, with someone
posting to it while you post at home, then you've got a chore merging the
two. I'd much rather have SQL Server to do that!

The learning curve is definitely the big one. By the way, SQL 2005 does an
equivalent to cross-tabs now!

I don't follow the "self contained" thing. You don't split FE and BE in a
Jet database?

You cannot integrate VBA into a SQL Server query. Neither can you integrate
a SQL Server User Defined Function into Jet. Just two different ways to
accomplish the same thing. For anyting I've seen done in VBA, there's never
been much of a challenge creating a UDF for me. But it is an additional
skill. We're back to the same fundamental difference. There's more to
learn in SQL Server. BUT, when you've learned it, there's also more you can
do, and it performs so very, very well.

Tom Ellison

 

[Tom Ellison]

Dear Brendan:

Please forgive. I must have completely misread you. I didn't mean . . .

Tom Ellison

 

[Brendan Reynolds]

No big deal, Tom, please don't worry about it.

 

[Tom Ellison]

Dear Brendan:

As with all the other Access MVPs, you're a pleasure to "talk" with here.
It would be great to get together sometime at one of the functions.

Tom Ellison

 

[Robert Morley]

The learning curve is definitely the big one. By the way, SQL 2005 does

an equivalent to cross-tabs now!

So I've heard...I can't wait!

I don't follow the "self contained" thing. You don't split FE and BE in a
Jet database?

For a small project, no...why bother? For a project that would actually
NEED a FE/BE design, I'd probably go to SQL Server anyway.

You cannot integrate VBA into a SQL Server query. Neither can you
integrate a SQL Server User Defined Function into Jet. Just two different
ways to accomplish the same thing. For anyting I've seen done in VBA,
there's never been much of a challenge creating a UDF for me.

True enough, though I find UDF's more clunky than VBA...maybe that's just
me, though.

But it is an additional skill. We're back to the same fundamental
difference. There's more to learn in SQL Server. BUT, when you've
learned it, there's also more you can do, and it performs so very, very
well.

Hehehe...definitely no argument there. With the exception of the
cross-tab's, which are undoubtedly slower because we're faking it rather
than doing it inately, I've found SQL Server's performance beats the pants
of Access' as a general rule, though there's always exceptions.



Later,
Rob

 

[Tom Ellison]

Dear Robert:

It's good to see so much agreement. Rare, too.

I believe there are some nasty drawbacks to not splitting FE/BE with Jet. I
strongly recommend the split. Also, why backup the app every time when you
back up data?

Now, the actor by the same name is a favorite of mine. "African Queen" and
the like.

Tom Ellison

 

[Robert Morley]

I'm assuming here that the app/data are very small...one-user (who is almost
certainly also the designer), a few tables, a handful of forms, maybe not
even any VBA code. For that type of app, why worry about the complexities
of maintaining links? I'm not talking about even the smallest of
corporate databases here, I'm talking about itty-bitty pet projects that are
probably only for your own use. Backing up the data, if done at all, would
presumably be done as part of your regular personal file backups, and the
combined data/app size would be so minute (probably a few 100k, tops) as to
be negligible.

And yeah, my predecessor passed away a number of years ago. Last thing I
saw him in was the TV mini-series "Around the World in 80 Days"...with
Pierce Brosnan, if I remember correctly.


Later,
Rob

 

[Tom Ellison]

Dear Robert:

Just one word: "corruption". That's the problem. I believe there are
posts around about that, and info on various web sites.

So, are you related?

Tom Ellison

 

[Robert Morley]

What kind of corruption? I've never had any corruption problems that a
de-compile/re-compile didn't fix.

And no, to my knowledge, we're not related, or if we are, it's so far back
that we don't know.



Rob

 

[Tom Ellison]

Dear Robert:

I believe there are good articles written and available to explain this. I
don't have a reference. I recommend you ask, perhaps in tabledesign.
Someone there will likely give you a link, and you'll be better informed
than I can do. Besides, it's not a slight thing, and I'd have to type it
out. It's already done.

Tom Ellison

 

[Robert Morley]

Yeah, but I'm still not understanding the question I should be asking. If I
just ask about tables becoming corrupted, there are easily a dozen different
sources for that sort of thing. What's different about it being a
monolothic project vs. a split-end project?


Rob

 

[Brendan Reynolds]

There are problems *sharing* a monolithic project, but I am not aware of any
problems with single-user monolithic projects, other than that it makes
deploying updates more difficult. In the scenario you described, Robert,
where a single user is developing an app for their own use, deploying
updates may not seem to be an issue, but it would still be wise to test
changes against a copy of the data first. That's easier to do if the app and
data are split.