SQL query slow

B

Bob Barrows

Arne said:
Everywhere that a value is concatenated into the sql string instead of using
a SQLCommand object to parameterized the sql.
Given that this is an Access (Jet) database, the vulnerability is mitigated
due to the lack of support for comment characters, multistatement queries,
etc. that enable sql injectors to do their thing.
 
M

MiB

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.

You also liked World War II, did you? :)
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.

That is what people in this thread keep telling me without any
reference to proofs at all. "Often Access is faster" under "some
conditions" is not cutting the mustard either. I did give my example,
where is theirs?
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.

I am ready to give pointers to third party references to any statement
I made. I am not going to write a treatise on my posts (I do have a
life beyond usenet) but if you want me to provide background
information on anything specific, try me. Regarding "classic German
positions", I am pretty sure you will find the same attribution of
stubborness towards plenty of countries, depends on who you ask.
Personally, I like or dislike individuals based on how they treat me
and not based on birth or heritage. Please, don't put me in a box
together with other people; if you want to hate me, hate me because of
ME :)
Es ist was es ist und Sie können es nicht kampfen.

:) but I can try...
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.

Maybe JET can handle the operation faster using its native interface.
That was not an option here, we had to use the software as is, we did
not program the transfer operation and had no say in how it was done.
SparxSystems Enterprise Architect (EA) connects to its persistence
layer via ODBC using the same SQL commands on the same logical target
schema with all DBMS benchmarked. I consider this a fair setup, JET
was not in a bad position because database engine and application
client were local to the same machine.
Because EA shall be useable with a range of DBMS, it cannot use tricks
or shortcuts in the SQL language, or the interface, not working on all
supported platforms indiscriminately. We noted the transactions
involved touched a lot of records, in fact we had to push a registry
setting for the number of permitted file locks by JET to make it work
at all. Without the tweak, the application would crash, rendering the
target file useless.

There was no guarantee the tweak would work with a larger database
size or when there is exisiting partial content that needs to be
merged. The operation had no problems when run against any of the
other candidate DBMS, so I believe its OK to blame the crash on JET.

The test rig was the platform designated by the customer asking for
the benchmark. The benchmark itself was the real operation the
customer intended to perform, including database size and structure
of data.
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.

See http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html:

"As indicated by the engine name, MEMORY tables are stored in
memory. They use hash indexes by default, which makes them
very fast, and very useful for creating temporary tables. However,
when the server shuts down, all rows stored in MEMORY tables
are lost. The tables themselves continue to exist because their
definitions are stored in .frm files on disk, but they are empty
when the server restarts."

We needed persistent storage, not transient. I don't think its rubbish
to devise a storage engine that actually gets back your data after
server shutdown.
The test was not for "what is the fastest DBMS", it was for "what DBMS
gets *our job* done fastest". I admit, I am generalizing myself from
this.
Your opinions are not facts, and never could be. Make peace with it.

I'm game.

Michael
 
B

Bob Barrows

I am not attacking you, only the misinformation you present.
MiB said:
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
..
OK, this is simply not correct. There is nothing in that WHERE clause that
would prevent an index from being used if it was in place. If you see
something I've missed, do point it out, please. Every criteria I see is
sargable.
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.

Not true. I and others here have created Enterprise-level applications using
Jet. One of mine is still running today, and there is no pressure to replace
it.
Where Jet is lacking is in its security model and inability to handle major
concurrency. In the event that those things limit performance/scalability,
SQL Server, or some other server-based rdbms needs to be used.
Consider using a relational database, i.e. SQL Server Express, its

Jet IS a relational database. It's file-based rather than server-based, but
it is relational. Frankly, if this query performs poorly with Jet due to
failure to use existing indexes, it will also perform poorly in SQL Server
for the same reason.
If indexes are in place and being used, there is a possiblity that we could
use advanced indexes and capabilities available in SQL 2012 to improve the
performance beyond that which is possible in Jet.
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 )
);

Check the execution plans for this statement vs the original. You will see
they are the same. An inner join is an inner join, whether it's performed
using JOIN ... ON... or the older WHERE clause syntax.

I would recommend following this suggestion anyways, as the JOIN syntax is
better supported by modern rdbm systems. In SQL Server, for instance, the
old *= syntax for performing outer joins was deprecated several versions
ago, so the JOIN syntax is the only way to create an outer join now.

And while the String.Format version is easier to read, it's no protection
against sql injection if that was your reason for switching to it.
Concatenation is concatenation. Test it to see for yourself. There is no
parameter binding here as would be the case with using parameter tokens and
passing the values via OledbParameter objects.

I've been very careful not to attack you - please do not read "attack" into
anything I've said here.
 
B

Bob Barrows

Marcel said:
Hi,


..., 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.
Agreed that the SQL needs to be analyzed but Jet (if Jet and not accdb) does
not lend itself to creating execution plans without tweaking the Registry.

To the OP:
My suggestion would be to output the _result_ of the concatenation (not the
unhelpful concatenation code) to the Immediate window so it can be copied
and pasted to an Access Query Builder SQL View where it can be analyzed.
From what I can see, the WHERE criteria are sargable so indexes should be
usable if in place. Make sure the fields referenced in the WHERE clause are
indexed. Test the performance in the Access environment before and after
adding any indexes so you can verify if the indexes you create are needed or
not. No need to create indexes that are not used or have no impact on
performance. You can verify index usage by using the undocumented
jetshowplan registry setting:

http://msdn.microsoft.com/en-us/library/Aa188211

