I was very excited to try the Remote Database Connector. I have been in the process of writing my own way to move data back and forth from tablets so I abandoned my little MS Access server that was moving files around (Hey! It's what I know) got RDC running, and rewrote my code to use it.
Unfortunately, while the RDC is blazingly fast, DBRequestManager seems to be extremely slow to move the data in to my local SQL database.
The way I did it I just wrote all the data to a delimited ASCII file (sample below).
I then parsed it out and wrote it to SQL using this:
It will insert 6000-8000 records in the SQL database in LESS than 7 seconds.
I really like GETTING the data with the RDC, unfortunately when I write the data to my local SQL lite as above it takes to 2.5 MINUTES! A portion of code for that is below.
Can anyone see any way to speed this up? It is hard for me to believe that my little routine for parsing a file (the whole file is about 500,000 bytes) can run 20 times faster than this. The SQLLite Statements are virtually identical evceppt for the source data, and yes I am sure the data coming back is fast. It is virtually instant.
Any ideas greatly appreciated.
Unfortunately, while the RDC is blazingly fast, DBRequestManager seems to be extremely slow to move the data in to my local SQL database.
The way I did it I just wrote all the data to a delimited ASCII file (sample below).
B4X:
016®ËChris®ËPak®Ë0®Ë0®ËÙé1001®ËJae Hyun®ËJi®Ë0®Ë0®ËÙé1002®ËDong Sik®ËLee®Ë0®Ë0®ËÙé1003®ËJung Min®ËNo®Ë0®Ë0®ËÙé1004®ËYung Chul®ËKim®Ë0®Ë0®ËÙé1005®ËHyun Kyu®ËCho®Ë0®Ë0®ËÙé1006®ËJingj In®ËPark®Ë0®Ë0®ËÙé1007®ËJea Wan®ËJang®Ë0®Ë0®ËÙé1008®ËSung Gun®ËLee®Ë0®Ë0®ËÙé1009®ËCHUN TAE®ËKIM®Ë0®Ë0®ËÙé1010®ËDUK HEE®ËYOON®Ë0®Ë0®ËÙé1011®ËSUN HO®ËKIM®Ë0®Ë0®ËÙé1012®ËCHUN GWON®ËSEO®Ë0®Ë0®ËÙé1013®ËSUNG HWAN®ËBANG®Ë0®Ë0®ËÙé1014®ËDAE YOUNG®ËLEE®Ë0®Ë0®ËÙé1015®ËNAE JUN®ËPARK®Ë0®Ë0®ËÙé1019®ËKI SEOK®ËPYO®Ë0®Ë0®ËÙé1020®ËHYUN SU®ËNO®Ë0®Ë0®ËÙé1021®ËKI CHUN®ËLEE®Ë0®Ë0®ËÙé1022®ËSUNG GUN®ËJO®Ë0®Ë0®ËÙé1023®ËSUNG OH®ËLEE®Ë0®Ë0®ËÙé1024®ËYOUNG HOON®ËKANG®Ë0®Ë0®ËÙé1025®ËYEON PYO®ËHONG®Ë0®Ë0®ËÙé1026®ËDONG UK®ËKIM®Ë0®Ë0®ËÙé1027®ËSANG HYUN®ËJUNG®Ë0®Ë0®ËÙé1028®ËJUNG MIN®ËKIM®Ë0®Ë0®ËÙé1029®ËJUNG SU®ËKIM®Ë0®Ë0®ËÙé1030®ËKYUNG YEOL®ËLEE®Ë0®Ë0®ËÙé1031®ËHYUK JIN®ËJUNG®Ë0®Ë0®ËÙé1032®ËHYO SUNG®ËKIM®Ë0®Ë0®ËÙé1033®ËAR ON®ËKIM®Ë0®Ë0®ËÙé1034®ËOH SANG®ËLEEM®Ë0®Ë0®ËÙé1035®ËKI HOON®ËSUNG®Ë0®Ë0®ËÙé1036®ËYOUNG SIK®ËYOO®Ë0®Ë0®ËÙé1037®ËTAEK SU®ËJUNG®Ë0®Ë0®ËÙé1038®ËYON HHA®ËKIM®Ë0®Ë0®ËÙé1039®ËHO YEON®ËAN®Ë0®Ë0®ËÙé1040®ËSUK CHAE®ËPARK®Ë0®Ë0®ËÙé1041®ËSUNG SU®ËPARK®Ë0®Ë0®ËÙé1042®ËKYUNG HOON®ËJUN®Ë0®Ë0®ËÙé1043®ËSeong Keum®ËLee®Ë0®Ë0®ËÙé1044®ËNa-Yean®ËLee®Ë0®Ë0®ËÙé11®ËMujo®ËFazlic®Ë0®Ë0®ËÙé12®ËEdgar®ËAgustin®Ë0®Ë0®ËÙé123®ËNoel C.®ËLopez®Ë0®Ë0®ËÙé156®ËAntonio R®ËSandoval®Ë0®Ë0®ËÙé16®ËJose D®ËGuerrero®Ë0®Ë0®ËÙé167®ËTony P.®ËDo®Ë0®Ë0®ËÙé173®ËDien Minh®ËTran®Ë0®Ë0®ËÙé184®ËAlfredo®ËRuelas®Ë0®Ë0®ËÙé187®ËFelipe®ËRuelas®Ë0®Ë0®ËÙé236®ËChuong Q.®ËTran®Ë0®Ë0®ËÙé24®ËApolonio C.®ËYera®Ë0®Ë0®ËÙé242®ËKinh V.®ËBui®Ë0®Ë0®ËÙé246®ËThanh Q.®ËNguyen®Ë0®Ë0®ËÙé25®ËMiguel®ËYera®Ë0®Ë0®ËÙé253®ËNam®ËMai®Ë0®Ë0®ËÙé266®ËGuillermo®ËValdes-Zapata®Ë0®Ë0®ËÙé27®ËChin Hyong®ËSo®Ë0®Ë0®ËÙé281®ËPerry®ËNguyen®Ë0®Ë0®ËÙé282®ËSavino®ËDel Villar®Ë0®Ë0®ËÙé298®ËFredeswindo M®ËKuizon®Ë0®Ë0®ËÙé312®ËTommy®ËVo®Ë0®Ë0®ËÙé320®ËChris®ËKim®Ë0®Ë0®ËÙé323®ËLuis®ËTorres®Ë0®Ë0®ËÙé329®ËFulgencio M.®ËSarmento®Ë0®Ë0®ËÙé349®ËGerman®ËVargas®Ë0®Ë0®ËÙé353®ËHenry®ËWilliams®Ë0®Ë0®ËÙé367®ËManuel®ËTorres®Ë0®Ë0®ËÙé370®ËHoang V.®ËMai®Ë0®Ë0®ËÙé380®ËThanh N.®ËNguyen®Ë0®Ë0®ËÙé394®ËLirio®ËSabado®Ë0®Ë0®ËÙé395®ËDaniel J.®ËWormuth®Ë0®Ë0®ËÙé402®ËEdwin W®ËCaoili®Ë0®Ë0®ËÙé403®ËLuis®ËVargas®Ë0®Ë0®ËÙé437®ËPatricia®ËKD®Ë0®Ë0®ËÙé445®ËSung Cheol®ËCho®Ë0®Ë0®ËÙé469®ËJose D.®ËVargas®Ë0®Ë0®ËÙé485®ËOsbaldo®ËPerez®Ë0®Ë0®ËÙé492®ËMiguel V®ËSanchez®Ë0®Ë0®ËÙé493®ËDavid®ËSanchez®Ë0®Ë0®ËÙé494®ËJon Paul®ËReed®Ë0®Ë0®ËÙé495®ËAntonio®ËMadriz®Ë0®Ë0®ËÙé497®ËDaniel L®ËDuran®Ë0®Ë0®ËÙé498®ËEnrique®ËAlonzo®Ë0®Ë0®ËÙé499®ËHeliodoro®ËMadriz®Ë0®Ë0®ËÙé500®ËDaniel 11®ËDuran®Ë0®Ë0®ËÙé501®ËPhung Minh®ËPhan®Ë0®Ë0®ËÙé503®ËAlfonso®ËVargas®Ë0®Ë0®ËÙé546®ËRamon®ËHerrera®Ë0®Ë0®ËÙé567®ËJesus®ËPerez®Ë0®Ë0®ËÙé57®ËYong Kil®ËPak®Ë0®Ë0®ËÙé58®ËYong Su®ËPak®Ë0®Ë0®ËÙé60®ËChin Ho®ËSo®Ë0®Ë0®ËÙé61®ËYoung K®ËCho®Ë0®Ë0®ËÙé619®ËNhu®ËNguyen®Ë0®Ë0®ËÙé631®ËOmar®ËPerez®Ë0®Ë0®ËÙé637®ËEddy Rene®ËBarrios®Ë0®Ë0®ËÙé638®ËJorge R.®ËBarrios®Ë0®Ë0®ËÙé648®ËJannet V®ËBuenviaje®Ë0®Ë0®ËÙé654®ËRoberto P.®ËPichardo®Ë0®Ë0®ËÙé667®ËAguedo A®ËGuanlao Jr®Ë0®Ë0®ËÙé674®ËDomingo®ËGuzman®Ë0®Ë0®ËÙé692®ËAndres®ËFlores®Ë0®Ë0®ËÙé7®ËThong®ËNguyen®Ë0®Ë0®ËÙé711®ËHoracio®ËMartinez®Ë0®Ë0®ËÙé740®ËHan R®ËLee®Ë0®Ë0®ËÙé759®ËOmar®ËGuzman®Ë0®Ë0®ËÙé76®ËYuh T.®ËYoon®Ë0®Ë0®ËÙé767®ËMarcelino®ËBailon®Ë0®Ë0®ËÙé788®ËEr Kun®ËWu®Ë0®Ë0®ËÙé8®ËHugo F.®ËCastillo®Ë0®Ë0®ËÙé802®ËMaxino®ËCrisostorno®Ë0®Ë0®ËÙé809®ËCarlos A.®ËDel Villar®Ë0®Ë0®ËÙé816®ËMyung Shin®ËKang®Ë0®Ë0®ËÙé821®ËJose Javier®ËDel Villar®Ë0®Ë0®ËÙé822®ËMoreno Eduardo®ËJimenez®Ë0®Ë0®ËÙé825®ËHieu®ËPham®Ë0®Ë0®ËÙé826®ËViet®ËNguyen®Ë0®Ë0®ËÙé828®ËJohn®ËFarkas®Ë0®Ë0®ËÙé829®ËPete®ËCorrea®Ë0®Ë0®ËÙé830®ËKevin®ËRainey®Ë0®Ë0®ËÙé89®ËHung V.®ËTran®Ë0®Ë0®ËÙéAKT1®ËTAE SU®ËAN®Ë0®Ë0®ËÙéAKT2®ËHOJ IN®ËSHIN®Ë0®Ë0®ËÙéAKT3®ËGANG JUN®ËNO®Ë0®Ë0®ËÙ陧1144-011®Ë1144-01®Ë1®Ë02/04/14®Ë02/14/14®Ë®Ë205®Ë0®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0020-73871.bmp®ËÙé183261®Ë18326®Ë1®Ë11/14/13®Ë11/21/13®Ë®Ë70®Ë70®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0023-71584.bmp®ËÙé183751®Ë18375®Ë1®Ë01/29/14®Ë02/04/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0244-71124.bmp®ËÙé183761®Ë18376®Ë1®Ë12/24/13®Ë01/06/14®Ë®Ë6®Ë6®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0023-72561.bmp®ËÙé183771®Ë18377®Ë1®Ë12/24/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0022-72399.bmp®ËÙé183781®Ë18378®Ë1®Ë12/24/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0022-72397.bmp®ËÙé183791®Ë18379®Ë1®Ë12/26/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0041-73262.bmp®ËÙé183801®Ë18380®Ë1®Ë11/18/13®Ë01/06/14®Ë®Ë2®Ë2®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0041-73314.bmp®ËÙé183811®Ë18381®Ë1®Ë11/18/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0041-73264.bmp®ËÙé183821®Ë18382®Ë1®Ë11/18/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-71240.bmp®ËÙé183831®Ë18383®Ë1®Ë11/18/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0024-73296.bmp®ËÙé183841®Ë18384®Ë1®Ë12/24/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0024-71915.bmp®ËÙé183851®Ë18385®Ë1®Ë12/24/13®Ë01/06/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0024-71916.bmp®ËÙé183861®Ë18386®Ë1®Ë11/18/13®Ë01/06/14®Ë®Ë96®Ë96®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0190-71607.bmp®ËÙé183871®Ë18387®Ë1®Ë11/18/13®Ë01/06/14®Ë®Ë2®Ë2®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-71242.bmp®ËÙé184001®Ë18400®Ë1®Ë11/20/13®Ë12/05/13®Ë®Ë10®Ë7®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0015-71910.bmp®ËÙé185291®Ë18529®Ë1®Ë12/04/13®Ë12/12/13®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0015-73581.bmp®ËÙé185341®Ë18534®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë6®Ë6®Ë0®Ë®Ë ®ËÙé185351®Ë18535®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë18®Ë18®Ë0®Ë®Ë ®ËÙé185361®Ë18536®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé185371®Ë18537®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë6®Ë6®Ë0®Ë®Ë ®ËÙé185381®Ë18538®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë6®Ë6®Ë0®Ë®Ë ®ËÙé185391®Ë18539®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë22®Ë22®Ë0®Ë®Ë ®ËÙé185401®Ë18540®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë4®Ë4®Ë0®Ë®Ë ®ËÙé185411®Ë18541®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé185421®Ë18542®Ë1®Ë12/05/13®Ë12/06/13®Ë®Ë4®Ë4®Ë0®Ë®Ë ®ËÙé185461®Ë18546®Ë1®Ë12/05/13®Ë12/12/13®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0040-74917.bmp®ËÙé186081®Ë18608®Ë1®Ë12/13/13®Ë12/20/13®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé186241®Ë18624®Ë1®Ë01/29/14®Ë02/07/14®Ë®Ë240®Ë240®Ë0®Ë®Ë ®ËÙé186251®Ë18625®Ë1®Ë01/22/14®Ë02/07/14®Ë®Ë240®Ë240®Ë0®Ë®Ë ®ËÙé186261®Ë18626®Ë1®Ë01/31/14®Ë02/07/14®Ë®Ë240®Ë240®Ë0®Ë®Ë ®ËÙé186271®Ë18627®Ë1®Ë04/04/14®Ë04/14/14®Ë®Ë180®Ë180®Ë0®Ë®Ë ®ËÙé18628-0011®Ë18628-001®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0020-67305.bmp®ËÙé18628-0021®Ë18628-002®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-72206.bmp®ËÙé18628-0031®Ë18628-003®Ë1®Ë03/11/14®Ë03/24/14®Ë®Ë2®Ë2®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-72247.bmp®ËÙé18628-0041®Ë18628-004®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-72729.bmp®ËÙé18628-0051®Ë18628-005®Ë1®Ë03/12/14®Ë03/24/14®Ë®Ë2®Ë2®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-74532.bmp®ËÙé18628-0061®Ë18628-006®Ë1®Ë03/10/14®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-74611.bmp®ËÙé18628-0071®Ë18628-007®Ë1®Ë03/10/14®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-74612.bmp®ËÙé186281®Ë18628®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0243-74254.bmp®ËÙé18629-0011®Ë18629-001®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0020-67305.bmp®ËÙé18629-0021®Ë18629-002®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-72206.bmp®ËÙé18629-0031®Ë18629-003®Ë1®Ë03/11/14®Ë03/24/14®Ë®Ë2®Ë2®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-72247.bmp®ËÙé18629-0041®Ë18629-004®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-72729.bmp®ËÙé18629-0051®Ë18629-005®Ë1®Ë03/12/14®Ë03/24/14®Ë®Ë2®Ë2®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-74532.bmp®ËÙé18629-0061®Ë18629-006®Ë1®Ë03/10/14®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-74611.bmp®ËÙé18629-0071®Ë18629-007®Ë1®Ë03/10/14®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-74612.bmp®ËÙé186291®Ë18629®Ë1®Ë12/17/13®Ë03/24/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0243-74255.bmp®ËÙé186441®Ë18644®Ë1®Ë12/18/13®Ë01/15/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0270-72544.bmp®ËÙé186451®Ë18645®Ë1®Ë12/18/13®Ë01/15/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0270-72561.bmp®ËÙé186621®Ë18662®Ë1®Ë12/23/13®Ë12/26/13®Ë®Ë30®Ë30®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0025-73090.bmp®ËÙé187111®Ë18711®Ë1®Ë01/02/14®Ë02/03/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0270-72560.bmp®ËÙé187631®Ë18763®Ë1®Ë01/13/14®Ë04/03/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé187641®Ë18764®Ë1®Ë01/13/14®Ë04/03/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé188241®Ë18824®Ë1®Ë01/23/14®Ë01/30/14®Ë®Ë1®Ë1®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0021-71008.bmp®ËÙé18830-0011®Ë18830-001®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0021®Ë18830-002®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0031®Ë18830-003®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®ËS:\PICS\AKT 0023-74164.bmp®ËÙé18830-0041®Ë18830-004®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0051®Ë18830-005®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0061®Ë18830-006®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0071®Ë18830-007®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0081®Ë18830-008®Ë1®Ë01/24/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0091®Ë18830-009®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0101®Ë18830-010®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0111®Ë18830-011®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë4®Ë4®Ë0®Ë®Ë ®ËÙé18830-0121®Ë18830-012®Ë1®Ë01/24/14®Ë03/14/14®Ë®Ë4®Ë4®Ë0®Ë®Ë ®ËÙé18830-0131®Ë18830-013®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë8®Ë8®Ë0®Ë®Ë ®ËÙé18830-0141®Ë18830-014®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé18830-0151®Ë18830-015®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0161®Ë18830-016®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0171®Ë18830-017®Ë1®Ë03/04/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0181®Ë18830-018®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0191®Ë18830-019®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0201®Ë18830-020®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0211®Ë18830-021®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0221®Ë18830-022®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0231®Ë18830-023®Ë1®Ë03/04/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0241®Ë18830-024®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0251®Ë18830-025®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0261®Ë18830-026®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0271®Ë18830-027®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0281®Ë18830-028®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0291®Ë18830-029®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0301®Ë18830-030®Ë1®Ë01/24/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0311®Ë18830-031®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0321®Ë18830-032®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0331®Ë18830-033®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0341®Ë18830-034®Ë1®Ë01/24/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0351®Ë18830-035®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0361®Ë18830-036®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0371®Ë18830-037®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0381®Ë18830-038®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0391®Ë18830-039®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0401®Ë18830-040®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0411®Ë18830-041®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0421®Ë18830-042®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0431®Ë18830-043®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0441®Ë18830-044®Ë1®Ë01/24/14®Ë03/14/14®Ë®Ë4®Ë4®Ë0®Ë®Ë ®ËÙé18830-0451®Ë18830-045®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë8®Ë8®Ë0®Ë®Ë ®ËÙé18830-0461®Ë18830-046®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé18830-0471®Ë18830-047®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë4®Ë4®Ë0®Ë®Ë ®ËÙé18830-0481®Ë18830-048®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0491®Ë18830-049®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0501®Ë18830-050®Ë1®Ë03/04/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0511®Ë18830-051®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0521®Ë18830-052®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0531®Ë18830-053®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0541®Ë18830-054®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0551®Ë18830-055®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0561®Ë18830-056®Ë1®Ë01/24/14®Ë03/14/14®Ë®Ë10®Ë4®Ë0®Ë®Ë ®ËÙé18830-0571®Ë18830-057®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0581®Ë18830-058®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0591®Ë18830-059®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0601®Ë18830-060®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0611®Ë18830-061®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0621®Ë18830-062®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0631®Ë18830-063®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0641®Ë18830-064®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé18830-0651®Ë18830-065®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé18830-0661®Ë18830-066®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé18830-0671®Ë18830-067®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0681®Ë18830-068®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0691®Ë18830-069®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0701®Ë18830-070®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë132®Ë132®Ë0®Ë®ËS:\PICS\AKT 0041-73976.bmp®ËÙé18830-0711®Ë18830-071®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0721®Ë18830-072®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0731®Ë18830-073®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®ËS:\PICS\AKT 0021-71759.bmp®ËÙé18830-0741®Ë18830-074®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®Ë ®ËÙé18830-0751®Ë18830-075®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0761®Ë18830-076®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0771®Ë18830-077®Ë1®Ë03/05/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®ËS:\PICS\AKT 0026-71395.bmp®ËÙé18830-0781®Ë18830-078®Ë1®Ë03/04/14®Ë03/14/14®Ë®Ë2®Ë2®Ë0®Ë®Ë\\A1SERVER01\Shopkeeper Docs\PICS\AKT 0026-71396.bmp®ËÙé18830-0791®Ë18830-079®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0801®Ë18830-080®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0811®Ë18830-081®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0821®Ë18830-082®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0831®Ë18830-083®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0841®Ë18830-084®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0851®Ë18830-085®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0861®Ë18830-086®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0871®Ë18830-087®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0881®Ë18830-088®Ë1®Ë04/17/14®Ë05/09/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0891®Ë18830-089®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë20®Ë20®Ë0®Ë®ËS:\PICS\AKT 0015-71062.bmp®ËÙé18830-0901®Ë18830-090®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®ËS:\PICS\AKT 0021-74628.bmp®ËÙé18830-0911®Ë18830-091®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®ËS:\PICS\AKT 0021-74631.bmp®ËÙé18830-0921®Ë18830-092®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®ËS:\PICS\AKT 0021-74632.bmp®ËÙé18830-0931®Ë18830-093®Ë1®Ë02/28/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®Ë ®ËÙé18830-0941®Ë18830-094®Ë1®Ë03/14/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®ËS:\PICS\AKT 0021-74634.bmp®ËÙé18830-0951®Ë18830-095®Ë1®Ë02/28/14®Ë03/14/14®Ë®Ë1®Ë1®Ë0®Ë®ËS:\PICS\AKT 0021-74635.bmp®ËÙé18830-0961®Ë18830-096®Ë1®Ë02/28/14®Ë03/14/14®Ë®Ë3®Ë3®Ë0®Ë®Ë\\A1SERI then parsed it out and wrote it to SQL using this:
B4X:
Sub GetData
DimTableSepAsString' : TableSep = "!T&B%L!" 'SPECIAL CHARACTERS DO NOT USE! \ ^ ${ } [ ] ( ) . + ? | - &
DimRecordSepAsString' : RecordSep = "!R&E%C!" ' \ is the escape char and the char after it is the literal
DimFieldSepAsString' : FieldSep = "!F&L%D!"
TableSep =Chr(153) & Chr(167) ' ™§
RecordSep =Chr(217) & Chr(233) ' Ùé
FieldSep =Chr(174) & Chr(203) '®Ë
Dim Tbls() As String, Rec() As String, fld() As String, RecX As Int, TblX As Long, Ftxt As String ', FldX As Long, Test As Long
Dim TR As TextReader, In As InputStream
Try
In = File.OpenInput(File.DirInternal, "PartsAndTime")
TR.Initialize2(In, "ISO-8859-1")
Ftxt = TR.ReadAll
In.Close
If Main.SQL1.IsInitialized = False Then Main.SQL1.Initialize(File.DirInternal, "PartsAndTime.db", False)
Main.SQL1.ExecNonQuery("DELETE FROM EmpInfoLookups;")
Main.SQL1.ExecNonQuery("DELETE FROM ReleaseLookups;")
Main.SQL1.ExecNonQuery("DELETE FROM OPsLookups;")
Main.SQL1.ExecNonQuery("DELETE FROM JobsLookups;")
Main.SQL1.ExecNonQuery("DELETE FROM ScheduledStuff;")
Main.SQL1.BeginTransaction
Tbls=Regex.Split(TableSep, Ftxt)
TblX=0
Rec=Regex.Split(RecordSep,Tbls(TblX))
For RecX = 0 To Rec.Length - 1
fld=Regex.Split(FieldSep,Rec(RecX))
Main.SQL1.ExecNonQuery2("INSERT INTO EmpInfoLookups VALUES(?, ?, ?, ?, ?)", Array As Object(fld(0), fld(1), fld(2), fld(3), fld(4) ) )
Next
TblX=1
Rec=Regex.Split(RecordSep,Tbls(TblX))
For RecX = 0 To Rec.Length - 1
fld=Regex.Split(FieldSep,Rec(RecX))
Main.SQL1.ExecNonQuery2("INSERT INTO ReleaseLookups VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As Object(fld(0), fld(1), fld(2), fld(3), fld(4), fld(5), fld(6), fld(7), fld(8), fld(9), fld(10) ) )
Next
TblX=2
Rec=Regex.Split(RecordSep,Tbls(TblX))
For RecX = 0 To Rec.Length - 1
fld=Regex.Split(FieldSep,Rec(RecX))
Main.SQL1.ExecNonQuery2("INSERT INTO OPsLookups VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As Object(fld(0), fld(1), fld(2), fld(3), fld(4), fld(5), fld(6), fld(7), fld(8), fld(9), fld(10), fld(11), fld(12) ) )
Next
TblX=3
Rec=Regex.Split(RecordSep,Tbls(TblX))
For RecX = 0 To Rec.Length - 1
fld=Regex.Split(FieldSep,Rec(RecX))
Main.SQL1.ExecNonQuery2("INSERT INTO JobsLookups VALUES(?, ?, ?)", Array As Object(fld(0), fld(1), fld(2) ) )
Next
TblX=4
Rec=Regex.Split(RecordSep,Tbls(TblX))
For RecX = 0 To Rec.Length - 1
fld=Regex.Split(FieldSep,Rec(RecX))
Main.SQL1.ExecNonQuery2("INSERT INTO ScheduledStuff VALUES(?, ?)", Array As Object(fld(0), fld(1) ) )
Next
Main.SQL1.TransactionSuccessful
Main.SQL1.EndTransaction
ToastMessageShow("Local Data Updated", False)
' UpdateActiveJobsData.LastDataUpdate = DateTime.Now
' UpdateActiveJobsData.WORKING = False
Catch
' UpdateActiveJobsData.WORKING = False
ToastMessageShow("Your Data was downloaded but there was an error updating the database!" & CRLF & LastException.Message, "")
End Try
End SubIt will insert 6000-8000 records in the SQL database in LESS than 7 seconds.
I really like GETTING the data with the RDC, unfortunately when I write the data to my local SQL lite as above it takes to 2.5 MINUTES! A portion of code for that is below.
B4X:
Sub JobDone(Job As HttpJob)
Dim X As Long
Dim cmd As DBCommand
Try
If Job.Success = False Then
ToastMessageShow("Error: " & Job.ErrorMessage,True)
Msgbox(Job.ErrorMessage,"")
Else
'DisplayData.Text=""
If Job.JobName = "DBRequest" Then
Dim result As DBResult = reqManager2.HandleJob(Job)
If result.Tag = "GetEmployees" Then 'query tag
For Each records() As Object In result.Rows
'Main.SQL1.ExecNonQuery2( "INSERT INTO EmpInfoLookups VALUES(?, ?, ?, ?, ?)", Array As List(records(result.Columns.Get("Emp_Num")), records(result.Columns.Get("Emp_FirstName")), records(result.Columns.Get("Emp_LastName")), records(result.Columns.Get("Emp_AllowEdits")), records(result.Columns.Get("Emp_Rate")) ))
Main.SQL1.ExecNonQuery2( "INSERT INTO EmpInfoLookups VALUES(?, ?, ?, ?, ?)", records) 'Array As List(records(result.Columns.Get("Emp_Num")), records(result.Columns.Get("Emp_FirstName")), records(result.Columns.Get("Emp_LastName")), records(result.Columns.Get("Emp_AllowEdits")), records(result.Columns.Get("Emp_Rate")) ))
Next
ToastMessageShow(result.Rows.Size & " Employees added", False)
cmd.Initialize
cmd.Name = "ReleaseLookups"
reqManager2.ExecuteQuery(cmd, 0, "ReleaseLookups")
Else If result.Tag = "ReleaseLookups" Then 'query tag
For Each records() As Object In result.Rows
X = X + 1: If X Mod 300 = 0 Then ToastMessageShow(X & " of " & result.Rows.Size & " Jobs/Releases added...", False): DoEvents
Try 'THis is to catch duplicate records error caused by more than one picc set to Print on trav
Main.SQL1.ExecNonQuery2( "INSERT INTO ReleaseLookups VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", records)
Catch
If LastException.Message.Contains("JobRel is not unique") Then
Else
Msgbox("Error trying to Insert data in table " & result.Tag & "." & CRLF & LastException.Message,"IPISetup: RefreshdataBtn_Click")
RefreshdataBtn.Enabled = True
Return
End If
End Try
Next
ToastMessageShow(result.Rows.Size & " Jobs/Releases added", False): DoEvents
cmd.Initialize
cmd.Name = "OpsLookups"
reqManager2.ExecuteQuery(cmd, 0, "OpsLookups")
Else If result.Tag = "OpsLookups" Then 'query tag
For Each records() As Object In result.Rows
X = X + 1: If X Mod 300 = 0 Then ToastMessageShow(X & " of " & result.Rows.Size & " OPs added...", False): DoEvents
Main.SQL1.ExecNonQuery2("INSERT INTO OPsLookups VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", records)
Next
ToastMessageShow(result.Rows.Size & " Ops added", False): DoEvents
cmd.Initialize
cmd.Name = "JobsLookups"
reqManager2.ExecuteQuery(cmd, 0, "JobsLookups")
Else If result.Tag = "JobsLookups" Then 'query tag
For Each records() As Object In result.Rows
X = X + 1: If X Mod 300 = 0 Then ToastMessageShow(X & " of " & result.Rows.Size & " Jobs added...", False): DoEvents
Main.SQL1.ExecNonQuery2("INSERT INTO JobsLookups VALUES(?, ?, ?)", records)
Next
ToastMessageShow(result.Rows.Size & " Jobs added", False): DoEvents
cmd.Initialize
cmd.Name = "ScheduledStuff"
reqManager2.ExecuteQuery(cmd, 0, "ScheduledStuff")
Else If result.Tag = "ScheduledStuff" Then 'query tag
For Each records() As Object In result.Rows
X = X + 1: If X Mod 300 = 0 Then ToastMessageShow(X & " of " & result.Rows.Size & " Scheduling Info added...", False): DoEvents
Main.SQL1.ExecNonQuery2("INSERT INTO ScheduledStuff VALUES(?, ?)", records)
Next
ToastMessageShow(result.Rows.Size & " Scheduling Info added", False): DoEvents
Msgbox("Your Data was updated in " & ((DateTime.Now-RefreshCntr)/DateTime.TicksPerSecond) & " Seconds.", "")
RefreshdataBtn.Enabled = True
End If
End If
End If
Job.Release
Catch
Msgbox("Error trying to Insert data in table " & result.Tag & "." & CRLF & LastException.Message,"IPISetup: RefreshdataBtn_Click")
RefreshdataBtn.Enabled = True
End Try
End SubCan anyone see any way to speed this up? It is hard for me to believe that my little routine for parsing a file (the whole file is about 500,000 bytes) can run 20 times faster than this. The SQLLite Statements are virtually identical evceppt for the source data, and yes I am sure the data coming back is fast. It is virtually instant.
Any ideas greatly appreciated.
