Performance of datatable.select() func against database query or

Hi all
I am developing a webportal on VS2005 and ASP.NET 2.0
Using MsSQL SERVER 2005.
I have some static records(around 15000) in the database.These contents
never changed while the application is running.
what i m doing is querying the database only once at the
application_start event of the global.asax file and storing the records
in dataset...and further no query to database is done for this data.
wherever I need this data I fetch it from the dataset...through
datatable.select(filter expression) method.this is done several time for
a single page request and the website is high traffic portal.
Is it an efficient way of doing or there is any other better way by
which I can achieve the same..,as the amount of data is very
large...approx 15,000 records.
Plz Suggest me ........
Thanxxxxxxxxxxxxxx
Regards
Deepti Yadav
NoidaOK, I will suggest you. 15,000 rows for a select is a lot of data to displa
y
in an ASP.NET web application. Are your users realistically going to need to
see all 15,000 rows? why not have a more restrictive select with some sort
of paging mechanism if they want to move to the next page of results?
Peter
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net
"Mukesh" wrote:

> Hi all
> I am developing a webportal on VS2005 and ASP.NET 2.0
> Using MsSQL SERVER 2005.
> I have some static records(around 15000) in the database.These contents
> never changed while the application is running.
> what i m doing is querying the database only once at the
> application_start event of the global.asax file and storing the records
> in dataset...and further no query to database is done for this data.
> wherever I need this data I fetch it from the dataset...through
> datatable.select(filter expression) method.this is done several time for
> a single page request and the website is high traffic portal.
> Is it an efficient way of doing or there is any other better way by
> which I can achieve the same..,as the amount of data is very
> large...approx 15,000 records.
> Plz Suggest me ........
>
> Thanxxxxxxxxxxxxxx
> Regards
> Deepti Yadav
> Noida
>

Performance of datatable.select() func against database query orthe xquery

Hi all

I am developing a webportal on VS2005 and ASP.NET 2.0
Using MsSQL SERVER 2005.
I have some static records(around 15000) in the database.These contents
never changed while the application is running.

what i m doing is querying the database only once at the
application_start event of the global.asax file and storing the records
in dataset...and further no query to database is done for this data.
wherever I need this data I fetch it from the dataset...through
datatable.select(filter expression) method.this is done several time for
a single page request and the website is high traffic portal.

Is it an efficient way of doing or there is any other better way by
which I can achieve the same..,as the amount of data is very
large...approx 15,000 records.
Plz Suggest me ........

Thanxxxxxxxxxxxxxx

Regards

Deepti Yadav
NoidaOK, I will suggest you. 15,000 rows for a select is a lot of data to display
in an ASP.NET web application. Are your users realistically going to need to
see all 15,000 rows? why not have a more restrictive select with some sort
of paging mechanism if they want to move to the next page of results?

Peter
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net
"Mukesh" wrote:

Quote:

Originally Posted by

>
Hi all
>
I am developing a webportal on VS2005 and ASP.NET 2.0
Using MsSQL SERVER 2005.
I have some static records(around 15000) in the database.These contents
never changed while the application is running.
>
what i m doing is querying the database only once at the
application_start event of the global.asax file and storing the records
in dataset...and further no query to database is done for this data.
wherever I need this data I fetch it from the dataset...through
datatable.select(filter expression) method.this is done several time for
a single page request and the website is high traffic portal.
>
Is it an efficient way of doing or there is any other better way by
which I can achieve the same..,as the amount of data is very
large...approx 15,000 records.
Plz Suggest me ........
>
>
Thanxxxxxxxxxxxxxx
>
Regards
>
Deepti Yadav
Noida
>

Hi Deepti,

Although you're not directly displaying all 15,000 records to end-user
(used DataTable.Select to filter), it's still not a good idea to cache all
15,000 records at server-side.

I would suggest to optimize at the database side: with careful index
design, I think you should get good performance when frequently selecting
from the table. After all, the database server also does pretty well to
cache data for frequently queried data.

Hope this helps.

Regards,
Walter Wang (wawang@.online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

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
>
>

Performance problem: DataSet 2 database

Hi group,
i've got a major performance problem.

I've got a dataset with 1 datatable.
This datatable has 3 columns with the following datatypes:
string 18;
datetime
decimal

There are approx 150.000 records in the datatable.
I need to save the values to the database.
Saving the records using a sp is way to slow (12 minutes!)

Does anybody have a faster way?
Ive tried:
http://www.codeproject.com/cs/database/generic_OpenXml.asp?df=100&forumid=171911&select=1344569&msg=1344569
But this didnt work due to the datetime column.

Please help me on this one!!!

TIA

VisualSanderDon't load all 150,000 records at the same time. Is there a reason you need all 150,000 records in there? Can't you do it in batches of, say, 1000?

performance question

Lets just say my app is done HOO HOO.

Now, I'm accessing the database via a web service and one thing i noticed
that my app is running real slow. When I first started working on the app is
ran pretty quick returned the data to the screens in about 2 - 3 seconds. Now
its going about 5 - 10 seconds. How can I beef it up for better performance.> Lets just say my app is done HOO HOO.

I hate it when my app is done HOO HOO. But what are you going to do? Live it
or live with it, as the young lady says...

;-)

Performance. This is one of the reasons why Uncle Chutney says "Big things
are made up of lots of little things." In a client-server app, this is
especially true. Small increases or decreases in performance can make a big
difference when a lot of clients are accessing a web app. Multiply the
difference in performance by the number of clients, and you can see why.

There are lots of little things you can do to dramatically increase
performance. I'll try to hit the big ones.

1. In a client-server app, any work done by the client means a big savings
on the server. The more clients the bigger the savings. Use client-side
processing and JavaScript whenever possible.
2. If your App is written in VB.Net, make sure that Option Strict is turned
ON! Late-binding is anathema to performance.
3. Avoid duplication in your app. Don't duplicate values, and don't
duplicate code. Any time you see the same few lines of code in several
places, you're looking at a good candidate for a function or Sub. If you
have created a number of classes that have the same fields, properties,
and/or methods, you're looking at a good candidate for a base class, and
derived classes.
4. Avoid using the "drag and drop" tools in the toolbox. Some of them are
quite useful; others are one-size-fits-all memory hogs for beginners, to
make it easy for them to make something happen without much trouble.
5. Close and/or Dispose any objects that use unmanaged resources as quickly
as possible.
6. Close database connections ASAP.

On a similar note, it should be stressed that performance is a factor in
designing your app. The fastest apps have much more lower-level code in
them, and are also more proprietary overall. Writing an app that is easily
extensible may involve a trade-off of performance over code maintainability.
Striking the right balance is an art.

I'm sure I've overlooked several things, but I do believe I've hit on the
majors in this list. At any rate, I hope it helps.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
> Lets just say my app is done HOO HOO.
> Now, I'm accessing the database via a web service and one thing i noticed
> that my app is running real slow. When I first started working on the app
is
> ran pretty quick returned the data to the screens in about 2 - 3 seconds.
Now
> its going about 5 - 10 seconds. How can I beef it up for better
performance.
All good points. Actually all the db connections are closed and all the db
transactions are being done in the web service. The html(aspx) pages call the
function needed and then i do formatting on the aspx pages, such as the
datagrids.
The only question i have it the Option Strict. I turned that ON and when I
recompiled the project. I got all kinds of errors such as

Option Strict On Disallows implicit conversions from 'String' to 'Short'

how do i fix that?

"Kevin Spencer" wrote:

> > Lets just say my app is done HOO HOO.
> I hate it when my app is done HOO HOO. But what are you going to do? Live it
> or live with it, as the young lady says...
> ;-)
> Performance. This is one of the reasons why Uncle Chutney says "Big things
> are made up of lots of little things." In a client-server app, this is
> especially true. Small increases or decreases in performance can make a big
> difference when a lot of clients are accessing a web app. Multiply the
> difference in performance by the number of clients, and you can see why.
> There are lots of little things you can do to dramatically increase
> performance. I'll try to hit the big ones.
> 1. In a client-server app, any work done by the client means a big savings
> on the server. The more clients the bigger the savings. Use client-side
> processing and JavaScript whenever possible.
> 2. If your App is written in VB.Net, make sure that Option Strict is turned
> ON! Late-binding is anathema to performance.
> 3. Avoid duplication in your app. Don't duplicate values, and don't
> duplicate code. Any time you see the same few lines of code in several
> places, you're looking at a good candidate for a function or Sub. If you
> have created a number of classes that have the same fields, properties,
> and/or methods, you're looking at a good candidate for a base class, and
> derived classes.
> 4. Avoid using the "drag and drop" tools in the toolbox. Some of them are
> quite useful; others are one-size-fits-all memory hogs for beginners, to
> make it easy for them to make something happen without much trouble.
> 5. Close and/or Dispose any objects that use unmanaged resources as quickly
> as possible.
> 6. Close database connections ASAP.
> On a similar note, it should be stressed that performance is a factor in
> designing your app. The fastest apps have much more lower-level code in
> them, and are also more proprietary overall. Writing an app that is easily
> extensible may involve a trade-off of performance over code maintainability.
> Striking the right balance is an art.
> I'm sure I've overlooked several things, but I do believe I've hit on the
> majors in this list. At any rate, I hope it helps.
> --
> HTH,
> Kevin Spencer
> ..Net Developer
> Microsoft MVP
> I get paid good money to
> solve puzzles for a living
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
> > Lets just say my app is done HOO HOO.
> > Now, I'm accessing the database via a web service and one thing i noticed
> > that my app is running real slow. When I first started working on the app
> is
> > ran pretty quick returned the data to the screens in about 2 - 3 seconds.
> Now
> > its going about 5 - 10 seconds. How can I beef it up for better
> performance.
>
Dim x as string = "99"
Dim y as short = CType(x, Short)
OR
Dim y as short = CShort(x)

You'll use a lot of CType with Option Strict On, but it is worth the extra
effort.

Greg

"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:8AEAF9A6-319B-4298-AC79-2991C3F89A2A@.microsoft.com...
> All good points. Actually all the db connections are closed and all the db
> transactions are being done in the web service. The html(aspx) pages call
> the
> function needed and then i do formatting on the aspx pages, such as the
> datagrids.
> The only question i have it the Option Strict. I turned that ON and when I
> recompiled the project. I got all kinds of errors such as
> Option Strict On Disallows implicit conversions from 'String' to 'Short'
> how do i fix that?
> "Kevin Spencer" wrote:
>> > Lets just say my app is done HOO HOO.
>>
>> I hate it when my app is done HOO HOO. But what are you going to do? Live
>> it
>> or live with it, as the young lady says...
>>
>> ;-)
>>
>> Performance. This is one of the reasons why Uncle Chutney says "Big
>> things
>> are made up of lots of little things." In a client-server app, this is
>> especially true. Small increases or decreases in performance can make a
>> big
>> difference when a lot of clients are accessing a web app. Multiply the
>> difference in performance by the number of clients, and you can see why.
>>
>> There are lots of little things you can do to dramatically increase
>> performance. I'll try to hit the big ones.
>>
>> 1. In a client-server app, any work done by the client means a big
>> savings
>> on the server. The more clients the bigger the savings. Use client-side
>> processing and JavaScript whenever possible.
>> 2. If your App is written in VB.Net, make sure that Option Strict is
>> turned
>> ON! Late-binding is anathema to performance.
>> 3. Avoid duplication in your app. Don't duplicate values, and don't
>> duplicate code. Any time you see the same few lines of code in several
>> places, you're looking at a good candidate for a function or Sub. If you
>> have created a number of classes that have the same fields, properties,
>> and/or methods, you're looking at a good candidate for a base class, and
>> derived classes.
>> 4. Avoid using the "drag and drop" tools in the toolbox. Some of them are
>> quite useful; others are one-size-fits-all memory hogs for beginners, to
>> make it easy for them to make something happen without much trouble.
>> 5. Close and/or Dispose any objects that use unmanaged resources as
>> quickly
>> as possible.
>> 6. Close database connections ASAP.
>>
>> On a similar note, it should be stressed that performance is a factor in
>> designing your app. The fastest apps have much more lower-level code in
>> them, and are also more proprietary overall. Writing an app that is
>> easily
>> extensible may involve a trade-off of performance over code
>> maintainability.
>> Striking the right balance is an art.
>>
>> I'm sure I've overlooked several things, but I do believe I've hit on the
>> majors in this list. At any rate, I hope it helps.
>>
>> --
>> HTH,
>> Kevin Spencer
>> ..Net Developer
>> Microsoft MVP
>> I get paid good money to
>> solve puzzles for a living
>>
>> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
>> > Lets just say my app is done HOO HOO.
>>> > Now, I'm accessing the database via a web service and one thing i
>> > noticed
>> > that my app is running real slow. When I first started working on the
>> > app
>> is
>> > ran pretty quick returned the data to the screens in about 2 - 3
>> > seconds.
>> Now
>> > its going about 5 - 10 seconds. How can I beef it up for better
>> performance.
>>>>
>>
>
That worked on the one error.
Now i'm getting Option Strict On disallows Late binding.
I'm doing some formatting of the datagrids based on certain data that is
returned.

How can i fix that issue now?
I need to format the grids based on the data being returned

"Greg Burns" wrote:

> Dim x as string = "99"
> Dim y as short = CType(x, Short)
> OR
> Dim y as short = CShort(x)
> You'll use a lot of CType with Option Strict On, but it is worth the extra
> effort.
> Greg
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:8AEAF9A6-319B-4298-AC79-2991C3F89A2A@.microsoft.com...
> > All good points. Actually all the db connections are closed and all the db
> > transactions are being done in the web service. The html(aspx) pages call
> > the
> > function needed and then i do formatting on the aspx pages, such as the
> > datagrids.
> > The only question i have it the Option Strict. I turned that ON and when I
> > recompiled the project. I got all kinds of errors such as
> > Option Strict On Disallows implicit conversions from 'String' to 'Short'
> > how do i fix that?
> > "Kevin Spencer" wrote:
> >> > Lets just say my app is done HOO HOO.
> >>
> >> I hate it when my app is done HOO HOO. But what are you going to do? Live
> >> it
> >> or live with it, as the young lady says...
> >>
> >> ;-)
> >>
> >> Performance. This is one of the reasons why Uncle Chutney says "Big
> >> things
> >> are made up of lots of little things." In a client-server app, this is
> >> especially true. Small increases or decreases in performance can make a
> >> big
> >> difference when a lot of clients are accessing a web app. Multiply the
> >> difference in performance by the number of clients, and you can see why.
> >>
> >> There are lots of little things you can do to dramatically increase
> >> performance. I'll try to hit the big ones.
> >>
> >> 1. In a client-server app, any work done by the client means a big
> >> savings
> >> on the server. The more clients the bigger the savings. Use client-side
> >> processing and JavaScript whenever possible.
> >> 2. If your App is written in VB.Net, make sure that Option Strict is
> >> turned
> >> ON! Late-binding is anathema to performance.
> >> 3. Avoid duplication in your app. Don't duplicate values, and don't
> >> duplicate code. Any time you see the same few lines of code in several
> >> places, you're looking at a good candidate for a function or Sub. If you
> >> have created a number of classes that have the same fields, properties,
> >> and/or methods, you're looking at a good candidate for a base class, and
> >> derived classes.
> >> 4. Avoid using the "drag and drop" tools in the toolbox. Some of them are
> >> quite useful; others are one-size-fits-all memory hogs for beginners, to
> >> make it easy for them to make something happen without much trouble.
> >> 5. Close and/or Dispose any objects that use unmanaged resources as
> >> quickly
> >> as possible.
> >> 6. Close database connections ASAP.
> >>
> >> On a similar note, it should be stressed that performance is a factor in
> >> designing your app. The fastest apps have much more lower-level code in
> >> them, and are also more proprietary overall. Writing an app that is
> >> easily
> >> extensible may involve a trade-off of performance over code
> >> maintainability.
> >> Striking the right balance is an art.
> >>
> >> I'm sure I've overlooked several things, but I do believe I've hit on the
> >> majors in this list. At any rate, I hope it helps.
> >>
> >> --
> >> HTH,
> >> Kevin Spencer
> >> ..Net Developer
> >> Microsoft MVP
> >> I get paid good money to
> >> solve puzzles for a living
> >>
> >> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> >> news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
> >> > Lets just say my app is done HOO HOO.
> >> >> > Now, I'm accessing the database via a web service and one thing i
> >> > noticed
> >> > that my app is running real slow. When I first started working on the
> >> > app
> >> is
> >> > ran pretty quick returned the data to the screens in about 2 - 3
> >> > seconds.
> >> Now
> >> > its going about 5 - 10 seconds. How can I beef it up for better
> >> performance.
> >> >> >>
> >>
> >>
>
Hi Mike,

> The only question i have it the Option Strict. I turned that ON and when I
> recompiled the project. I got all kinds of errors such as

