|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi,
I have an Access 2003 .mdb/.mde that queries linked tables from a SQL db in
order to provide Access reports to the user. It also combines with the SQL
queries, data from a few very small tables within the Access db.
On most systems/servers/networks, this app works well. But there are two
systems/networks (one running Server 2003 and another running Windows 2000)
where the queries are unacceptably slow.
This is not simply a matter of database size. For example, one report
returns nearly 5000 records in <10secs at a "working" site, while the same
report returns only 48 records (correctly) in over 1.5 minutes at one of the
"problem" sites. So to clarify, the query is working properly at the
"problem" site, just painfully slow. No other problems or other performance
issues are reported on that network.
Any thoughts on what might be causing the problems, or on best ways to
troubleshoot would be very much appreciated.
Thanks,
Jeff
|
|
This could be a network issue: with a database access, a lot of trafic is
generated and this will often uncover a network issue that would have
otherwise remained hidden.
If possible, you can try changing the network card, the TCP/IP address or
even switching the location of two machines to make sure that you don't have
a network problem.
Other possibilities would be a virus/trojan/spyware or an antivirus or
firewall going out of control.
If you are using a VPN, then a lot of other potential problems could be the
culprit.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message
news:79F29AEB-8A43-469F-8F46-9C159379534D[ at ]microsoft.com...
[Quoted Text] > Hi,
>
> I have an Access 2003 .mdb/.mde that queries linked tables from a SQL db
> in
> order to provide Access reports to the user. It also combines with the
> SQL
> queries, data from a few very small tables within the Access db.
>
> On most systems/servers/networks, this app works well. But there are two
> systems/networks (one running Server 2003 and another running Windows
> 2000)
> where the queries are unacceptably slow.
>
> This is not simply a matter of database size. For example, one report
> returns nearly 5000 records in <10secs at a "working" site, while the same
> report returns only 48 records (correctly) in over 1.5 minutes at one of
> the
> "problem" sites. So to clarify, the query is working properly at the
> "problem" site, just painfully slow. No other problems or other
> performance
> issues are reported on that network.
>
> Any thoughts on what might be causing the problems, or on best ways to
> troubleshoot would be very much appreciated.
>
> Thanks,
> Jeff
|
|
Sylvain,
Thanks for your time. One of my thoughts was an antivirus checker causing
problems or conflict as well, but I tried disabling antivirus temporarily at
one of the sites with no better results.
I'm doubting it would be a basic network problem such as network card or IP
addr since there is another program that looks at the same db without any
problems. It, however, is not an Access program.
In any event, I'll continue to check these things you suggest. Please let
me know if you have any other ideas or testing suggestions.
Thanks,
Jeff
"Sylvain Lafontaine" wrote:
[Quoted Text] > This could be a network issue: with a database access, a lot of trafic is > generated and this will often uncover a network issue that would have > otherwise remained hidden. > > If possible, you can try changing the network card, the TCP/IP address or > even switching the location of two machines to make sure that you don't have > a network problem. > > Other possibilities would be a virus/trojan/spyware or an antivirus or > firewall going out of control. > > If you are using a VPN, then a lot of other potential problems could be the > culprit. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message > news:79F29AEB-8A43-469F-8F46-9C159379534D[ at ]microsoft.com... > > Hi, > > > > I have an Access 2003 .mdb/.mde that queries linked tables from a SQL db > > in > > order to provide Access reports to the user. It also combines with the > > SQL > > queries, data from a few very small tables within the Access db. > > > > On most systems/servers/networks, this app works well. But there are two > > systems/networks (one running Server 2003 and another running Windows > > 2000) > > where the queries are unacceptably slow. > > > > This is not simply a matter of database size. For example, one report > > returns nearly 5000 records in <10secs at a "working" site, while the same > > report returns only 48 records (correctly) in over 1.5 minutes at one of > > the > > "problem" sites. So to clarify, the query is working properly at the > > "problem" site, just painfully slow. No other problems or other > > performance > > issues are reported on that network. > > > > Any thoughts on what might be causing the problems, or on best ways to > > troubleshoot would be very much appreciated. > > > > Thanks, > > Jeff > > > |
|
The next best thing to do would be to take a look with the SQL-Server
Profiler to make sure that they are sending the right requests.
For example, you could have a filter problem, resulting in a too large
number of records sent over the network. We don't have access to your
program, so it's hard to see what's going on under the hood; however, with
the Profiler, maybe you will see some horror story.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message
news:93DA5900-B37D-4C01-A113-FD513097B448[ at ]microsoft.com...
[Quoted Text] > Sylvain, > > Thanks for your time. One of my thoughts was an antivirus checker causing > problems or conflict as well, but I tried disabling antivirus temporarily > at > one of the sites with no better results. > > I'm doubting it would be a basic network problem such as network card or > IP > addr since there is another program that looks at the same db without any > problems. It, however, is not an Access program. > > In any event, I'll continue to check these things you suggest. Please let > me know if you have any other ideas or testing suggestions. > > Thanks, > Jeff > > "Sylvain Lafontaine" wrote: > >> This could be a network issue: with a database access, a lot of trafic is >> generated and this will often uncover a network issue that would have >> otherwise remained hidden. >> >> If possible, you can try changing the network card, the TCP/IP address or >> even switching the location of two machines to make sure that you don't >> have >> a network problem. >> >> Other possibilities would be a virus/trojan/spyware or an antivirus or >> firewall going out of control. >> >> If you are using a VPN, then a lot of other potential problems could be >> the >> culprit. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> "Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message >> news:79F29AEB-8A43-469F-8F46-9C159379534D[ at ]microsoft.com... >> > Hi, >> > >> > I have an Access 2003 .mdb/.mde that queries linked tables from a SQL >> > db >> > in >> > order to provide Access reports to the user. It also combines with the >> > SQL >> > queries, data from a few very small tables within the Access db. >> > >> > On most systems/servers/networks, this app works well. But there are >> > two >> > systems/networks (one running Server 2003 and another running Windows >> > 2000) >> > where the queries are unacceptably slow. >> > >> > This is not simply a matter of database size. For example, one report >> > returns nearly 5000 records in <10secs at a "working" site, while the >> > same >> > report returns only 48 records (correctly) in over 1.5 minutes at one >> > of >> > the >> > "problem" sites. So to clarify, the query is working properly at the >> > "problem" site, just painfully slow. No other problems or other >> > performance >> > issues are reported on that network. >> > >> > Any thoughts on what might be causing the problems, or on best ways to >> > troubleshoot would be very much appreciated. >> > >> > Thanks, >> > Jeff >> >> >>
|
|
hi Jeff,
Jeff wrote:
[Quoted Text] > Any thoughts on what might be causing the problems, or on best ways to
> troubleshoot would be very much appreciated.
As you can exclude the network as reason, check your indices on the
server and update the statistics of the involved tables.
btw, post your record source, maybe it's something obvious.
mfG
--> stefan <--
|
|
It sometimes happens when there are DNS problems. At the
problems sites, is the client on the same subnet as the server?
if not, is the client on the same subnet as the NS?
I don't know why, but problems like that seem to multiply
the SQL Server response time in Access, instead of just
adding to it.
(david)
"Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message
news:79F29AEB-8A43-469F-8F46-9C159379534D[ at ]microsoft.com...
[Quoted Text] > Hi,
>
> I have an Access 2003 .mdb/.mde that queries linked tables from a SQL db
in
> order to provide Access reports to the user. It also combines with the
SQL
> queries, data from a few very small tables within the Access db.
>
> On most systems/servers/networks, this app works well. But there are two
> systems/networks (one running Server 2003 and another running Windows
2000)
> where the queries are unacceptably slow.
>
> This is not simply a matter of database size. For example, one report
> returns nearly 5000 records in <10secs at a "working" site, while the same
> report returns only 48 records (correctly) in over 1.5 minutes at one of
the
> "problem" sites. So to clarify, the query is working properly at the
> "problem" site, just painfully slow. No other problems or other
performance
> issues are reported on that network.
>
> Any thoughts on what might be causing the problems, or on best ways to
> troubleshoot would be very much appreciated.
>
> Thanks,
> Jeff
|
|
Thanks David. It really does seem like the answer is going to come down to
some sort of configuration issue like that. But, at the 2 problem sites, I
see the problem even if I run the app directly on the server (so Access app
is on the same physical system as the sql db). And, that system is on the
same subnet as the DNS.
"david[ at ]epsomdotcomdotau" wrote:
[Quoted Text] > It sometimes happens when there are DNS problems. At the
> problems sites, is the client on the same subnet as the server?
> if not, is the client on the same subnet as the NS?
>
> I don't know why, but problems like that seem to multiply
> the SQL Server response time in Access, instead of just
> adding to it.
>
> (david)
>
>
> "Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message
> news:79F29AEB-8A43-469F-8F46-9C159379534D[ at ]microsoft.com...
> > Hi,
> >
> > I have an Access 2003 .mdb/.mde that queries linked tables from a SQL db
> in
> > order to provide Access reports to the user. It also combines with the
> SQL
> > queries, data from a few very small tables within the Access db.
> >
> > On most systems/servers/networks, this app works well. But there are two
> > systems/networks (one running Server 2003 and another running Windows
> 2000)
> > where the queries are unacceptably slow.
> >
> > This is not simply a matter of database size. For example, one report
> > returns nearly 5000 records in <10secs at a "working" site, while the same
> > report returns only 48 records (correctly) in over 1.5 minutes at one of
> the
> > "problem" sites. So to clarify, the query is working properly at the
> > "problem" site, just painfully slow. No other problems or other
> performance
> > issues are reported on that network.
> >
> > Any thoughts on what might be causing the problems, or on best ways to
> > troubleshoot would be very much appreciated.
> >
> > Thanks,
> > Jeff
>
>
>
|
|
hm. Check for debug/tracing? ODBC logging can do this too.
Also, try posting in some of the other SQL Server groups?
You might want to try run the queries from MSQuery/Excel
first, just so you can re-assure them that it is not an Access
client problem.
(david)
"Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message
news:D2E27481-BF44-4524-99B1-B193D46528A8[ at ]microsoft.com...
[Quoted Text] > Thanks David. It really does seem like the answer is going to come down
to
> some sort of configuration issue like that. But, at the 2 problem sites,
I
> see the problem even if I run the app directly on the server (so Access
app
> is on the same physical system as the sql db). And, that system is on the
> same subnet as the DNS.
>
> "david[ at ]epsomdotcomdotau" wrote:
>
> > It sometimes happens when there are DNS problems. At the
> > problems sites, is the client on the same subnet as the server?
> > if not, is the client on the same subnet as the NS?
> >
> > I don't know why, but problems like that seem to multiply
> > the SQL Server response time in Access, instead of just
> > adding to it.
> >
> > (david)
> >
> >
> > "Jeff" <Jeff[ at ]discussions.microsoft.com> wrote in message
> > news:79F29AEB-8A43-469F-8F46-9C159379534D[ at ]microsoft.com...
> > > Hi,
> > >
> > > I have an Access 2003 .mdb/.mde that queries linked tables from a SQL
db
> > in
> > > order to provide Access reports to the user. It also combines with
the
> > SQL
> > > queries, data from a few very small tables within the Access db.
> > >
> > > On most systems/servers/networks, this app works well. But there are
two
> > > systems/networks (one running Server 2003 and another running Windows
> > 2000)
> > > where the queries are unacceptably slow.
> > >
> > > This is not simply a matter of database size. For example, one report
> > > returns nearly 5000 records in <10secs at a "working" site, while the
same
> > > report returns only 48 records (correctly) in over 1.5 minutes at one
of
> > the
> > > "problem" sites. So to clarify, the query is working properly at the
> > > "problem" site, just painfully slow. No other problems or other
> > performance
> > > issues are reported on that network.
> > >
> > > Any thoughts on what might be causing the problems, or on best ways to
> > > troubleshoot would be very much appreciated.
> > >
> > > Thanks,
> > > Jeff
> >
> >
> >
|
|
|