Increase program speed

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

FingerlakesconstAsked:
Who is Participating?
 
SStoryCommented:
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
 
SStoryCommented:
#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
 
FingerlakesconstAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
SStoryCommented:
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
 
FingerlakesconstAuthor Commented:
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
 
SStoryCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.