Hi,

I have a table with 8 millions records. Some of the fields needed to
be parse and modified. Example, "ABC DEF" will be parsed into two
fields "ABC" and "DEF". And If there is "123" in the field, if will
be replaced by "456". For example, "XYZ 123 AAA" will be "XYZ 456
AAA". Also, update query and append query will be used in the
project.

Give the above information, what will be a faster database to do this
- MS-Access 2003 or SQL Server 2000?

SQL Learner wrote:

Note that an Access front-end can be connected to either a Jet or SQL
back-end. (The former leads to a self-contained .MDB file.)

Beware of premature optimization. How often do these sorts of changes
occur? Once a year, month, day, hour? If it's infrequent, then
speeding them up may be less important than optimizing other factors,
e.g. ease of use, ease of development, low memory footprint.

Hi Ed,

Thank you for your reply. I just need to know which one is faster
since the queries that used for this project can be many. If both are
the same, then I will just use Access. This is just a one time event,
but the update queries can be slow. The example that I provided was
simplified for demostration purpose only. Let's assume, I need to run
the such query on a 8 millions records table 10 times. Give I have a
2GB Rem, with very fast hard disk and fast Due CPU, Would SQL Server
be faster? If yes, by how much?

- Grasshopper -

"SQL Learner" <excelmodeling (AT) gmail (DOT) com> wrote

your skills it may take longer to write the queries on SQL Server 2000 than
it takes to write and run in Access...

Hi Russ,

To you guess, how much faster would that be? Would it be
significant?

- Grasshopper -

SQL Learner,

A benchmark test would probably be the only way to provide a difinitive
answer.

-- Bill

"SQL Learner" <excelmodeling (AT) gmail (DOT) com> wrote

SQL Learner (excelmodeling (AT) gmail (DOT) com) writes:
Benchmark, don't ask. Performance questions are very difficult to
answer up front, because there are so many "it depends". What I can
say, is that if you do this in Access, you should do it in a local
Jet database, not not a linked SQL Server table.

If you ask me to place my bets, I put them on Access for this one.
String processing is not SQL Server's best game.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

And further, I'll again suggest perl.

It may very well be faster to export the data, manipulate it in perl which
is designed for string processing and reimport it.

Especially if it appears to be a one time thing.





--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Greg D. Moore (Strider) (mooregr_deleteth1s (AT) greenms (DOT) com) writes:
I could consider doing a thing like this in Perl, since I'm fluent in
that language, and thus I could cut down the total time, that is
development + execution. Assuming, that is, it is a one-off.

If there is need to do this on a regular basis, there should be other
factors to determine whether to do this in Access or SQL Server. Just
moving the data to SQL Server - or a file - and then back, is likely
to be a loser.

Of course, it's perfectly possible to use ADO from Perl to access the
Access database.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"SQL Learner" <excelmodeling (AT) gmail (DOT) com> wrote

My guess would have been a factor of 10 since SQL server is far more
efficient in its memory management.

My expirimentation has produced the following results on a dual Xeon with 2
GB memory:

I have a working notes table that contains 9.6 million rows.

--3 minutes to copy 9.6 million rows into an expirimental table
--Added 3 varchar columns to hold expirimental results

--20 minutes to split column into two fields
UPDATE dbo.WNTemp SET Split1 = SUBSTRING(Comments, 1, 3), Split2 =
SUBSTRING(Comments, 4, 3)

--12 minutes to nullify columns, so 8 minutes for SUBSTRING split function.
UPDATE dbo.WNTemp SET Split1 = NULL, Split2 = NULL

--Export to Access mdb yields 2GB file

--Same query using Mid function in place of SUBSTRING function

--Access at 3 minutes gave modal warning that no undo is possible
--At 34 minutes gave error and stopped at 8.1 million records.
--According to Task Manager Access read 3.8GB and wrote 1.6 GB during
partial update

My conclusion is that Access is only moderately slower, but I would guess
that the difference would grow if the width of the table was larger.

Although a partial update is nice in this circumstance, I believe it would
be a negative result in a production environment.