Perdormance Issues Querying a table over the network

Greetings,
I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
When I remote in to the server with the DB installed on it and run a query
it runs in less than a second.
When I run the same query from my remote machine it takes anywhere from 45
seconds to a minute and a half.
The table has less than 10 records in it and the query is a simple select
query (SELECT * FROM [aspnet_Profile])
I can run another query on the same database from the remote machine that
will return much more data (100s of heavier rows) on the same database and
the same connection and it will run much much faster.
I have no idea where to even start troubleshooting this.
Any help would be appreciated.
--
Sagi Shkedy
http://blog.shkedy.comI've seen this a lot when your workstation (or the machine that's doing
the querying) has a lot of CPU load. Take a look at Task Manager and
make sure there's nothing taking up your CPU.
--
-Dave Markle
http://www.markleconsulting.com/blog
Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>|||Hi
First of all , don't use '*' with SELECT statement , it may hurt perfomance.
Is it stored procedure?
SET NOCOUNT ON
SELECT * FROM [aspnet_Profile]
I'd also run SQL Server Profile to see what is going on
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>|||Hello Shkedy,
To understand the issue better, I'd like to know if you run the query by
using Query Analyzer? As Shkedy mentioned, you may want to use profiler to
trace the queries for both situaitons. If profiler trace does not show any
differences, it shall be a server side issue. You may check if there is
differentce on session options such as "ANSI_PADDING" etc.
Also, please check if the issue occurs on different client machines. Try to
run query analyzer on different clients and try to run the query to test
the result.
If we isolate to the client side issue specific to this client machine, you
may want to use task manager or perfmon Dave mentioned to see if client has
performance problem.
I'd also like to know if the clients are in the same net segement on the
server? It might be network/name resolution issues if they are in different
sites or network segment.
Please let's know if you have any update on the issue. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
What is the structure of the table aspnet_Profile? There may only be 10
rows in that table, but how much data do those 10 rows represent? You
could be pulling back 10 rows with TEXT columns that each have a gig of
data in them! Pulling that across the network would obviously be slower.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I identified the problem -
the built in serilaizer in the .net framework was creating bloated objects
when it was serializing an business object which made the serilized value be
really big and hence slow in moving over the network.
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>