Slow Data Transfer From Recordset To Named-Datasource Follow
Hi,
I read several csv files using the ADODB and the Microsoft Jet OLEDB driver in vbscript. Establishing the connection and performing an SQL query to a recordset is very fast. But moving the elements from the recordset to named datasources is very slow. I have about 25 "moves" which take about 4-5 seconds. Here is an example:
Format.NamedSubStrings("Mail").Value = rs("E-Mail").value
Am I doing anything wrong here or how can I improve the performance?
BR,
Konrad
6 comments

Legacy Poster
4-5 seconds each? Seems like something's not right...
No, not each. 4-5 seconds for all 25 moves. But in my opinion this is also very slow. I'm talking about "simple moves". It seems that "named datasources" are generally slow.
I agree that 4-5 secs is still much too slow... but I guess you'd have to provide your code/file for people to be able to find problems with it.
Here is the code 'Allgemeine Unterroutinen, Funktionen und Variablen können hier definiert werden, um 'von anderen Ereignissen verwendet zu werden. Option Explicit Public Function CSVConnection() dim objConn dim strPath 'Path to CSV file strPath = "K:\Path\BarTender\" 'Create connection Set objConn = CreateObject("ADODB.Connection") objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=YES;FMT=Delimited;CharacterSet=65001"";" objConn.Open Set CSVConnection = objConn End Function Public Function printLabel() dim conn dim rs dim sql Set conn = CSVConnection() Set rs = CreateObject("ADODB.Recordset") 'epos_deu.csv sql = "SELECT * FROM epos_deu.csv WHERE Materialnummer = '" & Format.NamedSubStrings("Artikelnummer").Value & "'" Set rs = conn.Execute(sql) If Not rs.EOF And Not rs.BOF Then If Not IsNull(rs("Gefahr_Komponenten")) then Format.NamedSubStrings("Gefahrstoff").Value = rs("Gefahr_Komponenten") else Format.NamedSubStrings("Gefahrstoff").Value = "" end if If Not IsNull(rs("CAS_Nummer")) then Format.NamedSubStrings("CAS_Nummer").Value = rs("CAS_Nummer") else Format.NamedSubStrings("CAS_Nummer").Value = "" end if If Not IsNull(rs("Index_Nummer")) then Format.NamedSubStrings("Index_Nummer").Value = rs("Index_Nummer") else Format.NamedSubStrings("Index_Nummer").Value = "" end if If Not IsNull(rs("UN_Nummer")) and rs("UN_Nummer") <> "0000" then Format.NamedSubStrings("UN_Nummer").Value = rs("UN_Nummer") else Format.NamedSubStrings("UN_Nummer").Value = "" end if If Not IsNull(rs("GHS_Piktogramme")) then Format.NamedSubStrings("GHS_Pictograms").Value = rs("GHS_Piktogramme") else Format.NamedSubStrings("GHS_Pictograms").Value = "" end if If Not IsNull(rs("H_Sätze")) then Format.NamedSubStrings("H_Sätze_deu").Value = rs("H_Sätze") else Format.NamedSubStrings("H_Sätze_deu").Value = "" end if If Not IsNull(rs("EUH_Codes")) then Format.NamedSubStrings("EUH_Codes_deu").Value = rs("EUH_Codes") else Format.NamedSubStrings("EUH_Codes_deu").Value = "" end if If Not IsNull(rs("EUH_Sätze")) then Format.NamedSubStrings("EUH_Sätze_deu").Value = rs("EUH_Sätze") else Format.NamedSubStrings("EUH_Sätze_deu").Value = "" end if If Not IsNull(rs("P_Sätze")) then Format.NamedSubStrings("P_Sätze_deu").Value = rs("P_Sätze") else Format.NamedSubStrings("P_Sätze_deu").Value = "" end if If Not IsNull(rs("Signalwort_Text")) then Format.NamedSubStrings("Signal_deu").Value = rs("Signalwort_Text") else Format.NamedSubStrings("Signal_deu").Value = "" end if Format.NamedSubStrings("Länge_HP-Sätze_DEU").Value = Len(Format.NamedSubStrings("H_Sätze_deu").Value) + Len(Format.NamedSubStrings("EUH_Sätze_deu").Value) + Len(Format.NamedSubStrings("P_Sätze_deu").Value) else Format.NamedSubStrings("Gefahrstoff").Value = "" Format.NamedSubStrings("CAS_Nummer").Value = "" Format.NamedSubStrings("Index_Nummer").Value = "" Format.NamedSubStrings("UN_Nummer").Value = "" Format.NamedSubStrings("GHS_Pictograms").Value = "" Format.NamedSubStrings("H_Sätze_deu").Value = "" Format.NamedSubStrings("EUH_Codes_deu").Value = "" Format.NamedSubStrings("EUH_Sätze_deu").Value = "" Format.NamedSubStrings("P_Sätze_deu").Value = "" Format.NamedSubStrings("Signal_deu").Value = "" Format.NamedSubStrings("Länge_HP-Sätze_DEU").Value = 0 end if 'Clean up rs.Close 'epos_enu.csv sql = "SELECT * FROM epos_enu.csv WHERE Materialnummer = '" & Format.NamedSubStrings("Artikelnummer").Value & "'" Set rs = conn.Execute(sql) If Not rs.EOF And Not rs.BOF Then If Not IsNull(rs("H_Sätze")) then Format.NamedSubStrings("H_Sätze_enu").Value = rs("H_Sätze") else Format.NamedSubStrings("H_Sätze_enu").Value = "" end if If Not IsNull(rs("EUH_Codes")) then Format.NamedSubStrings("EUH_Codes_enu").Value = rs("EUH_Codes") else Format.NamedSubStrings("EUH_Codes_enu").Value = "" end if If Not IsNull(rs("EUH_Sätze")) then Format.NamedSubStrings("EUH_Sätze_enu").Value = rs("EUH_Sätze") else Format.NamedSubStrings("EUH_Sätze_enu").Value = "" end if If Not IsNull(rs("P_Sätze")) then Format.NamedSubStrings("P_Sätze_enu").Value = rs("P_Sätze") else Format.NamedSubStrings("P_Sätze_enu").Value = "" end if If Not IsNull(rs("Signalwort_Text")) then Format.NamedSubStrings("Signal_enu").Value = rs("Signalwort_Text") else Format.NamedSubStrings("Signal_enu").Value = "" end if else Format.NamedSubStrings("H_Sätze_enu").Value = "" Format.NamedSubStrings("EUH_Codes_enu").Value = "" Format.NamedSubStrings("EUH_Sätze_enu").Value = "" Format.NamedSubStrings("P_Sätze_enu").Value = "" Format.NamedSubStrings("Signal_enu").Value = "" end if 'Clean up rs.Close 'epos_fra.csv sql = "SELECT * FROM epos_fra.csv WHERE Materialnummer = '" & Format.NamedSubStrings("Artikelnummer").Value & "'" Set rs = conn.Execute(sql) If Not rs.EOF And Not rs.BOF Then If Not IsNull(rs("H_Sätze")) then Format.NamedSubStrings("H_Sätze_fra").Value = rs("H_Sätze") else Format.NamedSubStrings("H_Sätze_fra").Value = "" end if If Not IsNull(rs("EUH_Codes")) then Format.NamedSubStrings("EUH_Codes_fra").Value = rs("EUH_Codes") else Format.NamedSubStrings("EUH_Codes_fra").Value = "" end if If Not IsNull(rs("EUH_Sätze")) then Format.NamedSubStrings("EUH_Sätze_fra").Value = rs("EUH_Sätze") else Format.NamedSubStrings("EUH_Sätze_fra").Value = "" end if If Not IsNull(rs("P_Sätze")) then Format.NamedSubStrings("P_Sätze_fra").Value = rs("P_Sätze") else Format.NamedSubStrings("P_Sätze_fra").Value = "" end if If Not IsNull(rs("Signalwort_Text")) then Format.NamedSubStrings("Signal_fra").Value = rs("Signalwort_Text") else Format.NamedSubStrings("Signal_fra").Value = "" end if else Format.NamedSubStrings("H_Sätze_fra").Value = "" Format.NamedSubStrings("EUH_Codes_fra").Value = "" Format.NamedSubStrings("EUH_Sätze_fra").Value = "" Format.NamedSubStrings("P_Sätze_fra").Value = "" Format.NamedSubStrings("Signal_fra").Value = "" end if 'Clean up rs.Close 'Firma.csv sql = "SELECT * FROM Firma.csv WHERE ID = '2'" Set rs = conn.Execute(sql) If Not rs.EOF And Not rs.BOF Then Format.NamedSubStrings("Firma").Value = rs("Firma").value Format.NamedSubStrings("Straße").Value = rs("Strasse").value Format.NamedSubStrings("PLZ").Value = rs("PLZ").value Format.NamedSubStrings("Ort").Value = rs("Ort").value Format.NamedSubStrings("Telefon").Value = rs("Telefon").value Format.NamedSubStrings("Fax").Value = rs("Fax").value Format.NamedSubStrings("Mail").Value = rs("E-Mail").value Format.NamedSubStrings("Webseite").Value = rs("Webseite").value else Format.NamedSubStrings("Firma").Value = "" Format.NamedSubStrings("Straße").Value = "" Format.NamedSubStrings("PLZ").Value = "" Format.NamedSubStrings("Ort").Value = "" Format.NamedSubStrings("Telefon").Value = "" Format.NamedSubStrings("Fax").Value = "" Format.NamedSubStrings("Mail").Value = "" Format.NamedSubStrings("Webseite").Value = "" end if 'Clean up rs.Close conn.Close End Function
Ok... Bear in mind that I'm a VBS noob so maybe ignore this post ^_^ I'm not seeing any obvious problems but:
- First of all: Does this performance problem also appear in more basic scenarios? Say in a scenario with minimal VBS: Just make 1 connection to a file, pull a record, assign a few values to a few data sources (can probably be done in say 15 lines total) - do you still get 4-5 seconds?
- The way I usually reference recordset fields is as follows: " rs.Fields("MyField") " I see that you apply various methods - they seem to work I guess, but then I also guess it can't hurt to try mine...
- You can probably skip most of those " If Not IsNull(... " sections: Simply assign the value and concatenate with "" at the end, e.g. " Format.NamedSubStrings("MySource").Value = rs.Fields("MyField") & "" ", thus, as far as I know, VBS will treat the result automatically as a string and you don't get type mismatch errors.
- Is there a reason you have your all your code encapsulated in functions? If I had to guess, I'd say you're just querying that one Artikelnummer probably based on user entry, which will print one label? Or do you call these functions somewhere - or what I guess I'm asking is: Does your code re-establish all connections etc. for each printed label (of which there may be many)?
Sorry for the late reply...
I just tested reading from several databases. It seems that reading from a "real" database (MS SQL Server or DB2) is much faster 1-2 seconds. It seems that the problem regarding the response time is in reading CSV files.
BR,
Konrad
Please sign in to leave a comment.