Performance question

[MikeB]

I'm not really sure where this question is best asked. If someone can
suggest a better group I'd really appreciate it.

I have an oldish (ca. 2003) 2Ghz Pentium 4 PC running Win XP Pro with
2 54rpm drives.

I am working with a database that grows by about 1,000,000 records
every 6-9 months.

Lately getting ad-hoc queries done is a real pain, by the time I get
the query done, I've forgotten my train of thought.

So I'm wondering what to upgrade.

Should I upgrade to SQLServer, or is Access suitable for databases of
this nature? Should I invest in a new Pentium Duo or does access not
really exploit dual-core processors? Does SQL Server Express or
Developer edition?

Should I instead focus on getting faster hard drives? I guess hard
drive upgrades fall in line with PC upgrade, but I guess that if
faster drives will be an improvement I can look at getting something
like RAID or SATA or something as well.

If I upgrade, I'd really like to get a better laptop rather than a
desktop, but I guess if I'm going to crunch a lot of data the
performance characteristics of the Desktop outweighs those of the
laptop?

 

[Albert D. Kallal]

I'm not really sure where this question is best asked. If someone can
suggest a better group I'd really appreciate it.

I have an oldish (ca. 2003) 2Ghz Pentium 4 PC running Win XP Pro with
2 54rpm drives.

I am working with a database that grows by about 1,000,000 records
every 6-9 months.

Lately getting ad-hoc queries done is a real pain, by the time I get
the query done, I've forgotten my train of thought.

Can you consider building a "little" data warehouse? Perhaps you can build a
munch and crunch program that spends the whole night summarizing the
millions of records into a detail summary, and then you execute your daily
queries against the smaller summarized data table (your data warehouse!)

So I'm wondering what to upgrade.

Should I upgrade to SQLServer, or is Access suitable for databases of
this nature? Should I invest in a new Pentium Duo or does access not
really exploit dual-core processors? Does SQL Server Express or
Developer edition?

The above answer is not so simple. If no network is involved, you find that
for MOST queries, ms-access (jet) is considerable FASTER then sql server.

If I upgrade, I'd really like to get a better laptop rather than a
desktop, but I guess if I'm going to crunch a lot of data the
performance characteristics of the Desktop outweighs those of the
laptop?

Notebooks are good deal weaker then desktop units. As always, I think
perhaps a design approach might fix this.

for example, it can take a large amount of time to scan 5 million records.
However, if you can query he data on an indexed column that only returns 5
records, then your query will be instant in terms of performance. Note how
the solution here was not to throw hardware at the problem, but change the
*approach* to the problem.

So, take a look at what kinds of data, and query information your actually
looking at. A human rarely needs to ever look at more then 2 dozen records.
If you presenting more records to the users, then how can they summarize and
read the data themselves? So, you might be able to process those new records
into a summary database that you use for queries.

You could certainly download and give sql server a try to see if it runs
faster. I don't think it would (sql server is more like larger boat. -- a
small speed boat can do 40 knots on the water..and carry 4 people. An
aircraft carrier can also do 40 knots, but carries a crew of 5000 people.

So, sql server is able to handle more users, and it not always going to be
much faster.

I would consider playing and trying the free edition of sql server to see if
you get more speed (my experience is you get less then what JET gives --
*assuming* no network involved for access of data).

 

[pietlinden]

And if you really want to have a lot of fun, read Ralph Kimball's
books on data warehousing... he even has one that's specifically for
SQL Server. (But that's assuming it's worthwhile to you...) You
could try creating a snowflake schema and testing with that... but I
would definitely index first. DW's can be a lot of work. That's not
to say you couldn't mark your records as you output them to your DW...
you'll no doubt need to create queries that put the data in the
"shape" the DW is in, since you're denormalizing for query speed. If
you have $50 or so, Kimball's books are a MUST read if you're
considering data warehousing. The cost of doing it wrong is steep,
and there are a lot of pitfalls to consider, and he covers them for
you, explaining various models and how they work.

 

[Guest]

SQL Server Express explicitly does not exploit dual-core.

Access can exploit dual core, but, like SQL Server, that
allows you to do more things in parallel, not one thing
faster.

The gain of SQL Server express is that it is likely to have
a more appropriate index caching plan, exploiting memory
better than Access does.

However, if you open the Access database in exclusive
mode, Windows might load the whole file into memory
(as it does when you compact the database), making the
Jet caching plan irrelevant.

