SQL query slow

[Sonnich Jensen]

Hi

In the code below I found that it takes 1.3 seconds to load.
There is a possible reason, it reads from a local MDB file (access),
and it reads from some 270000 rows (result some 15-20 rows, where the
highest are counted 150000, 50000, 40000, 26000, remaining are 100's).

This is probably more Access that Visual Studio, though - is there a
way to split it up or cancel the query?
And can I read this faster?

string sql =
"SELECT b.name, a.event_id, count(a.event_id) as x
" +
"FROM events a, events_descriptions b" +
" WHERE a.event_id=b.id and a.equipment_id=" +
EquipmentID(Equipment) +
" and record_time>=" +
StartDate.ToOADate().ToString(FloatFormat, myCulture) +
" and record_time<=" +
EndDate.ToOADate().ToString(FloatFormat, myCulture);
sql += " group by event_id,name";
_Log("Calc thread #2");
OleDbCommand com = new OleDbCommand(sql, con);
_Log("Calc thread #3");

From #2 to #3 it takes 1.3 seconds. Remaing code takes less that 0.1
second.
So the query is what slows it down.

 

[Marcel Müller]

Hi,

This is probably more Access that Visual Studio, though - is there a
way to split it up or cancel the query?
And can I read this faster?

string sql =
"SELECT b.name, a.event_id, count(a.event_id) as x
" +
"FROM events a, events_descriptions b" +
" WHERE a.event_id=b.id and a.equipment_id=" +
EquipmentID(Equipment) +
" and record_time>=" +
StartDate.ToOADate().ToString(FloatFormat, myCulture) +
" and record_time<=" +
EndDate.ToOADate().ToString(FloatFormat, myCulture);
sql += " group by event_id,name";
_Log("Calc thread #2");
OleDbCommand com = new OleDbCommand(sql, con);
_Log("Calc thread #3");

From #2 to #3 it takes 1.3 seconds. Remaing code takes less that 0.1
second.
So the query is what slows it down.

Besides that your code is full of SQL injection bugs because of missing
parameter bindings, you need to analyze the SQL. Create an execution
plan of your statement. The free SQL server edition might help for this
task. And an appropriate index might help to avoid the full table access.

By the way, grouping by a.event_id in conjunction with count(a.event_id)
makes no sense to me.


Marcel

 

[MiB]

In the code below I found that it takes 1.3 seconds to load.

No big surprise. The WHERE condition causes a full table scan, i.e. in
order to find out if a record from your db is in the result set or
not, each and every record needs to be loaded and compared. The JET
engine behind Access is not suited to handle databases beyond stamp
collection size and lacks most features of a "real" database
management system.

Consider using a relational database, i.e. SQL Server Express, its
free. Make sure to set primary key properties to
events_descriptions.id and mark a.events_id as a foreign key to
events_descriptions.id.
Add a clustered index to the record_time column. If you insist on SQL,
I'd change your code like this:

string sql = String.Format( @"
SELECT b.name, a.event_id, COUNT( a.event_id ) AS x
FROM events a INNER JOIN events_descriptions b ON a.event_id =
b.id
WHERE a.equipment_id = {0} AND record_time BETWEEN {1} AND {2}
GROUP BY event_id, name",
EquipmentID( Equipment ),
StartDate.ToOADate().ToString( FloatFormat, myCulture ),
EndDate.ToOADate().ToString( FloatFormat, myCulture )
);

I expect the query time to go down by a factor of 15-20.

However, using SQL strings like this feels sooo 1980. Consider
switching to an entity framework data model and use LINQ to query the
DB. You get client side caching for free and don't need to worry about
problems like SQL injection attacks to you application.

best,

MiB.

 

[Access Developer]

The JET engine behind Access is not suited
to handle databases beyond stamp
collection size and lacks most features of a
"real" database management system.

The JET database engine is often faster and more efficient than similar
retrieval with SQL Server. And simply switching to SQL Server often results
in poorer, not better, performance.

Biased statements such as yours simply expose your lack of knowledge -- if
you choose to make denigrating claims about Jet or Ace, you should restrict
your post to the dotnet / csharp newsgroups, so those in
comp.databases.ms-access who _do_ know about Jet and Ace won't see and
correct your erroneous claims.

Larry Linson
Microsoft Office Access MVP
Co-Author, "Microsoft Access Small Business Solutions", Wiley, 2010

 

[Albert D. Kallal]

"MiB" wrote in message

No big surprise. The WHERE condition causes a full table scan, i.e. in
order to find out if a record from your db is in the result set or
not, each and every record needs to be loaded and compared.

Utter rubbish and utter nonsense. If a index is available then a full table
scan does not occur.

And as pointed out on the same machine and hardware, JET in MOST cases is
faster than SQL server and by a good margin.

One main reason of course is the type of connection to the data, if you have
to connect to SQL server, you're forced to go through a socket type of
connection, and often this force you to utilize and network stack, even on
your local machine.

The jet data engine essentially becomes an "in process" hunk of code that is
not even being executed as a service. As a result you don't have a layer of
transactions, or network stacks between you and your data, but essentially a
system that scrapes data right off the disk drive. So even the inter process
communication here is faster when using JET. And since much less ram is
being used, then again additional benefits arise here.

As a result, in MOST cases as noted on the same hardware and same machine;
jet is substantially faster than SQL server.

Basically your advice comes down to not knowing much of anything about
databases, not understanding computer architecture, and your response can
simply can be summed up as your general ignorance on the matter how
computers and database engines function and operate in our industry.

So your galactic stupidity and ignorance is well noted here.

The beautiful thing about the Internet and Usenet?
Your stupidity will live with you for years to come since Usenet has a great
memory like an elephant and all that is stated here goes down as recorded
history.

 

[MiB]

On Mar 5, 6:44 pm, "Albert D. Kallal" <[email protected]>
wrote:
[Comments not included due to utter lack of manners on behalf of Mr.
Kallal.]

Dear Mr. Kallal,

Access' default JET engine, like it or not, was abandoned by its very
maker Microsoft, first in favor of MSDE and later for SQL Server
Express. There was no new version for more than 10 years - version 4
came with Office 2000. If JET is so vastly superior to regular
database servers, kindly share the pearls of your wisdom and explain
why companies like Oracle (guess what product) or Microsoft (SQL
Server) prefer to license these for plenty-a-thousands of bucks while
JET is a free download. This does not really occur to me as sound
economic rationale, but I am sure you will enlighten us.

Can you also explain, please, why database client products like
Sparxsystems Enterprise Architect using the same relational schema,
same index structures, and same SQL statements alternatively targeting
JET 4 and SQL Server require substantially more time (like 2 to 4
times) for the same operations when used with JET? Maybe the people
behind are galactic stupid and ignorant, just like me?

Of course, I am a lowly university graduate of information sciences
with twenty-four years of job experiences only. I am certainly not in
a position to criticize an important MVP like you. Maybe this source
is: http://databases.aspfaq.com/database/what-are-the-limitations-of-ms-access.html.
I would like to humbly hint your highness to the collection of
references on that site to Microsoft's knowledge base articles that
make clear why JET is crap, i.e. MDB databases going corrupt, which is
unacceptable even if you merely store your stamp collection in it.

Sequaciously,

Michael Böhnisch,
Essen, Germany

 

[Patrick Finucane]

No big surprise. The WHERE condition causes a full table scan, i.e. in
order to find out if a record from your db is in the result set or
not, each and every record needs to be loaded and compared. The JET
engine behind Access is not suited to handle databases beyond stamp
collection size and lacks most features of a "real" database
management system.

Consider using a relational database, i.e. SQL Server Express, its
free. Make sure to set primary key properties to
events_descriptions.id and mark a.events_id as a foreign key to
events_descriptions.id.
Add a clustered index to the record_time column. If you insist on SQL,
I'd change your code like this:

string sql = String.Format( @"
    SELECT b.name, a.event_id, COUNT( a.event_id ) AS x
    FROM events a INNER JOIN events_descriptions b ON a.event_id =
b.id
    WHERE a.equipment_id = {0} AND record_time BETWEEN {1} AND {2}
    GROUP BY event_id, name",
    EquipmentID( Equipment ),
    StartDate.ToOADate().ToString( FloatFormat, myCulture ),
    EndDate.ToOADate().ToString( FloatFormat, myCulture )
);

I expect the query time to go down by a factor of 15-20.

However, using SQL strings like this feels sooo 1980. Consider
switching to an entity framework data model and use LINQ to query the
DB. You get client side caching for free and don't need to worry about
problems like SQL injection attacks to you application.

best,

   MiB.

Creeating a data string in memory should be near instantaneous and is
expected. I'm not sure why the OP would expect retrieving data from a
disk drive or CD/DVD drive to return faster or as fast results than
data in memory.

Filtering 270,000 records in 1.3 seconds from a disk drive to a result
set seconds seems snappy. I might suggest the OP spring for some
faster, more up-to-date and state-of-the-art hardware if he's bitching
about a 1.3 second return from that filter.

 

[MiB]

Biased statements such as yours simply expose your lack of knowledge -- if
you choose to make denigrating claims about Jet or Ace, you should restrict
your post to the dotnet / csharp newsgroups, so those in
comp.databases.ms-access who _do_ know about Jet and Ace won't see and
correct your erroneous claims.

 Larry Linson
 Microsoft Office Access MVP
 Co-Author, "Microsoft Access Small Business Solutions", Wiley, 2010

Dear Mr. Linson,

Unlike Albert D. Kallal MVP in this same thread you seem not inclined
to attack me on a personally derogative level. Thank you for that.

First of all, I was not talking about ACE at all, and I do not mind
being corrected where I am wrong when its done in a civilized manner.
Actually, I was responding to a question in
microsoft.public.dotnet.languages.csharp unaware of the crosspost to
your obvious home base. If I am not welcome here, so be it.

However, I *do* have some experience with JET and it should be
legitimate even in comp.databases.ms-access to criticize it. Heck, I
have no problems whatsoever with Access, only with the JET engine. I
lost more than one mdb file to corruption, mostly due to transactions
larger than JET can handle or other limitations, and I am almost
certain you will not claim it never happened to you. Also, I did a
performance analysis for a customer just three years ago where JET
ended up dead last. The task was simple and local to one machine
sharing client and server - an environment where JET should excel;
transfer a database stored UML model of roughly 170MB from a JET mdb
file to a different DBMS. Candidates for the target platform were
Oracle Express, MySQL, PostgreSQL, SQL Server and a second JET
database for reference, all tests done on Windows Server 2003, same
hardware, only one dbms installed at any given time. SQL Server (15
Minutes) won the competition hands down, outperforming runner-up
PostgreSQL (25 Minutes), Oracle and MySQL shared 3rd place, both at 30
minutes and left JET last at 1 hour and 5 minutes. JET as target
required changes to the Windows registry entry MaxLocksPerFile, or the
transfer would crash the application outright.
I expected different myself, guessing Oracle or MySQL should have the
edge. Maybe on a different operating system they would. Access was not
even involved, JET was connected via ODBC from Sparx' Enterprise
Architect doing the data transfer.

Other than trivial cases where establishing the initial database
connection outweighs the actual transaction processing, I've never
experienced JET being faster than SQL Server, or any other major
relational dbms. Your mileage may vary, I am only talking about me,
not insinuating you're wrong just because your experience is different
from mine. I expect the same respect in return.

best,

Michael Böhnisch

 

[Albert D. Kallal]

"MiB" wrote in message

Dear Mr. Kallal,

Access' default JET engine, like it or not, was abandoned by its very
maker Microsoft, first in favor of MSDE and later for SQL Server
Express. There was no new version for more than 10 years - version 4
came with Office 2000.

The above is incorrect, it was not abandoned, and it's been a superseded by
a new version of the engine called ACE.
This new version has support for database triggers, stored procedures, 64
bit edition. This new data engine also supports offline data caching and
disconnected mode when you connect to SharePoint.

It's not my fault you have such galactic ignorance about the product you
speak of.

If JET is so vastly superior to regular
database servers,

Now why you're being so ridiculous and ignorant and stupid here?
I always lament the fact when someone is shown to be rather stupid and
ignorant in public, they resort to FABRICATION and lying here?
Why are you putting words into my mouth and for what reason do you resort to
such underhanded claims?

I made no claim of "vastly superior" and such a it's not only a ridiculous
claim on your part, but it's an arrogant fabrication of facts and evidence
on your part.

I stated that on the same hardware in many cases the Access data engine is
going to read data faster than SQL server. I know this is so because there's
less software layers that you have to go through to interact with the actual
data file on the disk drive. And once again if you carefully read my
statement, obviously this is in the context of not using database over a
network.

I stand by the statements made and that of the speed of retrieving data
using the Access data engine is faster. And it is faster because of a more
of a direct pathway between the software and the disk drive reading the
data. You do not even have a ACID transaction layer here.

I made no such claims as to performance when a network is involved here and
your straw man "attempt" at putting the words "vastly superior" into my
mouth really is a testimony to your lack of integrity as a human being and
having an intelligent discussion.

You made a fool of yourself with errors in your first post, and now your
only way out is to make lame attempts at attributing statements not make by
me? I find such lack of integrity on your part morally reprehensible and
disgusting.

I should also point out that the base DAO technology used in Access 2010 is
one of the first products to have built in and baked in support for SQL
Azure. Once again, so much for obsolescence.

In other words while oleDB connections to Azure SQL are not allowed,
Extensions backed into Access 2010 allows the product to happily connects to
SQL Azure.

kindly share the pearls of your wisdom and explain
why companies like Oracle (guess what product) or Microsoft (SQL
Server) prefer to license these for plenty-a-thousands of bucks while
JET is a free download. This does not really occur to me as sound
economic rationale, but I am sure you will enlighten us.

Yes as a matter of fact I can enlighten you.
Only a foolish ignorant person would deny that ALL OF the above vendors
including Oracle have express and lightweight editions of their database
engines for free.

So in other words basing the cost as to this meaning that the higher cost
data engines read data faster now? It not at all clear what your logical
assumption is based on here, but the price will not as a general rule change
the speed of reading data from the disk drive. (how stupid can you get?).

And then again let's not forget MySQL or PostgreSQL.

So your above straw man point is that PostgreSQL is a no good because of the
cost? So you mean that PostgreSQL is to be eliminated as a serious choice
based on your above logical reasoning?

Gee, that makes even worse sense then your previous points!

So now we eliminate PostgreSQL because of the cost? So now lets ignore
performance and only look at cost? Too funny! So if a product is less cost
but performs better you going to choose based on price? Now this is becoming
really funny!

So are you now actually standing in public attempting to make the logical
assumption that the amount you spend on that data engine is going to change
how fast the system can read data from the file? In fact I don't even
believe there's a mathematical and financial correlation between the above
point that reading rows of data from a data file is going to occur faster as
you move up the price list.

In fact, in most cases the speed of reading data is not changed, but the
capacity to use more processors and more memory, or scale out to more
servers is the issue.

So in most cases the read rate of rows does not change from that file. And
as I stated the Access data engine is faster than SQL server in the above
mentioned scenario in most cases (and I happen to the know the cases where
it performs slower also!).

Can you also explain, please, why database client products like
Sparxsystems Enterprise Architect using the same relational schema,
same index structures, and same SQL statements alternatively targeting
JET 4 and SQL Server require substantially more time (like 2 to 4
times) for the same operations when used with JET? Maybe the people
behind are galactic stupid and ignorant, just like me?

You'll have to cite the environment and scenario you speak of, and you'll
have to cite if a network is involved or not.
But then again, the fact that you failed to include the context and
environment already shows your lack of knowledge.
And you have to cite what they are doing and is the code optimized for a
particular setup.

Of course, I am a lowly university graduate of information sciences
with twenty-four years of job experiences only

Then why are you making such stupid and galactic unfounded statements of
which are completely fabricated and not true?
You're clearly not aware of the new JET engine called ACE.
You not aware of why a local data engine reading data from a file can be
faster even on the same hardware compared to a service based system like SQL
server.
And this idea about cost = speed of reading rows? Well, that even more
silly!

The new ACE engine has store procedures, table triggers, features like a
disconnected offline mode with SharePoint, a 64 bit edition all point to
showing that this database engine for use with Access is continuing to
receive investment dollars and new features from the folks in Redmond.

It's not my fault you don't know about these things. Hey, I enjoy seeing you
put your foot in your mouth!

So if your information and evidence you provide here is consistently shown
to be incorrect, and you consistently show a lack of knowledge about the
state of our industry, why should I respect anything of your opinion here
then?

Maybe you should do a bit of reading and learning about something before you
speak and make such a fool of yourself.

In a nutshell, you have to step it up a notch or two, since right now you
are showing you spent a lot of years in this industry but failed to learn
basic concepts along the way.

 

[MiB]

On Mar 5, 10:22 pm, Patrick Finucane <[email protected]>
wrote:
[..]

Filtering 270,000 records in 1.3 seconds from a disk drive to a result
set seconds seems snappy.  I might suggest the OP spring for some
faster, more up-to-date and state-of-the-art hardware if he's bitching
about a 1.3 second return from that filter.

The point is the selectivity of the query, there are 15 to 20 result
records only compared to 270,000 table records. Reading all these
records in 1.3 seconds is "snappy" indeed but could be avoided
outright by a clustered index on the date field referenced in the
WHERE clause of the statement. A database engine capable of doing
range based queries should require logarithmic time (proportional to
log 270.000) only to locate the first and last records of the index
sequence containing the requested records. This can be enhanced by
caching index data in memory, making file system reads unnecessary.
Lacking an index all candidate records need to be touched, from disk,
linear proportional to the number of table entries.

My estimate is a processing time in the order of a few dozen
milliseconds instead of 1.3 seconds.

best,
Michael Böhnisch

 

[MiB]

In a nutshell, you have to step it up a notch or two, since right now you
are showing you spent a lot of years in this industry but failed to learn
basic concepts along the way.

At least *I* learned some manners, a basic concept in communication.

 

[Albert D. Kallal]

"MiB" wrote in message

At least *I* learned some manners, a basic concept in communication.

You made some mistakes here, and you simply suffering from this.

First of all, I was not talking about ACE at all, and I do not mind

being corrected where I am wrong when its done in a civilized manner.

Unfortunately that's not what occurred, YOU MADE the assumption that we're
not talking about ACE and jumped on this to denigrate the fact and give the
impression that somehow the Access product is old and not receiving any more
development.

The context the original post does NOT restrict this discussion to JET and
it NEVER EVER did. And in fact it does not reference it by name.

YOU ARE THE ONE IN PUBLIC that choose to make this assumption, and as I'm
pointing out it's a bad assumption on your part.

it should be

legitimate even in comp.databases.ms-access to criticize it. Heck

I have no problem to the criticisms, but not ones that are based on false
information and incorrect information? Yes, I most certainly do!

, I did aperformance analysis for a customer just three years ago where JET

ended up dead last.

As noted, you'll have to inform us of what technologies were used, and such
things as which drivers and what vendors provided the software stack and
layers you were utilizing.

Only a person of great foolhardiness would make assumptions that these
issues inform you as to JET being slower here.

I've never

experienced JET being faster than SQL Server, or any other major
relational dbms. Your mileage may vary, I am only talking about me,
not insinuating you're wrong just because your experience is different
from mine. I expect the same respect in return.

Then limit your comments to your own personal experience, and don't attempt
to make an industry wide generalization here as you did.

So sure, ok, then you now speak of YOUR individual experience? Sure, ok
that's different, but that is not what you did and that is why I did not
give your position any respect.

In fact, with a local data engine, you're not even going to have to use a
oleDB layer nor do you need to use some some type of ODBC driver. This right
out of the box pretty much hands the winning speed to using the Access
database engine here.

The idea that you're hoping people here don't have the knowledge and are
assuming that the people here have ignorance of these architectures and
differences is a big mistake on your part. Nothing worse is when people
attempt to use their position in public to denigrate a product without
merit. Again: the issue is not criticisms, its criticisms without merit.

As I pointed out, you'll have to outline the scenario in which you that
experienced less performance, and I can pretty much assure you that I can
point out the bottlenecks and eliminate them.

Once again, I stand by that in most cases on the same hardware you're going
to find read and update speed of access data engine faster than that of SQL
server simply based on the architecture and understanding of the differences
of how they operate and that of the software layers you'll be forced to
interact with to get at the data.

Anyone of experience and credibility will have taken the time to understand
both technologies will realize why and how you get better performance with
the Access setup in the constraints of the original question. I know this
to be the case because I use SQL server on a daily basis.

The other point here?

You should be careful who's tree you piss on, since some people might point
out that you are in fact pissing and not spewing out facts.

 

[Arne Vajhøj]

In the code below I found that it takes 1.3 seconds to load.
There is a possible reason, it reads from a local MDB file (access),
and it reads from some 270000 rows (result some 15-20 rows, where the
highest are counted 150000, 50000, 40000, 26000, remaining are 100's).

This is probably more Access that Visual Studio, though - is there a
way to split it up or cancel the query?
And can I read this faster?

string sql =
"SELECT b.name, a.event_id, count(a.event_id) as x
" +
"FROM events a, events_descriptions b" +
" WHERE a.event_id=b.id and a.equipment_id=" +
EquipmentID(Equipment) +
" and record_time>=" +
StartDate.ToOADate().ToString(FloatFormat, myCulture) +
" and record_time<=" +
EndDate.ToOADate().ToString(FloatFormat, myCulture);
sql += " group by event_id,name";
_Log("Calc thread #2");
OleDbCommand com = new OleDbCommand(sql, con);
_Log("Calc thread #3");

From #2 to #3 it takes 1.3 seconds. Remaing code takes less that 0.1
second.
So the query is what slows it down.

Do you have index on:

a.event_id
a.equipment_id
b.name

?

(I assume that b.id is PK)

Arne

 

[Arne Vajhøj]

Besides that your code is full of SQL injection bugs because of missing
parameter bindings,
Where?

you need to analyze the SQL. Create an execution
plan of your statement. The free SQL server edition might help for this
task.

But no guarantee that SQLServer analysis will help with Access.

And an appropriate index might help to avoid the full table access.

Best guess.

By the way, grouping by a.event_id in conjunction with count(a.event_id)
makes no sense to me.

If a.event_id is not unique or if it is a join (which it is in
this case), then there can be more than one row with the
same a.event_id.

Arne

 

[Arne Vajhøj]

No big surprise. The WHERE condition causes a full table scan, i.e. in
order to find out if a record from your db is in the result set or
not, each and every record needs to be loaded and compared.

That depends on whether there are indexes on the field or not.

The JET
engine behind Access is not suited to handle databases beyond stamp
collection size and lacks most features of a "real" database
management system.

Terms as "real database" is not a technical term used
by IT professionals.

Consider using a relational database,

Access is a relational database.

However, using SQL strings like this feels sooo 1980. Consider
switching to an entity framework data model and use LINQ to query the
DB.

LINQ and EF is from 2008 if I remember correct, so not using it can
not really by so 1980.

You get client side caching for free

EF only has first level cache, so if cache is the main criteria,
then go for something more mature like NHibernate.

Arne

 

[Arne Vajhøj]

The JET database engine is often faster and more efficient than similar
retrieval with SQL Server. And simply switching to SQL Server often results
in poorer, not better, performance.

For a desktop app, then that seems pretty logical.

Not quite as logical for a 4 node ASP.NET cluster
hitting a shared database.

To me Access/Jet and SQLServer are intended for
different purposes with relative little overlap.

Access/Jet and SQLServerCE must have a lot over
overlap in usage scenarios.

Arne

 

[Arne Vajhøj]

in message



Utter rubbish and utter nonsense. If a index is available then a full
table scan does not occur.

And as pointed out on the same machine and hardware, JET in MOST cases
is faster than SQL server and by a good margin.

I think "most cases" may depend a bit on what type of app
you work with.

One main reason of course is the type of connection to the data, if you
have to connect to SQL server, you're forced to go through a socket type
of connection, and often this force you to utilize and network stack,
even on your local machine.

SQLServer support named pipes for local access as well.

Arne

 

[Arne Vajhøj]

Access' default JET engine, like it or not, was abandoned by its very
maker Microsoft, first in favor of MSDE and later for SQL Server
Express.

Nope.

MS continued shipping Jet with products until 2007 when ACE arrived.

And that is long after MSDE and SQLServer Express showed up.

And why not - database servers and embedded databases really
targets different usage scenarios.

Can you also explain, please, why database client products like
Sparxsystems Enterprise Architect using the same relational schema,
same index structures, and same SQL statements alternatively targeting
JET 4 and SQL Server require substantially more time (like 2 to 4
times) for the same operations when used with JET? Maybe the people
behind are galactic stupid and ignorant, just like me?

There can be many reasons for that.

It is not really surprising that different benchmarks gives
different results.

Arne

 

[The Frog]

I would just like to point out my delight at seeing two of my
newsgroup favourites from totally different realms in the one thread.
I don't want to come across as a brown noser here, just letting you
know the pleasure I derive from seeing two of the most experienced
people working on the same problem.

MiB, I am sorry to say for you that in this context your attitude
towards both people and products, as well as your knowledge and
experience with them are dead wrong. Provably, repeatably, dead
wrong. Taking the classic German position if ' I'm right no matter
what anyone says because it is simply so' isn't going to help you
make any valid arguments against the experience and intellects you
are encountering here. Please drop the crap and stick to provable
facts.

Es ist was es ist und Sie können es nicht kampfen.

As for the benchmarks you used, I would question any operation that
took more than 10 mins or so to write 170mb to an mdb file. Appalling
test rig. MySql could have cleaned them all up with a MEMORY
database, it would all be in RAM. Seriously go and speak to some ETL
experts for a better test than whatever rubbish you devised. Your
opinions are not facts, and never could be. Make peace with it.

The Frog

 

[MiB]

You made some mistakes here, and you simply suffering from this.

Re-read the thread, please, and note carefully that you attacked me ad
hominem, dishing out insults. I am not "suffering" any mistakes just
for having different experience or opinion compared to yours.

Unfortunately that's not what occurred, YOU MADE the assumption that we're
not talking about ACE and jumped on this to denigrate the fact and give the
impression that somehow the Access product is old and not receiving any more
development.

Since your post responded mine, and you did not contribute to this
thread before, doesn't it feel a little prepostrous to claim I did not
know what you are going to talk about?
Please note also, I explicitly referred to the JET engine, not to
Access. You call yourself Access MVP and still seem to identify the
*GUI* Access with the DB engine it connects to. Access may have its
uses as a functionally limited replacement for BIDS in cases where the
complexity of such a tool is not required, plus as a platform for form-
heavy applications. In the program of the original poster he does not
say he is using Access at all, so why do you bend my words into caring
about the Access product? He does mention file-based MDB, though, I
can have that without installing Access.

The context the original post does NOT restrict this discussion to JET and
it NEVER EVER did. And in fact it does not reference it by name.

My original post explicitly references JET, not ACE, not MSDE. You are
committing a straw man fallacy yourself.
Actually, the original poster did not describe his environmental
scenario at all, so jumping to *your* conclusion its about a local app
with local database has no more merit than mine.

YOU ARE THE ONE IN PUBLIC  that choose to make this assumption, and as I'm
pointing out it's a bad assumption on your part.

Yes, and its my right to do so. I will say what I want in public and
you will not succeed in trying to silence me. Think about the picture
you present to the PUBLIC by giving bad names to others.

I have no problem to the criticisms, but not ones that are based on false
information and incorrect information? Yes, I most certainly do!

You have no idea on what information I base my criticism on. So how
come you infere they are wrong and incorrect?

As noted, you'll have to inform us of what technologies were used, and such
things as which drivers and what vendors provided the software stack and
layers you were utilizing.

Same goes for you. You did not give any source for your information,
so why should I believe you?

Only a person of great foolhardiness would make assumptions that these
issues inform you as to JET being slower here.

Ad hominem, again. Only a person of great foolhardiness would make the
assumption that JET is faster with no reference to tests given at all.

Then limit your comments to your own personal experience, and don't attempt
to make an industry wide generalization here as you did.

You don't do that either, so why should I?

So sure, ok, then you now speak of YOUR  individual experience? Sure, ok
that's different, but that is not what you did and that is why I did not
give your position any respect.

Or thought. What resources do YOU have other than your individual
experience? Marketing flyers? I believe you are biased because you
make a living out of Access and anything that tarnishes your favourite
toy feels like an attack on your livelihood. Get real.

In fact, with a local data engine, you're not even going to have to use a
oleDB layer nor do you need to use some some type of ODBC driver. This right
out of the box pretty much hands the winning speed to using the Access
database engine here.

Why not go all the way, then? Lets store all data in a text file. Make
it XML if you must. Read everything into memory (i.e. containers of
business classes) on application start and write everything on exit.
No need for clumsy SQL as intermediate interpreted language, use the
methods of your compiled programming language of choice. Must be
faster than JET, too. If you always write to a new file you'd even
avoid the data integrity corruptions that haunt mdb files.

The idea that you're hoping people here don't have the knowledge and are
assuming that the people here have ignorance of these architectures and
differences is a big mistake on your part.

The idea that any <insert your favourite tool here> is the center of
the universe is a big mistake on your part. I consider Access a niche
product, limiting applications to one single operating system platform
on client and server. Its default database engine has severe
limitations on database size, number of concurrent users, size of
transactions, no backup and cannot handle load well. There is a good
probability the database files corrupt on error conditions, and there
is often no recovery. Corruption can be the outcome of application
malfunction, accidental system shutdown, and more. When it happens,
JET can not read the database and its beyond repair (JETCOMP will
fail).

Nothing that encourages me to use it in any project. And yes, I can
give references to any of my statements, including coming from
Microsoft. I doubt you are unaware of these issues, try Google, but
feel free to ask for specific sources and I'll help you out.

Nothing worse is when people
attempt to use their position in public to denigrate a product without
merit. Again: the issue is not criticisms, its criticisms without merit.

See above. No merit?

As I pointed out, you'll have to outline the scenario in which you that
experienced less performance, and I can pretty much assure you that I can
point out the bottlenecks and eliminate them.

There is nothing you - or I - could have done. We had to choose a
persistence layer as target for a third party product with no leverage
on how this product works internally.

You should be careful who's tree you piss on, since some people might point
out that you are in fact pissing and not spewing out facts.

You can't really restrain yourself from using foul language, do you?
Is it some kind of Coprolalia? Why do you feel Access is a tree, does
it grow when you give it manure and water?

best,

Michael