repetitive query to sysobjects in an ADP

[Guest]

When I open an SQL 2005 table from within the ADP, it takes 3-4 minutes to
open it. This happens only once, and only for the first table that will be
opened.
Looking in the profiler I see 100's of statements like this:

select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk,
sysobjects sofk, sysobjects sotblfk, sysobjects
sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and
srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and
object_name(sofk.id) = N'FK_Purchase Order
Analysis_Purchase Order'

Apparently access is querying the system table for every single relationship
in the database.
Does anybody have a solution for this?

By the way the same thing happens to the same db but on SQL 2000. However
for some reason the querying is much faster in SQL2000 and the first table
opens up after about 1 minute.

Thanks
Dimitris

 

[Sylvain Lafontaine]

I don't explain why it's taking 3-4 minutes on SQL-Server 2005 but only 1
minute with the 2000 version.

However, the fact that it takes 1 minute with SQL-Server 2000 is already
symptomatic of some kind of network problem. Unless the schema of your
database is incredibly huge, it shouldn't take any longer than one or two
seconds.

A second possibility would be some kind of permission problems, for example
on the sysobjects table; with some internal time-outs as the result (hence
the waiting time, ADP would still work after that because the information
it tries to query is not really necessary if all of your code is based on
SP/Views/UDF) but the fact that the problem is also present on SQL-Server
2005 make it hard to believe that this could be the source of your problem.

Are you accessing these SQL-Servers through a VPN?

 

[aaron.kempf]

nope I am accessing them directly.

this is a fairly large schema.. maybe 1000 tables altogether

any idea if there is anything that I can index; etc?
are there other applications that use the extended properties
functionality?

I'm thinking that it's got bunk data in the extended properties; a
whole bunch of extra garbage..

-Aaron

 

[Guest]

Thanks Sylvain.
The servers are accessed through 100mbit LAN. The network setup is 1 domain
with two 2003 servers, and about 15 XP clients.

I ve seen somewhere on the net a similar question, where it was discussed
that the querying of the sysobjects during the first opening of a table, is a
by design "fault" of ms access. Perhaps the 1 minute for SQL 2000 is normal
considering that the database has about 600 tables and about 1000
relationships?

 

[Sylvain Lafontaine]

600 tables and 1000 relationships queried in one minute? I don't know
because I don't have any comparaison point but it's possible that maybe this
is normal in this range.

One way of testing this would be to open the ADP application directly on one
of the server. You could also try with an SQL-Server account instead of a
domain account.

You could also duplicate the queries yourself using another client than ADP
and see if this is an appropriate timing response.

Also, you've said that there is a difference for the first table but is
there also a difference for the first client to open the ADP project after a
reboot of the servers or a flushing of the buffers? Something in the line
of:

use MyDataBase
go
checkpoint
go
dbcc dropcleanbuffers
dbcc freeproccache -- Not sure of this one here for this kind of test.

....Then open the ADP application.

Of course, you must also check your servers for anything that could lead to
some performance problems.

 

[aaron.kempf]

I've definitely had the same problem; sorry I thought that this was MY
posting lol

sylvain you've been a great help; thanks

-Aaron

 

[Guest]

I tried opening the adp on the server and I get this error from Access:
"Unable to load communication module. Driver has not been correctly installed"

I also tried making a new adp and when I hit the combo to select the server
to connect to, I get this error: "Error enumerating data servers. Enumerator
reports 'unspecified error'.

I haven't found the source of this problem yet. I was suspecting the SQL
server browser service, but it appears it is running properly. I will try
your other 3 suggestions as soon as I find out whats going on with the first
one.

Thanks for your help

 

[Sylvain Lafontaine]

You don't need the browser service; simply write down the name of the
server.

Named pipes are often troublesome, particularly over a LAN, so instead of
using the name of the server, you can also use its internal TCP/IP address
to make use of the tcp/ip protocol instead of named pipes. Even on a local
machine, I would go with TCP/IP instead of named pipes.