For speed you should be in exclusive mode, and with a
local database, not linked, not networked.

Since the maximum mdb file size is 2GB, if you use Access/MDB
you should concentrate on getting more memory and using it,
not disk speed, except that loading 2GB from disk is slow.

If you are interested, it might be fun to look at the effect
of the size of the Jet cache. However, given how easy it
is, I would suggest converting to SQL Server Express first,
just to see what gains you find.

(david)

 

[Tony Toews [MVP]]

I am working with a database that grows by about 1,000,000 records
every 6-9 months.

That's a lot of records.

Lately getting ad-hoc queries done is a real pain, by the time I get
the query done, I've forgotten my train of thought.

I'm wondering if both your sorting fields and your selection criteria
fields are indexed?

Also in one query which was taking quite some time it turned out right
joins were my problem. Once I changed those I could to inner joins it
took one tenth the time. Trouble is if your data isn't suited to
using inner joins then you could start missing records.

Also possibly you could create intermediate summary tables with some
of your data and then build queries on those intermediate tables.

How often do you compact your MDB? How big is your MDB?

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/

 

[MikeB]

That's a lot of records.


I'm wondering if both your sorting fields and your selection criteria
fields are indexed?

I've not considered indexing, I assumed that the data would be indexed
by the primary key. For the frequency by which I reload the data, I
was also concerned that creating too many indexes would slow down the
data loading.

I can certainly consider indexing some fields.

Also in one query which was taking quite some time it turned out right
joins were my problem. Once I changed those I could to inner joins it
took one tenth the time. Trouble is if your data isn't suited to
using inner joins then you could start missing records.

This is where my knowledge is insufficient. How do I figure out if I'm
doing right joins vs nner joins vs outer joins?

Also possibly you could create intermediate summary tables with some
of your data and then build queries on those intermediate tables.

How often do you compact your MDB? How big is your MDB?

I don't often compact the MDB, on a fairly regular basis I redownload
the data and then delete all records before importing the new set of
data records.

The .MDB file is now just over 2GB.

 

[John W. Vinson]

I don't often compact the MDB, on a fairly regular basis I redownload
the data and then delete all records before importing the new set of
data records.

The .MDB file is now just over 2GB.

STOP!!!!!

2GByte is the maximum size for a MDB file. Adding another record may well
irreversibly corrupt it. Deleting and reimporting records will leave waste
space - Access does NOT clean out the deleted records.

I'd REALLY recommend the sequence:

- Back up the database
- Delete records from the tables
- Compact
- Import
- Check the size
- Compact again
- Check the size again; if it's pretty much unchanged from before the
compaction, don't bother with the second compact in the future.

Compacting will certainly improve performance. Proper indexing will make
searches run much more efficiently as well.

John W. Vinson [MVP]

 

[MikeB]

STOP!!!!!

2GByte is the maximum size for a MDB file. Adding another record may well
irreversibly corrupt it. Deleting and reimporting records will leave waste
space - Access does NOT clean out the deleted records.

I'd REALLY recommend the sequence:

- Back up the database
- Delete records from the tables
- Compact
- Import
- Check the size
- Compact again
- Check the size again; if it's pretty much unchanged from before the
compaction, don't bother with the second compact in the future.

Compacting will certainly improve performance. Proper indexing will make
searches run much more efficiently as well.

John W. Vinson [MVP]

LOL, thanks, you probably saved my bacon.

The file size is now 1,011,928KB

So before long I'll probably have to move to SQL Server just because
the file is getting too big for Access?

 

[Tony Toews [MVP]]

I've not considered indexing, I assumed that the data would be indexed
by the primary key. For the frequency by which I reload the data, I
was also concerned that creating too many indexes would slow down the
data loading.

That is correct.

I can certainly consider indexing some fields.

You may find it is faster to delete indexes, insert a lot of records
and the create the indexes.

This is where my knowledge is insufficient. How do I figure out if I'm
doing right joins vs nner joins vs outer joins?

When you look at the query does it have an arrow at either end? If
which case it's a right join.

The .MDB file is now just over 2GB.

John's already hit the high points on that sentence. <smile>

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/

 

[Tony Toews [MVP]]

The file size is now 1,011,928KB

So before long I'll probably have to move to SQL Server just because
the file is getting too big for Access?

Yes. Also SQL Server has some options such as bulk copy for
inserting lots of records quickly. Presumably this updates the
indexes once you're done inserting the records.

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/