Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Increase program speed

Posted on 2009-02-17
6
Medium Priority
?
211 Views
Last Modified: 2012-05-06
The following code takes what seems forever, I would appreciate any ideas/assistance in speeding it up a bit, if possible.
Private Sub b3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles b3.Click
        Dim sTN, sTA, sTC, sTZC, bTN, sW, sON, sMOD As String
        Dim dreader As OleDb.OleDbDataReader
        Dim zone1() As String = {"13113", "13166", "13033", "13111", "13024", "13080", "13021", "13060", "13153", "13137", "13156", "13119", "13074", "13027", "13164", "13108", "13031", "13069", "13135", "13209", "13219", "13115", "13090", "13089", "13088", "13110", "13215", "13218", "1325", "13221", "13261", "13251", "13217", "13201", "13225", "13290", "13132", "13204", "13208", "13207", "13252", "13041", "13126", "13212", "13203", "13205", "13202", "13211", "13244", "13220", "13206", "13120", "13210", "13224", "13029", "13036", "13039", "13214", "13057", "13066", "13167", "13076", _
 "13116", "13121", "13114", "13103", "13093", "13064", "13104", "13030", "13082", "13044", "13131", "13107", "13028", "13037", "13042", "13032", "13493", "13142", "13043", "13123", "13302", "13163", "13162", "13157", "13483", "13054", "13426", "13316", "13144", "13308", "13421", "13145", "13401", "13083", "13478", "13476", "13437", "13471", "13661", "13477", "13411", "13636", "13674", "13449", "13442", "13490", "13363", "13611", "13440", "13650", "13303", "13605", "13659", "13489", "13651", _
 "13469", "13486", "13606", "13325", "13354", "13685", "13682", "13309", "13304", "13473", "13301", "13628", "13435", "13433", "13438", "13368", "13693", "13634", "13352", "13601", "13338", "13626", "13657", "13367", "13618", "13615", "13305", "13627", "13312", "13345", "13343", "13404", "13622", "13631", "13616", "13612", "13324", "13494", "13643", "13638", "13620", "13602", "13619", "13632", "13603", "13637", "13472", "13327", "13454", "13624", "13656", "13641", "13665", "13673", "13691", "13692", "13640", "13675", "13607", "13608", "13679", "13648", "13671", "13642", "13623", "13690", "13670", "13646", "13635", "13639", "13681", "13614", "13664", "13684", "13630", "13652", "13633", "13654", "13683", "13645", "13699", "13680", "13669", "13617", "13687", "13658", "13647", "13677", "13676", "13672", "13660", "13668", "13696", "13678", "13697", "13621", "13667", "13613", "13662", "12957", "13649"}
        Dim zone2() As String = {"13112", "13078", "13141", "13084", "13159", "13138", "13020", "13063", "13087", "13077", "13051", "13035", "13158", "13122", "13101", "13052", "13061", "13134", "13408", "13072", "13484", "13364", "13461", "13465", "13409", "13124", "13334", "13155", "13136", "13310", "13362", "13040", "13803", "13402", "13346", "13599", "13425", "13464", "13328", "13758", "13794", "13504", "13332", "13863", "13323", "13844", "13832", "13321", "13355", "13801", "13480", "13797", "13418", "13424", "13455", "13492", "13460", "13413", "13417", "13814", "13403", "13862", "13495", "13841", "13505", "13456", "13314", "13319", "13479", "13318", "13502", "13815", "13501", "13485", "13503", "13313", "13830", "13777", "13322", "13778", "13411", "13335", "13491", "13415", "13746", "13340", "13780", "13315", "13843", "13482", "13431", "13342", "13357", "13809", "13808", "13457", "13407", "13416", "13350", "13776", "13848", "13810", "13733", "13439", "13348", "13406", "13730", "13787", "13796", "13468", "13337", "13838", "13365", "13849", "13825", "13488", "13859", "13326", "13361", "13475", "13826", "13813", "13807", "13861", "13865", "13820", "13775", "13834", "13839", "13747", "13333", "12116", "13804", "13450", "13320", "13846", "13353", "13331", "13470", "12155", "13452", "13751", "13339", "13806", "13754", "13420", "13410", "13757", "12197", "13750", "13317", "13428", "13856", "12064", "13459", "13360", "12032", "13753", "12036", "13739", "12149", "12139", "13782", "13786", "12166", "12175", "12043", "12031", "13842", "12068", "12093", "12016", "13752", "12438", "13860", "13847", "13774", "12187", "13783", "13095", "12072", "12078", "12167", "13740", "13788", "13755", "12108", "12160", "13756", "12194", "12073", "12177", "12092", "12035", "12164", "12864", "13731", "12069", "12066", "12117", "12190", "12070", "12434", "13695", "12071", "12131", "12459", "12157", "13436", "13666", "12474", "12122", "12010", "12025", "12134", "12455", "12076", "12086", "12421", "12053", "12141", "12776", "12812", "12760", "12137", "12741", "12927", "12430", "12736", "12847", "12468", "12074", "12023", "12150", "12345", "12842", "12469", "12325", "12301", "12056", "12107", "12850", "12766", "12406", "12745", "12465", "12055", "12147", "12452", "12758", "12410", "12723", "12496", "12009", "12492", "12422", "12306", "12059", "12811", "12120", "12460", "12863", "12444", "12407", "12835", "12302", "12307", "12748", "12305", "12791", "12750", "12303", "12308", "12859", "12027", "12878", "12856", "12418", "12480", "12304", "12423", "12424", "12768", "12008", "12843", "12922", "12193", "12242", "12236", "12225", "12725", "12226", "12201", "12229", "12228", "12237", "12241", "12240", "12239", "12439", "12973", "12232", "12238", "12186", "12787", "12220", "12234", "12224", "12833", "12230", "12231", "12020", "12235", "12214", "12223", "12227", "12233", "12454", "12247", "12261", "12257", "21288", "12256", "12255", "12252", "12250", "12260", "12248", "12244", "12243", "12245", "12249", "12085", "12442", "12148", "12309", "12007", "12019", "12726", "12041", "12752", "12822", "12084", "12431", "12754", "12405", "12083", "12810", "12846", "12159", "12464", "12450", "12067", "12886", "12866", "12783", "12765", "12986", "12727", "12222", "12862", "12852", "13625", "12151", "12065", "12416", "12740", "12205", "12203", "12720", "12046", "12851", "12212", "12749", "12734", "12759", "12473", "12054", "12436", "12853", "12110", "12722", "12724", "12885", "12785", "12767", "12764", "12208", "12413", "12128", "12427", "12495", "12485", "12781", "12161", "12857", "12176", "12457", "12206", "12786", "12209", "12211", "12762", "12747", "12742", "12448", "12470", "12831", "12077", "12202", "12158", "12788", "12170", "12778", "12210", "12412", "12817", "12189", "12207", "12804", "12058", "12087", "12118", "12204", "12451", "12188", "12047", "12246", "12183", "12482", "12845", "12494", "12409", "12143", "12779", "12463", "12751", "12181", "12144", "12182", "12801", "12803", "12045", "12042", "12733", "12481", "12192", "12154", "12402", "12180", "12124", "12441", "12588", "12871", "12808", "12824", "12789", "12156", "12701", "12433", "12792", "12860", "12884", "12784", "12498", "12051", "12828", "12198", "12061", "12820", "12435", "12121", "12033", "12844", "12414", "12732", "12461", "12814", "12839", "12815", "12763", "12491", "12743", "12719", "12140", "12185", "12738", "12196", "12848", "12173", "12051", "12106", "12458", "12777", "12477", "12870", "12490", "12063", "12834", "12174", "12050", "12184", "12453", "12475", "12428", "12094", "12489", "12446", "12456", "12130", "12132", "12123", "12775", "12945", "12018", "12172", "12534", "12526", "12443", "12404", "12872", "12484", "12809", "12028", "12449", "12052", "12827", "12737", "12483", "13694", "12823", "12024", "12115", "12983", "12769", "12504", "12583", "12153", "12401", "12432", "12770", "12420", "12513", "12082", "12544", "12816", "12838", "12419", "12841", "12440", "12136", "12970", "12507", "12075", "12790", "12855", "12980", "12472", "12530", "12411", "12858", "12565", "12466", "12037", "12062", "12541", "12417", "12057", "12874", "12523", "12090", "12946", "12571", "12836", "12487", "12593", "12965", "12873", "12574", "12486", "12865", "12780", "12471", "12138", "12195", "12133", "12977", "12879", "12521", "12721", "12729", "12060", "12819", "12022", "12821", "12572", "12089", "12168", "12887", "12915", "12976", "12040", "12939", "12566", "10985", "12913", "12429", "12746", "12943", "12949", "12169", "12561", "12832", "12854", "12165", "12930", "12493", "12967", "12849", "12029", "12502", "12861", "12580", "12525", "12017", "12771", "12125", "12837", "12529", "12989", "12942", "12883", "12528", "12548", "12538", "12589", "12515", "12568", "12928", "12549", "12567", "12516", "12987", "12517", "12503", "12514", "12960", "12964", "12586", "12956", "12961", "12998", "12578", "12581", "12601", "12974", "12997", "10973", "12916", "12547", "10988", "12543", "12923", "10998", "12569", "12555", "12603", "12552", "12542", "12551", "12941", "10997", "12966", "12604", "12506", "12511", "12510", "12602", "12546", "12545", "12575", "12550", "12993", "12537", "12512", "10969", "12914", "12577", "12585", "12501", "12665", "12969", "10992", "12553", "12950", "12590", "13655", "12912", "12584", "12953", "12540", "10981", "12527", "12508", "12524", "12518", "12937", "12592", "10990", "12520", "12533", "12978", "12522", "12570", "12936", "12952", "12926", "12985", "10996", "12924", "12582", "10963", "12996", "12955", "10923", "12917", "12594", "10979", "10919", "10915", "10975", "12531", "12911", "12981", "10987", "12944", "12564", "12920", "12918", "12972", "10940", "12933", "12934", "10974", "10986", "12975", "12962", "10980", "12563", "10933", "10984", "10970", "10982", "10993", "12923", "10958", "10915", "10912", "10916", "10924", "12901", "10959", "10943", "12935", "10941", "12903", "10977", "12992", "12910", "12929", "12959", "12958", "12921", "12919", "12979"}
        Dim zone3() As String = {"14433", "14542", "13146", "14489", "13154", "14516", "13117", "13140", "14513", "14413", "14590", "14532", "13143", "14551", "14537", "14518", "13148", "13165", "14522", "14555", "14505", "13034", "14432", "14449", "13022", "14589", "14456", "13024", "14548", "14504", "13021", "14568", "14538", "13065", "14502", "13160", "14520", "14563", "14561", "14519", "14425", "13152", "14463", "14541", "14424", "14450", "14564", "14415", "14526", "14453", "13147", "14445", "13026", "14580", "14547", "14625", "14443", "14461", "14534", "14506", "13139", "14618", "14475", "14610", "14469", "14544", "14441", "14609", "14588", "145211", "14622", "14607", "14467", "14604", "14605", "14585", "14620", "14603", "14472", "14617", "14621", "14627", "14623", "14527", "14614", "14608", "14619", "13081", "13118", "14613", "14611", "14507", "14485", "13071", "14616", "14543", "14615", "14586", "14606", "14847", "14612", "14471", "14860", "14842", "14624", "14626", "14643", "14430", "14651", "14466", "13092", "14694", "14692", "14683", "14673", "14664", "14639", "14652", "14650", "14649", "14647", "14646", "14645", "14644", "14642", "14638", "14602", "14478", "14653", "14418", "14515", "14546", "14558", "14414", "14514", "14487", "14559", "14512", "14480", "14882", "14468", "14428", "14423", "14511", "14410", "14886", "13073", "14560", "14857", "14837", "14841", "14874", "14435", "14854", "14454", "14592", "14873", "14420", "14416", "13102", "14545", "14539", "14488", "14556", "14486", "14893", "14533", "14808", "13045", "14482", "14878", "14863", "14464", "13068", "14818", "14462", "13062", "14876", "14851", "14481", "14557", "14572", "13738", "14850", "14525", "14852", "14826", "14470", "14510", "14422", "14840", "14853", "14887", "13053", "14143", "14529", "14476", "14437", "14815", "14891", "14869", "14054", "14591", "14865", "14805", "14809", "14867", "14530", "14058", "14881", "14021", "14020", "14549", "14817", "14477", "14856", "14411", "13784", "14517", "14810", "14039", "14125", "13835", "14005", "14879", "14812", "14836", "14569", "14427", "14450", "14571", "14824", "14807", "14822", "13864", "14429", "14452", "14508", "14864", "14056", "14479", "14846", "14884", "14011", "14872", "14883", "14103", "13736", "14536", "14821", "14013", "14036", "14040", "14066", "14843", "14167", "14098", "14816", "14889", "14130", "14845", "14838", "14038", "14827", "14823", "14870", "14804", "14819", "14105", "13743", "14001", "14113", "14814", "14902", "14024", "14820", "14925", "14735", "14037", "14745", "13811", "14830", "14145", "14903", "14004", "14082", "14067", "14744", "14012", "14802", "14803", "14901", "14083", "14709", "14031", "14102", "14029", "14859", "14777", "14905", "13802", "13744", "14032", "14904", "14801", "14169", "14139", "14861", "14009", "14008", "14885", "14831", "14086", "14094", "14052", "14711", "13827", "14060", "14059", "14026", "14065", "14871", "14133", "14839", "14717", "14051", "14858", "13845", "14108", "14080", "14140", "14043", "13734", "14894", "14221", "14030", "14898", "14806", "13745", "13760", "14068", "14028", "14173", "14813", "14241", "14126", "14227", "14042", "14225", "14228", "13833", "13840", "14134", "14892", "13761", "14170", "14224", "13790", "13763", "13851", "13737", "13749", "13762", "13812", "13901", "14885", "14226", "13732", "14069", "14127", "14714", "14880", "13905", "14206", "14033", "14215", "14877", "14172", "13904", "14150", "14055", "14210", "13850", "14211", "14212", "14214", "14120", "14223", "14220", "14217", "14273", "14107", "14132", "13902", "14208", "14218", "14216", "14302", "14109", "14095", "14222", "14201", "14895", "14209", "13903", "14204", "14203", "14739", "14101", "14264", "14131", "14207", "14213", "14201", "14025", "14202", "14897", "14075", "14737", "14141", "14010", "14151", "14110", "14280", "14276", "14304", "14240", "13795", "14072", "14205", "14231", "14171", "14233", "14272", "14260", "14261", "14263", "14265", "14267", "14269", "14270", "14727", "13748", "14092", "14774", "14707", "14057", "14305", "14085", "14303", "14786", "14301", "14174", "14144", "14715", "14111", "14047", "14708", "14743", "14729", "14035", "14112", "14091", "14034", "14731", "14754", "14006", "14778", "14766", "14741", "14070", "14788", "14027", "14168", "14770", "14760", "14721", "14061", "14081", "14706", "14129", "14041", "14755", "14719", "14748", "14136", "14779", "14753", "14138", "14751", "14062", "14135", "14726", "14730", "14723", "14772", "14048", "14732", "14783", "14063", "14740", "14718", "14747", "14166", "14752", "14782", "14716", "14784", "14769", "14758", "14733", "14738", "14701", "14728", "14720", "14742", "14756", "14712", "14702", "14750", "14757", "147878", "14722", "14785", "14710", "14781", "14767", "14775", "14736", "14724", "18840", "18446", "18657", "16901"}
        Dim index As Integer
        Dim item As String
        Dim zone() As String
        Dim iDate, wDD As Date
        If zon1.Checked = True Then
            zone = zone1
        End If
        If zon2.Checked = True Then
            zone = zone2
        End If
        If zon3.Checked = True Then
            zone = zone3
        End If
               For Each item In zone
 
            OleDbConnection2.Open()  
            OleDbCommand2.Parameters("ShipToZipCode").Value() = item
 
 
            dreader = OleDbCommand2.ExecuteReader
            Do While dreader.Read
                wDD = dreader(0).ToShortDateString  ''keep
                sTN = dreader(1).ToString()  ''keep
                sTA = dreader(2).ToString()  ''keep
                sTC = dreader(3).ToString()  ''keep
                sTZC = dreader(4).ToString()  ''keep
                bTN = dreader(5).ToString()  ''keep
                sW = dreader(6).ToString()  ''keep
                sON = dreader(7).ToString() ''keep
                sMOD = dreader(8).ToString()  ''keep
                          
                iDate = tDate.Text ''keep
                If sMOD = "WBC FREIGHT    " And iDate >= wDD Or sMOD = "PICK UP        " And iDate >= wDD Then ''keep" Then
                    ListBox1.Items.Add(wDD + " " + sTN + " " + sTA + " " + sTC + " " + sTZC + " " + bTN + " " + sMOD + " " + sON + "  " + sW)                       ''keep
                End If  ''keep
            Loop
            dreader.Close()  ''keep
            OleDbConnection2.Close()  ''keep
        Next
  End Sub

