Performance hit if I use SELECT DISTINCT?

We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code

SELECT DISTINCT email FROM Subscribers

to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).

I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.

What do you performance gurus think?Hi

Distinct does have an overhead but against 3500 records it should go unnoticed I would expect. Suck it an see - test both queries and see how they hold up.

If you are only selecting email and email is indexed (nonclustered) then you have a covered query (the data comes from the index). These values are not duplicated so I would imagine (never tested) that there would be 0 overhead for this. Someone could confirm or correct this. Either way - the overhead should be minimal.

HTH|||Thanks, I also don't think there will be a hit on this small a recordset, but I wanted to ask. As for indexing and other modifications... not today. I need a 30-second solution to this problem created in someone else's old code. These issues will be addressed in our next upgrade, which should be done next month.

Thanks again!|||DISTINCT can have a significant impact on performance, but again this will only be noticable on large datasets.sql

Performance Comparison - Code vs SqlDataSource, Gridview etc vs PlainControl

There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.

So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.

What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.

Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?

I don't have a tested answer for you, just an opinion. Re your own connect vs SqlDataSource, there should be no differenct. The SqlDataSource has to do everything you do so I don't see any performance implication on a single screen. With that said, though, I could see somepotential connection pooling issues since minor differences in connection strings prevent asp.net from reusing the same connection. Without going into details, I think that awell writtenconnection object (that gets reused) is more likely to allow pooling than typing your connection details into wizards all the time. In any case, I wouldn't worry about it at this stage of the game.

Re GridView vs writing all that stuff yourself. If you use all or most or even some of that functionality, I don't think you should attempt to rewrite it -- it's just not worth it. You may even make performance worse if you write it wrong, and frankly, programmer time is worth more than cpu time.

If you're interested in pursuing the issue, Farhan Muhammed wrote a book which gives some fairly detailed numbers comparing different access methods and different controls -- he did real performance comparisons. Real World ASP.NET Best Practiceshttp://www.amazon.com/Real-World-ASP-NET-Best-Practices/dp/1590591003

|||

Thanks a lot for your opinion. By "well written connection object that gets reused" do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope?

|||

ilovecats:

do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope

Well, what I really mean is that you have a common object used by all pages & modules in your application that manages connections for you, along the lines of an Application Block like this onehttp://msdn2.microsoft.com/en-us/library/aa480458.aspx. Using something like this not only hides the details of the connection from the programmer, it goes a long way towards assuring consistency in connection strings -- which is something you need if asp.net is to be able to pool connections (ie, getting a connection is very expensive, so asp.net keeps a pool of connections around for reuse, if you request a connection and one is available from the pool that has an identical connection string, it gets that one instead of creating a new one).

Now, I'm sure that MS uses good programming when they obtain connections, but it used to be the case -- and I think still is largely the case -- that any differences in your connection string (even cosmetic ones, like case and extra white space) prevent sharing (pooling) connections, so if 2 people put in the same connection strings but with different case, they could not share connections in the pool. I don't know anymore how true this, I seem to recall reading that leading and trailing spaces don't matter anymore, but I'm not sure. A data connection application block would typically get the connection info from a config file, which is to say that everyone uses the same config file, which is to say everyone who connects to sqlserver1.mydatabase has an identical connection string. OTOH, if you use the SqlDataSource, every programmer is entering the connectio info (isn't that right -- perhaps I'm missing something because I haven't used them very much, but I think that's how it has to work -- if I'm wrong I hope someone will correct me), raising the possibility that the connection strings will be a little different, hence they cannot share connections.

I think I'll post a question on this topic to see if my understanding is current.

However, I frankly wouldn't worry about it too much. If you get into it, fine, but unless you're working on a high volume application it won't make a meaningful difference.

In my shop, we do use a data access application block that someone else wrote, but that didn't stop me from using the"no programming" features of asp.net 2.0 SqlDataSource because we just aren't a high volume app.

Performance and Sizing question

Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:

> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file

> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.

> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)

> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article

> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.

> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!

Performance and Sizing question

Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:

> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file

> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.

> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)

> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]

> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.

> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!

Performance and Sizing question

Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!

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.com
I'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
>

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/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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
>

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
>