big performance problem

[francois]

Hi guys,

I have quite big troublesome performance problem.

then i did a very simpkle page with a dropdownlist, when u click on it it
makes a simple sql select on the SQL server and then display a very little
bit of data on the page.

When SQL server runs on my local machine it is pretty fast, but when SQL
server runs on our production server it is so slow.

I put a Trace=true directive in my simple ASPX page and i noticed :

1.when I request the page for the first time
aspx.page End Init 0.000163 0.000163
aspx.page Begin PreRender 1.513427 1.513264
aspx.page End PreRender 1.513587 0.000160


As you see the Begin PreREnder takes 1.5 seconds. Also what does that mean?
Does it meand that the time between calling the PAge.Init() method and the
Page.PreRender() method takes 1.5 seconds? or does it mean something else?
When the SQL Server is on my local machine this number is 0.00xx instead.

2. When i select a value on the ddl, then it postaback the page, display
some info from the DB fecthed by the event handler for the
DropDownList.OnSelectedIndexChanged event handler.

aspx.page Begin ProcessPostData 0.001000 0.000073
aspx.page End ProcessPostData 0.001200 0.000201
aspx.page Begin ProcessPostData Second Try 1.518187 1.516987
aspx.page End ProcessPostData Second Try 1.518327 0.000140
aspx.page Begin Raise ChangedEvents 1.518388 0.000061
aspx.page End Raise ChangedEvents 3.057865 1.539477
aspx.page Begin Raise PostBackEvent 3.057999 0.000134


as you see between End ProcessPostData and Begin ProcessPostData Second Try
and Begin Raise ChangedEvents and End Raise ChangedEvents each time 1.5 half
second is spent. which makes 3 seconds... which is a little bit unacceptable
as the page is very small.

I do not know why in the case the SQL server is on the production server
then those numbers get so big. for me at least the Begin PreRender and the
Begin ProcessPostData Second Try should be fast anyway as i do not access
the DB at that stage. I access the DB at page_Load time (to initialize the
dropdownlist) and at when the DropDownList.OnSelectedIndexChanged event is
raised to fetch info from the DB.

Is there someone would could help me on this matter, it would be VERY
appraciated. Maybe explain me what means those numbers? what is the
processing executed during those intervals that make it so slow?

Thanks in advance

Francois

 

[Arthur Mnev]

How large is the drop down list and how do you access it (i.e. datareader,
dataset).

Also, check what kind of authentication you are using. Passthrough or SQL;
passthrough can be a hinder if machines are not in the same domain.Get
request - > get denied, search for the toke, request again, discover that it
is expired etc...)

In my experience; the best performance was:

Local SQL authentication
Stored Procedures
DataReader
WebService
Depending on the type of data (most of data for drop downs doesnt really
change much) can be cached on the web service side (just set the paramter &
number of seconds) also make sure that you have EnableSession set to false
(unless you actually use it)

If all that fails, try to do manual query to the sql server and turn sql
tracing on you might see the latency on the network, though this would be
the last place I'd look.
-Arthur

 

[francois]

I am using a form authentication turned off right now as I am just testing
the application.
in the web.config i have:

<authorization>
<allow users="*" />
</authorization>

is it what u mean by passtrough authentication?

I am using stored procedure and datareader and also the DB size is very
little just a few records.

My main problem is actually to know what means those numbers i spoke abotu
in my first message to be able to understand what piece of code i have to
look at.

Thank you anyway I will try to look on the SQL server side too.

Francois

 

[Arthur Mnev]

When your system authenticates to SQL server (i.e. connection string
parameter)
You could ether have something like: user="sa" password="123456" or you
could have "Trusted" (dont remember what the exact string is)
The latter one is what I refered to.