We help IT Professionals succeed at work.

Increase program speed

Fingerlakesconst
on
Medium Priority
220 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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
#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

Author

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.
CERTIFIED EXPERT
Commented:
OleDbConnection2.Open()
       For Each item In zone
            OleDbCommand2.Parameters("ShipToZipCode").Value() = item
            dreader = OleDbCommand2.ExecuteReader
            .
            .
            .
            Do While ...
                      .
                      .
                      .
                     dreader.Close()  ''keep
            Loop
       Next
       OleDbConnection2.Close


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

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

Author

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!
CERTIFIED EXPERT

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.