Performance problem with asp.net and database connection

Hallo,

I have an ASP.NET application with masterpages, skins and diffrent themes.
The application works fine, but the performance is not realy good. If I load
an ASPX file, which has no database connection, is the performance ok. ASPX
file with one or more database queries have a answer time about 15 or 20
seconds. Each database query need maximal a second to give the answer. So
the query works ok!
Know you a reason, why the time with a database connection is so long? How
can I make my application faster?

Thanks for the answers and tips

ThomasHello Thomas,

Do you use connection pooling?!
Seems that your delay is the reason that no connection pooling is activated
and new connection takes some ammount of time to be initiated

--
WBR, Michael Nemtsev [.NET/C# MVP].
My blog: http://spaces.live.com/laflour
Team blog: http://devkids.blogspot.com/
"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo

THI have an ASP.NET application with masterpages, skins and diffrent
THthemes.
THThe application works fine, but the performance is not realy good.
THIf I load
THan ASPX file, which has no database connection, is the performance
THok. ASPX
THfile with one or more database queries have a answer time about 15
THor 20
THseconds. Each database query need maximal a second to give the
THanswer. So
THthe query works ok!
THKnow you a reason, why the time with a database connection is so
THlong? How
THcan I make my application faster?
On Tue, 3 Jul 2007 07:26:48 +0000 (UTC), Michael Nemtsev
<nemtsev@.msn.comwrote:

Quote:

Originally Posted by

>Hello Thomas,
>
>Do you use connection pooling?!
>Seems that your delay is the reason that no connection pooling is activated
>and new connection takes some ammount of time to be initiated

I was under the impession that connection pooling was activated by
default.

One bad thing about connection pooling is the default pool size is zero
- the number of permanently open connections.

* Add a "Min Pool Size" entry to your connection string. 5 seems a
suitable minimum pool size.

* Check all your database code to ensure that connections are closed
after being used. the "using" keyword is handy here as it limits the
scope of the connection. Although your app should just fail if this
isn't the case rather than just going slow.

* If you are using datasets - what happens when you have no data in the
dataset?
- make sure you check with something like
if ( rs != null ) ... before you try to access a dataset

* Consider using data caching for some of the data.

* Try not to use datasets when you don't need to. For instance accessing
a DataTable or, better some kind of generic list (e.g. List<T>) is
nearly always faster - this will often use a datareader to read the
actual data - there is no real syntax shortcut but it seems to always
exectute faster.

Read this: http://www.15seconds.com/issue/040830.htm
I can't add anything more to this as we really need to see the code
before commenting further.

Quote:

Originally Posted by

>THI have an ASP.NET application with masterpages, skins and diffrent
>THthemes.
>THThe application works fine, but the performance is not realy good.
>THIf I load
>THan ASPX file, which has no database connection, is the performance
>THok. ASPX
>THfile with one or more database queries have a answer time about 15
>THor 20
>THseconds. Each database query need maximal a second to give the
>THanswer. So
>THthe query works ok!
>THKnow you a reason, why the time with a database connection is so
>THlong? How
>THcan I make my application faster?
>

Hi Thomas,

In addition to what Mark said, you could easily identify bottlenecks on the
page by turning on trace (see <trace web.config element). Plus, inspect the
size of the ViewState as it could have grown up. There's one more potential
cause I have seen in several systems. Check how many queries are run for each
page impression, and how it affects the database (Profiler is the best tool
to use in this case).

Hope this helps
--
Milosz

"Hahn, Thomas" wrote:

Quote:

Originally Posted by

Hallo,
>
I have an ASP.NET application with masterpages, skins and diffrent themes.
The application works fine, but the performance is not realy good. If I load
an ASPX file, which has no database connection, is the performance ok. ASPX
file with one or more database queries have a answer time about 15 or 20
seconds. Each database query need maximal a second to give the answer. So
the query works ok!
Know you a reason, why the time with a database connection is so long? How
can I make my application faster?
>
Thanks for the answers and tips
>
Thomas
>
>
>

Performance problem with asp.net and database connection

Hallo,
I have an ASP.NET application with masterpages, skins and diffrent themes.
The application works fine, but the performance is not realy good. If I load
an ASPX file, which has no database connection, is the performance ok. ASPX
file with one or more database queries have a answer time about 15 or 20
seconds. Each database query need maximal a second to give the answer. So
the query works ok!
Know you a reason, why the time with a database connection is so long? How
can I make my application faster?
Thanks for the answers and tips
ThomasHello Thomas,
Do you use connection pooling?!
Seems that your delay is the reason that no connection pooling is activated
and new connection takes some ammount of time to be initiated
WBR, Michael Nemtsev [.NET/C# MVP].
My blog: http://spaces.live.com/laflour
Team blog: http://devkids.blogspot.com/
"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo
TH> I have an ASP.NET application with masterpages, skins and diffrent
TH> themes.
TH> The application works fine, but the performance is not realy good.
TH> If I load
TH> an ASPX file, which has no database connection, is the performance
TH> ok. ASPX
TH> file with one or more database queries have a answer time about 15
TH> or 20
TH> seconds. Each database query need maximal a second to give the
TH> answer. So
TH> the query works ok!
TH> Know you a reason, why the time with a database connection is so
TH> long? How
TH> can I make my application faster?
On Tue, 3 Jul 2007 07:26:48 +0000 (UTC), Michael Nemtsev
<nemtsev@.msn.com> wrote:

>Hello Thomas,
>Do you use connection pooling?!
>Seems that your delay is the reason that no connection pooling is activated
>and new connection takes some ammount of time to be initiated
I was under the impession that connection pooling was activated by
default.
One bad thing about connection pooling is the default pool size is zero
- the number of permanently open connections.
* Add a "Min Pool Size" entry to your connection string. 5 seems a
suitable minimum pool size.
* Check all your database code to ensure that connections are closed
after being used. the "using" keyword is handy here as it limits the
scope of the connection. Although your app should just fail if this
isn't the case rather than just going slow.
* If you are using datasets - what happens when you have no data in the
dataset?
- make sure you check with something like
if ( rs != null ) ... before you try to access a dataset
* Consider using data caching for some of the data.
* Try not to use datasets when you don't need to. For instance accessing
a DataTable or, better some kind of generic list (e.g. List<T> ) is
nearly always faster - this will often use a datareader to read the
actual data - there is no real syntax shortcut but it seems to always
exectute faster.
Read this: http://www.15seconds.com/issue/040830.htm
I can't add anything more to this as we really need to see the code
before commenting further.

>TH> I have an ASP.NET application with masterpages, skins and diffrent
>TH> themes.
>TH> The application works fine, but the performance is not realy good.
>TH> If I load
>TH> an ASPX file, which has no database connection, is the performance
>TH> ok. ASPX
>TH> file with one or more database queries have a answer time about 15
>TH> or 20
>TH> seconds. Each database query need maximal a second to give the
>TH> answer. So
>TH> the query works ok!
>TH> Know you a reason, why the time with a database connection is so
>TH> long? How
>TH> can I make my application faster?
>
Hi Thomas,
In addition to what Mark said, you could easily identify bottlenecks on the
page by turning on trace (see <trace > web.config element). Plus, inspect th
e
size of the ViewState as it could have grown up. There's one more potential
cause I have seen in several systems. Check how many queries are run for eac
h
page impression, and how it affects the database (Profiler is the best tool
to use in this case).
Hope this helps
--
Milosz
"Hahn, Thomas" wrote:

> Hallo,
> I have an ASP.NET application with masterpages, skins and diffrent themes.
> The application works fine, but the performance is not realy good. If I lo
ad
> an ASPX file, which has no database connection, is the performance ok. ASP
X
> file with one or more database queries have a answer time about 15 or 20
> seconds. Each database query need maximal a second to give the answer. So
> the query works ok!
> Know you a reason, why the time with a database connection is so long? How
> can I make my application faster?
> Thanks for the answers and tips
> Thomas
>
>

Persist a database connection across postbacks

Hi All,
I'm a new comer to web development as well as asp.net. So when
i fired up my first database driven page, i knew i had a problem of not
having a persistent database connection across postbacks. I tried to
piggyback the connection object to application object and it didn't
work. Well i do know that you ASP.net experts have a default way of
handling this scenario. Please enlighten me.Godwin Burby wrote:
> Hi All,
> I'm a new comer to web development as well as asp.net. So when
> i fired up my first database driven page, i knew i had a problem of not
> having a persistent database connection across postbacks. I tried to
> piggyback the connection object to application object and it didn't
> work. Well i do know that you ASP.net experts have a default way of
> handling this scenario. Please enlighten me.
Just my two-penneth, and may not be the answer you're looking for:
Create a function that creates/sets up your connection. Whenever you
need to interact with the database, call this function, do your data
work, and close the connection. Let the .NET framework worry about
connections and connection pooling.
This may at first sound counterintuitive, and up until a few months
ago, I was going along the route of "create one connection, share it
around, and keep it open". I had a stubborn application that was taking
four hours to perform some processing (admittedly, I was abusing
ASP.NET quite considerably to get this working). I switched around to
opening connections when I needed them, doing the db work, and closing
the connection. The same process then started taking less than ten
minutes.
The Open/Use/Close approach should definitely scale better than either
a) one connection for the whole application, or b) one connection per
session. (a) has problems because you have to do a lot of locking
around every db interaction to make sure no-one else is using it. (b)
can fail if the user opens a second browser window and fires off two db
related tasks simultaneously.
Damien
Hi,
Objects are destroyed every time page is rendered to client browser ..
so you need to create connection again..
One thing you can do is connection pooling but still you need to open a
connection from your application
..
"Godwin Burby" <godwinburby@.gmail.com> wrote in message
news:1142395833.438452.265750@.z34g2000cwc.googlegroups.com...
> Hi All,
> I'm a new comer to web development as well as asp.net. So when
> i fired up my first database driven page, i knew i had a problem of not
> having a persistent database connection across postbacks. I tried to
> piggyback the connection object to application object and it didn't
> work. Well i do know that you ASP.net experts have a default way of
> handling this scenario. Please enlighten me.
>
Thank you Damien for sharing your hardearned knowledge.
Thanx Hussain. I choose to open and close connection each time.

Persist a database connection across postbacks

Hi All,
I'm a new comer to web development as well as asp.net. So when
i fired up my first database driven page, i knew i had a problem of not
having a persistent database connection across postbacks. I tried to
piggyback the connection object to application object and it didn't
work. Well i do know that you ASP.net experts have a default way of
handling this scenario. Please enlighten me.Godwin Burby wrote:
> Hi All,
> I'm a new comer to web development as well as asp.net. So when
> i fired up my first database driven page, i knew i had a problem of not
> having a persistent database connection across postbacks. I tried to
> piggyback the connection object to application object and it didn't
> work. Well i do know that you ASP.net experts have a default way of
> handling this scenario. Please enlighten me.

Just my two-penneth, and may not be the answer you're looking for:

Create a function that creates/sets up your connection. Whenever you
need to interact with the database, call this function, do your data
work, and close the connection. Let the .NET framework worry about
connections and connection pooling.

This may at first sound counterintuitive, and up until a few months
ago, I was going along the route of "create one connection, share it
around, and keep it open". I had a stubborn application that was taking
four hours to perform some processing (admittedly, I was abusing
ASP.NET quite considerably to get this working). I switched around to
opening connections when I needed them, doing the db work, and closing
the connection. The same process then started taking less than ten
minutes.

The Open/Use/Close approach should definitely scale better than either
a) one connection for the whole application, or b) one connection per
session. (a) has problems because you have to do a lot of locking
around every db interaction to make sure no-one else is using it. (b)
can fail if the user opens a second browser window and fires off two db
related tasks simultaneously.

Damien
Hi,
Objects are destroyed every time page is rendered to client browser ..

so you need to create connection again..
One thing you can do is connection pooling but still you need to open a
connection from your application

...
"Godwin Burby" <godwinburby@.gmail.com> wrote in message
news:1142395833.438452.265750@.z34g2000cwc.googlegr oups.com...
> Hi All,
> I'm a new comer to web development as well as asp.net. So when
> i fired up my first database driven page, i knew i had a problem of not
> having a persistent database connection across postbacks. I tried to
> piggyback the connection object to application object and it didn't
> work. Well i do know that you ASP.net experts have a default way of
> handling this scenario. Please enlighten me.
Thank you Damien for sharing your hardearned knowledge.
Thanx Hussain. I choose to open and close connection each time.