Please Help me Optimize my Code

Discussion in 'ASP .Net' started by TF, May 29, 2007.

  1. TF

    TF Guest

    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>
     
    TF, May 29, 2007
    #1
    1. Advertising

  2. 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" wrote:

    > 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>
    >
    >
     
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, May 29, 2007
    #2
    1. Advertising

  3. 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" wrote:

    > 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>
    >
    >
     
    =?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?=, May 30, 2007
    #3
  4. TF

    TF Guest

    On May 29, 3:37 pm, Peter Bromberg [C# MVP]
    <> wrote:
    > 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
    >
    > > Response.Write("compblue1
    > > = " & compBlue1 & "<br>")

    >
    > ...
    >
    > read more »


    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
     
    TF, May 30, 2007
    #4
  5. TF

    TF Guest

    On May 29, 8:56 pm, Sergey Poberezovskiy
    <> wrote:
    > 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" wrote:
    > > 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


    > > Response.Write("compfact1
    > > = " & compFactor1 & "<br>")
    > > Response.Write("compred1
    > > = " &

    >
    > ...
    >
    > read more »


    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
     
    TF, May 30, 2007
    #5
  6. 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.


    "TF" wrote:

    > On May 29, 8:56 pm, Sergey Poberezovskiy
    > <> wrote:
    > > 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" wrote:
    > > > 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

    >
    > > > Response.Write("compfact1
    > > > = " & compFactor1 & "<br>")
    > > > Response.Write("compred1
    > > > = " &

    > >
    > > ...
    > >
    > > read more »

    >
    > 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
    >
    >
     
    =?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?=, May 30, 2007
    #6
  7. TF

    TF Guest

    On May 30, 3:59 pm, Sergey Poberezovskiy
    <> wrote:
    > 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.
    >
    > "TF" wrote:
    > > On May 29, 8:56 pm, Sergey Poberezovskiy
    > > <> wrote:
    > > > 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" wrote:
    > > > > 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

    >
    > > > > Response.Write("compfact1
    > > > > = " & compFactor1 & "<br>")
    > > > > Response.Write("compred1
    > > > > = " &

    >
    > > > ...

    >
    > > > read more »

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


    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.
     
    TF, May 30, 2007
    #7
  8. 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.




    "TF" wrote:

    > On May 30, 3:59 pm, Sergey Poberezovskiy
    > <> wrote:
    > > 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.
    > >
    > > "TF" wrote:
    > > > On May 29, 8:56 pm, Sergey Poberezovskiy
    > > > <> wrote:
    > > > > 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" wrote:
    > > > > > 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

    > >
    > > > > > Response.Write("compfact1
    > > > > > = " & compFactor1 & "<br>")
    > > > > > Response.Write("compred1
    > > > > > = " &

    > >
    > > > > ...

    > >
    > > > > read more »

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

    >
    > 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.
    >
    >
    >
     
    =?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?=, May 31, 2007
    #8
  9. TF

    TF Guest

    On May 30, 11:49 pm, Sergey Poberezovskiy <
    > wrote:
    > 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.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

    '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>
     
    TF, Jun 7, 2007
    #9
  10. TF

    TF Guest

    On May 30, 11:49 pm, Sergey Poberezovskiy
    <> wrote:
    > 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.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


    '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>
     
    TF, Jun 8, 2007
    #10
  11. 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" wrote:

    > On May 30, 11:49 pm, Sergey Poberezovskiy
    > <> wrote:
    > > 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.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

    >
    > '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 />
     
    =?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?=, Jun 28, 2007
    #11
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. KK
    Replies:
    2
    Views:
    651
    Big Brian
    Oct 14, 2003
  2. mclaugb
    Replies:
    4
    Views:
    2,346
    Robert Kern
    Jan 21, 2007
  3. gtippery
    Replies:
    40
    Views:
    1,022
  4. Daniel T.

    Optimize this please?

    Daniel T., Nov 29, 2006, in forum: C++
    Replies:
    13
    Views:
    556
    kwikius
    Nov 29, 2006
  5. please help optimize sub

    , Oct 18, 2007, in forum: Perl Misc
    Replies:
    16
    Views:
    196
Loading...

Share This Page