Awesome! Sounds like you're coming from a VB background. Option Strict
ensures that you use correct data types in your code. The difference between
late and early binding is that Late Binding means that your app explicitly
declares data types, and doesn't mix and match them. This way, the app
already knows at run-time how much memory to allocate for each object. When
late-binding is used, the data type of an object may NOT be known at
run-time, and the Platform has to figure out how much memory to allocate by
calculation, which often involves the use of Reflection. It can slow your
app down quite a bit.

The errors can be fixed by going through your code, and making sure that you
explicitly declare the correct data type for each field, property, or
variable that you are using. Also, avoid the use of the "Object" data type.
Object is the base class for ALL data types, and is therefore a
"late-binding class." So, for example, here is a field and Property declared
with no Data Types:

Private _DayOfWeek = 1
Public Property DayofWeek
Get
Return _DayOfWeek
End Get
Set (ByVal Value)
_DayOfWeek = Value
End Set
End Property

Using this (Option Strinct OFF), you could assign a TexBox value (always a
string) to DayOfWeek, and it would compile fine. At run-time, the String
would have to be converted to a number by the Platform.

Instead, you would put (Option Strict ON):

Private _DayOfWeek As Short = 1
Public Property DayofWeek As Short
Get
Return _DayOfWeek
End Get
Set (ByVal Value As Short)
_DayOfWeek = Value
End Set
End Property

Your code will compile fine, as long as nothing in your code tries to assign
a String to DayOfWeek. You will get a "Data Type Mismatch" error if you do.

As I'm sure you can see, this also prevents a lot of errors (assigning a
non-numeric string to be used as a Short, for example), as well as informing
the Platform to allocate 16 Bits for _DayOfWeek.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:8AEAF9A6-319B-4298-AC79-2991C3F89A2A@.microsoft.com...
> All good points. Actually all the db connections are closed and all the db
> transactions are being done in the web service. The html(aspx) pages call
the
> function needed and then i do formatting on the aspx pages, such as the
> datagrids.
> The only question i have it the Option Strict. I turned that ON and when I
> recompiled the project. I got all kinds of errors such as
> Option Strict On Disallows implicit conversions from 'String' to 'Short'
> how do i fix that?
> "Kevin Spencer" wrote:
> > > Lets just say my app is done HOO HOO.
> > I hate it when my app is done HOO HOO. But what are you going to do?
Live it
> > or live with it, as the young lady says...
> > ;-)
> > Performance. This is one of the reasons why Uncle Chutney says "Big
things
> > are made up of lots of little things." In a client-server app, this is
> > especially true. Small increases or decreases in performance can make a
big
> > difference when a lot of clients are accessing a web app. Multiply the
> > difference in performance by the number of clients, and you can see why.
> > There are lots of little things you can do to dramatically increase
> > performance. I'll try to hit the big ones.
> > 1. In a client-server app, any work done by the client means a big
savings
> > on the server. The more clients the bigger the savings. Use client-side
> > processing and JavaScript whenever possible.
> > 2. If your App is written in VB.Net, make sure that Option Strict is
turned
> > ON! Late-binding is anathema to performance.
> > 3. Avoid duplication in your app. Don't duplicate values, and don't
> > duplicate code. Any time you see the same few lines of code in several
> > places, you're looking at a good candidate for a function or Sub. If you
> > have created a number of classes that have the same fields, properties,
> > and/or methods, you're looking at a good candidate for a base class, and
> > derived classes.
> > 4. Avoid using the "drag and drop" tools in the toolbox. Some of them
are
> > quite useful; others are one-size-fits-all memory hogs for beginners, to
> > make it easy for them to make something happen without much trouble.
> > 5. Close and/or Dispose any objects that use unmanaged resources as
quickly
> > as possible.
> > 6. Close database connections ASAP.
> > On a similar note, it should be stressed that performance is a factor in
> > designing your app. The fastest apps have much more lower-level code in
> > them, and are also more proprietary overall. Writing an app that is
easily
> > extensible may involve a trade-off of performance over code
maintainability.
> > Striking the right balance is an art.
> > I'm sure I've overlooked several things, but I do believe I've hit on
the
> > majors in this list. At any rate, I hope it helps.
> > --
> > HTH,
> > Kevin Spencer
> > ..Net Developer
> > Microsoft MVP
> > I get paid good money to
> > solve puzzles for a living
> > "Mike" <Mike@.discussions.microsoft.com> wrote in message
> > news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
> > > Lets just say my app is done HOO HOO.
> > > > Now, I'm accessing the database via a web service and one thing i
noticed
> > > that my app is running real slow. When I first started working on the
app
> > is
> > > ran pretty quick returned the data to the screens in about 2 - 3
seconds.
> > Now
> > > its going about 5 - 10 seconds. How can I beef it up for better
> > performance.
> >
Please post some code.

Greg

"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:24DE9539-DB6D-446D-A5BA-E7B6E4A19960@.microsoft.com...
> That worked on the one error.
> Now i'm getting Option Strict On disallows Late binding.
> I'm doing some formatting of the datagrids based on certain data that is
> returned.
> How can i fix that issue now?
> I need to format the grids based on the data being returned
> "Greg Burns" wrote:
>> Dim x as string = "99"
>> Dim y as short = CType(x, Short)
>> OR
>> Dim y as short = CShort(x)
>>
>> You'll use a lot of CType with Option Strict On, but it is worth the
>> extra
>> effort.
>>
>> Greg
>>
>> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> news:8AEAF9A6-319B-4298-AC79-2991C3F89A2A@.microsoft.com...
>> > All good points. Actually all the db connections are closed and all the
>> > db
>> > transactions are being done in the web service. The html(aspx) pages
>> > call
>> > the
>> > function needed and then i do formatting on the aspx pages, such as the
>> > datagrids.
>> > The only question i have it the Option Strict. I turned that ON and
>> > when I
>> > recompiled the project. I got all kinds of errors such as
>>> > Option Strict On Disallows implicit conversions from 'String' to
>> > 'Short'
>>> > how do i fix that?
>>> > "Kevin Spencer" wrote:
>>> >> > Lets just say my app is done HOO HOO.
>> >>
>> >> I hate it when my app is done HOO HOO. But what are you going to do?
>> >> Live
>> >> it
>> >> or live with it, as the young lady says...
>> >>
>> >> ;-)
>> >>
>> >> Performance. This is one of the reasons why Uncle Chutney says "Big
>> >> things
>> >> are made up of lots of little things." In a client-server app, this is
>> >> especially true. Small increases or decreases in performance can make
>> >> a
>> >> big
>> >> difference when a lot of clients are accessing a web app. Multiply the
>> >> difference in performance by the number of clients, and you can see
>> >> why.
>> >>
>> >> There are lots of little things you can do to dramatically increase
>> >> performance. I'll try to hit the big ones.
>> >>
>> >> 1. In a client-server app, any work done by the client means a big
>> >> savings
>> >> on the server. The more clients the bigger the savings. Use
>> >> client-side
>> >> processing and JavaScript whenever possible.
>> >> 2. If your App is written in VB.Net, make sure that Option Strict is
>> >> turned
>> >> ON! Late-binding is anathema to performance.
>> >> 3. Avoid duplication in your app. Don't duplicate values, and don't
>> >> duplicate code. Any time you see the same few lines of code in several
>> >> places, you're looking at a good candidate for a function or Sub. If
>> >> you
>> >> have created a number of classes that have the same fields,
>> >> properties,
>> >> and/or methods, you're looking at a good candidate for a base class,
>> >> and
>> >> derived classes.
>> >> 4. Avoid using the "drag and drop" tools in the toolbox. Some of them
>> >> are
>> >> quite useful; others are one-size-fits-all memory hogs for beginners,
>> >> to
>> >> make it easy for them to make something happen without much trouble.
>> >> 5. Close and/or Dispose any objects that use unmanaged resources as
>> >> quickly
>> >> as possible.
>> >> 6. Close database connections ASAP.
>> >>
>> >> On a similar note, it should be stressed that performance is a factor
>> >> in
>> >> designing your app. The fastest apps have much more lower-level code
>> >> in
>> >> them, and are also more proprietary overall. Writing an app that is
>> >> easily
>> >> extensible may involve a trade-off of performance over code
>> >> maintainability.
>> >> Striking the right balance is an art.
>> >>
>> >> I'm sure I've overlooked several things, but I do believe I've hit on
>> >> the
>> >> majors in this list. At any rate, I hope it helps.
>> >>
>> >> --
>> >> HTH,
>> >> Kevin Spencer
>> >> ..Net Developer
>> >> Microsoft MVP
>> >> I get paid good money to
>> >> solve puzzles for a living
>> >>
>> >> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> >> news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
>> >> > Lets just say my app is done HOO HOO.
>> >>> >> > Now, I'm accessing the database via a web service and one thing i
>> >> > noticed
>> >> > that my app is running real slow. When I first started working on
>> >> > the
>> >> > app
>> >> is
>> >> > ran pretty quick returned the data to the screens in about 2 - 3
>> >> > seconds.
>> >> Now
>> >> > its going about 5 - 10 seconds. How can I beef it up for better
>> >> performance.
>> >>> >>> >>
>> >>
>> >>
>>
>>
>

