Speedieest way to set a group of fields

HCL Domino Domino Forum
1

Hi all,I’ve some data gathered via ODBC in an fields array of 25 elements.

With a for loop I write these data in 25 row of fields.

But the speed of this write is so slow that I can ,litterally, see the row being write a field a time!!!

There is a way to speed up this process?

The code that I use is the follow:

Function dataPump(pos As Integer, posOffset As Integer, uidoc As NotesUIDocument) As Boolean

dataPump= True

On Error Goto errore

Dim i As Integer

Dim si As String



For  i = 1 To posOffset

	result.CurrentRow = pos+ i

	si= Cstr(i)

	Call uidoc.FieldSetText("fId_"+si, result.GetValue("No_"))

	Call uidoc.FieldSetText("fName_"+si, result.GetValue("Name"))

	Call uidoc.FieldSetText("fAddress_"+si, result.GetValue("Address"))

	Call uidoc.FieldSetText("fCity_"+si, result.GetValue("City"))

	Call uidoc.FieldSetText("fCounty_"+si, result.GetValue("County"))

	Call uidoc.FieldSetText("fZip_"+si, result.GetValue("Zip"))

’ Call uidoc.FieldSetText(“fCountry_”+si, result.GetValue(“Country”))

	Call uidoc.FieldSetText("fLang_"+si, result.GetValue("Language"))

’ Call doc.ReplaceItemValue(“fTel_”+Cstr(i), result.GetValue(“Phone”))

’ Call doc.ReplaceItemValue(“fMobile_”+Cstr(i), result.GetValue(“Mobile”))

Next



Exit Function

errore:

Messagebox "Error" & Str(Err) & ": " & Error$

dataPump= False

Exit Function

End Function

2

Subject: Speedieest way to set a group of fields

I’d guess that the speed slowdown is more in the retrieval from the resultSet.

Perhaps someone more knowledgeable can comment on the relative speed of using CurrentRow via NextRow, and maybe getValue(num) versus getValue(name$).

3

Subject: Attempt updates to the backend doc…

In addition to others comments about your ODBC query, I’d suggest moving all of your document updates to the back end NotesDocument object. UI methods tend to be slower and each call of uidoc.FieldSetText causes the screen to re-paint.

I’d also disable the AutoReload of the uidocument.

i.e. uidoc.AutoReload = false

loop here and do set all necessary fields using a NotesDocument object

then turn AutoReload back on with uidoc.AutoReload = true. You may also have to use uidoc.Reload().

Doing that will give you relatively nice improvement in speed since it’s all done in memory with only one repaint of the screen. This is especially useful when you potentially have lots of document updates (i.e. updating a table as you appear to be doing).

4

Subject: RE: Attempt updates to the backend doc…

Many thanks to all who responded to my queries.Using all the tips you gave me I’ve changed my code to using just backend class and disabled autorefresh in the form by code. Now it redraw at a very acceptable pace!

Mybe my code can be optimized:

  • using result.GetValue() where number is the field position in the query that you whant get the data from. I’m using a view shared between some apps on an external rdbms and don’t want take this route in the case of a view modification.

  • If there is something faster that ReplaceItemValue this ca be used to get some more speed.

For the one interessed the draw code is here:

Function dataPump(pos As Integer, posOffset As Integer, uidoc As NotesuiDocument) As Boolean

dataPump= True

On Error Goto errore



Dim i As Integer

Dim si As String

Set doc= ws.CurrentDocument.Document



uidoc.AutoReload= False

For  i = 1 To posOffset

	result.CurrentRow = pos+ i

	si= Cstr(i)

	Call doc.ReplaceItemValue("fId_"+si, result.GetValue("No_"))

	Call doc.ReplaceItemValue("fName_"+si, result.GetValue("Name"))

	Call doc.ReplaceItemValue("fAddress_"+si, result.GetValue("Address"))

	Call doc.ReplaceItemValue("fCity_"+si, result.GetValue("City"))

	Call doc.ReplaceItemValue("fCounty_"+si, result.GetValue("County"))

	Call doc.ReplaceItemValue("fZip_"+si, result.GetValue("Zip"))

	Call doc.ReplaceItemValue("fCountry_"+si, result.GetValue("Country"))

	Call doc.ReplaceItemValue("fLang_"+si, result.GetValue("Language"))

’ Call doc.ReplaceItemValue(“fTel_”+si, result.GetValue(“Phone”))

’ Call doc.ReplaceItemValue(“fMobile_”++si, result.GetValue(“Mobile”))

