Please Help me Optimize my Code

T

TF

Hello all,

I made a ASP.NET 2.0 site that shows possible "recipes" for paint
colors stored in an access dbase. Basically, 1000 colors are stored
with specific RGB values in separate columns. A user sees all the
colors listed on the page with hyperlinks that open the "mixes" page.
The mixes page goes through each record, compares it with all other
records in a ratio up to "maxratio". If it finds a ratio that matches
the redvalue of the chosen color, it checks the green and blue as
well. If it's a close match, within "maxdiff" tolerance, it writes
that combo to the page. The code works on my development p4 laptop,
1gb ram, running xppro and developing with VWDEx05. The problem is
that it takes 2.5 minutes to run it. The resulting page isn't huge,
it just takes a long time to get there. This is going on a free host
so I can't change any server settings. I used stringbuilder and put
andalso where I thought it would help but now I don't know what to
do. Please, any help you can give to streamline this will be greatly
appreciated. And of course if it's a hopeless effort, I'd like to
hear any alternatives to get the same sort of results. My code is
below. Thanks very much for any advice you can give.

TF

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

'coming from PM page w/querystring vals - make sure the're
what is expected.
'if not, bail out of the page load sub
'should be 4 values: r,g,b,id. r,g,b must be between (not
equal) 0 and 256.
If Request.QueryString.Count <> 4 _
Or Request.QueryString("red") < 0 Or
Request.QueryString("red") > 255 _
Or Request.QueryString("green") < 0 Or
Request.QueryString("green") > 255 _
Or Request.QueryString("blue") < 0 Or
Request.QueryString("blue") > 255 Then

'bail from the page load sub
Exit Sub

Else

'dim all variables first
Dim startRed, testRed, compRed1, compRed2 As Integer
Dim compFactor1, compFactor2 As Integer
Dim startGreen, testGreen, compGreen1, compGreen2 As
Integer
Dim startBlue, testBlue, compBlue1, compBlue2 As Integer
Dim maxRatio, maxDiff As Integer
Dim Rows1, Rows2 As Integer
Dim startID As Integer

Dim usedIDStr As String = "xxxxxxxxxxx"
Dim testIDStr1 As String = "" 'id pairs
Dim testIDStr2 As String = "" 'id pairs reversed

'create the stringbuilder objects
Dim testID1 As New Text.StringBuilder(15)
Dim testID2 As New Text.StringBuilder(15)
Dim usedIDs As New Text.StringBuilder(500)

'starting r,g,b,id; get from querystring
startRed = Request.QueryString("red")
startGreen = Request.QueryString("green")
startBlue = Request.QueryString("blue")
startID = Request.QueryString("id")

'max ratio of comparison. too high and this REALLY crawls
maxRatio = 8

'max diff from start color for display: tolerance window
maxDiff = 3

'the database connction stuff
Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.
4.0; Data Source=C:\Inetpub\WebSite1\App_Data\PaintsDbase.mdb"
Dim myConn As Data.OleDb.OleDbConnection = New
Data.OleDb.OleDbConnection(myConnString)
Dim mySQLText As String = "SELECT [id],[brand],[redvalue],
[greenvalue],[bluevalue],[thinnedby] FROM [Allpaints] "
Dim myCmd1 As Data.OleDb.OleDbCommand = New
Data.OleDb.OleDbCommand(mySQLText, myConn)
myConn.Open()

'QUERIED COLUMN ORDNALS
'0 - ID; 1 - BRAND; 2 - REDVALUE; 3 - GREENVALUE; 4 -
BLUEVALUE; 5 - THINNER

'dim myReader1 for the outside loop; forward moving only
Dim myReader1 As Data.OleDb.OleDbDataReader =
myCmd1.ExecuteReader()

'dim and fill myDataSet2 for the inside loop; need to go
both directions
Dim dataAdapter As New
System.Data.OleDb.OleDbDataAdapter(mySQLText, myConn)
Dim myDataSet2 As System.Data.DataSet = New
System.Data.DataSet
Dim myDS2Row As System.Data.DataRow
dataAdapter.Fill(myDataSet2)

'start first/outside reader loop
While myReader1.Read()

'this loops through each record
For Rows1 = 0 To myReader1.FieldCount - 1

'get comp color values for the current OUTSIDE
loop record
compRed1 =
myReader1.Item(myReader1.GetOrdinal("redvalue"))
compGreen1 =
myReader1.Item(myReader1.GetOrdinal("greenvalue"))
compBlue1 =
myReader1.Item(myReader1.GetOrdinal("bluevalue"))

'the actual inside looper; goes through each
myDataSet2 record
For Each myDS2Row In myDataSet2.Tables(0).Rows

'get comp color values for the current INSIDE
loop
compRed2 = myDS2Row.Item(2)
compGreen2 = myDS2Row.Item(3)
compBlue2 = myDS2Row.Item(4)

'first/outside ratio loop
For compFactor1 = 1 To maxRatio

'second/inside ratio loop
For compFactor2 = 1 To maxRatio

'the math to get testRed, testGreen,
testBlue based on current ratios
testRed = ((compRed1 * compFactor1) +
(compRed2 * compFactor2)) / (compFactor1 + compFactor2)
testGreen = ((compGreen1 *
compFactor1) + (compGreen2 * compFactor2)) / (compFactor1 +
compFactor2)
testBlue = ((compBlue1 * compFactor1)
+ (compBlue2 * compFactor2)) / (compFactor1 + compFactor2)


If (testRed < startRed + maxDiff And
testRed > startRed - maxDiff) _
AndAlso (testGreen < startGreen +
maxDiff And testGreen > startGreen - maxDiff) _
AndAlso (testBlue < startBlue +
maxDiff And testBlue > startBlue - maxDiff) Then

'use stringbuilder to create fwd/
rev ID pairs to test against used pairs
testID1.Remove(0,
testID1.Length())
testID1.Append("::" &
myReader1.Item(myReader1.GetOrdinal("id")) & ":" & myDS2Row.Item(0) &
"::")
testID2.Remove(0,
testID2.Length())
testID2.Append("::" &
myDS2Row.Item(0) & ":" & myReader1.Item(myReader1.GetOrdinal("id")) &
"::")

'the inner/outer brand should be
the same and block out already displayed combos
'at least for now;later we can
maybe turn on mixed brand recipes
If
myReader1.Item(myReader1.GetOrdinal("brand")) = myDS2Row.Item(1) _
AndAlso
myReader1.Item(myReader1.GetOrdinal("id")) <> startID _
AndAlso myDS2Row.Item(0) <>
startID _
AndAlso
usedIDs.ToString().IndexOf(testID1.ToString()) = -1 _
AndAlso
usedIDs.ToString().IndexOf(testID2.ToString()) = -1 Then


usedIDs.Append(testID1.ToString())

