Slow Data Transfer From Recordset To Named-Datasource Follow

Legacy Poster

February 04, 2016 13:50

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

February 04, 2016 14:04

4-5 seconds each? Seems like something's not right...

Legacy Poster

February 04, 2016 14:47

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.

Legacy Poster

February 04, 2016 18:27

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.

Legacy Poster

February 05, 2016 15:24

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

Legacy Poster

February 05, 2016 19:08

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)?

Legacy Poster

February 19, 2016 13:00

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.