performance question

Lets just say my app is done HOO HOO.
Now, I'm accessing the database via a web service and one thing i noticed
that my app is running real slow. When I first started working on the app is
ran pretty quick returned the data to the screens in about 2 - 3 seconds. No
w
its going about 5 - 10 seconds. How can I beef it up for better performance.> Lets just say my app is done HOO HOO.
I hate it when my app is done HOO HOO. But what are you going to do? Live it
or live with it, as the young lady says...
;-)
Performance. This is one of the reasons why Uncle Chutney says "Big things
are made up of lots of little things." In a client-server app, this is
especially true. Small increases or decreases in performance can make a big
difference when a lot of clients are accessing a web app. Multiply the
difference in performance by the number of clients, and you can see why.
There are lots of little things you can do to dramatically increase
performance. I'll try to hit the big ones.
1. In a client-server app, any work done by the client means a big savings
on the server. The more clients the bigger the savings. Use client-side
processing and JavaScript whenever possible.
2. If your App is written in VB.Net, make sure that Option Strict is turned
ON! Late-binding is anathema to performance.
3. Avoid duplication in your app. Don't duplicate values, and don't
duplicate code. Any time you see the same few lines of code in several
places, you're looking at a good candidate for a function or Sub. If you
have created a number of classes that have the same fields, properties,
and/or methods, you're looking at a good candidate for a base class, and
derived classes.
4. Avoid using the "drag and drop" tools in the toolbox. Some of them are
quite useful; others are one-size-fits-all memory hogs for beginners, to
make it easy for them to make something happen without much trouble.
5. Close and/or Dispose any objects that use unmanaged resources as quickly
as possible.
6. Close database connections ASAP.
On a similar note, it should be stressed that performance is a factor in
designing your app. The fastest apps have much more lower-level code in
them, and are also more proprietary overall. Writing an app that is easily
extensible may involve a trade-off of performance over code maintainability.
Striking the right balance is an art.
I'm sure I've overlooked several things, but I do believe I've hit on the
majors in this list. At any rate, I hope it helps.
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
> Lets just say my app is done HOO HOO.
> Now, I'm accessing the database via a web service and one thing i noticed
> that my app is running real slow. When I first started working on the app
is
> ran pretty quick returned the data to the screens in about 2 - 3 seconds.
Now
> its going about 5 - 10 seconds. How can I beef it up for better
performance.
>
All good points. Actually all the db connections are closed and all the db
transactions are being done in the web service. The html(aspx) pages call th
e
function needed and then i do formatting on the aspx pages, such as the
datagrids.
The only question i have it the Option Strict. I turned that ON and when I
recompiled the project. I got all kinds of errors such as
Option Strict On Disallows implicit conversions from 'String' to 'Short'
how do i fix that?
"Kevin Spencer" wrote:

> I hate it when my app is done HOO HOO. But what are you going to do? Live
it
> or live with it, as the young lady says...
> ;-)
> Performance. This is one of the reasons why Uncle Chutney says "Big things
> are made up of lots of little things." In a client-server app, this is
> especially true. Small increases or decreases in performance can make a bi
g
> difference when a lot of clients are accessing a web app. Multiply the
> difference in performance by the number of clients, and you can see why.
> There are lots of little things you can do to dramatically increase
> performance. I'll try to hit the big ones.
> 1. In a client-server app, any work done by the client means a big savings
> on the server. The more clients the bigger the savings. Use client-side
> processing and JavaScript whenever possible.
> 2. If your App is written in VB.Net, make sure that Option Strict is turne
d
> ON! Late-binding is anathema to performance.
> 3. Avoid duplication in your app. Don't duplicate values, and don't
> duplicate code. Any time you see the same few lines of code in several
> places, you're looking at a good candidate for a function or Sub. If you
> have created a number of classes that have the same fields, properties,
> and/or methods, you're looking at a good candidate for a base class, and
> derived classes.
> 4. Avoid using the "drag and drop" tools in the toolbox. Some of them are
> quite useful; others are one-size-fits-all memory hogs for beginners, to
> make it easy for them to make something happen without much trouble.
> 5. Close and/or Dispose any objects that use unmanaged resources as quickl
y
> as possible.
> 6. Close database connections ASAP.
> On a similar note, it should be stressed that performance is a factor in
> designing your app. The fastest apps have much more lower-level code in
> them, and are also more proprietary overall. Writing an app that is easily
> extensible may involve a trade-off of performance over code maintainabilit
y.
> Striking the right balance is an art.
> I'm sure I've overlooked several things, but I do believe I've hit on the
> majors in this list. At any rate, I hope it helps.
> --
> HTH,
> Kevin Spencer
> ..Net Developer
> Microsoft MVP
> I get paid good money to
> solve puzzles for a living
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1DA19F5F-C332-4818-959F-7881D0D3A61A@.microsoft.com...
> is
> Now
> performance.
>
>
Dim x as string = "99"
Dim y as short = CType(x, Short)
OR
Dim y as short = CShort(x)
You'll use a lot of CType with Option Strict On, but it is worth the extra
effort.
Greg
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:8AEAF9A6-319B-4298-AC79-2991C3F89A2A@.microsoft.com...
> All good points. Actually all the db connections are closed and all the db
> transactions are being done in the web service. The html(aspx) pages call
> the
> function needed and then i do formatting on the aspx pages, such as the
> datagrids.
> The only question i have it the Option Strict. I turned that ON and when I
> recompiled the project. I got all kinds of errors such as
> Option Strict On Disallows implicit conversions from 'String' to 'Short'
> how do i fix that?
> "Kevin Spencer" wrote:
>
That worked on the one error.
Now i'm getting Option Strict On disallows Late binding.
I'm doing some formatting of the datagrids based on certain data that is
returned.
How can i fix that issue now?
I need to format the grids based on the data being returned
"Greg Burns" wrote:

> Dim x as string = "99"
> Dim y as short = CType(x, Short)
> OR
> Dim y as short = CShort(x)
> You'll use a lot of CType with Option Strict On, but it is worth the extra
> effort.
> Greg
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:8AEAF9A6-319B-4298-AC79-2991C3F89A2A@.microsoft.com...
>
>
Hi Mike,

> The only question i have it the Option Strict. I turned that ON and when I
> recompiled the project. I got all kinds of errors such as
Awesome! Sounds like you're coming from a VB background. Option Strict
ensures that you use correct data types in your code. The difference between
late and early binding is that Late Binding means that your app explicitly
declares data types, and doesn't mix and match them. This way, the app
already knows at run-time how much memory to allocate for each object. When
late-binding is used, the data type of an object may NOT be known at
run-time, and the Platform has to figure out how much memory to allocate by
calculation, which often involves the use of Reflection. It can slow your
app down quite a bit.
The errors can be fixed by going through your code, and making sure that you
explicitly declare the correct data type for each field, property, or
variable that you are using. Also, avoid the use of the "Object" data type.
Object is the base class for ALL data types, and is therefore a
"late-binding class." So, for example, here is a field and Property declared
with no Data Types:
Private _DayOfW = 1
Public Property DayofW
Get
Return _DayOfW
End Get
Set (ByVal Value)
_DayOfW = Value
End Set
End Property
Using this (Option Strinct OFF), you could assign a TexBox value (always a
string) to DayOfW, and it would compile fine. At run-time, the String
would have to be converted to a number by the Platform.
Instead, you would put (Option Strict ON):
Private _DayOfW As Short = 1
Public Property DayofW As Short
Get
Return _DayOfW
End Get
Set (ByVal Value As Short)
_DayOfW = Value
End Set
End Property
Your code will compile fine, as long as nothing in your code tries to assign
a String to DayOfW. You will get a "Data Type Mismatch" error if you do.
As I'm sure you can see, this also prevents a lot of errors (assigning a
non-numeric string to be used as a Short, for example), as well as informing
the Platform to allocate 16 Bits for _DayOfW.
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:8AEAF9A6-319B-4298-AC79-2991C3F89A2A@.microsoft.com...
> All good points. Actually all the db connections are closed and all the db
> transactions are being done in the web service. The html(aspx) pages call
the
> function needed and then i do formatting on the aspx pages, such as the
> datagrids.
> The only question i have it the Option Strict. I turned that ON and when I
> recompiled the project. I got all kinds of errors such as
> Option Strict On Disallows implicit conversions from 'String' to 'Short'
> how do i fix that?
> "Kevin Spencer" wrote:
>
Live it
things
big
savings
turned
are
quickly
easily
maintainability.
the
noticed
app
seconds.
Please post some code.
Greg
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:24DE9539-DB6D-446D-A5BA-E7B6E4A19960@.microsoft.com...
> That worked on the one error.
> Now i'm getting Option Strict On disallows Late binding.
> I'm doing some formatting of the datagrids based on certain data that is
> returned.
> How can i fix that issue now?
> I need to format the grids based on the data being returned
> "Greg Burns" wrote:
>

