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
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.
