Help! Site crashing on data access when busy!

Clearly, my code isn't written as well as it should be. I don't understand enough about data access and could use some help.

I have several database tables, but one primary table that is the most accessed. Generally, I need to build a list from the data based on some filter. I'm using a repeater control, since all I need to display per record is a name, maybe a city or birthday, and possibly a little graphic, and my customer doesn't want a grid type of display. The filter is determined by the page requested. The exception is a search page where the user builds the filter and a grid is used to display the results.

The results always contain a link to a page that has more detail on the selected record.

What is the best way to handle this? I'm still trying to get a handle on different ways to get data and I'm not doing much with caching. Would it make sense to keep the data in memory from the page that displays the list (or search page) to the detail page? What if the detail page is accessed directly, say from a bookmark? How do I cache this?

I'm currently using strongly typed datasets.

Below is an example of what I'm doing - this is from the code behind of one of the list pages - members with birthdays this month.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim theMonth As String = DateTime.Now.ToString("MMMM")
Me.LabelMonth.Text = theMonth

Dim MemberAdapter As New WAPTableAdapters.membersTableAdapter
Repeater1.DataSource = MemberAdapter.GetBirthday("Female", DatePart("m", Today))
Repeater1.DataBind()
Repeater2.DataSource = MemberAdapter.GetBirthday("Male", DatePart("m", Today))
Repeater2.DataBind()
End Sub

Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)
Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow)
If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then
LabelIcon.Visible = True
End If
End If
End Sub

Protected Sub Repeater2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater2.ItemDataBound
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)
Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow)
If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then
LabelIcon.Visible = True
End If
End If
End Sub

It seems pretty simple and straightforward to me, but these pages shouldn't be crashing when the site gets busy, so I have to be doing something wrong.

Diane

I would definitely cache the datasets. It takes so much load off.

|||

How do I do that?

Diane

|||

I would probably put the repeaters into a user control and use partial page caching for it. Say update once per minute should help reduce your server load, and it's really easy to implement (And since you've moved it to a user control, you can reuse it on a different page if necessary). There are obviously more efficient caching mechanisms to gain even more efficiency, but I would start with that and see if it helps since it's so easy to implement.

http://asp.net/learn/videos/video-41.aspx

|||

If Cache("malebirthday") is nothing then

Dim ds as dataset = MemberAdapter.GetBirthday("Male", DatePart("m", Today))

Repeater2.DataSource = ds

Cache.Insert("malebirthday", ds, Nothing, now.addminutes(5), timespan.zero)

else

Repeater2.DataSource = Ctype(Cache("malebirthday"), dataset)

End if

Repeater2.DataBind()

|||

Thank you! That helps tremendously.

Diane

sql

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegroups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegroups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
---
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
---
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegroups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> ---
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> ---
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>|||Thanks Tibor! That was helpful.

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?Separate presentation from data. SQL Server returns data values and the valu
e 0 is the same as the
value 0.00. The client application presents the binary values returned by SQ
L Server in a
human-readable form (like number with decimal points). If you didn't write t
he client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Serv
ices, etc, then these
tools will present the values according to the datatype *for the column'. I.
e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the
person writing the too
who makes these decision, but it applies to all values for the column. The o
nly way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to
some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to ha
ndle this in the client
app. All client app designed for end users has functionality for formatting
data (like for instance
a format() function).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegroups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?
Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegro ups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>
|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.
|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.
|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegr oups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>
|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.
|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegr oups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>
|||Thanks Tibor! That was helpful.