Performance Question with Database Connections

I was just wondering what other people's opinions and experiences were in
regards to using Database Connections throughout a website. Supposing that a
single webpage accesses a database anywhere from 5-30 times throughout a
webpage do you prefer to open the database connection upon say
initialization and reuse it elsewhere whenever possible and then dispose of
it upon disposing or do you write 5 - 10 lines of code opening and closing
it every single time you want to use the database.It doesn't really matter much because of the built in database connection
pooling.
Creating a database connection 5 times in a page has no significant
performance penalty, so do it whichever way seems most intuitive for your
web site.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

<johndoe@.driver.net> wrote in message
news:u9lhwP8PEHA.3232@.TK2MSFTNGP11.phx.gbl...
> I was just wondering what other people's opinions and experiences were in
> regards to using Database Connections throughout a website. Supposing that
a
> single webpage accesses a database anywhere from 5-30 times throughout a
> webpage do you prefer to open the database connection upon say
> initialization and reuse it elsewhere whenever possible and then dispose
of
> it upon disposing or do you write 5 - 10 lines of code opening and closing
> it every single time you want to use the database.
I kind of assumed this but there are thousands of scenarios that are coming
to my mind for example if you have 150 - 350 concurrent users accessing a
webpage at once what type of load would this put on with having 350
connections open at one time versus 1000 - 3000 connections opening and
closing, etc so I thought I would see what other people would have to say.

does this
"Steve C. Orr [MVP, MCSD]" <Steve@.Orr.net> wrote in message
news:O6OStZ8PEHA.1048@.tk2msftngp13.phx.gbl...
> It doesn't really matter much because of the built in database connection
> pooling.
> Creating a database connection 5 times in a page has no significant
> performance penalty, so do it whichever way seems most intuitive for your
> web site.
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://Steve.Orr.net
>
> <johndoe@.driver.net> wrote in message
> news:u9lhwP8PEHA.3232@.TK2MSFTNGP11.phx.gbl...
>> I was just wondering what other people's opinions and experiences were in
>> regards to using Database Connections throughout a website. Supposing
>> that
> a
>> single webpage accesses a database anywhere from 5-30 times throughout a
>> webpage do you prefer to open the database connection upon say
>> initialization and reuse it elsewhere whenever possible and then dispose
> of
>> it upon disposing or do you write 5 - 10 lines of code opening and
>> closing
>> it every single time you want to use the database.
>>
>>
The connections aren't really opening and closing though, so from a
performance perspective the 2 scenarios scenarios are virtually identical.
So then it comes down to opinion about how you prefer to structure your
code.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

<johndoe@.driver.net> wrote in message
news:%23CE34T9PEHA.3944@.tk2msftngp13.phx.gbl...
> I kind of assumed this but there are thousands of scenarios that are
coming
> to my mind for example if you have 150 - 350 concurrent users accessing a
> webpage at once what type of load would this put on with having 350
> connections open at one time versus 1000 - 3000 connections opening and
> closing, etc so I thought I would see what other people would have to say.
Hmm do Connections Opened with .Open not correlate to SqlConnections
because I know that
New SqlConnection("...") ;
If a .Open is not called an exception is thrown stating the
connection is not thrown.

If the Page takes 1.3 seconds to render from the time Page_Load is
called and the DbConnection is .Open()ed to the time when the page is
finished doing its thinking and .Close()ed is the Connection not "open" ?
versus opening something 10 times.

Upon further reflection, logistically speaking it seems that Opening a
DbConnection might be good for a page that renders quickly versus a page
that might take an extended period of time to complete its rendering
(especially if there is the possibility of many users hitting it at the same
time).
Unless of course they just make us .Open and .Close and throw exceptions
when they are not done so properly just for the fun of it.

"Steve C. Orr [MVP, MCSD]" <Steve@.Orr.net> wrote in message
news:O4mjGk9PEHA.1348@.TK2MSFTNGP12.phx.gbl...
> The connections aren't really opening and closing though, so from a
> performance perspective the 2 scenarios scenarios are virtually identical.
> So then it comes down to opinion about how you prefer to structure your
> code.
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://Steve.Orr.net
>
> <johndoe@.driver.net> wrote in message
> news:%23CE34T9PEHA.3944@.tk2msftngp13.phx.gbl...
>> I kind of assumed this but there are thousands of scenarios that are
> coming
>> to my mind for example if you have 150 - 350 concurrent users accessing a
>> webpage at once what type of load would this put on with having 350
>> connections open at one time versus 1000 - 3000 connections opening and
>> closing, etc so I thought I would see what other people would have to
>> say.

Performance Question with Database Connections

I was just wondering what other people's opinions and experiences were in
regards to using Database Connections throughout a website. Supposing that a
single webpage accesses a database anywhere from 5-30 times throughout a
webpage do you prefer to open the database connection upon say
initialization and reuse it elsewhere whenever possible and then dispose of
it upon disposing or do you write 5 - 10 lines of code opening and closing
it every single time you want to use the database.It doesn't really matter much because of the built in database connection
pooling.
Creating a database connection 5 times in a page has no significant
performance penalty, so do it whichever way seems most intuitive for your
web site.
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
<johndoe@.driver.net> wrote in message
news:u9lhwP8PEHA.3232@.TK2MSFTNGP11.phx.gbl...
> I was just wondering what other people's opinions and experiences were in
> regards to using Database Connections throughout a website. Supposing that
a
> single webpage accesses a database anywhere from 5-30 times throughout a
> webpage do you prefer to open the database connection upon say
> initialization and reuse it elsewhere whenever possible and then dispose
of
> it upon disposing or do you write 5 - 10 lines of code opening and closing
> it every single time you want to use the database.
>
I kind of assumed this but there are thousands of scenarios that are coming
to my mind for example if you have 150 - 350 concurrent users accessing a
webpage at once what type of load would this put on with having 350
connections open at one time versus 1000 - 3000 connections opening and
closing, etc so I thought I would see what other people would have to say.
does this
"Steve C. Orr [MVP, MCSD]" <Steve@.Orr.net> wrote in message
news:O6OStZ8PEHA.1048@.tk2msftngp13.phx.gbl...
> It doesn't really matter much because of the built in database connection
> pooling.
> Creating a database connection 5 times in a page has no significant
> performance penalty, so do it whichever way seems most intuitive for your
> web site.
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://Steve.Orr.net
>
> <johndoe@.driver.net> wrote in message
> news:u9lhwP8PEHA.3232@.TK2MSFTNGP11.phx.gbl...
> a
> of
>
The connections aren't really opening and closing though, so from a
performance perspective the 2 scenarios scenarios are virtually identical.
So then it comes down to opinion about how you prefer to structure your
code.
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
<johndoe@.driver.net> wrote in message
news:%23CE34T9PEHA.3944@.tk2msftngp13.phx.gbl...
> I kind of assumed this but there are thousands of scenarios that are
coming
> to my mind for example if you have 150 - 350 concurrent users accessing a
> webpage at once what type of load would this put on with having 350
> connections open at one time versus 1000 - 3000 connections opening and
> closing, etc so I thought I would see what other people would have to say.
Hmm do Connections Opened with .Open not correlate to SqlConnections
because I know that
New SqlConnection("...") ;
If a .Open is not called an exception is thrown stating the
connection is not thrown.
If the Page takes 1.3 seconds to render from the time Page_Load is
called and the DbConnection is .Open()ed to the time when the page is
finished doing its thinking and .Close()ed is the Connection not "open" ?
versus opening something 10 times.
Upon further reflection, logistically speaking it seems that Opening a
DbConnection might be good for a page that renders quickly versus a page
that might take an extended period of time to complete its rendering
(especially if there is the possibility of many users hitting it at the same
time).
Unless of course they just make us .Open and .Close and throw exceptions
when they are not done so properly just for the fun of it.
"Steve C. Orr [MVP, MCSD]" <Steve@.Orr.net> wrote in message
news:O4mjGk9PEHA.1348@.TK2MSFTNGP12.phx.gbl...
> The connections aren't really opening and closing though, so from a
> performance perspective the 2 scenarios scenarios are virtually identical.
> So then it comes down to opinion about how you prefer to structure your
> code.
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://Steve.Orr.net
>
> <johndoe@.driver.net> wrote in message
> news:%23CE34T9PEHA.3944@.tk2msftngp13.phx.gbl...
> coming
>

