Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Asp.Net Programming

Please Help me Optimize my Code


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

read more »

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

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

...

read more »

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

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

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

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

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

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

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

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

...

read more »

On May 29, 3:37 pm, Peter Bromberg [C# MVP]

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

-----------------------------------------------Reply-----------------------------------------------

On May 29, 8:56 pm, Sergey Poberezovskiy

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

-----------------------------------------------Reply-----------------------------------------------

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.

On May 30, 3:59 pm, Sergey Poberezovskiy

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.

-----------------------------------------------Reply-----------------------------------------------

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.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc