Query Speed ... Access XP vs Access 2000

[David Sumlin]

I have a few sample queries that my users have (nothing overly complicated). I'm trying to get them off of linking to an Access back end since it's growing each day and will soon be over 2 gigs. Anyway, I'm now proving to them that linking to a SQL Server will be just as fast if not faster. I go in as a consultant, link my version of Access (XP) to the SQL Server and run their queries. The queries run almost instantaneously. I then send them my test .mdb and they run it on their machines. They have Access 2000. The same queries linked to the same SQL Server take about 3 minutes to run on their machines. Everybody is on XP operating system. We all have updated MDAC 2.8

If I just open a large table and click the last record button, both versions of Access seem to take the same amount of time. I can change the query on the Access 2000 machine to a SQL Pass Thru query and the results come back instantaneous. I know the problem is not the hardware, the SQL Server, or the MDAC.

Is the newer version of Access really that much more intelligent & quicker

 

[Alex Ivanov]

Try to delete links altogether and then recreate them on a client machine
and see if this makes a difference. I think the delay occurs when Access
tries to resolve the links.

Alex.

I have a few sample queries that my users have (nothing overly

complicated). I'm trying to get them off of linking to an Access back end
since it's growing each day and will soon be over 2 gigs. Anyway, I'm now
proving to them that linking to a SQL Server will be just as fast if not
faster. I go in as a consultant, link my version of Access (XP) to the SQL
Server and run their queries. The queries run almost instantaneously. I
then send them my test .mdb and they run it on their machines. They have
Access 2000. The same queries linked to the same SQL Server take about 3
minutes to run on their machines. Everybody is on XP operating system. We
all have updated MDAC 2.8.

If I just open a large table and click the last record button, both

versions of Access seem to take the same amount of time. I can change the
query on the Access 2000 machine to a SQL Pass Thru query and the results
come back instantaneous. I know the problem is not the hardware, the SQL
Server, or the MDAC.

 

[David Sumlin]

Nope. I've sat in the same room with 3 different computers (2 running Access 2000, 1 running Access XP) and created new databases, linked to the same SQL Server using identical ODBC connections and the same thing happens. And like I mentioned before, when I access data in a table directly the speed seems to be similar. It's like Access XP knows more about how to preprocess the query on SQL Server than Access 2000

----- Alex Ivanov wrote: ----

Try to delete links altogether and then recreate them on a client machin
and see if this makes a difference. I think the delay occurs when Acces
tries to resolve the links

Alex

I have a few sample queries that my users have (nothing overl

complicated). I'm trying to get them off of linking to an Access back en
since it's growing each day and will soon be over 2 gigs. Anyway, I'm no
proving to them that linking to a SQL Server will be just as fast if no
faster. I go in as a consultant, link my version of Access (XP) to the SQ
Server and run their queries. The queries run almost instantaneously.
then send them my test .mdb and they run it on their machines. They hav
Access 2000. The same queries linked to the same SQL Server take about
minutes to run on their machines. Everybody is on XP operating system. W
all have updated MDAC 2.8versions of Access seem to take the same amount of time. I can change th
query on the Access 2000 machine to a SQL Pass Thru query and the result
come back instantaneous. I know the problem is not the hardware, the SQ
Server, or the MDAC

 

[Alex Ivanov]

Maybe you are right, but one more thing to check, do you have the
latest-greatest service pacs installed? Did you try to convert the front end
into an adp project?

Alex.

Nope. I've sat in the same room with 3 different computers (2 running

Access 2000, 1 running Access XP) and created new databases, linked to the
same SQL Server using identical ODBC connections and the same thing happens.
And like I mentioned before, when I access data in a table directly the
speed seems to be similar. It's like Access XP knows more about how to
preprocess the query on SQL Server than Access 2000.

 

[David Sumlin]

Yes, I've made sure everybody had latest Windows & Office Service Packs & Patches installed.

I didn't try converting it to a .adp. What do you think that would show me

Also....do you think there might be some sort of behind the scenes selection of jet vs ado retrieval going on that would make that kind of difference?

 

[Alex Ivanov]

With adp all SQL processing is done on the server. You will eliminate huge
overhead of downloading all the data from all tables mentioned in a query
that match selection criteria (and often even that don't match the criteria
at all) and assembling the recordset on the client. Some folks may object on
this statement, saying that Jet engine performs some optimizations to the
queries before it sends the requests to the SQL server, but the key word
here is "some", which is better translated to "almost no".

On the other side conversion of a complex access database to an adp project
is not an easy process and sometimes partial conversion may be preferred.
You convert Access queues into SQL Server Views and stored procedures and
link your Access tables to the views and/or use pass-through queries to
access the data.

Alex.

Yes, I've made sure everybody had latest Windows & Office Service Packs & Patches installed.

I didn't try converting it to a .adp. What do you think that would show me?

Also....do you think there might be some sort of behind the scenes

selection of jet vs ado retrieval going on that would make that kind of
difference?