performance difference between query analyzer and .NET code.

Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection time out problem with that dynamic SQl when I call
it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comWhen you say "everything works fine", do you also mean that the execution
time in Query Analyzer is within the connection timeout you have set in your
.Net application?
ML
http://milambda.blogspot.com/|||> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection time out problem with that dynamic SQl when I
> call
> it from .NET code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
If "everything works fine" still means it's slow, and extends beyond the
timeout setting that ML mentioned, then everything is not working fine.
One thing you might try is a different paging approach. There are probably
a handful of samples here that will be faster and safer than dynamic SQL:
http://www.aspfaq.com/2120

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 comparision of different connection types

Do you know some performance differences between reading data from a stored procedure using:

1. OLE DB Connection and OLE DB Source

2. ADO.NET Connection and DatareaderSource

Przemo

If you could do some testing and share it here I'm sure people would be intreested in reading it.

-Jamie

performance

Hi All:
When I design the report use VB.Net and view the report by using the
Preview tab and take about 1 min to get the result. However, once I deploy
the report and view the report through Report Manager, it take about a hour
to get the result. Why this happen? Anyone can help me on this?
Thanks,
KentKent,
When the report is previewed in the Report Designer the RS web facades are
bypassed. Instead, the Report Designer invokes the Report Server report
rendering functions directly. Perhaps, your performance issue is network
related. Does the Report Manager comes up quickly when you navigate from one
folder to another? If you believe that this is report rendering issue, look
at the Execution Log to find out how much time the Report Server spends in
querying, processing and rendering the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"kent kent" <kentkent@.discussions.microsoft.com> wrote in message
news:06DE6559-7DDB-4FD5-B13F-1262466ACC1B@.microsoft.com...
> Hi All:
> When I design the report use VB.Net and view the report by using the
> Preview tab and take about 1 min to get the result. However, once I
deploy
> the report and view the report through Report Manager, it take about a
hour
> to get the result. Why this happen? Anyone can help me on this?
> Thanks,
> Kent|||In addition to what Teo suggested, I think that 1 minute is mightly long.
Many of the tables I report against are between 1 and 10 million rows. Most
of my reports take just a few seconds. How many records are shown in the
final report. How many records are in the base table. Are you using query
parameters or filters. Filters bring all the data over and then filters it.
Query parameters perform the filter at the server and only bring down the
resulting data.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"kent kent" <kentkent@.discussions.microsoft.com> wrote in message
news:06DE6559-7DDB-4FD5-B13F-1262466ACC1B@.microsoft.com...
> Hi All:
> When I design the report use VB.Net and view the report by using the
> Preview tab and take about 1 min to get the result. However, once I
> deploy
> the report and view the report through Report Manager, it take about a
> hour
> to get the result. Why this happen? Anyone can help me on this?
> Thanks,
> Kent

Perform calculation using sql script

Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.
If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

Perform calculation using sql script

Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

Perform calculation using sql script

Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

perfmon to remote server

I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>

perfmon to remote server

I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:

> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>

perfmon to remote server

I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
Josh
Local Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:

> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>

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
>