'the ligter/darker flag
If testRed > startRed And
testGreen > startGreen And testBlue > startBlue Then
Response.Write("Slightly
Lighter than chosen color<BR>")
ElseIf testRed < startRed And
testGreen < startGreen And testBlue < startBlue Then
Response.Write("Slightly
Darker than chosen color<BR>")
End If

Response.Write("testred
= " & testRed & "<br>")
Response.Write("testgreen
= " & testGreen & "<br>")
Response.Write("testblue
= " & testBlue & "<br>")

Response.Write(myReader1.Item(1) & "<br>")
Response.Write("compfact1
= " & compFactor1 & "<br>")
Response.Write("compred1
= " & compRed1 & "<br>")
Response.Write("compgreen1
= " & compGreen1 & "<br>")
Response.Write("compblue1
= " & compBlue1 & "<br>")

Response.Write(myDS2Row.Item(1) & "<br>")
Response.Write("compfact2
= " & compFactor2 & "<br>")
Response.Write("compred2
= " & compRed2 & "<br>")
Response.Write("compgreen2
= " & compGreen2 & "<br>")
Response.Write("compblue2
= " & compBlue2 & "<br><br><br>")
End If

End If

Next compFactor2

Next compFactor1

Next myDS2Row

Next Rows1

End While

End If

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="font-size: x-small; font-family: Verdana">
<form id="form1" runat="server">
<div>
Mixer Results<br />
<br />

</div>
</form>
</body>
</html>
 
G

Guest

Aside from the fact that you are using MS Access, I don't see anywhere in
your sample code where you are closing your connections or DataReaders. If
you get all your data into memory (dataSets, not readers) you will only need
to get it once and then you can do all your manipulations without open
connections.
If that helps, then you can optimize more by using databinding to controls
rather than a series of Response.Write's .
Peter

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




TF said:
Hello all,

I made a ASP.NET 2.0 site that shows possible "recipes" for paint
colors stored in an access dbase. Basically, 1000 colors are stored
with specific RGB values in separate columns. A user sees all the
colors listed on the page with hyperlinks that open the "mixes" page.
The mixes page goes through each record, compares it with all other
records in a ratio up to "maxratio". If it finds a ratio that matches
the redvalue of the chosen color, it checks the green and blue as
well. If it's a close match, within "maxdiff" tolerance, it writes
that combo to the page. The code works on my development p4 laptop,
1gb ram, running xppro and developing with VWDEx05. The problem is
that it takes 2.5 minutes to run it. The resulting page isn't huge,
it just takes a long time to get there. This is going on a free host
so I can't change any server settings. I used stringbuilder and put
andalso where I thought it would help but now I don't know what to
do. Please, any help you can give to streamline this will be greatly
appreciated. And of course if it's a hopeless effort, I'd like to
hear any alternatives to get the same sort of results. My code is
below. Thanks very much for any advice you can give.

TF

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

'coming from PM page w/querystring vals - make sure the're
what is expected.
'if not, bail out of the page load sub
'should be 4 values: r,g,b,id. r,g,b must be between (not
equal) 0 and 256.
If Request.QueryString.Count <> 4 _
Or Request.QueryString("red") < 0 Or
Request.QueryString("red") > 255 _
Or Request.QueryString("green") < 0 Or
Request.QueryString("green") > 255 _
Or Request.QueryString("blue") < 0 Or
Request.QueryString("blue") > 255 Then

'bail from the page load sub
Exit Sub

Else

'dim all variables first
Dim startRed, testRed, compRed1, compRed2 As Integer
Dim compFactor1, compFactor2 As Integer
Dim startGreen, testGreen, compGreen1, compGreen2 As
Integer
Dim startBlue, testBlue, compBlue1, compBlue2 As Integer
Dim maxRatio, maxDiff As Integer
Dim Rows1, Rows2 As Integer
Dim startID As Integer

Dim usedIDStr As String = "xxxxxxxxxxx"
Dim testIDStr1 As String = "" 'id pairs
Dim testIDStr2 As String = "" 'id pairs reversed

'create the stringbuilder objects
Dim testID1 As New Text.StringBuilder(15)
Dim testID2 As New Text.StringBuilder(15)
Dim usedIDs As New Text.StringBuilder(500)

'starting r,g,b,id; get from querystring
startRed = Request.QueryString("red")
startGreen = Request.QueryString("green")
startBlue = Request.QueryString("blue")
startID = Request.QueryString("id")

'max ratio of comparison. too high and this REALLY crawls
maxRatio = 8

'max diff from start color for display: tolerance window
maxDiff = 3

'the database connction stuff
Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.
4.0; Data Source=C:\Inetpub\WebSite1\App_Data\PaintsDbase.mdb"
Dim myConn As Data.OleDb.OleDbConnection = New
Data.OleDb.OleDbConnection(myConnString)
Dim mySQLText As String = "SELECT [id],[brand],[redvalue],
[greenvalue],[bluevalue],[thinnedby] FROM [Allpaints] "
Dim myCmd1 As Data.OleDb.OleDbCommand = New
Data.OleDb.OleDbCommand(mySQLText, myConn)
myConn.Open()

'QUERIED COLUMN ORDNALS
'0 - ID; 1 - BRAND; 2 - REDVALUE; 3 - GREENVALUE; 4 -
BLUEVALUE; 5 - THINNER

'dim myReader1 for the outside loop; forward moving only
Dim myReader1 As Data.OleDb.OleDbDataReader =
myCmd1.ExecuteReader()

'dim and fill myDataSet2 for the inside loop; need to go
both directions
Dim dataAdapter As New
System.Data.OleDb.OleDbDataAdapter(mySQLText, myConn)
Dim myDataSet2 As System.Data.DataSet = New
System.Data.DataSet
Dim myDS2Row As System.Data.DataRow
dataAdapter.Fill(myDataSet2)

'start first/outside reader loop
While myReader1.Read()

'this loops through each record
For Rows1 = 0 To myReader1.FieldCount - 1

'get comp color values for the current OUTSIDE
loop record
compRed1 =
myReader1.Item(myReader1.GetOrdinal("redvalue"))
compGreen1 =
myReader1.Item(myReader1.GetOrdinal("greenvalue"))
compBlue1 =
myReader1.Item(myReader1.GetOrdinal("bluevalue"))

'the actual inside looper; goes through each
myDataSet2 record
For Each myDS2Row In myDataSet2.Tables(0).Rows

'get comp color values for the current INSIDE
loop
compRed2 = myDS2Row.Item(2)
compGreen2 = myDS2Row.Item(3)
compBlue2 = myDS2Row.Item(4)

'first/outside ratio loop
For compFactor1 = 1 To maxRatio

'second/inside ratio loop
For compFactor2 = 1 To maxRatio

'the math to get testRed, testGreen,
testBlue based on current ratios
testRed = ((compRed1 * compFactor1) +
(compRed2 * compFactor2)) / (compFactor1 + compFactor2)
testGreen = ((compGreen1 *
compFactor1) + (compGreen2 * compFactor2)) / (compFactor1 +
compFactor2)
testBlue = ((compBlue1 * compFactor1)
+ (compBlue2 * compFactor2)) / (compFactor1 + compFactor2)


If (testRed < startRed + maxDiff And
testRed > startRed - maxDiff) _
AndAlso (testGreen < startGreen +
maxDiff And testGreen > startGreen - maxDiff) _
AndAlso (testBlue < startBlue +
maxDiff And testBlue > startBlue - maxDiff) Then

'use stringbuilder to create fwd/
rev ID pairs to test against used pairs
testID1.Remove(0,
testID1.Length())
testID1.Append("::" &
myReader1.Item(myReader1.GetOrdinal("id")) & ":" & myDS2Row.Item(0) &
"::")
testID2.Remove(0,
testID2.Length())
testID2.Append("::" &
myDS2Row.Item(0) & ":" & myReader1.Item(myReader1.GetOrdinal("id")) &
"::")

'the inner/outer brand should be
the same and block out already displayed combos
'at least for now;later we can
maybe turn on mixed brand recipes
If
myReader1.Item(myReader1.GetOrdinal("brand")) = myDS2Row.Item(1) _
AndAlso
myReader1.Item(myReader1.GetOrdinal("id")) <> startID _
AndAlso myDS2Row.Item(0) <>
startID _
AndAlso
usedIDs.ToString().IndexOf(testID1.ToString()) = -1 _
AndAlso
usedIDs.ToString().IndexOf(testID2.ToString()) = -1 Then


usedIDs.Append(testID1.ToString())

'the ligter/darker flag
If testRed > startRed And
testGreen > startGreen And testBlue > startBlue Then
Response.Write("Slightly
Lighter than chosen color<BR>")
ElseIf testRed < startRed And
testGreen < startGreen And testBlue < startBlue Then
Response.Write("Slightly
Darker than chosen color<BR>")
End If

Response.Write("testred
= " & testRed & "<br>")
Response.Write("testgreen
= " & testGreen & "<br>")
Response.Write("testblue
= " & testBlue & "<br>")

Response.Write(myReader1.Item(1) & "<br>")
Response.Write("compfact1
= " & compFactor1 & "<br>")
Response.Write("compred1
= " & compRed1 & "<br>")
Response.Write("compgreen1
= " & compGreen1 & "<br>")
Response.Write("compblue1
= " & compBlue1 & "<br>")

Response.Write(myDS2Row.Item(1) & "<br>")
Response.Write("compfact2
= " & compFactor2 & "<br>")
Response.Write("compred2
= " & compRed2 & "<br>")
Response.Write("compgreen2
= " & compGreen2 & "<br>")
Response.Write("compblue2
= " & compBlue2 & "<br><br><br>")
End If

End If

Next compFactor2

Next compFactor1

Next myDS2Row

Next Rows1

End While

End If

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="font-size: x-small; font-family: Verdana">
<form id="form1" runat="server">
<div>
Mixer Results<br />
<br />

</div>
</form>
</body>
</html>
Click to expand...
 
G

Guest

TF, you have just a HUGE number of iterations happenning on the page - and
some of them are unnecessary. A few suggestions:

- create a vew on the datatable from the dataset
Dim myView As DataView = myDS2Row.Tables(0).DefaultView

and then on every reader row assign a filter to it:

myView.RowFilter = "brand='" & myReader1("brand").ToString & "' and id<>" &
myReader1("id").ToString

then instead of going though the whole table loop through the view rows

- instead of StringBuilders to keep IDs I would suggest using generic lists
of Integer:
Dim usedIDs As List(Of Integer) = New List(Of Integer)

then you could easily test whether an ID is already there (Contains method)
and add new ID (Add method)

Try that for a start, I may try to come up with a few more suggestions if I
have some more time...


TF said:
Hello all,

I made a ASP.NET 2.0 site that shows possible "recipes" for paint
colors stored in an access dbase. Basically, 1000 colors are stored
with specific RGB values in separate columns. A user sees all the
colors listed on the page with hyperlinks that open the "mixes" page.
The mixes page goes through each record, compares it with all other
records in a ratio up to "maxratio". If it finds a ratio that matches
the redvalue of the chosen color, it checks the green and blue as
well. If it's a close match, within "maxdiff" tolerance, it writes
that combo to the page. The code works on my development p4 laptop,
1gb ram, running xppro and developing with VWDEx05. The problem is
that it takes 2.5 minutes to run it. The resulting page isn't huge,
it just takes a long time to get there. This is going on a free host
so I can't change any server settings. I used stringbuilder and put
andalso where I thought it would help but now I don't know what to
do. Please, any help you can give to streamline this will be greatly
appreciated. And of course if it's a hopeless effort, I'd like to
hear any alternatives to get the same sort of results. My code is
below. Thanks very much for any advice you can give.

TF

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

'coming from PM page w/querystring vals - make sure the're
what is expected.
'if not, bail out of the page load sub
'should be 4 values: r,g,b,id. r,g,b must be between (not
equal) 0 and 256.
If Request.QueryString.Count <> 4 _
Or Request.QueryString("red") < 0 Or
Request.QueryString("red") > 255 _
Or Request.QueryString("green") < 0 Or
Request.QueryString("green") > 255 _
Or Request.QueryString("blue") < 0 Or
Request.QueryString("blue") > 255 Then

'bail from the page load sub
Exit Sub

Else

'dim all variables first
Dim startRed, testRed, compRed1, compRed2 As Integer
Dim compFactor1, compFactor2 As Integer
Dim startGreen, testGreen, compGreen1, compGreen2 As
Integer
Dim startBlue, testBlue, compBlue1, compBlue2 As Integer
Dim maxRatio, maxDiff As Integer
Dim Rows1, Rows2 As Integer
Dim startID As Integer

Dim usedIDStr As String = "xxxxxxxxxxx"
Dim testIDStr1 As String = "" 'id pairs
Dim testIDStr2 As String = "" 'id pairs reversed

'create the stringbuilder objects
Dim testID1 As New Text.StringBuilder(15)
Dim testID2 As New Text.StringBuilder(15)
Dim usedIDs As New Text.StringBuilder(500)

'starting r,g,b,id; get from querystring
startRed = Request.QueryString("red")
startGreen = Request.QueryString("green")
startBlue = Request.QueryString("blue")
startID = Request.QueryString("id")

'max ratio of comparison. too high and this REALLY crawls
maxRatio = 8

'max diff from start color for display: tolerance window
maxDiff = 3

'the database connction stuff
Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.
4.0; Data Source=C:\Inetpub\WebSite1\App_Data\PaintsDbase.mdb"
Dim myConn As Data.OleDb.OleDbConnection = New
Data.OleDb.OleDbConnection(myConnString)
Dim mySQLText As String = "SELECT [id],[brand],[redvalue],
[greenvalue],[bluevalue],[thinnedby] FROM [Allpaints] "
Dim myCmd1 As Data.OleDb.OleDbCommand = New
Data.OleDb.OleDbCommand(mySQLText, myConn)
myConn.Open()

'QUERIED COLUMN ORDNALS
'0 - ID; 1 - BRAND; 2 - REDVALUE; 3 - GREENVALUE; 4 -
BLUEVALUE; 5 - THINNER

'dim myReader1 for the outside loop; forward moving only
Dim myReader1 As Data.OleDb.OleDbDataReader =
myCmd1.ExecuteReader()

'dim and fill myDataSet2 for the inside loop; need to go
both directions
Dim dataAdapter As New
System.Data.OleDb.OleDbDataAdapter(mySQLText, myConn)
Dim myDataSet2 As System.Data.DataSet = New
System.Data.DataSet
Dim myDS2Row As System.Data.DataRow
dataAdapter.Fill(myDataSet2)

'start first/outside reader loop
While myReader1.Read()

'this loops through each record
For Rows1 = 0 To myReader1.FieldCount - 1

'get comp color values for the current OUTSIDE
loop record
compRed1 =
myReader1.Item(myReader1.GetOrdinal("redvalue"))
compGreen1 =
myReader1.Item(myReader1.GetOrdinal("greenvalue"))
compBlue1 =
myReader1.Item(myReader1.GetOrdinal("bluevalue"))

'the actual inside looper; goes through each
myDataSet2 record
For Each myDS2Row In myDataSet2.Tables(0).Rows

'get comp color values for the current INSIDE
loop
compRed2 = myDS2Row.Item(2)
compGreen2 = myDS2Row.Item(3)
compBlue2 = myDS2Row.Item(4)

'first/outside ratio loop
For compFactor1 = 1 To maxRatio

'second/inside ratio loop
For compFactor2 = 1 To maxRatio

'the math to get testRed, testGreen,
testBlue based on current ratios
testRed = ((compRed1 * compFactor1) +
(compRed2 * compFactor2)) / (compFactor1 + compFactor2)
testGreen = ((compGreen1 *
compFactor1) + (compGreen2 * compFactor2)) / (compFactor1 +
compFactor2)
testBlue = ((compBlue1 * compFactor1)
+ (compBlue2 * compFactor2)) / (compFactor1 + compFactor2)


If (testRed < startRed + maxDiff And
testRed > startRed - maxDiff) _
AndAlso (testGreen < startGreen +
maxDiff And testGreen > startGreen - maxDiff) _
AndAlso (testBlue < startBlue +
maxDiff And testBlue > startBlue - maxDiff) Then

'use stringbuilder to create fwd/
rev ID pairs to test against used pairs
testID1.Remove(0,
testID1.Length())
testID1.Append("::" &
myReader1.Item(myReader1.GetOrdinal("id")) & ":" & myDS2Row.Item(0) &
"::")
testID2.Remove(0,
testID2.Length())
testID2.Append("::" &
myDS2Row.Item(0) & ":" & myReader1.Item(myReader1.GetOrdinal("id")) &
"::")

'the inner/outer brand should be
the same and block out already displayed combos
'at least for now;later we can
maybe turn on mixed brand recipes
If
myReader1.Item(myReader1.GetOrdinal("brand")) = myDS2Row.Item(1) _
AndAlso
myReader1.Item(myReader1.GetOrdinal("id")) <> startID _
AndAlso myDS2Row.Item(0) <>
startID _
AndAlso
usedIDs.ToString().IndexOf(testID1.ToString()) = -1 _
AndAlso
usedIDs.ToString().IndexOf(testID2.ToString()) = -1 Then


usedIDs.Append(testID1.ToString())

'the ligter/darker flag
If testRed > startRed And
testGreen > startGreen And testBlue > startBlue Then
Response.Write("Slightly
Lighter than chosen color<BR>")
ElseIf testRed < startRed And
testGreen < startGreen And testBlue < startBlue Then
Response.Write("Slightly
Darker than chosen color<BR>")
End If

Response.Write("testred
= " & testRed & "<br>")
Response.Write("testgreen
= " & testGreen & "<br>")
Response.Write("testblue
= " & testBlue & "<br>")

Response.Write(myReader1.Item(1) & "<br>")
Response.Write("compfact1
= " & compFactor1 & "<br>")
Response.Write("compred1
= " & compRed1 & "<br>")
Response.Write("compgreen1
= " & compGreen1 & "<br>")
Response.Write("compblue1
= " & compBlue1 & "<br>")

Response.Write(myDS2Row.Item(1) & "<br>")
Response.Write("compfact2
= " & compFactor2 & "<br>")
Response.Write("compred2
= " & compRed2 & "<br>")
Response.Write("compgreen2
= " & compGreen2 & "<br>")
Response.Write("compblue2
= " & compBlue2 & "<br><br><br>")
End If

End If

Next compFactor2

Next compFactor1

Next myDS2Row

Next Rows1

End While

End If

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="font-size: x-small; font-family: Verdana">
<form id="form1" runat="server">
<div>
Mixer Results<br />
<br />

</div>
</form>
</body>
</html>
Click to expand...
 
T

TF

Aside from the fact that you are using MS Access, I don't see anywhere in
your sample code where you are closing your connections or DataReaders. If
you get all your data into memory (dataSets, not readers) you will only need
to get it once and then you can do all your manipulations without open
connections.
If that helps, then you can optimize more by using databinding to controls
rather than a series of Response.Write's .
Peter

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


...

read more »
Click to expand...

Thanks. I originally had datasets for both main loops and it was even
slower. I learned about datareaders being faster so I tried that and
it was faster but I had to keep the dataset for the inner loop because
otherwise I have to recreate the datareader with each loop (forward
moving only) and it took over 6 min. Are you saying there is a way to
"clone" the table in ram and then close the db connections and work
off the table in ram? I should tell you the result page is completely
static. No user inputs, just a report page really. Also, I'm no
programmer. The learning curve needs to be...well...flat. Thanks.

TF
 
T

TF

TF, you have just a HUGE number of iterations happenning on the page - and
some of them are unnecessary. A few suggestions:

- create a vew on the datatable from the dataset
Dim myView As DataView = myDS2Row.Tables(0).DefaultView

and then on every reader row assign a filter to it:

myView.RowFilter = "brand='" & myReader1("brand").ToString & "' and id<>" &
myReader1("id").ToString

then instead of going though the whole table loop through the view rows

- instead of StringBuilders to keep IDs I would suggest using generic lists
of Integer:
Dim usedIDs As List(Of Integer) = New List(Of Integer)

then you could easily test whether an ID is already there (Contains method)
and add new ID (Add method)

Try that for a start, I may try to come up with a few more suggestions if I
have some more time...




...

read more »
Click to expand...

Sergey,
I really think most of my iterations are looping just to get skipped
with no results. Let me see if I understand your first suggestion.
Create the view on the table before any looping. Then inside the
first/outer loop filter the view so that the inner loop will only see
brands that match the outer loop? Also exclude the id? If that's
what I think it is then that will cut the inner loops by about 70%
since the most records of any brand is about 255. Hmmm. Very
interesting. For the second suggestion, I see how it could be more
efficient but I don't understand how it will tell me what combos were
used. I track the IDs in pairs because it's ok if either component
gets used again with some other recipe. Maybe I'm missing your
point. Thanks very much!

TF
 
G

Guest

TF,

I did not quite understand your logic with IDs - so if you could explain it
better, I will try to come up with a solution. Meanwhile, we could shrink the
number of calculations that your code performs. Though arithmetic operations
do not seem to be expensive, but considering the number of times you loop
inside your code...
For example,

a) consider the following line (executed for every 1000 rows in reader):
compRed1 = myReader1.Item(myReader1.GetOrdinal("redvalue"))
I would declare a redOrdinal variable instead before the loop:
Dim columns As DataColumnCollection = myDS2Row.Tables(0).Columns
Dim redOrdinal As Integer = columns.IndexOf("redvalue")

b) testRed = (compRed1 * compFactor1 + compRed2 * compFactor2) /
(compFactor1 + compFactor2)
(compFactor1 + compFactor2) - in your algorithm is calculated 3 times
instead of 1 for 1000 rows (in reader) * 255 (by brand) * maxRatio * maxRatio
- and now it makes sense to declare a variable:
Dim compFactorSum As Integer = compFactor1 + compFactor2

c) "startRed + maxDiff" and "startRed - maxDiff" could also be calculated
outside the very first (reader) loop

d) myReader1.GetOrdinal("brand")) and myReader1.GetOrdinal("id") - again -
move outside the loops

e) in your inner loop, where you check:
If (testRed < startRed + maxDiff And testRed > startRed - maxDiff) _
AndAlso (testGreen < startGreen + maxDiff And testGreen > startGreen -
maxDiff) _
AndAlso (testBlue < startBlue + maxDiff And testBlue > startBlue - maxDiff)
Then
ensure that you use AndAlso instead of And - this way you can cut quite a
number of comparisons as the second token in the And statement will not be
evaluated (for AndAlso) if the first one is False.


I know that some of the suggestions look like very minor improvements, but
taken into account the number of iterations, I think every bit counts.
 
T

TF

TF,

I did not quite understand your logic with IDs - so if you could explain it
better, I will try to come up with a solution. Meanwhile, we could shrink the
number of calculations that your code performs. Though arithmetic operations
do not seem to be expensive, but considering the number of times you loop
inside your code...
For example,

a) consider the following line (executed for every 1000 rows in reader):
compRed1 = myReader1.Item(myReader1.GetOrdinal("redvalue"))
I would declare a redOrdinal variable instead before the loop:
Dim columns As DataColumnCollection = myDS2Row.Tables(0).Columns
Dim redOrdinal As Integer = columns.IndexOf("redvalue")

b) testRed = (compRed1 * compFactor1 + compRed2 * compFactor2) /
(compFactor1 + compFactor2)
(compFactor1 + compFactor2) - in your algorithm is calculated 3 times
instead of 1 for 1000 rows (in reader) * 255 (by brand) * maxRatio * maxRatio
- and now it makes sense to declare a variable:
Dim compFactorSum As Integer = compFactor1 + compFactor2

c) "startRed + maxDiff" and "startRed - maxDiff" could also be calculated
outside the very first (reader) loop

d) myReader1.GetOrdinal("brand")) and myReader1.GetOrdinal("id") - again -
move outside the loops

e) in your inner loop, where you check:
If (testRed < startRed + maxDiff And testRed > startRed - maxDiff) _
AndAlso (testGreen < startGreen + maxDiff And testGreen > startGreen -
maxDiff) _
AndAlso (testBlue < startBlue + maxDiff And testBlue > startBlue - maxDiff)
Then
ensure that you use AndAlso instead of And - this way you can cut quite a
number of comparisons as the second token in the And statement will not be
evaluated (for AndAlso) if the first one is False.

I know that some of the suggestions look like very minor improvements, but
taken into account the number of iterations, I think every bit counts.
Click to expand...

Sergey,
First the IDs. It's just an autonumber field w/no duplicates. When I
started this I found that paint pairs that met the criteria at, say
2:1 ratio, were also displayed at 4:2, 8:4, etc. I want them to only
display the first time so I started concatting strings (later traded
for stringbuilder). I needed the ID pairs to be fwd and rev because
the outer loop would eventually get to the second record of the combo
and it would display again. It's the only workaround I could come up
with to keep used pairs from displaying more than once.

I created lowRed, hiRed, etc variables before the first loop. Good
idea. Would like to tackle an issue with your previous suggestion
before going into the others. I put in the statement...

Dim myView As Data.DataView = myDS2Row.Tables(0).DefaultView

....right after the "dataAdapter.Fill(myDataSet2)" line. VWD made me
change it to...

Dim myView As System.Data.DataView = myDS2Row.Table().DefaultView

....and VWD is now warning me that myDS2 is used before it's been
assigned a value. Also errors out when I try to run it. I think
filtering out most of the rows before the inner loop starts would be a
huge step. Am I missing something?

Thanks for your help, Sergey.
 
G

Guest

TF,

1) You should use dataset, not datarow to get the initial dataview:
Dim myView As Data.DataView = myDataSet2.Tables(0).DefaultView

2) To avoid duplicate IDs you do not have to use any of the methods you
described - you could just change your loops as follows:

For compFactor1 = 1 To maxRatio
For compFactor2 = compFactor1 To maxRatio

and this way you will cut the number of iterations, as well as resolve the
problem of possible duplicates.
 
T

TF

TF,
Click to expand...

Sergey,

I thought I posted this already but I guess it didn't get through.
Here
goes again. Thank you so much for all your help so far. I
implemented most
of your excellent suggestions and as I was going through my code I
realized
I was looping through all six columns on every loop. I took that
datareader.fieldcount loop out and now the page loads in 40 seconds
even
with maxratio at 10. Of course that made me VERY happy. But I still
can't
get the filter to work. I tried running with the filter and
commenting it
out and it's still 40 seconds. Also I get combos of mixed brands and
that
shouldn't happen if the filter's working. I'm sure I'm missing
something
simple. Here's my revised code. Can you see what's going wrong here?
Thanks again.

TF (code below)

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

'coming from PM - make sure the querystring values are what is
expected.
'if not, bail out of the page load sub
'should be 4 values: r,g,b,id. r,g,b must be between (not
equal) 0
and 256.
If Request.QueryString.Count <> 4 _
Or Request.QueryString("red") < 0 Or
Request.QueryString("red") >
255 _
Or Request.QueryString("green") < 0 Or
Request.QueryString("green")
255 _
Click to expand...
Or Request.QueryString("blue") < 0 Or
Request.QueryString("blue") >
255 Then

'bail from the page load sub
Exit Sub

Else

'dim all variables first
Dim startRed, testRed, compRed1, compRed2 As Integer
Dim hiRed, loRed As Integer
Dim redColOrdinal, blueColOrdinal, greenColOrdinal As
Integer
Dim startGreen, testGreen, compGreen1, compGreen2 As
Integer
Dim hiGreen, loGreen As Integer
Dim startBlue, testBlue, compBlue1, compBlue2 As Integer
Dim hiBlue, loBlue As Integer
Dim compFactor1, compFactor2, compFactorSum As Integer
Dim maxRatio, maxDiff As Integer
Dim startID, outerID, innerID As Integer
Dim outerBrand, innerBrand As String
Dim usedIDStr As String = "xxxxxxxxxxx"
Dim testIDStr1 As String = "" 'id pairs
Dim testIDStr2 As String = "" 'id pairs reverse order

'create the stringbuilder objects
Dim testID1 As New Text.StringBuilder(15)
Dim testID2 As New Text.StringBuilder(15)
Dim usedIDs As New Text.StringBuilder(500)

'max ratio of comparison
maxRatio = 10

'max diff from start color for display: +/- tolerance
maxDiff = 3

'starting id,r,g,b; get from querystring
startID = Request.QueryString("id")
startRed = Request.QueryString("red")
startGreen = Request.QueryString("green")
startBlue = Request.QueryString("blue")

'hi/lo bounds of RGB colors
hiRed = startRed + maxDiff
loRed = startRed - maxDiff
hiGreen = startGreen + maxDiff
loGreen = startGreen - maxDiff
hiBlue = startBlue + maxDiff
loBlue = startBlue - maxDiff

'the database connction stuff
Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.
4.0;
Data Source=C:\Inetpub\WebSite1\App_Data\PaintsDbase.mdb"
Dim myConn As Data.OleDb.OleDbConnection = New
Data.OleDb.OleDbConnection(myConnString)
Dim mySQLText As String = "SELECT
[id],[brand],[redvalue],[greenvalue],[bluevalue],[thinnedby] FROM
[Allpaints] ORDER BY [brand]"
Dim myCmd1 As Data.OleDb.OleDbCommand = New
Data.OleDb.OleDbCommand(mySQLText, myConn)
myConn.Open()

'QUERIED COLUMN ORDNALS
'0 - ID; 1 - BRAND; 2 - REDVALUE; 3 - GREENVALUE; 4 -
BLUEVALUE;
5 - THINNER

'dim myReader1 for the outside loop; forward moving only
Dim myReader1 As Data.OleDb.OleDbDataReader =
myCmd1.ExecuteReader()

'dim and fill myDataSet2 for the inside loop; need to go
both
directions
Dim dataAdapter As New
System.Data.OleDb.OleDbDataAdapter(mySQLText,
myConn)
Dim myDataSet2 As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(myDataSet2)

'set red, green, blue column ordinal variables
redColOrdinal = myReader1.GetOrdinal("redvalue")
greenColOrdinal = myReader1.GetOrdinal("greenvalue")
blueColOrdinal = myReader1.GetOrdinal("bluevalue")

'uncommented for debugging
Response.Write("maxdiff=" & maxDiff & "<br>")
Response.Write("maxratio=" & maxRatio & "<br><br>")

'dim myview to try and filter the dataset2
Dim myView As Data.DataView =
myDataSet2.Tables(0).DefaultView
Dim myDVRow As Data.DataRow
Dim myFilterStr As String
Dim filterFlag As String = ""
Dim insideLoopCount As Integer = 0

'start first/outside reader loop
While myReader1.Read()

'set the outerID and outerBrand variables
outerID = myReader1.Item(myReader1.GetOrdinal("id"))
outerBrand =
myReader1.Item(myReader1.GetOrdinal("brand"))

'takes same time with or without filter; not working.
If filterFlag <> outerBrand Then
myFilterStr = "Brand = '" & outerBrand & "'"
myView.RowFilter = myFilterStr
Response.Write("Just changed filter.<BR>")
Response.Write("Loops: " & insideLoopCount &
"<BR>")
Response.Write("rowfilter tostring: " &
myView.RowFilter.ToString() & "<BR>")
insideLoopCount = 0
filterFlag = outerBrand
End If

'get comp color values for the current OUTSIDE loop
record
compRed1 = myReader1.Item(redColOrdinal)
compGreen1 = myReader1.Item(greenColOrdinal)
compBlue1 = myReader1.Item(blueColOrdinal)

'the actual inside looper; goes through each myView
record
For Each myDVRow In myView.Table().Rows()

'get comp color values for the current INSIDE loop
compRed2 = myDVRow.Item(2)
compGreen2 = myDVRow.Item(3)
compBlue2 = myDVRow.Item(4)

'set the innerID variable
innerID = myDVRow.Item(0)
innerBrand = myDVRow.Item(1)

'increment the inside loop counter
insideLoopCount = insideLoopCount + 1

'first/outside ratio loop
For compFactor1 = 1 To maxRatio

'second/inside ratio loop
For compFactor2 = 1 To maxRatio

'compfactor1 plus compfactor2 variable
compFactorSum = compFactor1 + compFactor2

'the math to get testRed, testGreen,
testBlue
based on current ratios
testRed = ((compRed1 * compFactor1) +
(compRed2
* compFactor2)) / compFactorSum
testGreen = ((compGreen1 * compFactor1) +
(compGreen2 * compFactor2)) / compFactorSum
testBlue = ((compBlue1 * compFactor1) +
(compBlue2 * compFactor2)) / compFactorSum

If testRed < hiRed AndAlso testRed > loRed
_
AndAlso testGreen < hiGreen AndAlso
testGreen >
loGreen _
AndAlso testBlue < hiBlue AndAlso testBlueloBlue Then

'use stringbuilder to create fwd/rev
ID
pairs to test against used pairs
testID1.Remove(0, testID1.Length())
testID1.Append("::" & outerID & ":" &
innerID & "::")
testID2.Remove(0, testID2.Length())
testID2.Append("::" & innerID & ":" &
outerID & "::")

'the inner/outer brand should be the
same
and block out used id pairs
'at least for now;later we can maybe
turn on
mixed brand recipes
If outerID <> startID _
AndAlso innerID <> startID _
AndAlso usedIDs.ToString().IndexOf(
testID1.ToString()) = -1 _
AndAlso usedIDs.ToString().IndexOf(
testID2.ToString()) = -1 Then

'append the used id pair
usedIDs.Append(testID1.ToString())

'display for debugging
Response.Write(usedIDs.ToString()
&
"<br>")

'the ligter/darker flag
If testRed > startRed AndAlso
testGreen
startGreen AndAlso testBlue > startBlue Then
Click to expand...
Response.Write("Slightly
Lighter<BR>")
ElseIf testRed < startRed AndAlso
testGreen < startGreen AndAlso testBlue < startBlue Then
Response.Write("Slightly
Darker<BR>")
End If

'display for debugging
Response.Write("testred =
" & testRed & "<br>")
Response.Write("testgreen
= " & testGreen & "<br>")
Response.Write("testblue =
" & testBlue & "<br>")
Response.Write(outerBrand &
"<br>")
Response.Write("compfact1
= "
& compFactor1 & "<br>")
Response.Write("compred1
= "
& compRed1 & "<br>")
Response.Write("compgreen1 =
" & compGreen1 & "<br>")
Response.Write("compblue1 =
" & compBlue1 & "<br>")
'Response.Write(myDS2Row.Item(1) &
"<br>")
Response.Write(innerBrand &
"<br>")
Response.Write("compfact2
= "
& compFactor2 & "<br>")
Response.Write("compred2
= "
& compRed2 & "<br>")
Response.Write("compgreen2 =
" & compGreen2 & "<br>")
Response.Write("compblue2 =
" & compBlue2 & "<br><br><br>")
End If

End If

Next compFactor2

Next compFactor1

Next myDVRow

End While

End If

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="font-size: x-small; font-family: Verdana">
<form id="form1" runat="server">
<div>
Mixer Results<br />
<br />

</div>
</form>
</body>
</html>
 
T

TF

TF,
Click to expand...

Sergey,

I thought I posted this already but I guess it didn't get through.
Here goes again. Thank you so much for all your help so far. I
implemented most of your excellent suggestions and as I was going
through my code I realized I was looping through all six columns on
every loop. I took that datareader.fieldcount loop out and now the
page loads in 40 seconds even with maxratio at 10. Of course that
made me VERY happy. But I still can't get the filter to work. I
tried running with the filter and commenting it out and it's still 40
seconds. Also I get combos of mixed brands and that shouldn't happen
if the filter's working. I'm sure I'm missing something simple.
Here's my revised code. Can you see what's going wrong here? Thanks
again.

TF (code below)

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

'coming from PM - make sure the querystring values are what is
expected.
'if not, bail out of the page load sub
'should be 4 values: r,g,b,id. r,g,b must be between (not
equal) 0 and 256.
If Request.QueryString.Count <> 4 _
Or Request.QueryString("red") < 0 Or
Request.QueryString("red") > 255 _
Or Request.QueryString("green") < 0 Or
Request.QueryString("green") > 255 _
Or Request.QueryString("blue") < 0 Or
Request.QueryString("blue") > 255 Then

'bail from the page load sub
Exit Sub

Else

'dim all variables first
Dim startRed, testRed, compRed1, compRed2 As Integer
Dim hiRed, loRed As Integer
Dim redColOrdinal, blueColOrdinal, greenColOrdinal As
Integer
Dim startGreen, testGreen, compGreen1, compGreen2 As
Integer
Dim hiGreen, loGreen As Integer
Dim startBlue, testBlue, compBlue1, compBlue2 As Integer
Dim hiBlue, loBlue As Integer
Dim compFactor1, compFactor2, compFactorSum As Integer
Dim maxRatio, maxDiff As Integer
Dim startID, outerID, innerID As Integer
Dim outerBrand, innerBrand As String
Dim usedIDStr As String = "xxxxxxxxxxx"
Dim testIDStr1 As String = "" 'id pairs
Dim testIDStr2 As String = "" 'id pairs reverse order

'create the stringbuilder objects
Dim testID1 As New Text.StringBuilder(15)
Dim testID2 As New Text.StringBuilder(15)
Dim usedIDs As New Text.StringBuilder(500)

'max ratio of comparison
maxRatio = 10

'max diff from start color for display: +/- tolerance
maxDiff = 3

'starting id,r,g,b; get from querystring
startID = Request.QueryString("id")
startRed = Request.QueryString("red")
startGreen = Request.QueryString("green")
startBlue = Request.QueryString("blue")

'hi/lo bounds of RGB colors
hiRed = startRed + maxDiff
loRed = startRed - maxDiff
hiGreen = startGreen + maxDiff
loGreen = startGreen - maxDiff
hiBlue = startBlue + maxDiff
loBlue = startBlue - maxDiff

'the database connction stuff
Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.
4.0; Data Source=C:\Inetpub\WebSite1\App_Data\PaintsDbase.mdb"
Dim myConn As Data.OleDb.OleDbConnection = New
Data.OleDb.OleDbConnection(myConnString)
Dim mySQLText As String = "SELECT [id],[brand],[redvalue],
[greenvalue],[bluevalue],[thinnedby] FROM [Allpaints] ORDER BY
[brand]"
Dim myCmd1 As Data.OleDb.OleDbCommand = New
Data.OleDb.OleDbCommand(mySQLText, myConn)
myConn.Open()

'QUERIED COLUMN ORDNALS
'0 - ID; 1 - BRAND; 2 - REDVALUE; 3 - GREENVALUE; 4 -
BLUEVALUE; 5 - THINNER

'dim myReader1 for the outside loop; forward moving only
Dim myReader1 As Data.OleDb.OleDbDataReader =
myCmd1.ExecuteReader()

'dim and fill myDataSet2 for the inside loop; need to go
both directions
Dim dataAdapter As New
System.Data.OleDb.OleDbDataAdapter(mySQLText, myConn)
Dim myDataSet2 As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(myDataSet2)

'set red, green, blue column ordinal variables
redColOrdinal = myReader1.GetOrdinal("redvalue")
greenColOrdinal = myReader1.GetOrdinal("greenvalue")
blueColOrdinal = myReader1.GetOrdinal("bluevalue")

'uncommented for debugging
Response.Write("maxdiff=" & maxDiff & "<br>")
Response.Write("maxratio=" & maxRatio & "<br><br>")

'dim myview to try and filter the dataset2
Dim myView As Data.DataView =
myDataSet2.Tables(0).DefaultView
Dim myDVRow As Data.DataRow
Dim myFilterStr As String
Dim filterFlag As String = ""
Dim insideLoopCount As Integer = 0

'start first/outside reader loop
While myReader1.Read()

'set the outerID and outerBrand variables
outerID = myReader1.Item(myReader1.GetOrdinal("id"))
outerBrand =
myReader1.Item(myReader1.GetOrdinal("brand"))

'takes same time with or without filter; not working.
If filterFlag <> outerBrand Then
myFilterStr = "Brand = '" & outerBrand & "'"
myView.RowFilter = myFilterStr
Response.Write("Just changed filter.<BR>")
Response.Write("Loops: " & insideLoopCount &
"<BR>")
Response.Write("rowfilter tostring: " &
myView.RowFilter.ToString() & "<BR>")
insideLoopCount = 0
filterFlag = outerBrand
End If

'get comp color values for the current OUTSIDE loop
record
compRed1 = myReader1.Item(redColOrdinal)
compGreen1 = myReader1.Item(greenColOrdinal)
compBlue1 = myReader1.Item(blueColOrdinal)

'the actual inside looper; goes through each myView
record
For Each myDVRow In myView.Table().Rows()

'get comp color values for the current INSIDE loop
compRed2 = myDVRow.Item(2)
compGreen2 = myDVRow.Item(3)
compBlue2 = myDVRow.Item(4)

'set the innerID variable
innerID = myDVRow.Item(0)
innerBrand = myDVRow.Item(1)

'increment the inside loop counter
insideLoopCount = insideLoopCount + 1

'first/outside ratio loop
For compFactor1 = 1 To maxRatio

'second/inside ratio loop
For compFactor2 = 1 To maxRatio

'compfactor1 plus compfactor2 variable
compFactorSum = compFactor1 + compFactor2

'the math to get testRed, testGreen,
testBlue based on current ratios
testRed = ((compRed1 * compFactor1) +
(compRed2 * compFactor2)) / compFactorSum
testGreen = ((compGreen1 * compFactor1) +
(compGreen2 * compFactor2)) / compFactorSum
testBlue = ((compBlue1 * compFactor1) +
(compBlue2 * compFactor2)) / compFactorSum

If testRed < hiRed AndAlso testRed > loRed
_
AndAlso testGreen < hiGreen AndAlso
testGreen > loGreen _
AndAlso testBlue < hiBlue AndAlso testBlue
loBlue Then
Click to expand...

'use stringbuilder to create fwd/rev
ID pairs to test against used pairs
testID1.Remove(0, testID1.Length())
testID1.Append("::" & outerID & ":" &
innerID & "::")
testID2.Remove(0, testID2.Length())
testID2.Append("::" & innerID & ":" &
outerID & "::")

'the inner/outer brand should be the
same and block out used id pairs
'at least for now;later we can maybe
turn on mixed brand recipes
If outerID <> startID _
AndAlso innerID <> startID _
AndAlso
usedIDs.ToString().IndexOf(testID1.ToString()) = -1 _
AndAlso
usedIDs.ToString().IndexOf(testID2.ToString()) = -1 Then

'append the used id pair
usedIDs.Append(testID1.ToString())

'display for debugging
Response.Write(usedIDs.ToString()
& "<br>")

'the ligter/darker flag
If testRed > startRed AndAlso
testGreen > startGreen AndAlso testBlue > startBlue Then
Response.Write("Slightly
Lighter<BR>")
ElseIf testRed < startRed AndAlso
testGreen < startGreen AndAlso testBlue < startBlue Then
Response.Write("Slightly
Darker<BR>")
End If

'display for debugging
Response.Write("testred
= " & testRed & "<br>")
Response.Write("testgreen
= " & testGreen & "<br>")
Response.Write("testblue
= " & testBlue & "<br>")
Response.Write(outerBrand &
"<br>")
Response.Write("compfact1
= " & compFactor1 & "<br>")
Response.Write("compred1
= " & compRed1 & "<br>")
Response.Write("compgreen1
= " & compGreen1 & "<br>")
Response.Write("compblue1
= " & compBlue1 & "<br>")
'Response.Write(myDS2Row.Item(1) &
"<br>")
Response.Write(innerBrand &
"<br>")
Response.Write("compfact2
= " & compFactor2 & "<br>")
Response.Write("compred2
= " & compRed2 & "<br>")
Response.Write("compgreen2
= " & compGreen2 & "<br>")
Response.Write("compblue2
= " & compBlue2 & "<br><br><br>")
End If

End If

Next compFactor2

Next compFactor1

Next myDVRow

End While

End If

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="font-size: x-small; font-family: Verdana">
<form id="form1" runat="server">
<div>
Mixer Results<br />
<br />

</div>
</form>
</body>
</html>
 
G

Guest

TF,

I have not looked at newsgroups for a while - please confirm if you still
have not resolved it and want me to take a look.

TF said:
TF,
Click to expand...

Sergey,

I thought I posted this already but I guess it didn't get through.
Here goes again. Thank you so much for all your help so far. I
implemented most of your excellent suggestions and as I was going
through my code I realized I was looping through all six columns on
every loop. I took that datareader.fieldcount loop out and now the
page loads in 40 seconds even with maxratio at 10. Of course that
made me VERY happy. But I still can't get the filter to work. I
tried running with the filter and commenting it out and it's still 40
seconds. Also I get combos of mixed brands and that shouldn't happen
if the filter's working. I'm sure I'm missing something simple.
Here's my revised code. Can you see what's going wrong here? Thanks
again.

TF (code below)

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)

'coming from PM - make sure the querystring values are what is
expected.
'if not, bail out of the page load sub
'should be 4 values: r,g,b,id. r,g,b must be between (not
equal) 0 and 256.
If Request.QueryString.Count <> 4 _
Or Request.QueryString("red") < 0 Or
Request.QueryString("red") > 255 _
Or Request.QueryString("green") < 0 Or
Request.QueryString("green") > 255 _
Or Request.QueryString("blue") < 0 Or
Request.QueryString("blue") > 255 Then

'bail from the page load sub
Exit Sub

Else

'dim all variables first
Dim startRed, testRed, compRed1, compRed2 As Integer
Dim hiRed, loRed As Integer
Dim redColOrdinal, blueColOrdinal, greenColOrdinal As
Integer
Dim startGreen, testGreen, compGreen1, compGreen2 As
Integer
Dim hiGreen, loGreen As Integer
Dim startBlue, testBlue, compBlue1, compBlue2 As Integer
Dim hiBlue, loBlue As Integer
Dim compFactor1, compFactor2, compFactorSum As Integer
Dim maxRatio, maxDiff As Integer
Dim startID, outerID, innerID As Integer
Dim outerBrand, innerBrand As String
Dim usedIDStr As String = "xxxxxxxxxxx"
Dim testIDStr1 As String = "" 'id pairs
Dim testIDStr2 As String = "" 'id pairs reverse order

'create the stringbuilder objects
Dim testID1 As New Text.StringBuilder(15)
Dim testID2 As New Text.StringBuilder(15)
Dim usedIDs As New Text.StringBuilder(500)

'max ratio of comparison
maxRatio = 10

'max diff from start color for display: +/- tolerance
maxDiff = 3

'starting id,r,g,b; get from querystring
startID = Request.QueryString("id")
startRed = Request.QueryString("red")
startGreen = Request.QueryString("green")
startBlue = Request.QueryString("blue")

'hi/lo bounds of RGB colors
hiRed = startRed + maxDiff
loRed = startRed - maxDiff
hiGreen = startGreen + maxDiff
loGreen = startGreen - maxDiff
hiBlue = startBlue + maxDiff
loBlue = startBlue - maxDiff

'the database connction stuff
Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.
4.0; Data Source=C:\Inetpub\WebSite1\App_Data\PaintsDbase.mdb"
Dim myConn As Data.OleDb.OleDbConnection = New
Data.OleDb.OleDbConnection(myConnString)
Dim mySQLText As String = "SELECT [id],[brand],[redvalue],
[greenvalue],[bluevalue],[thinnedby] FROM [Allpaints] ORDER BY
[brand]"
Dim myCmd1 As Data.OleDb.OleDbCommand = New
Data.OleDb.OleDbCommand(mySQLText, myConn)
myConn.Open()

'QUERIED COLUMN ORDNALS
'0 - ID; 1 - BRAND; 2 - REDVALUE; 3 - GREENVALUE; 4 -
BLUEVALUE; 5 - THINNER

'dim myReader1 for the outside loop; forward moving only
Dim myReader1 As Data.OleDb.OleDbDataReader =
myCmd1.ExecuteReader()

'dim and fill myDataSet2 for the inside loop; need to go
both directions
Dim dataAdapter As New
System.Data.OleDb.OleDbDataAdapter(mySQLText, myConn)
Dim myDataSet2 As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(myDataSet2)

'set red, green, blue column ordinal variables
redColOrdinal = myReader1.GetOrdinal("redvalue")
greenColOrdinal = myReader1.GetOrdinal("greenvalue")
blueColOrdinal = myReader1.GetOrdinal("bluevalue")

'uncommented for debugging
Response.Write("maxdiff=" & maxDiff & "<br>")
Response.Write("maxratio=" & maxRatio & "<br><br>")

'dim myview to try and filter the dataset2
Dim myView As Data.DataView =
myDataSet2.Tables(0).DefaultView
Dim myDVRow As Data.DataRow
Dim myFilterStr As String
Dim filterFlag As String = ""
Dim insideLoopCount As Integer = 0

'start first/outside reader loop
While myReader1.Read()

'set the outerID and outerBrand variables
outerID = myReader1.Item(myReader1.GetOrdinal("id"))
outerBrand =
myReader1.Item(myReader1.GetOrdinal("brand"))

'takes same time with or without filter; not working.
If filterFlag <> outerBrand Then
myFilterStr = "Brand = '" & outerBrand & "'"
myView.RowFilter = myFilterStr
Response.Write("Just changed filter.<BR>")
Response.Write("Loops: " & insideLoopCount &
"<BR>")
Response.Write("rowfilter tostring: " &
myView.RowFilter.ToString() & "<BR>")
insideLoopCount = 0
filterFlag = outerBrand
End If

'get comp color values for the current OUTSIDE loop
record
compRed1 = myReader1.Item(redColOrdinal)
compGreen1 = myReader1.Item(greenColOrdinal)
compBlue1 = myReader1.Item(blueColOrdinal)

'the actual inside looper; goes through each myView
record
For Each myDVRow In myView.Table().Rows()

'get comp color values for the current INSIDE loop
compRed2 = myDVRow.Item(2)
compGreen2 = myDVRow.Item(3)
compBlue2 = myDVRow.Item(4)

'set the innerID variable
innerID = myDVRow.Item(0)
innerBrand = myDVRow.Item(1)

'increment the inside loop counter
insideLoopCount = insideLoopCount + 1

'first/outside ratio loop
For compFactor1 = 1 To maxRatio

'second/inside ratio loop
For compFactor2 = 1 To maxRatio

'compfactor1 plus compfactor2 variable
compFactorSum = compFactor1 + compFactor2

'the math to get testRed, testGreen,
testBlue based on current ratios
testRed = ((compRed1 * compFactor1) +
(compRed2 * compFactor2)) / compFactorSum
testGreen = ((compGreen1 * compFactor1) +
(compGreen2 * compFactor2)) / compFactorSum
testBlue = ((compBlue1 * compFactor1) +
(compBlue2 * compFactor2)) / compFactorSum

If testRed < hiRed AndAlso testRed > loRed
_
AndAlso testGreen < hiGreen AndAlso
testGreen > loGreen _
AndAlso testBlue < hiBlue AndAlso testBlue
loBlue Then
Click to expand...

'use stringbuilder to create fwd/rev
ID pairs to test against used pairs
testID1.Remove(0, testID1.Length())
testID1.Append("::" & outerID & ":" &
innerID & "::")
testID2.Remove(0, testID2.Length())
testID2.Append("::" & innerID & ":" &
outerID & "::")

'the inner/outer brand should be the
same and block out used id pairs
'at least for now;later we can maybe
turn on mixed brand recipes
If outerID <> startID _
AndAlso innerID <> startID _
AndAlso
usedIDs.ToString().IndexOf(testID1.ToString()) = -1 _
AndAlso
usedIDs.ToString().IndexOf(testID2.ToString()) = -1 Then

'append the used id pair
usedIDs.Append(testID1.ToString())

'display for debugging
Response.Write(usedIDs.ToString()
& "<br>")

'the ligter/darker flag
If testRed > startRed AndAlso
testGreen > startGreen AndAlso testBlue > startBlue Then
Response.Write("Slightly
Lighter<BR>")
ElseIf testRed < startRed AndAlso
testGreen < startGreen AndAlso testBlue < startBlue Then
Response.Write("Slightly
Darker<BR>")
End If

'display for debugging
Response.Write("testred
= " & testRed & "<br>")
Response.Write("testgreen
= " & testGreen & "<br>")
Response.Write("testblue
= " & testBlue & "<br>")
Response.Write(outerBrand &
"<br>")
Response.Write("compfact1
= " & compFactor1 & "<br>")
Response.Write("compred1
= " & compRed1 & "<br>")
Response.Write("compgreen1
= " & compGreen1 & "<br>")
Response.Write("compblue1
= " & compBlue1 & "<br>")
'Response.Write(myDS2Row.Item(1) &
"<br>")
Response.Write(innerBrand &
"<br>")
Response.Write("compfact2
= " & compFactor2 & "<br>")
Response.Write("compred2
= " & compRed2 & "<br>")
Response.Write("compgreen2
= " & compGreen2 & "<br>")
Response.Write("compblue2
= " & compBlue2 & "<br><br><br>")
End If

End If

Next compFactor2

Next compFactor1

Next myDVRow

End While

End If

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="font-size: x-small; font-family: Verdana">
<form id="form1" runat="server">
<div>
Mixer Results<br />
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with my responsive home page 2
Please, help me. 1
Help please 8
I need help fixing my website 2
Parameterized Query and Record Filters 1
Need help fixing code 1
Need help with code on website (noob) 2
Help with Loop 0

Members online

Total: 57 (members: 1, guests: 56)
Robots: 470

Forum statistics

Threads
473,994
Messages
2,570,222
Members
46,810
Latest member
Kassie0918

Latest Threads

Top