If you require better performance than you can get from Access, that is the
point you need to look into switching to a more robust backend database
engine as Marcel suggets. i assume you had valid reasons for using Jet or
ACE in the first place so those reasons will need to be reconsidered.
 
B

Bob Barrows

Sonnich said:
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 =
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.

Wait ... I don't see where the query is executed here. Are you telling us
that it takes 1.3 seconds to create a command object and assign a string to
its commandtext property? If so, something is wrong here but it has nothing
to do with the backend database ... you need help from the .Net gurus in the
dotnet group, not the Access group.

Or is the query executed as part of the call to the _Log method?
 
M

MiB

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

Please refer to the next paragraph in my original post, where I write
pretty much the same. If there was an appropriate index, the query as
given would not take 1.3 seconds.

[..]
Access is a relational database.

I beg to differ. Access is a GUI for defining databases for an
external storage engine, plus a platform for applications accessing
the external engine. JET is not an integral part of Access, it can be
replaced by others. Before, a wide range of
DBMS was supported via ODBC, the number became a lot smaller with
recent versions of Access.

JET is also not truely relational. I'd like to cite from the original
definition by E. F. Codd:

"all information in the database must be cast explicitly in terms
of values in relations and in no other way"

AFAIR, the JET engine provides schema metadata via OpenSchema()
violating the "in no other way" part. I admit, this is extremely
nitpicky.

More serious is the non-conformance to the four principles Atomicity,
Concurrency, Isolation, Durability (ACID) that are the basis of
relational operations in any database. See http://support.microsoft.com/kb/180223:

"The Jet Engine was not designed to support ACID (Atomic,
Concurrent, Independent [sic], Durable) transactions and should not be
used in any application environment where such transactions are
required."
LINQ and EF is from 2008 if I remember correct, so not using it can
not really by so 1980.

The need for a database abstraction layer that gives some independence
of application code to the persistence layers is way older than LINQ
and EF. A frequent horror IMHO are refactorings of applications
written for one database (say MySQL, using SKIP / TAKE for paging) to
another (SQL Server, paging via a ROWNUM() construct). Especially if
you have snippets of SQL spread in the whole App, or programaticlly
constructed SQL statements.

LINQ to EF is just a modern way to handle the problem, before we had
LINQ to SQL, ADO.NET and JDBC already.
EF only has first level cache, so if cache is the main criteria,
then go for something more mature like NHibernate.

An extra, not the main criterion. NHibernate is fine, too.

best,

Michael
 
B

Bob Barrows

MiB said:
I beg to differ. Access is a GUI for defining databases for an

Sigh - pay attention to context please and stop playing gotcha. Arne was
obviously referring to Jet here. The distinction does get blurred when one
gets used to only dealing with mdb files.
JET is also not truely relational. I'd like to cite from the original
definition by E. F. Codd:

"all information in the database must be cast explicitly in terms
of values in relations and in no other way"
If you require a strict adherence to Codd's definition, you will find it
very hard to find a "truly" relational database among any of the SQL
Servers, Oracles, MySQLs, etc. out there. All have made compromises to the
theory in favor of performance, etc.
AFAIR, the JET engine provides schema metadata via OpenSchema()
violating the "in no other way" part. I admit, this is extremely
nitpicky.

Yes, please stop doing that.
More serious is the non-conformance to the four principles Atomicity,
Concurrency, Isolation, Durability (ACID) that are the basis of
relational operations in any database.

That's a questionable statement - ACID is a property of transactional
systems, not just relational databases. But now I'm being nitpicky ...
See
http://support.microsoft.com/kb/180223:

"The Jet Engine was not designed to support ACID (Atomic,
Concurrent, Independent [sic], Durable) transactions and should not be
used in any application environment where such transactions are
required."

Yes - one of the compromises made in favor of performance (the lazy write
buffer that this article is about) does break the ACID contract. But that
does not mean Jet is not a relational database, only that it is not a
perfect one, just as none of the other major rdbms are perfect.
No argument there. The problem is, beginners are taught dynamic sql because
I guess it's so easy to teach ... except that nobody teaches it well enough
to make beginners realize they should not be using it.

I've seen reports of some pretty horrendous queries being generated by LINQ,
but that was back in the early days of its use. Maybe it's been improved
since then? I haven't used the technology but I hope developers are given
more control over the generated sql than they used to have.
 
M

MiB

I am not attacking you, only the misinformation you present.MiB wrote:

Don't worry, I can live with criticism, I only don't like if people
that don't know me at all, or my background, think its proper to give
me names.
.
OK, this is simply not correct. There is nothing in that WHERE clause that
would prevent an index from being used if it was in place. If you see
something I've missed, do point it out, please. Every criteria I see is
sargable.

I suggested an index in my original post and merely assumed there was
none because of the query processing time mentioned. I should have
written "If there is no index, processing the WHERE condition causes
the a full table scan".
Not true. I and others here have created Enterprise-level applications using
Jet. One of mine is still running today, and there is no pressure to replace
it.

Enterprise level with a theoretical maximum of 255 concurrent threads
and a practical limit of maybe 8?
I don't doubt the usage of your application in some big company, but
this does not feel as enterprise level to me, sorry.
May I ask you the amount of data (roughly) stored in your DB?
Jet IS a relational database. It's file-based rather than server-based, but
it is relational.

It does not provide ACID properties, which I think is a basic
requirement, see KB 180223: "The Jet Engine was not designed to
support ACID (Atomic, Concurrent, Independent, Durable) transactions
and should not be used in any application environment where such
transactions are required."
Frankly, if this query performs poorly with Jet due to
failure to use existing indexes, it will also perform poorly in SQL Server
for the same reason.