In your case, it's also possible than an alias has been defined but with the
wrong protocol. Take a look at the SQL-Server Network Client Utility (not
sure of the translation here).

 

[Guest]

Apparently the error "Error enumerating data servers" was caused due to a
missing dll from system32. The dll was dbnetlib.dll.

I made a few speed tests on two different servers running Win2003 sp1,
SQL2005 sp1:
The tests were:
1.ececuted localy and remotely
2.with tcp/ip and with named pipes
3.on the same db and on the same table
4.on two different servers of came cpu/ram (one is domain server)
5.using windows nt security as well as SQL server security

All tests gave the same result:
Time to open first table 2:45 !

I don't know what else to try.

 

[Sylvain Lafontaine]

Well, 2:45 is a long time! Particularly when the same job on SQL2000 take
*only* 1 minute.

I don't have any comparaison point, so I cannot tell you more about this but
if you can do, you should open a technical incident with the technical
support at Microsoft.

It's possible that your servers are badly configured in some way but it's
also possible that this is normal. In the later case, your only option
would be to remove the relationships (and use triggers if you need some DRI)
or replace ADP with something else like .NET.

There is also Office 2007 that should be soon out but for any amelioration
of ADP under Office 2007, I wouln't hold my breath too long. (But we will
see in some months).

 

[aaron.kempf]

ADP rocks!

Long live ADP.

I've just definitely had some performance problems accessing super-big
databases

I think that Microsoft should have equivalent support for

VLDB = very large
VWDB = very wide database tables - 100 - 300 columns.
VNDB = very normalized with a high # of tables.

-Aaron

 

[Sylvain Lafontaine]

Another possible solution would be to connect instead to an empty database
(or a database with only tables and no relationship) and use the full names
of SP and tables, something like MyDatabase.dbo.MySP instead of just
dbo.MySP.

Of course, for the record sources of your forms, you may have to use the
EXEC statement and dynamically build your queries strings with their
parameters instead of just writing down using the name of the SP but this
should not be a big deal.

I will make some tests this week when I will have the time.

 

[aaron.kempf]

great idea sylvain; i reccomend that we do something like this at work
the other day; with views-- and they kinda laughed at me

I think that my isssue - similiar- might be related to having a lot of
custom datatypes and just a crapload of tables and columns...

it just seems like i've hit large databases with ADP before and not had
this problem; this looks specific to the number of tables that we've
got in this database

I just want to rewrite some of these system sprocs into using a
checksum hash / index for example.. so that instead of searching for a
string named 'mySuperTable' I could search on the hash; and rewrite the
system sprocs to work against my updated sp_helpcolumns or whatever
it's firing.

-Aaron

 

[Guest]

SQL 2000 was slow because of sp_tables. We implemented a modified sp_tables
in our application database that does not resolve Active Directory since we
control application access through the menu structure of the ADP and users
have no access to the db window. The first time so anything on the
connection to SQL 2000, you will notice a call to sp_tables that can be
really slow. I cannot comment on SQL 2005.

 

[Guest]

The problem with querying sysobjects persists with Access 2007. We are
investigating moving from Access 2000 to Access 2007 ADPs, but the problem
(which doesn't exist in Access 2000) makes our app unusable with Access 2007
(and even worse with when connecting to SQL 2005 not SQL 2000). We have
about 1400 tables and about 3000 relationships. IMHO, this problem kills ADP
as a usable technology for corporate development, which means we will have to
find something else for our app (which runs in our branch offices in 100+
countries).

 

[Sylvain Lafontaine]

Yeah, I observed the same insane slugginesh when I made my first test with
the beta version ADP 2007: running the Profiler while an ADP 2007 project is
connected is like making a visit a little museum of horror.

I will soon take the time myself to install the RTM version of A2007 and
repeat my own tests, however, in the meantime, you could add your opinion to
the thread "Opinion on Access 2007" from Allen Browne on 2006-12-22 in the
microsoft.public.access and comp.databases.ms-access newsgroups.

 

[aaron.kempf]

fluow


that was really really really helpful information.

thank you SO much

-Aaron