130m rows in MDB? MDB vs ADP performance lol

[aaron.kempf]

Hey do you guys remember when someone was talking about 120m rows in
an Access MDB?

I want to talk to that person.. aka challenge them to disclose all of
the schema information in this test.

I can demonstrate a full fledged data warehousing / spidering
database-- that I've used for testing-- that supports 500m rows in a
similiar sized database.. And this is a real system that has 3
columns and it's _EXACTLY_ suited for the purposes of spidering--
which I do a lot of.

I just thought that it would be nice; once and all-- to do some
performance tests... to compare Access and SQL performance.

Can we all agree on some basic performance tests?

Thanks

-Aaron

 

[Jerry Whittle]

Hi Aaron,

It was I. We had a little contest to see the maximum number of rows in a
single MDB file. Basically all the attempts were just one table with one
field and no indexes. I tried both a Yes/No data type and Text set to 1
filled with nulls.

If you can squeeze in 500m rows of useful information into one MDB file, I'd
be very impressed.

 

[Wayne-I-M]

Hi Aaron

Even though it "may" (?) be possible to run ###m's of rows in a DB - I think
you'll agree thats not a fair test. Access is used for all the other "stuff"
it can do. SQL is operated for the other stuff "it" can do. A simple test
of running a few fields until a processes crashes isn't going to prove
anything other than the limits of storage of non functioning data.

But - LoL if you have an sql with 500m rows that still would be something to
see.

 

[John W. Vinson]

I can demonstrate a full fledged data warehousing / spidering
database-- that I've used for testing-- that supports 500m rows in a
similiar sized database.. And this is a real system that has 3
columns and it's _EXACTLY_ suited for the purposes of spidering--
which I do a lot of.

Sure. Nobody's going to argue with you on this, Aaron, because you're quite
correct: a JET mdb file cannot handle 500,000,000 rows. We agree. SQL is
indeed the correct tool for this application.

I'll also say that if I had to haul twenty one-ton hay bales I'd use a big
semitrailer truck instead of a Ford F150 pickup. Does that mean that Ford F150
pickups are worthless?

 

[Tony Toews [MVP]]

I'll also say that if I had to haul twenty one-ton hay bales I'd use a big
semitrailer truck instead of a Ford F150 pickup. Does that mean that Ford F150
pickups are worthless?

Small square bales or large round bales? <smile>

Tony (moved many a small square bale as a teenager)
--
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/

 

[John W. Vinson]

Small square bales or large round bales? <smile>

Tony (moved many a small square bale as a teenager)

Just saw two semis as I was driving into Parma - each had about twelve
rectangular bales. Each bale was about as big as a Ford F150 pickup... that's
what brought the analogy to mind.

 

[aaron.kempf]

I used to work at Microsoft doing 'intrusiton detection' and we got
300 million rows-- 100 gb of data per day... We kept track of all of
the internet traffic for Microsoft worldwide I think.. something
ridiculous like that.

I'm still trucking on this; if my calculation are right.. yah 500m.
I'll send an update when I'm a lot closer on the watch.
Gosh bit fields huh? I'll bet I could do 4 billion then ROFL jk

-Aaron

 

[aaron.kempf]

in the same 2gb container?

Sure. Nobody's going to argue with you on this, Aaron, because you're quite
correct: a JET mdb file cannot handle 500,000,000 rows. We agree. SQL is
indeed the correct tool for this application.

I'll also say that if I had to haul twenty one-ton hay bales I'd use a big
semitrailer truck instead of a Ford F150 pickup. Does that mean that Ford F150
pickups are worthless?

 

[aaron.kempf]

btw; anyone want a copy of that MS intrusion detection data.
I think that I'm past my 3 years' statute of limitations.. isn't that
how it's supposed to work??

(WHAM as the ms gestapo breaks down my door)

-Aaron

 

[aaron.kempf]

and if the F150 was 'harder to drive' and 'more expensive to purchase'
and 'more expensive to maintain' and 'the people that used it cost the
same amount (aka CDL -> MCDBA)'

let alone
safer
more reliable
more secure
more popular

Then yah.. If a truck-- the same size-- could hold 21 times as much..
yah I think that the F150 would be pretty worthless.

I'll give you guys another update in a day or two; maybe a week lol

-Aaron

 

[Wayne-I-M]

There is 2 expresion in England
Horses for Courses
and
The right tool for the job
??

--
Wayne
Manchester, England.

and if the F150 was 'harder to drive' and 'more expensive to purchase'
and 'more expensive to maintain' and 'the people that used it cost the
same amount (aka CDL -> MCDBA)'

let alone
safer
more reliable
more secure
more popular

Then yah.. If a truck-- the same size-- could hold 21 times as much..
yah I think that the F150 would be pretty worthless.

I'll give you guys another update in a day or two; maybe a week lol

-Aaron

 

[aaron.kempf]

I just don't think that MDB is a tool.. anymore than VietNam was a
'vacation' for Ameircan troops in the 60s and 70s.

-Aaron

 

[rumkus]

I just don't think that MDB is a tool.. anymore than VietNam was a

'vacation' for Ameircan troops in the 60s and 70s.

It is a very good tool actually at least for me along with excel. They
help me organise our work stations work load , do critical
calculations etc. People say there are other similar tools I can use.
Probably i say but never used.They just come handy i think. I even use
excel for my letters. It is pity i can't use mdb for it. Don't know
about your Vietnam story but i think MDB is a very useful tool and am
very thankful to the producer.

rgds

 

[aaron.kempf]

I disagree.

Just because a bunch of retards on this newsgroup- think that living
with unacceptable performance, stability and reliabilty.. It doesn't
mean that is what is best for you.

SQL Server has better tools; it is 'just as free' and it runs 10+
times faster.

Do you like PivotTables on steroids (OLAP / SSAS)? PDFs getting
emailed to you once a night automatically?
Those are included with the standard edition of SQL Server.

The free edition (express) is great with Access 2007.
MSDE - any flavor - is great with Access 2000-2003.

Good luck in waking up and smeling the coffee---

Honestly-- you'll need it.

-Aaron

 

[rumkus]

I disagree.

Just because a bunch of retards on this newsgroup- think that living
with unacceptable performance, stability and reliabilty.. It doesn't
mean that is what is best for you.

A bunch of retards here we old engineers had had a motto for along
time which was " handsomely shaped ideas is our steam
locomotive" .Latest technologies pressed us to change it to " Our
steam locomotive is still handsomely shaped ideas but only when
prompted by work analysers"
I'll pass your comments to them as mine to save you.They are quite old
but wear very modern glasses. A bunch of diehards they are and rarely
look to computers for help.

Thank you for reminding me coffee traditionally we have tea at
breakfast table.

Rgds

 

[bcarlson50]

Hi Aaron

Even though it "may" (?) be possible to run ###m's of rows in a DB - I
think
you'll agree thats not a fair test. Access is used for all the other
"stuff"
it can do. SQL is operated for the other stuff "it" can do. A simple
test
of running a few fields until a processes crashes isn't going to prove
anything other than the limits of storage of non functioning data.

But - LoL if you have an sql with 500m rows that still would be something
to
see.

 

[Bruno Laurans]

strdyfguhjk


Le 28/03/08 21:28, dans (e-mail address removed),

 

[aaron.kempf]

You use Excel for your letters?

That's funny.. I use Access for my letters LoL
I did some form letters with SQL Reporting Services a couple of
times.. that's a pretty sharp tool!

-Aaron