Admitted. However, in the JET documentation I cannot find a
description on how to set the index type. What type of index does it
use by default? (E.g. hash, clustered, non-clustered)?
Check the execution plans for this statement vs the original. You will see
they are the same. An inner join is an inner join, whether it's performed
using JOIN ... ON... or the older WHERE clause syntax.

I made the changes to the statement to improve readability. All
concurrent databases I am aware of optimize a cross join plus related
WHERE clause to an inner join.
And while the String.Format version is easier to read, it's no protection
against sql injection if that was your reason for switching to it.

I considered using a parameterized SqlCommand object instead but felt
it would sidestep the worries of the OP in regard to performance. I
decided against pressing it when I reviewed the parameters wrapped in
functions. In this special case, assuming EquipmentId() returns int,
there is no danger of SQL injections. You can provide any DateTime
object and it will never get converted to "; DROP DATABASE; GO --".
In general, of course, this is bad style and any layer, be it LINQ or
SQL parameters are the preferred way of handling the issue.
I've been very careful not to attack you - please do not read "attack" into
anything I've said here.

I must have made some impression here. As I said before, I got no hard
feelings towards anybody disagreeing with what I say. I just don't
like to get a full blast of arrogance and insult on first sight, so I
clawed back.

best,

Michael
 
A

Access Developer

MiB said:
However, I *do* have some experience with
JET and it should be legitimate even in comp.-
databases.ms-access to criticize it.

Cavalierly dismissing Jet on a global basis, as you did, is distressingly
detrimental to your reputation... regardless of your education and
experience. I would point out that the equally-uninformed posters who
claimed that "Access (Jet) falls over with four users" had "some" experience
with Jet, too -- my response was that "if you do just about everything
wrong, no matter what software tools you use, you can create an application
that won't satisfactorily support even _one_ user." At least, I spared you
_that_ response.

The same caveat applies to a performance analysis -- the design of the tests
affects the results.

And, as you did not limit your comments to a particular environment, you
_did_ "cavalierly dismiss Jet on a global basis". Had you described a
networked, or web-based, or cloud environment, or comparing a file-server
database (Jet) in such an environment versus server databases, with a very
limited number of cases, then there might have been some basis for
discussion.

Lest you feel "left out" because you felt relatively "unflamed" by my
response as opposed to Albert's (just for the record, Albert is a personal
friend, highly respected in the community for his knowledge and ability,
and, in my view, a database _expert_ with Access, Jet, Ace, and an
impressively wide variety of server databases), I could have, with complete
justification, just responded to your original post with something like:

"Spoken like a true code monkey, who should limit the scope of his
database comments to his favored csharp newsgroup."

However, attributing malice to those who are only ignorant of the subject
they discuss isn't usually productive, until they (usually later in the
discussion) react by contending that their ignorance is really superior
knowledge, understanding, and wisdom.

Finally, before you again denigrate Microsoft Most Valuable Professional
(MVP) status, perhaps you should visit http://mvp.support.microsoft.com for
a description -- of what MVPs are, the basis on which Microsoft selects
them, etc.. There are MVP specialties for DotNet, and for each of the
DotNet languages -- perhaps if you were active in supporting the software
user community, you might be considered.

Larry Linson
Microsoft Office Access MVP
 
M

MiB

Cavalierly dismissing Jet on a global basis, as you did, is distressingly
detrimental to your reputation...

As is treating it like the holy grail.
The same caveat applies to a performance analysis -- the design of the tests
affects the results.

It was not an artificial test, I tested exactly what my customer
wanted to do with the target platform.
Lest you feel "left out" because you felt relatively "unflamed" by my
response as opposed to Albert's (just for the record, Albert is a personal
friend,

Yours, not mine. I tend to rather not like him.
highly respected in the community for his knowledge and ability,
and, in my view, a database _expert_ with Access, Jet, Ace, and an
impressively wide variety of server databases),

His lack of manners and his foul language will not help in earning
*my* respect.
I could have, with complete
justification, just responded to your original post with something like:
 "Spoken like a true code monkey, who should limit the scope of his
  database comments to his favored csharp newsgroup."

Welcome to the "no manners club". Your insult and attempt to forbid me
to speak up in your usenet group does not raise my respect for you
either. If you don't like what I write, how about you don't read it?
However, attributing malice to those who are only ignorant of the subject
they discuss isn't usually productive,

You do. Your friend did. You are tip-toeing around to make it an
indirect insult. That's what *I* call malice.
Finally, before you again denigrate Microsoft Most Valuable Professional
(MVP) status,

I don't care about MVP status. Not yours, not Mr. Kallal's. I don't
owe you anything. Microsoft is a *company* and not an official body
entitled to hand out nobility titles that are worth more than the
paper they are written on. Let them proclaim you Grand Mugwump of
Accessinien if you think that makes you even more valuable. And for
the record, until just now I "denigrated" Mr. Kallal only, not his
shiny MVP status medal.
There are MVP specialties for DotNet, and for each of the
DotNet languages -- perhaps if you were active in supporting the software
user community, you might be considered.

Why should I even care to "be considered"?
 Larry Linson
 Microsoft Office Access MVP

Michael.
 
A

Albert D. Kallal

"MiB" wrote in message
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.

An ad Hominem attack is attacking someone based on not arguing the subject
matter. I'm directly countering and calling out your incorrect subject
matter being pointed out here, and as such it not ad hominem at all.
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.

Absolutely nobody else in this discussion is attempting to limit this to JET
and there is absolutely no reasonable reason to do so.

If there's no reasonable argument or intellectual reason for limiting this
discussion to jet, then why are you insisting on doing so?
You call yourself Access MVP and still seem to identify the
*GUI* Access with the DB engine it connects to.

No, that's not what I did it all. It is a logical assumption that the user
is either dealing with JET or ACE. We don't know which, but we know based on
the given testimony it is certainly one of these two choices.

In other words I'm not willing to argue on fallacy and false assumptions
like you are so willing to do.

In fact I use the term access data engine or default access engine because
the term encompasses both possibilities and I was well aware as such.

You see, we don't know at this point in time which data engine we are
talking about.

However it is of ABSOLUTELY NO INTELLECTUAL USE TO LIMIT this discussion to
jet when we cannot make that assumption.

As I pointed out, coming here and pointing out that a database engine has
been depreciated and is not under development is a completely misleading
statement when in fact the code base you spoke of is currently under
development by the folks in Redmond. This is not an opinion, but a given
fact.

The fact that the product you spoke of has undergone a name change is
absolutely no excuse for coming here and attempting to state that such a
product has been depreciated.

The code base and product you speak of continues under development.

We ONLY know for sure that the poster is talking about a Access default
engine used with access, and that certainly does not mean that such a data
engine has been depreciated and thus your advice in this regards is of
little use and without merit.

To make a claim in public that jet is not receiving development dollars, but
leaving out the fact that there's a code compatible "successor" engine still
in current development shows that you prefer dishonesty and are willing to
spread misinformation in public.

Such a claim on my part is not an ad Hominem attack on you but simply based
on the factual evidence that you've presented here so far.
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.

Where did I make a claim that you care or not care about the access product?
However you did come here to piss and that is rather clear.

My point here is you don't care about the truth and choose to spread
misinformation by leaving important facts out of your discussion.
Leaving half the facts out means your spreading damaging misinformation
based on your ignorance.

Such behavior now becomes actual malice on your part now that I pointed
this out to you.
My original post explicitly references JET, not ACE, not MSDE. You are
committing a straw man fallacy yourself.

No I'm not, because I did a limit my context of discussion to jet, but
simply to that of the current data engine used with access and that is a
HUGE difference.
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.

Yes my position is different because regardless of his position and his
claim of (or lack of) a network the basic information STILL says that a
access data engine is being used.
We don't know if it JET or ACE but as I pointed out based on the reasoning
and evidence given here such a distinction is not relevant to this
discussion at all. I'm not making the claim that it's necessary local app,
but I am most certainly making the claim that the access data engine is
involved here. And my claim is not limited to just jet.

You simply don't have ANY other evidence to make any other conclusion is
that in this regards EXCEPT that Access is involved here. That pretty much
suggest and limits are choices to the two database engines. But it DOES NOT
limit us to jet! So my case is VERY different the your claim.

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.

Who trying to silence you? You are however doing a great job of making a
fool yourself. I'm simply calling out your misinformation and lack of
knowledge on this subject, and you're now attempting to turn this around and
somehow limit this discussion to jet when there's no factual reason to do
so.

Furthermore my claim stands that you in public its stated that the Access
database technology called JET has been depreciated and it is not undergoing
development from the folks in Redmond. This is complete wrong since that
code is still currently under development. A name change is not a excuse for
ignorance here.

Since when is pointing out falsehoods and information an attempt to silence
you? it's not an attempt to silence you, it's a attempt to point out your
misinformation here.

As I pointed out, at the end of the day to me the truth matters. For you it
just a case of attempting to get away with a position to justify you dislike
of some product or the so called holier than now feeling superior than
everybody else attitude you displayed here.

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

Your claim of the database engine being depreciated and not under
development is a false one.

For me to come out and publicly state that SQL server is not under
development anymore, but fail to mention that there are new versions that
succeeded and are code compatible would be extremely dishonest on Anyone's
part. This would be DOUBLE so if a name change of the product and code base
had occurred.

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

My information was based on architecture. Are you actually telling me as a
physicist that you have to be told if you drop a ball it'll fall because you
don't realize the effects of gravity?

Are you actually telling you have to have a test that some hand coded
assembler is not going to run faster than some interpreted scripting
language? In other words you're flat out in public admitting that you can't
make any logical conclusions based on your years of training. Why am I not
surprised?
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.

I gave the context and limitations of where you'll see that better
performance.

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

Because I'm basing my claims on fundamental and basic architecture knowledge
that everybody in this industry should have. My claims not based on personal
experience, it's based on an understanding of computer architectures and how
they function.

Therefore before I ever had to do any testing, understanding the
architectures of the products told me what the outcome was going to be
beforehand. The fact that I've been very able to verify these results with
personal experience is rather a moot point since those observations I've
made are the result of that computer architecture, and they are not subject
to my opinion or whims like your position is.
Or thought. What resources do YOU have other than your individual
experience? Marketing flyers?

No, I spent time to learn about the computer industry and therefore I'm able
to make logical conclusions here. This is no different than a person has
been trained in math doesn't have to have the answer to every single
problem, but using logic and a set of math rules you can deduce that outcome
with intelligence and reasoning, something you're clearly claiming that
you're unwilling to do!

In case you're wondering, we can use a computer and math to prove that a
triangle must have 3 sides. In fact we never even have to have seen a real
triangle or even know that one ever existed before hand. We simply can use
logical reasoning and math rules and prove that the triangle must have 3
sides. This type of logic and reasoning is the very act of writing software
and as such it should be no surprise to you that we can use a set of rules
to expand our knowledge to determine outcomes here.
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.

Actually the problem is reverse. You are the one that stepped in here with
your holier then now all knowing attitude. And as a result you attempted to
make some foolish claims about performance based on one lame experience and
not knowledge about the computer industry.

And this simply shows me that you're willing to toss aside training and
intelligence, all for that of an emotional outburst.

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 always realize I'm totally pawing my opponent and winning the discussion
when the person has to cook up all kinds a bizarre and strange and stupid
straw man arguments. Who the hell on earth brought the issue of xml and all
these bizarre text file technologies? Now might be interesting to note that
the new version of ACE does have support for SharePoint complex data types
(something that SQL server lacks by the way - and with more data going into
SharePoint, this is real problem for SQL server).

At the end of the day however talking about the complex data support in ACE
is really moot since that of xml text files and the like is NOT related to
this discussion about the architecture of a "in process" database engine
that access has compared to the server based architecture system like SQL
server.

Bringing up ridiculous concepts like xml and text files into this discussion
has little if any relevance here. How does xml and text files have any
relevance to the comparison of the two architectures in the context of the
database engine that we are discussing here? I suppose we could bring up
some text processing engines, but such an attempt on your part is silly and
is a hollow straw man attempt to change the subject here.

The idea that any <insert your favourite tool here> is the center of
the universe is a big mistake on your part.

Arguing about Ford VS. Chevy was exciting back in grade school days. Your
pathetic attempt to misdirect as discussion about some favorite tool and yet
at the same time coming here to spread misinformation about a product you
clearly know little about really shows your true colors.

Worse you coming here and making claims about a product that supposedly you
claim knowledge about is rather pathetic.

Your misinformation and willingness to confuse claims about jet having been
depreciated while leaving out the successor as is well noted. And as noted,
you claims in terms of performance are also well noted.
I consider Access a niche
product, limiting applications to one single operating system platform
on client and server.

What single platform you talking about? Access 2010 has now the option of
horizontally scalable cloud publishing options. When you publish Access
applications to office 365 or SharePoint then Access forms are converted
into xaml (zammel). The form code becomes browser compatible JavaScript and
the reports become RDL (they use SQL server reporting services). This is a
true cloud OS architecture and this publishing to office 365 does not limit
Access to using JET/ACE nor is JET involved after you publish to the cloud
and consume the application on say your iPad (no ActiveX or silver light is
required). Here is a video of mine if you wondering how this looks:


And more interesting is, the stored procedures and table triggers that are
available in access 2010 now make the trip up to and run on office 365
intact. ACE now has table triggers and store procedures at the engine level.
And MOST interesting is these triggers and code now go up into the cloud.
Once again, just like your lack of knowledge as to the state of affairs in
terms of the access database engine used, I also suspect you not up to date
in regards to the features and what access is all about.
Nothing that encourages me to use it in any project. And yes, I can
give references to any of my statements

I'm not here to encourage you to use any product, but I do want some honesty
and integrity from you, and that's what you've been lacking here.
 
G

Gene Wirchenko

On Tue, 6 Mar 2012 12:04:32 -0800 (PST), MiB

[snip]
His lack of manners and his foul language will not help in earning
*my* respect.

I wonder if anyone here particularly cares for your respect.
Given the way you have been posting, if you were to approve of
something I was doing, I would probably check to see if I were doing
something wrong. It is rather telling to me that you have created
that situation is such a short time.

You might try chilling out rather than writing yet another post
the justifies your behaviour. Sincere apologies would not be out of
place either.

[snip]

Sincerely,

Gene Wirchenko
 
A

Arne Vajhøj

Everywhere that a value is concatenated into the sql string instead of using
a SQLCommand object to parameterized the sql.

No.

Only if there is concatenated a value controlled by the user.

That does not seem to be the case here unless EquipmentID does
something else than the name indicates.
Given that this is an Access (Jet) database, the vulnerability is mitigated
due to the lack of support for comment characters, multistatement queries,
etc. that enable sql injectors to do their thing.

No.

SQL injection works fine without comments and multi statement.

Arne
 
A

Arne Vajhøj

Please refer to the next paragraph in my original post, where I write
pretty much the same. If there was an appropriate index, the query as
given would not take 1.3 seconds.

The WHERE condition does not cause a table scan.

A missing index may cause a table scan.
[..]
Access is a relational database.

I beg to differ. Access is a GUI for defining databases for an
external storage engine, plus a platform for applications accessing
the external engine. JET is not an integral part of Access, it can be
replaced by others. Before, a wide range of
DBMS was supported via ODBC, the number became a lot smaller with
recent versions of Access.

JET ships with Access.

(or shipped if we no consider the new one just a new version of JET)
JET is also not truely relational. I'd like to cite from the original
definition by E. F. Codd:

"all information in the database must be cast explicitly in terms
of values in relations and in no other way"

AFAIR, the JET engine provides schema metadata via OpenSchema()
violating the "in no other way" part. I admit, this is extremely
nitpicky.

????

OpenSchema is part of the OLE DB API and every database
with an OLE DB provider including SQLServer, Oracle and DB2
support that function.
More serious is the non-conformance to the four principles Atomicity,
Concurrency, Isolation, Durability (ACID) that are the basis of
relational operations in any database. See http://support.microsoft.com/kb/180223:

"The Jet Engine was not designed to support ACID (Atomic,
Concurrent, Independent [sic], Durable) transactions and should not be
used in any application environment where such transactions are
required."

Transactional support (which is certainly not Access'es strong side)
it not required to be a relational database.
The need for a database abstraction layer that gives some independence
of application code to the persistence layers is way older than LINQ
and EF. A frequent horror IMHO are refactorings of applications
written for one database (say MySQL, using SKIP / TAKE for paging) to
another (SQL Server, paging via a ROWNUM() construct). Especially if
you have snippets of SQL spread in the whole App, or programaticlly
constructed SQL statements.

LINQ to EF is just a modern way to handle the problem, before we had
LINQ to SQL, ADO.NET and JDBC already.

LINQ to SQL is also from 2008.

ADO.NET and JDBC uses SQL strings.

Arne
 
A

Access Developer

As is treating it like the holy grail.

Certainly neither Albert nor I "treat Jet like the holy grail" (whatever
that means -- seems to me to be just 'content free' babbling on your part).
Both of us strongly support using the appropriate tool for a given
application, and both of us often use server databases -- in my case, I've
been using Access since Jan 1993, and the majority of the paying work I've
done with it has been as a client application using server databases
(including Informix, Sybase SQL Anywhere, Sybase SQL Server, and Microsoft
SQL Server).
It was not an artificial test, I tested exactly what my
customer wanted to do with the target platform.

"How" you implemented the "what" your customer wanted is the question. As
you've clearly demonstrated here, your ignorance of Jet and other
file-server databases plainly and simply shows that you could not
(therefore, did not) optimize to take advantage of the features those
offered. Given the number of other DBs you list, only the fact that they
use a lot of SQL features in the same way would make the tests even partly
optimized for them.
Yours, not mine. I tend to rather not like him.

I would not presume to pick your friends, nor Albert's friends. Of course,
you are, no doubt, winning friends hands over fist here in
comp.databases.ms-access.
His lack of manners and his foul language will
not help in earning *my* respect.

I know that not having your respect will likely be a severe blow to Albert.
I'm sure others will join me in trying to cheer him up.
Welcome to the "no manners club". Your insult
and attempt to forbid me to speak up in your usenet
group does not raise my respect for you either.

Oh, that's not an insult, Michael -- if you think it is, you need to expand
your scope of reading in newsgroups. And, you are obviously equally or more
ignorant regarding newsgroups as you are regarding databases. No one can
"forbid you" to speak up in a USENET newsgroup... and even the official
authority on newsgroups would have to convince your ISP to take action. I
only suggest you limit your ignorant, erroneous responses to newsgroups
frequented by other "code monkeys" so you won't have to face making a fool
of yourself in public, again, as the other "code monkeys" may know no
better, either.

Just for the record, however, let me clearly state that I do not consider
everyone who uses C# to be a "code monkey" nor that only "code monkeys" use
C#.
If you don't like what I write, how about you don't read it?

When you post erroneous, denigrating, misleading information in
comp.databases.ms-access, those of us who do know the products and software
feel an obligation to correct the misinformation so that you don't lead new
users astray. That's why you won't find either Albert nor I invoking
newsreader rules to put you in the Bozo Bin so we wouldn't have to read your
babblings.
You do. Your friend did. You are tip-toeing around
to make it an indirect insult. That's what *I* call malice.

Better look up "malice", too, code monkey. When you author or edit your own
dictionary, your vote may count as to what "malice" means.
Finally, before you again denigrate Microsoft Most Valuable Professional
(MVP) status,

Ah, so you are _deliberately_ ignorant of what an MVP is and why they are
selected, choosing to pretend that Microsoft "hands out nobility titles".
Ignorance is sufficiently demeaning to you; deliberate ignorance is
deliberately demeaning yourself even more.
And for the record, until just now I "denigrated" Mr.
Kallal only, not his shiny MVP status medal.

"The record" is not whatever you decide you'd like it to be at any given
moment. Let us set the record straight by quoting from your response to
Albert:
I am certainly not in a position to criticize an important
MVP like you. . . . I would like to humbly hint your highness

That certainly indicates that you either cannot read what you wrote,
yourself, or that you choose not to, and try to obfuscate what you said and
implied.

Why should I even care to "be considered"?

A quick read would have saved you the embarrassment of making a fool of
yourself in public on yet a different subject -- Microsoft _recognizes as
MVPs_ those who contribute to the user community, one year at a time, and
they do not do so lightly. However, since all you've done here is spread
mis-information (and though I am suspicious that it is, I won't claim, as I
don't have evidence that you are, deliberately spreading DIS-information,
which, BTW, would qualify as "malicious") that could damage, not help, the
user community, that seems not to be one of your goals.

A final note: neither Albert nor I would "cavalierly globablly dismiss C#"
because we recognize that it has its place in the panoply of development
tools. Even if it does attract more than its share of code monkeys, I am
acquainted with quite a number of capable, skilled architecrts, designers,
developers, and project managers who use C# (and quite a few of those are
current or former MVPs who did want to and did help the community). Too bad
you'll have to go through a conceitdectomy before you could take the first
baby steps toward helping anyone.

Larry Linson
Microsoft Office Access MVP
 
B

Bob Barrows

Arne said:
Yes

Only if there is concatenated a value controlled by the user.

Ever hear of secondary sql injection? Just because a value did not come from
the immediate user does not mean the value might not have been maliciously
entered into the database/system in a prior operation.
But to your point, given that this is Jet, and the statement ends with a
group by clause, I have trouble coming up with a way that malicious sql
could be injected into this statement without raising an error, unless the
hacker has in-depth knowledge of the database and the code.
That does not seem to be the case here unless EquipmentID does
something else than the name indicates.


No.

SQL injection works fine without comments and multi statement.
Yes, of course it does. I said "mitigated", not "prevented". There are
exploits that can be perfomed against a Jet database, just not as many as
are possible against, say, SQL Server.
 
M

MiB

"How" you implemented the "what" your customer wanted is the question. As
you've clearly demonstrated here, your ignorance of Jet and other
file-server databases plainly and simply shows that you could not
(therefore, did not) optimize to take advantage of the features those
offered.

You did not read properly what I described. The task was to find a
database mangement system that allows transportation of data under
control of an established 3rd party UML design tool from one
repository to another. We had no leverage whatsoever on how this tool
works internally, therefore no way to optimize it for either Jet nor
for any other database. For you, in simple words: code monkey
configure ODBC connection in tool, tool put data into db, Jet lost.
Given the number of other DBs you list, only the fact that they
use a lot of SQL features in the same way would make the tests even partly
optimized for them.

Eh? So Jet is better because it sucks on some features more than on
others? You lost me here.
 > > "Spoken like a true code monkey, who should limit the scope of his
 > > database comments to his favored csharp newsgroup."

 > Welcome to the "no manners club". Your insult
 > and attempt to forbid me to speak up in your usenet
 > group does not raise my respect for you either.

No one can
"forbid you" to speak up in a USENET newsgroup...

Still, you tried. Insist otherwise and be called a liar.
Better look up "malice", too, code monkey.  When you author or edit your own
dictionary, your vote may count as to what "malice" means.

From Wikipedia:
"Malice (law), a legal term describing the intent to harm."

I am not a native English speaker, but I am quite sure I am right on
spot with the term.
Ah, so you are _deliberately_ ignorant of what an MVP is and why they are
selected, choosing to pretend that Microsoft "hands out nobility titles".
Ignorance is sufficiently demeaning to you; deliberate ignorance is
deliberately demeaning yourself even more.

Let me put my view on this in clear words.

Microsoft is a company and as such oriented towards profit. They are
not a charity and I do not expect them to do *anything* that is not
intended to first and foremost raise this profit. If they did
otherwise, the shareholders would sue them.
The MVP program is part of this, if you look at the rules they lay
down publicly, MVP status is bestowed to people they deem competent,
well-networked in some communtity, and have a record to improve the
ties between customers and Microsoft.

My guess is, the last point is the only one really important to them,
the other two are secondary while helpful to their reputation. For
you, to play along happily, they grant you exclusive access to
ressources, invite you to conferences and you get a Microsoft employee
as contact person. The label MVP also helps to sell the books you
write, so I think you do have some extra financial benefit, too. You
may also get some pleasure from standing out from the crowd by putting
the label in your sig, you are the only person that can confirm or
deny this and I will not insinuate this is your motivation.

So where is the profit for Microsoft in here? First, lobbyists are not
as effective if they are known employees, paying external services is
expensive. You are volunteering for the job and therefore a relatively
cheap buy (in terms of money, of course, no pun intended).
Second, and the real trick, is to set it up as a kind of competition:
You get your status for one year only and need to win it again and
again. This effectively keeps you from straying away from their line,
you will continue to market Microsoft products actively. If you openly
criticize Microsoft, or their products, you likely receive less
consideration next time.
But even smarter is, other people *not* MVP will strive to be "in" the
competition. You yourself suggested to me, I should do exactly this
for a different area of expertise. I doubt this was an honest
suggestion, though, just another veiled attempt to get rid of me.
These people are the real gain - Microsoft produces an unknown number
(hundreds? thousands? more?) of fan-boys, competent or not, that will
go for your jugular if you name a competitor or a competitor's product
in usenet groups. And they do it at no cost for Microsoft whatsoever.

I neither condemn Microsoft, nor you or Mr. Kallal to be part of this.
Microsoft is doing a smart and, probably, legal business move to
attract customers. You receive extra advantages and I do not expect
you to be a second Mother Theresa, you need to care about your
livelihood, too.

However, I reject to see more than a commercial plan in this. I
believe this is a fair analysis, and it is my honest opinion on the
topic, no crouching tigers or hidden dragons. Rip me apart for it, if
you must.

To press my point, there is a tradition for citizens of the German
cities of Hamburg of Bremen (I am neither), to reject *ANY* honorary
titles or medals offered to them. The intention is to make clear that
extraordinary deeds for the greater good do not require nor deserve a
medal. Did an american citizen ever reject a Congressional Gold Medal
offered to him on the grounds, "I did it what I did because it was the
right thing to do, not because of a medal"? This is an attitude I'd
respect, deeply. For reference, see the Wikipedia article on the
German equivalent civil medal, http://en.wikipedia.org/wiki/Order_of_Merit_of_the_Federal_Republic_of_Germany
(second paragraph).
"The record" is not whatever you decide you'd like it to be at any given
moment. Let us set the record straight by quoting from your response to
Albert:

 > I am certainly not in a position to criticize an important
 > MVP like you. . . . I would like to humbly hint your highness

That certainly indicates that you either cannot read what you wrote,
yourself, or that you choose not to, and try to obfuscate what you said and
implied.

So, kindly, where is the denigration if I speak in praising words,
even if overly? If "denigration" was a tort, I doubt you'd
successfully sue me on this.
 > > There are MVP specialties for DotNet, and for each of the
 > > DotNet languages -- perhaps if you were active in supporting the
software
 > > user community, you might be considered.

Why should I even care to "be considered"?