Next

Call uidoc.Reload()

uidoc.AutoReload= True

Exit Function

errore:

Messagebox "Error" & Str(Err) & ": " & Error$

dataPump= False

Exit Function

End Function

5

Subject: RE: Attempt updates to the backend doc…

ReplaceItemValue is about as fast as you’re going to get in terms of updating the NotesDocument object. I haven’t done any testing on the GetValue method for your return set but I would imagine that’s about as fast an access method as you’ll get once you have your result set back.

6

Subject: ODBC is very, very slow.

  • Or at least with Notes it is.- We had a snapshot report that used ODBC to collate the data, and it took seven minutes to run. Since it was generated on-the-fly from a click on a web page, that wasn’t acceptable.

  • So I rewrote it using a simple View traversal, with a LotusScript object model to collate the data how we wanted it, which was not by any criteria Notes natively supported. This new system ran the same 20,000 document report in four seconds.

  • If you can possibly avoid ODBC, I recommend you do so. Hope this helps…

7

Subject: RE: ODBC is very, very slow.

Do you mean NotesSQL?

8

Subject: RE: ODBC is very, very slow.

I’m using NotesSql for the ODBC conenction

9

Subject: RE: ODBC is very, very slow.

  • Could have been using NotesSQL. It was many years ago, and the first object-oriented LotusScript I ever wrote. (grin) I completely scrapped any and all ODBC and ran it fully “native” … just LotusScript. The speed difference was astounding. It really is amazing how quickly Domino is able to fetch data, considering it’s not relational, and it has a very open data storage model.- Naturally this was also an entirely back-end process, since it was generating a web page…
10

Subject: RE: ODBC is very, very slow.

Hi, I’ve tryed to use the lsxlc connection to get the data. But was stuck by the impossibility to navigate to row x of the resultset without navigate thougth all the previous data. If there is a way to do a: lc_resultset.gotoRow(x)I can avoid alltogheder the odbc.

11

Subject: Speedieest way to set a group of fields

I’ve noticed that some pictures, especially if they are placed in the same table as updated fields, can cause very slow update speed. Try to run same code on a clean form with only fields and nothing else. Does it work better if there are only 10 fields?Check also that “Automatically refresh fields” form property is not enabled.

12

Subject: RE: Speedieest way to set a group of fields

Hi, the data is inserted in a table with yust 5 button (getdata, (top) next, (top) previous, (botton) next, (botton) previous.I’ve started with a table with 50 row and the speed od the iser was 2 time that of the table with 25 row…

13

Subject: Speedieest way to set a group of fields

Question.

Can you use an agent to build the form containing this information without the user having to get the information?

Example:

If( connect.execute(|select EMP_NUMBER, EMP_FIRST_NAME, EMP_MIDDLE_NAME,EMP_LAST_NAME, EMP_SSN, DEPARTMENT, EMP_DOB, BENEFIT_DATE_3, EMP_ADDRESS, EMP_CITY, EMP_STATE, EMP_ZIP, EMP_PHONE, EMP_GENDER,ANNUAL_SALARY

from LAWSON.Benefit_PreBenefit_View;|, fldLst) = 0) Then

Print "no records were fetched"

Else

Set fld =  fldlst.GetField(1) 



While(connect.Fetch(fldLst) > 0) 

	'Create a doc for each person in Oracle

	

	Dim success As Variant

	Set db = s.CurrentDatabase

	Set doc = db.CreateDocument		

	doc.Form = "edata"		

	

	

	'set Notes field values to equal Oracle info

	doc.enumber = fldlst.Emp_Number(0)

	doc.firstname = fldlst.Emp_First_Name(0)

	doc.Middle = fldlst.Emp_Middle_Name(0)

	doc.lastname = fldlst.Emp_Last_Name(0)

	doc.edepartment = fldlst.Department(0)	

	doc.edob = fldlst.Emp_DOB(0)

	doc.essnumber = fldlst.Emp_SSN(0)
14

Subject: RE: Speedieest way to set a group of fields

Can you use an agent to build the form containing this information without the user having to get the information?

Sadly no. The data is showed in a “view like” form where the user must schoose a customer from the customer table of own erp software. Then, with these customer at the phone, must insert some crm data…

Before you ask, I’ve tryed a LEI sync with this table to use a more easy to handle view. But the Lei direct transfer operation used 47h to get all the data!!! and the following sync doesn’t seemed to be more quick… :frowning: