On May 30, 11:49 pm, Sergey Poberezovskiy
<SergeyPoberezovs...@discussions.microsoft.comwrot e:
TF,
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.
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.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] 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
'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>