A quick read would have saved you the embarrassment of making a fool of
yourself in public on yet a different subject -- Microsoft _recognizes as
MVPs_ those who contribute to the user community, one year at a time, and
they do not do so lightly.

Of course not. They recognize who brings the most profit to them.
Linus Torvalds would *never, ever* be considered.

[,,]
which, BTW, would qualify as "malicious") that could damage, not help, the
user community, that seems not to be one of your goals.

I don't care about the abstract "user community". Understand this as a
neutral "don't care" like in "of no importance to me", not
denigration, just as I neutrally don't care about MVPs as a group.
A final note: neither Albert nor I would "cavalierly globablly dismiss C#"
because we recognize that it has its place in the panoply of development
tools.

You are putting words in my mouth by the fake citation, I never
claimed that. You are dishonest here.
  Larry Linson
  Microsoft Office Access MVP

Michael.
 
A

Access Developer

You did not read properly what I described.
The task was to find a database mangement
system that allows transportation of data under
control of an established 3rd party UML design
tool from one repository to another. We had no
leverage whatsoever on how this tool works
internally, therefore no way to optimize it for
either Jet nor for any other database. For you,
in simple words: code monkey configure ODBC
connection in tool, tool put data into db, Jet lost.

Oh, thank you so much for the explanation. I now understand that you base
your opinion of a product (Jet, and others) on a single test, with that
product being driven by yet another product, the method used by 'yet another
product' being unknown to you (so it could be a method detrimental to just
one product in the list, for whatever reason). I suppose it will be up to
the participants / readers here to determine whether that supports your
claim of education and professionalism.
Still, you tried. Insist otherwise and be called a liar.

Suggesting that you make stupid comments in a newsgroup likely to be more
congenial to your views _so you don't continue to make a fool of yourself in
public_ is not "an attempt to forbid you to speak up in a newsgroup". Insist
otherwise and it will be up to the participants here to decide who's a liar
and who is_making a fool of himself in public again_. <SIGH, SOME CONCEITED
CODE MONKEYS JUST NEVER LEARN>
From Wikipedia:
"Malice (law), a legal term describing the intent to harm."

I certainly have no intent to harm you; only the intent to prevent the
misinformation you've disseminated here from harming others.
I am not a native English speaker, but I am quite sure
I am right on spot with the term.

But you are not so good at determining intent, it seems.
Ah, so you are _deliberately_ ignorant of what an MVP is and why they are
selected, choosing to pretend that Microsoft "hands out nobility titles".
Ignorance is sufficiently demeaning to you; deliberate ignorance is
deliberately demeaning yourself even more.
Let me put my view on this in clear words.

For someone who claims "a neutral lack of interest", as you did in the
following quote
Understand this as a neutral "don't care"
like in "of no importance to me", not
denigration, just as I neutrally don't care
about MVPs as a group.

you certainly spent quite some time and effort in making sure everyone knew
that you are convinced that Microsoft, its employees, and those they
recognize for contributions to the user community are "in it only for their
own benefit".

FYI, "candidates" for MVP may not even know they are being considered, so
it'd be difficult for them to "compete" -- I know that I did not know I was
being considered until I was informed that I'd been selected.

Microsoft is said:
To press my point, there is a tradition for citizens
of the German cities of Hamburg of Bremen (I am
neither), to reject *ANY* honorary titles or medals
offered to them.

Very noble of them, I suppose. I am certainly not a citizen or resident of
either of those cities and have not taken such a vow. To the best of my
knowledge, I have not benefitted financially from being designated an MVP.
So, kindly, where is the denigration if I speak in praising
words, even if overly? If "denigration" was a tort, I doubt
you'd successfully sue me on this.

You might look up "SARCASM" in regard to "overly praising". I just point
out that you have to try to belittle those who identify and call you on your
errors, because you have no valid arguments to back up your false claims.
And, I think I've made that point.

At first, I thought that perhaps the concept of sarcasm was unknown in your
language, but there surely seems to be a word for it in some languages I
tried -- "Sarkasmus" in German, "sarkasme" in Norwegian and Danish,
"sarkasm" in Swedish, and "Sarcasme" in Dutch. If I've missed your first
language, sorry.
Linus Torvalds would *never, ever* be considered.

As Linus does not habitually contribute to the community of users of
Microsoft software products, likely not. Perhaps, however, you'd be
surprised to know that I've heard respectful, even admiring, comments about
him from members of Microsoft's management and technical staffs.
I don't care about the abstract "user community".

And, from what you write, not only do you not care about others in your
profession, but you are so highly condescending about those who do that it
seems difficult for you to conceive that anyone would help others unless
there was profit for him/herself in doing so.
You are putting words in my mouth by the fake
citation, I never claimed that. You are dishonest
here.

You wrote (the reason I responded to your misleading misinformation)

"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."

Perhaps the fact that English is not your native tongue is why you don't
realize that is "cavalierly globally dismissing Jet"; perhaps the postage
stamp collections with which you are familiar contain hundreds of thousands
or millions of stamps, as do many databases successfully processed in
production environments with Jet; perhaps classifying Jet as not a "real"
database management system doesn't qualify as "dismissive" in your veiw. If
none of the above, then it will just have to be up to the participants in
this newsgroup to decide just _who_ is being "dishonest here".

Responding to your "weasel words" as you try to "cover you ass" is getting
to be quite boring... so, I won't respond to any more of your posts here.

Thus, you can have the last words in this thread. Might as well, because you
seem perfectly capable of making a fool of yourself in public without any
help from me (or anyone else, for that matter).

Larry Linson
Microsoft Office Access MVP
 
Top