Performing MATH on database query results

Hello,

I have an page that is doing a query on a database and returning the results to the screen. The end result I need is that the page will send an email that has two columns, one for element name, and one for the calculated percentage. Essentially, I have the following...

I get a list of records that contain a timedate value and a value of trur or false in a col called 'value' These are up/down alerts for various servers(elements) I need to calculate to 4 decimal places, the amount of uptime for the reported time period and send that out via email. Can anyone help? Thanks!!!

Joe

Hi Joe,

I'm not sure we have enough information, but assuming that true is up and false is down, you could do a

SELECT COUNT(*) FROM theTable WHERE theDate between(date1 AND date2) AND Value = true

...(or 1, in some DBs). By the way, Value is probably not a good field name -- reserved words should never be used to name database objects -- but that's another issue.

Once you have a count of the true records and a count of the false records, it's easy to take those two numbers and divide the true number by the sum of the true and false checks. That's your percentage, though you can't make it accurate to 4 decimal places unless you have a big enough number (at least a hundred thousand "up" values).

permanently Removing a node from a treeview?

hi all

i have managed to remove a node from a treeview by pressing a button, however it doesnt remove it from the database and when i re-run the program the node is still there. Can anyone please help me?

Granto

During your deletion of the node, you'll need to run a delete statement against the table as well. Then refresh your datatable, dataset, or whatever you are using and rebind to your treeview. Should work fine.

Hi,

This problem which you have posted at:http://forums.asp.net/t/1191381.aspx

If the answers in above thread have helped you out, please mark this post as Resoved. If not, don't hesitate to let me know.

However, I provide some suggestions here. Before you remove the node, you should delete the datain database first. Because only remove nodes won't delete the datain database. You can follow the below codes.protected void RemoveNodeButton_Click(object sender, EventArgs e) { TreeNode nd=TreeView1.SelectedNode;if (nd.Parent ==null) {//you should find which are the children node of it. DeleteNode(nd); TreeView1.Nodes.Remove(nd);//which is parent node }else {//delete the data in database by "nd.Text", such as: delete from test where t1='"+nd.Text+"'" TreeView1.SelectedNode.Parent.ChildNodes.Remove(nd);//which is child node } }//delete the every child of the node.protected void DeleteNode(TreeNode nd) {if (nd.ChildNodes.Count == 0)// It is the deepest node. {//Delete the data of child node in database by "nd.Text", such as: delete from test where t1='"+nd.Text+"'"return; } TreeNodeCollection nds = nd.ChildNodes;foreach (TreeNode ein nds) { DeleteNode(e); }//Delete the data of parent node in database by "nd.Text", if you need to delete the data about parent node. Such as: delete from test where t1='"+nd.Text+"'"return; }

Hope this can help you.

cheers for the feedback, could you give an example with code i would need to use?

Granto

Permissions Error When Accessing Access Database

When I attempt to access a Microsoft Access database from my website, I
recieve the following error:

Server Error in '/' Application.
------------------------

Security Exception
Description: The application attempted to perform an operation not allowed
by the security policy. To grant this application the required permission
please contact your system administrator or change the application's trust
level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the
permission of type 'System.Data.OleDb.OleDbPermission, System.Data,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Take note that the description says that this can be fixed by modifying the
configuration file, which I am assuming is referring to the Web.config file.
I have successfully run this application on my machine using Visual Studio
2005's Development Server with the settings currently in the Web.config
file, so if modifying the Web.config file will fix it, what do I need to
change? Thanks.
--
Nathan Sokalski
njsokalski@dotnet.itags.org.hotmail.com
http://www.nathansokalski.com/On Jun 29, 2:50 am, "Nathan Sokalski" <njsokal...@.hotmail.comwrote:

Quote:

Originally Posted by

When I attempt to access a Microsoft Access database from my website, I
recieve the following error:
>
Server Error in '/' Application.
-----------------------*--
>
Security Exception
Description: The application attempted to perform an operation not allowed
by the security policy. To grant this application the required permission
please contact your system administrator or change the application's trust
level in the configuration file.
>
Exception Details: System.Security.SecurityException: Request for the
permission of type 'System.Data.OleDb.OleDbPermission, System.Data,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
>
Take note that the description says that this can be fixed by modifying the
configuration file, which I am assuming is referring to the Web.config file.
I have successfully run this application on my machine using Visual Studio
2005's Development Server with the settings currently in the Web.config
file, so if modifying the Web.config file will fix it, what do I need to
change? Thanks.
--
Nathan Sokalski
njsokal...@.hotmail.comhttp://www.nathansokalski.com/

Why do you use OleDb for MS Access?

http://msdn2.microsoft.com/en-us/li...connection.aspx
An application that creates an instance of the OleDbConnection object
can require all direct and indirect callers to have sufficient
permission to the code by setting declarative or imperative security
demands. OleDbConnection makes security demands using the
OleDbPermission object. Users can verify that their code has
sufficient permissions by using the OleDbPermissionAttribute object.
Users and administrators can also use the Code Access Security Policy
Tool (Caspol.exe) to modify security policy at the computer, user, and
enterprise levels. For more information, see Code Access Security and
ADO.NET.

http://msdn2.microsoft.com/en-us/library/0x4t63kb.aspx
I use OleDb for MS Access because of the namespaces that are included in the
..NET Framework (or that I have ever heard of from third parties)
System.Data.OleDb is the one that is supposed to be used when accessing a MS
Access database. The reason I am using MS Access for this website rather
than a higher quality database such as SQL Server or Oracle is because the
organization the site is for cannot afford anything else.

I have used System.Data.OleDb for MS Access databases in other sites I have
written, such as my personal website, with no problem, and any books that I
have read that use MS Access as their example database have as well, so I
know that it is the right one to use.

I have never had to deal with any of the objects you mention (except for
OleDbConnection, of course). Could you please either give me a code sample
or direct me to a site that gives one? Thanks.
--
Nathan Sokalski
njsokalski@.hotmail.com
http://www.nathansokalski.com/
"Alexey Smirnov" <alexey.smirnov@.gmail.comwrote in message
news:1183101114.868636.153110@.c77g2000hse.googlegr oups.com...
On Jun 29, 2:50 am, "Nathan Sokalski" <njsokal...@.hotmail.comwrote:

Quote:

Originally Posted by

When I attempt to access a Microsoft Access database from my website, I
recieve the following error:
>
Server Error in '/' Application.
-----------------------*--
>
Security Exception
Description: The application attempted to perform an operation not allowed
by the security policy. To grant this application the required permission
please contact your system administrator or change the application's trust
level in the configuration file.
>
Exception Details: System.Security.SecurityException: Request for the
permission of type 'System.Data.OleDb.OleDbPermission, System.Data,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
>
Take note that the description says that this can be fixed by modifying
the
configuration file, which I am assuming is referring to the Web.config
file.
I have successfully run this application on my machine using Visual Studio
2005's Development Server with the settings currently in the Web.config
file, so if modifying the Web.config file will fix it, what do I need to
change? Thanks.
--
Nathan Sokalski
njsokal...@.hotmail.comhttp://www.nathansokalski.com/

Why do you use OleDb for MS Access?

http://msdn2.microsoft.com/en-us/li...connection.aspx
An application that creates an instance of the OleDbConnection object
can require all direct and indirect callers to have sufficient
permission to the code by setting declarative or imperative security
demands. OleDbConnection makes security demands using the
OleDbPermission object. Users can verify that their code has
sufficient permissions by using the OleDbPermissionAttribute object.
Users and administrators can also use the Code Access Security Policy
Tool (Caspol.exe) to modify security policy at the computer, user, and
enterprise levels. For more information, see Code Access Security and
ADO.NET.

http://msdn2.microsoft.com/en-us/library/0x4t63kb.aspx
"Alexey Smirnov" <alexey.smirnov@.gmail.comwrote in message
news:1183101114.868636.153110@.c77g2000hse.googlegr oups.com...

Quote:

Originally Posted by

Why do you use OleDb for MS Access?

How else would you expect to use a Jet database within the .NET
Framework...?

--
http://www.markrae.net

Permissions Error When Accessing Access Database