Open in new window

0
Comment
Question by:Fingerlakesconst
  • 4
  • 2
6 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 23659494
#1, you are opening and closing the connection to the db for each zone. This is a costly procedure.  Why not open it once, outside of any loop and close it when finished with everything?

#2, ListBox1, has to refresh the list for each item you add. Most such items have a .BeginUpdate and .EndUpdate method.  BeginUpdate means don't refresh, I am adding items. Put this before you start the loops. .EndUpdate means I am done adding, so let the list refresh. Put this at the bottom--outside of any loops.

You may want to do
me.cursor=WaitCursor 'at the top

and
me.cursor=Default 'at the bottom
0
 

Author Comment

by:Fingerlakesconst
ID: 23659618
Hi SStory

Thanks for your quick response.

I had attempted to put the opening & closing outside of the loop thinking that should speed things up too, before the FOR and after the NEXT, but I got the following error:

"Additional information: ExecuteReader requires an open and available Connection. The connection's current state is Closed."

So I abandoned that idea.  Perhaps I was putting it in the wrong place outside of the loop.
0
 
LVL 25

Accepted Solution

by:
SStory earned 2000 total points
ID: 23660273
OleDbConnection2.Open()
       For Each item In zone
            OleDbCommand2.Parameters("ShipToZipCode").Value() = item
            dreader = OleDbCommand2.ExecuteReader
            .
            .
            .
            Do While ...
                      .
                      .
                      .
                     dreader.Close()  ''keep
            Loop
       Next
       OleDbConnection2.Close


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:SStory
ID: 23660583
Another thought is, can you somehow build a comma separated string of all the zipcodes you need before executing the query.  Then pass those in as a parameter to the storeprocedure (I assume)...

