How to speed up slow MS Access query.

[Dan V.]

We have a very slow MS Access select query that takes up to 15 seconds on
our 1.266 GHZ 1GB RAM P4 web server with windows 2000, iis5. (For one
person)
Even running the query from MS Access on this machine takes the same amount
of time (by-passing asp.net totally)

If we buy a dual 3.6 Xeon GHZ and 3 GB RAM with Windows 2003 std edition and
iis 6 - will that help?
What will the performance gain be ? Only 3-5 times faster?

The problem I think is the complex query does not use / can not use index
fields in the multi-table join. We would setup secondary indexes, but can't
with database desktop databases.

We would like to scale this web app (I didn't program it) to 100 concurrent
users...? What would you do?

thanks,

 

[Dan V.]

Is MS Access even dual processor capable? I believe MSDE 2000 is. I don't
know if it is feasible to switch, as then they would have to re-program
their asp.net code possibly, since MSDE 2000 is SQL Server 2000
compatible...?

 

[Larry Linson]

If you are accessing the database from ASP.NET, Access is not involved. My
guess is that you are using ADO.NET to access the Jet database. My other
guess is that you would have a far better chance of getting a useful answer
in a newsgroup devoted to ASP.NET.

This newsgroup is for Access database software, which can use Jet and
classic ADO, but which I've never seen nor read about using ADO.NET.

But, either here or there, please given an example of such a query, and
explain the tables and their indexes. Please simplify until it is
comprehensible, but still ehxibits the behavior you want to correct. It
wouldn't hurt to mention the Version of the Jet database engine and/or
ADO.NET you are running, and the Service Pack level.

As Ross Perot said, "The devil's in the details."

I am not certain just what you mean by "We would setup secondary indexes,
but can't with database desktop databases." So, it would help if you
clarified that, too.

Larry Linson
Microsoft Access MVP

 

[Dan V.]

The query is slow running the query from inside MS Access and only using the
tables from inside the one .mdb file. So it is not an ASP.NET issue.

What I meant by
"We would setup secondary indexes, but can't with database desktop
databases"
Is that we already have a primary index setup on the access table. We would
create a secondary index on this table to speed up non primary key searches,
but I heard you can' t with MS Access (and other desktop databases). If
there is a way, please let me know.

By the way, it is true that MS Access does not take advantage of
dual-processors, so this would not solve this specific MS Access slow query
problem? But MSDE does?

I will try to get the sql and table structure from my colleague.

thanks,

 

[Brendan Reynolds]

Of course you can have multiple indexes per table, you can have up to 32.
See the following on-line help topic (expand the 'Table' section).

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051868081033

 

[Dan V.]

You are correct about the # of indexes per table...
But MS Access does not take advantage of these secondary indexes when
running a query.
The using of secondary indexes in a query is the application we are
concerned about.

But please prove me wrong, or maybe there is an alternative?. (Besides
making more tables, and each table has a primary key of the criteria fields
that I am searching for...)

 

[Tony Toews]

You are correct about the # of indexes per table...
But MS Access does not take advantage of these secondary indexes when
running a query.

Yes Access does use any appropriate indexes when doing queries.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Brendan Reynolds]

I'll tell you what, Dan. When you can provide one shred of credible evidence
to
support your position, then ask me to prove mine.

 

[Dan V.]

I am just repeating what a colleague told me he heard from a newsgroup.
He mentioned 'desktop databases' like MS Access don't use secondary indexes
for queries, only the primary indexes.
Since I am not the programmer of that app. and they are not giving me the
query, I can't prove anything.
He did mention he will try some more indexes though.

Thanks for being very clear everyone. This is indeed good news.

 

[Dan V.]

Do you have any Microsoft Documentation to support that claim?
I am just repeating what I have been told - like I said.

 

[Brendan Reynolds]

I'm sure it is unintentional, Dan, but what you are doing is spreading
baseless misinformation. If you stop and think about it, I hope you will
realise that this is not a very responsible thing to do.

If you will stop and think about it, I hope you will also realise that
improving the performance of queries is the reason that indexes exist.

I have not made any 'claim' in this thread, so I have no need to support
anything. But if you want Microsoft documentation on the subject, be my
guest ...
http://search.microsoft.com/search/results.aspx?qu=jet+database+engine+query+index

 

[Tony Toews]

I am just repeating what a colleague told me he heard from a newsgroup.
He mentioned 'desktop databases' like MS Access don't use secondary indexes
for queries, only the primary indexes.

Send him our way. We'll straighten him out in a hurry. <smile> Also
what newsgroup did he read that?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm