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.md b"
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>