When I attempt to access a Microsoft Access database from my website, I
recieve the following error:
Server Error in '/' Application.
----
--
Security Exception
Description: The application attempted to perform an operation not allowed
by the security policy. To grant this application the required permission
please contact your system administrator or change the application's trust
level in the configuration file.
Exception Details: System.Security.SecurityException: Request for the
permission of type 'System.Data.OleDb.OleDbPermission, System.Data,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Take note that the description says that this can be fixed by modifying the
configuration file, which I am assuming is referring to the Web.config file.
I have successfully run this application on my machine using Visual Studio
2005's Development Server with the settings currently in the Web.config
file, so if modifying the Web.config file will fix it, what do I need to
change? Thanks.
--
Nathan Sokalski
njsokalski@dotnet.itags.org.hotmail.com
http://www.nathansokalski.com/On Jun 29, 2:50 am, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> When I attempt to access a Microsoft Access database from my website, I
> recieve the following error:
> Server Error in '/' Application.
> ----=
--=AD--
> Security Exception
> Description: The application attempted to perform an operation not allowed
> by the security policy. To grant this application the required permission
> please contact your system administrator or change the application's trust
> level in the configuration file.
> Exception Details: System.Security.SecurityException: Request for the
> permission of type 'System.Data.OleDb.OleDbPermission, System.Data,
> Version=3D2.0.0.0, Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089' =
failed.
> Take note that the description says that this can be fixed by modifying t=
he
> configuration file, which I am assuming is referring to the Web.config fi=
le.
> I have successfully run this application on my machine using Visual Studio
> 2005's Development Server with the settings currently in the Web.config
> file, so if modifying the Web.config file will fix it, what do I need to
> change? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
Why do you use OleDb for MS Access?
http://msdn2.microsoft.com/en-us/li...edbconnection.=
aspx
An application that creates an instance of the OleDbConnection object
can require all direct and indirect callers to have sufficient
permission to the code by setting declarative or imperative security
demands. OleDbConnection makes security demands using the
OleDbPermission object. Users can verify that their code has
sufficient permissions by using the OleDbPermissionAttribute object.
Users and administrators can also use the Code Access Security Policy
Tool (Caspol.exe) to modify security policy at the computer, user, and
enterprise levels. For more information, see Code Access Security and
ADO.NET.
http://msdn2.microsoft.com/en-us/library/0x4t63kb.aspx
I use OleDb for MS Access because of the namespaces that are included in the
.NET Framework (or that I have ever heard of from third parties)
System.Data.OleDb is the one that is supposed to be used when accessing a MS
Access database. The reason I am using MS Access for this website rather
than a higher quality database such as SQL Server or Oracle is because the
organization the site is for cannot afford anything else.
I have used System.Data.OleDb for MS Access databases in other sites I have
written, such as my personal website, with no problem, and any books that I
have read that use MS Access as their example database have as well, so I
know that it is the right one to use.
I have never had to deal with any of the objects you mention (except for
OleDbConnection, of course). Could you please either give me a code sample
or direct me to a site that gives one? Thanks.
--
Nathan Sokalski
njsokalski@.hotmail.com
http://www.nathansokalski.com/
"Alexey Smirnov" <alexey.smirnov@.gmail.com> wrote in message
news:1183101114.868636.153110@.c77g2000hse.googlegroups.com...
On Jun 29, 2:50 am, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> When I attempt to access a Microsoft Access database from my website, I
> recieve the following error:
> Server Error in '/' Application.
> ----
-_--
> Security Exception
> Description: The application attempted to perform an operation not allowed
> by the security policy. To grant this application the required permission
> please contact your system administrator or change the application's trust
> level in the configuration file.
> Exception Details: System.Security.SecurityException: Request for the
> permission of type 'System.Data.OleDb.OleDbPermission, System.Data,
> Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
> Take note that the description says that this can be fixed by modifying
> the
> configuration file, which I am assuming is referring to the Web.config
> file.
> I have successfully run this application on my machine using Visual Studio
> 2005's Development Server with the settings currently in the Web.config
> file, so if modifying the Web.config file will fix it, what do I need to
> change? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
Why do you use OleDb for MS Access?
http://msdn2.microsoft.com/en-us/li...y/0x4t63kb.aspx
"Alexey Smirnov" <alexey.smirnov@.gmail.com> wrote in message
news:1183101114.868636.153110@.c77g2000hse.googlegroups.com...

> Why do you use OleDb for MS Access?
How else would you expect to use a Jet database within the .NET
Framework...?
http://www.markrae.net

Permissions to Sql2005 database from IIS

Hi everyone, hope your all looking forward to xmas.
I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.
My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.
I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.
I do not want to use Sql Authentication.
How should i setup permissions on my database?
At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.
Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?IM new to SQL 2005 as well, but Im working through the implementation and
Maintainence book and it expplicitly tells you not to user the Network
Service account because it has too many privilages. It reccomends setting up
a new account to manage each instance.
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1166537606.961583.187640@.a3g2000cwd.googlegroups.com...
> Hi everyone, hope your all looking forward to xmas.
> I am setting up a Sql2005 database on a Windows Server, running Windows
> Server 2003. The database is going to be accessed via users using an
> ASP.Net website.
> My website is running the normal IUSR account. And users must login to
> the website, which uses Forms Authentication.
> I do not want to give users access directly to tables, so have created
> many stored procedures which will carry out the operations that i would
> like users to be able to do.
> I do not want to use Sql Authentication.
> How should i setup permissions on my database?
> At the moment i have set up the NETWORK SERVICE account (account for
> running asp.net) on my server to be a role member of the db_owner group
> for the database. This allows the user access to all the object in my
> database, as everything is owned by dbo.
> Is this correct? This doesnt sound right to me? Do i have to setup
> another user in my Sql database?
>
Hi nemesis.
Is you database server & Web Server located on the same machine?
If yes
You could create a local (technical) user account & have the application
run under an application pool with the technical user account.
On the DB level , just create the login & user account and grant execute
on the stored procedure.
If No
instead of a local (technical) user account have a domain account made.
the rest of the setup is just the same.
As a side note , never have any application account part of db_owner ,
this means the app. could just drop all tables in your DB .
Yvesl
Nemisis wrote:
Nemisis wrote:
> Hi everyone, hope your all looking forward to xmas.
> I am setting up a Sql2005 database on a Windows Server, running Windows
> Server 2003. The database is going to be accessed via users using an
> ASP.Net website.
> My website is running the normal IUSR account. And users must login to
> the website, which uses Forms Authentication.
> I do not want to give users access directly to tables, so have created
> many stored procedures which will carry out the operations that i would
> like users to be able to do.
> I do not want to use Sql Authentication.
> How should i setup permissions on my database?
> At the moment i have set up the NETWORK SERVICE account (account for
> running asp.net) on my server to be a role member of the db_owner group
> for the database. This allows the user access to all the object in my
> database, as everything is owned by dbo.
> Is this correct? This doesnt sound right to me? Do i have to setup
> another user in my Sql database?
> Hi everyone, hope your all looking forward to xmas.
> I am setting up a Sql2005 database on a Windows Server, running Windows
> Server 2003. The database is going to be accessed via users using an
> ASP.Net website.
> My website is running the normal IUSR account. And users must login to
> the website, which uses Forms Authentication.
> I do not want to give users access directly to tables, so have created
> many stored procedures which will carry out the operations that i would
> like users to be able to do.
> I do not want to use Sql Authentication.
> How should i setup permissions on my database?
> At the moment i have set up the NETWORK SERVICE account (account for
> running asp.net) on my server to be a role member of the db_owner group
> for the database. This allows the user access to all the object in my
> database, as everything is owned by dbo.
> Is this correct? This doesnt sound right to me? Do i have to setup
> another user in my Sql database?
>
Thanks alot for this, my database server and web server are on the same
machine.
I will try to setup a account and set the correct permission first
thing in the morning.
The new account that i will setup, does it need access to the tables?
Because before when i tried to execute a stored procedure, it said that
select permissions had to be set on the table as well. Is this still
the case'
Should the new account be apart of a role in the database? Or should i
created my own?
Thanks again for your help so far.
Hi,
Assuming you're only using stored procedure your new user (lets call him
LOCALSERVER\TEST) does not need any access to the tables.
It needs EXECUTE right to the stored procedures.
here's the script to grant access.
GRANT EXECUTE ON [dbo].[AddSp] TO [LOCALSERVER\TEST]
GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TEST]
For Role Membership, well it all depends on what your requirements are.
For more informed help you should probably ask this newsgroup:
microsoft.public.sqlserver.server
however my 2c:
Since your Database will be accessed only through 1 user account( the
one that your web app. runs under) I don't think it's necessary to
create any Role.
YvesL
Nemisis wrote:
> Thanks alot for this, my database server and web server are on the same
> machine.
> I will try to setup a account and set the correct permission first
> thing in the morning.
> The new account that i will setup, does it need access to the tables?
> Because before when i tried to execute a stored procedure, it said that
> select permissions had to be set on the table as well. Is this still
> the case'
> Should the new account be apart of a role in the database? Or should i
> created my own?
> Thanks again for your help so far.
>
Yves. L. wrote:
> Hi,
> Assuming you're only using stored procedure your new user (lets call him
> LOCALSERVER\TEST) does not need any access to the tables.
> It needs EXECUTE right to the stored procedures.
> here's the script to grant access.
> GRANT EXECUTE ON [dbo].[AddSp] TO [LOCALSERVER\TEST]
> GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TEST]
>
> For Role Membership, well it all depends on what your requirements are.
> For more informed help you should probably ask this newsgroup:
> microsoft.public.sqlserver.server
> however my 2=80c:
> Since your Database will be accessed only through 1 user account( the
> one that your web app. runs under) I don't think it's necessary to
> create any Role.
> YvesL
Yves,
What about if i need to run dynamic Sql? Will this still be ok? I
hear that there are some problems when running dynamic Sql?
>
> Yves,
> What about if i need to run dynamic Sql? Will this still be ok? I
> hear that there are some problems when running dynamic Sql?
>
I suppose that you mean SQL statements in code ?
Then you'll need to set the proper permission on the necessary tables,
views, etc,... or make your user part of the db_datawriter or
db_datareader roles (see the docs for reference).
Anyway, you wrote earlier that you have SP'for all data access, so you
should be fine .
Regards,
YvesL
Yes the users only execute stored procedures, but within some of the
stored procedures, i execute dynamic Sql, using the execute_sql system
stored procedure.
I was told that it was not a good idea to make a user apart of the
db_owner role, is making them a part of the db_datareader and
db_datawriter role ok?
I am using Sql2005, so does this still apply? I didnt know if i should
do something different for Sql2005?
Thanks
> Yes the users only execute stored procedures, but within some of the
> stored procedures, i execute dynamic Sql, using the execute_sql system
> stored procedure.
I don't know about that, you'll have to try it out or ask the sqlserver
newsgroup.