Inside that procedure you'd use the incoming param as part of an IN(<param here>) clause

That way, there would only be one call to the db to get all related records.... no opening and closing multiple times. You'd only loop through once.
Another problem is your string concatenation is creating zillions of little strings since every + creates another immutable string.  It would better to declare this at the top of the module:
dim sb as system.text.stringbuilder
Before the concatenation begins, do
sb=new system.text.stringbuilder
sb.append(wDD)
sb.append(sTn)
.
.
.


then when you want to add to the list item add it as sb.tostring to get the actual string
0
 

Author Comment

by:Fingerlakesconst
ID: 23660620
Following your instructions, I received the following error:

"Additional information: ExecuteReader requires an open and available Connection. The connection's current state is Open, Fetching."

So, after stepping through I found that the following works much better (& w/no errors):

OleDbConnection2.Open()
       For Each item In zone
            OleDbCommand2.Parameters("ShipToZipCode").Value() = item
            dreader = OleDbCommand2.ExecuteReader
            .
            .
            .
            Do While ...
                      .
                      .
                      .
                      .
            Loop
                      dreader.Close()  ''keep
       Next
       OleDbConnection2.Close

Thanks for your help!  Points awarded as deserved!
0
 
LVL 25

Expert Comment

by:SStory
ID: 23661656
Well, yes, I accidentally got the .Close of the datareader in the wrong place.  Of course it must remain open while in use.

The other thing I suggested would probably greatly speed it up (hit the database once with a list of Zipcodes to get back exactly what you need once.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question