Slow Data Transfer From Recordset To Named-Datasource

Legacy Poster

• 4. Februar 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

 

0

• 

  Legacy Poster

  ★ BarTender Hero ★

  • 4. Februar 2016 14:04

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

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 4. Februar 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.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 4. Februar 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.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 5. Februar 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
  

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 5. Februar 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)?

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 19. Februar 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

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.