> I was told that it was not a good idea to make a user apart of the
> db_owner role, is making them a part of the db_datareader and
> db_datawriter role ok?
Have a look at
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/udb9/html/7f3fa5f6-6b50-
43bb-9047-1544ade55e39.htm
it describes what access users get for those database roles
Regards,
YvesL

Permissions to Sql2005 database from IIS

Hi everyone, hope your all looking forward to xmas.

I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.

My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.

I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.

I do not want to use Sql Authentication.

How should i setup permissions on my database?

At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.

Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?IM new to SQL 2005 as well, but Im working through the implementation and
Maintainence book and it expplicitly tells you not to user the Network
Service account because it has too many privilages. It reccomends setting up
a new account to manage each instance.

"Nemisis" <darrens2005@.hotmail.comwrote in message
news:1166537606.961583.187640@.a3g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

Hi everyone, hope your all looking forward to xmas.
>
I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.
>
My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.
>
I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.
>
I do not want to use Sql Authentication.
>
How should i setup permissions on my database?
>
At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.
>
Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?
>

Hi nemesis.

Is you database server & Web Server located on the same machine?

If yes
You could create a local (technical) user account & have the application
run under an application pool with the technical user account.
On the DB level , just create the login & user account and grant execute
on the stored procedure.

If No
instead of a local (technical) user account have a domain account made.
the rest of the setup is just the same.

As a side note , never have any application account part of db_owner ,
this means the app. could just drop all tables in your DB .

Yvesl

Nemisis wrote:

Nemisis wrote:

Quote:

Originally Posted by

Hi everyone, hope your all looking forward to xmas.
>
I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.
>
My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.
>
I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.
>
I do not want to use Sql Authentication.
>
How should i setup permissions on my database?
>
At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.
>
Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?
>
Hi everyone, hope your all looking forward to xmas.
>
I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.
>
My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.
>
I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.
>
I do not want to use Sql Authentication.
>
How should i setup permissions on my database?
>
At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.
>
Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?
>

Thanks alot for this, my database server and web server are on the same
machine.

I will try to setup a account and set the correct permission first
thing in the morning.

The new account that i will setup, does it need access to the tables?
Because before when i tried to execute a stored procedure, it said that
select permissions had to be set on the table as well. Is this still
the case??

Should the new account be apart of a role in the database? Or should i
created my own?

Thanks again for your help so far.
Hi,
Assuming you're only using stored procedure your new user (lets call him
LOCALSERVER\TEST) does not need any access to the tables.
It needs EXECUTE right to the stored procedures.

here's the script to grant access.
GRANT EXECUTE ON [dbo].[AddSp] TO [LOCALSERVER\TEST]
GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TEST]

For Role Membership, well it all depends on what your requirements are.
For more informed help you should probably ask this newsgroup:
microsoft.public.sqlserver.server

however my 2c:
Since your Database will be accessed only through 1 user account( the
one that your web app. runs under) I don't think it's necessary to
create any Role.

YvesL

Nemisis wrote:

Quote:

Originally Posted by

Thanks alot for this, my database server and web server are on the same
machine.
>
I will try to setup a account and set the correct permission first
thing in the morning.
>
The new account that i will setup, does it need access to the tables?
Because before when i tried to execute a stored procedure, it said that
select permissions had to be set on the table as well. Is this still
the case??
>
Should the new account be apart of a role in the database? Or should i
created my own?
>
Thanks again for your help so far.
>

Yves. L. wrote:

Quote:

Originally Posted by

Hi,
Assuming you're only using stored procedure your new user (lets call him
LOCALSERVER\TEST) does not need any access to the tables.
It needs EXECUTE right to the stored procedures.
>
here's the script to grant access.
GRANT EXECUTE ON [dbo].[AddSp] TO [LOCALSERVER\TEST]
GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TEST]
>
>
>
For Role Membership, well it all depends on what your requirements are.
For more informed help you should probably ask this newsgroup:
microsoft.public.sqlserver.server
>
however my 2c:
Since your Database will be accessed only through 1 user account( the
one that your web app. runs under) I don't think it's necessary to
create any Role.
>
YvesL

Yves,

What about if i need to run dynamic Sql? Will this still be ok? I
hear that there are some problems when running dynamic Sql?
>

Quote:

Originally Posted by

Yves,
>
What about if i need to run dynamic Sql? Will this still be ok? I
hear that there are some problems when running dynamic Sql?
>

I suppose that you mean SQL statements in code ?
Then you'll need to set the proper permission on the necessary tables,
views, etc,... or make your user part of the db_datawriter or
db_datareader roles (see the docs for reference).

Anyway, you wrote earlier that you have SP'for all data access, so you
should be fine .

--
Regards,
YvesL
Yes the users only execute stored procedures, but within some of the
stored procedures, i execute dynamic Sql, using the execute_sql system
stored procedure.

I was told that it was not a good idea to make a user apart of the
db_owner role, is making them a part of the db_datareader and
db_datawriter role ok?

I am using Sql2005, so does this still apply? I didnt know if i should
do something different for Sql2005?

Thanks
Yes the users only execute stored procedures, but within some of the

Quote:

Originally Posted by

stored procedures, i execute dynamic Sql, using the execute_sql system
stored procedure.

I don't know about that, you'll have to try it out or ask the sqlserver
newsgroup.

Quote:

Originally Posted by

>
I was told that it was not a good idea to make a user apart of the
db_owner role, is making them a part of the db_datareader and
db_datawriter role ok?

Have a look at
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/udb9/html/7f3fa5f6-6b50-43bb-9047-1544ade55e39.htm

it describes what access users get for those database roles

--
Regards,
YvesL
Hope u all had a merry xmas, and happy new year too you all.

I have followed the steps in the article
http://www.sommarskog.se/grantperm.html.

1. Create a certificate
2. Create a user associated with that certificate
3. Grant that user SELECT rights on the table
4. Sign the procedure with the certificate EACH TIME you have changed
the procedure

Encase you dont know, i have had to create this certificate as i am
executing dynamic Sql within some of my stored procedures, and i dont
want to give the user any permissions to the tables.

I am a lil confused, as i have to create a user (above) to associate
with the certificate, and i have to give that user permissions to the
table directly. Would it not have been easier just to give the main
user (web application IIS user, NETWORK SERVICE) direct permissions to
the tables, instead of setting another user up?

Could someone please clarify why this is the case and the benefits of
it, i have to also inform other people why we are going to set the
application up like this.

Thanks