Matrix is slow in SBO 2004

[Former Member]

Hi

I have written a timesheet application which uses a matrix. I wrote the application in 6.5 and it works fine but it is slow to initially load the informartion (20 seconds) and slow when you tab through it. The matrix may have about 30 rows and will have up to 31 columns for the days of the month. I am using a user data source to update the fields.

I have run the program in 2004 and it still seems slow.

Can anyone give me any tips to speed it up please ?

Regards Andy

[Former Member · Accepted solution]

By adding all these columns in a xml-file and do all the binding there you would increase the speed by atleast 10... I have done your method in the past but never again..:) I had one app that took 5-7 sec's to load. Now it loads imediatly.

Regards Leif

[Former Member · Accepted solution]

Hi Leif

I normally use screen painter but this was one of my first applications.

How do you bind in the xml file and do you think I should change to DBdatasources ?

Thanks alot for your help

Regards Andy

[Former Member · Accepted solution]

You should absolutely use dbdatasources if possible. I only use userdatasources for certain items/columns which aren't stored in my application UDT's. Screenpainter is a god thing to start out with but there are a lot of things you can't do yet so you have to edit the xml file that is generated anyway...when you have done that a few times it's only a matter of cut'n paste from previous apps...now I don't use it. Here is a short example of how to add a matrix-column binded to a datasource and connected to a linked button :

<column editable="0" title="ItmCode" type="116" uid="Col4" val_off="" val_on="" visible="1" width="80">

<databind alias="ItemCode" databound="1" table="INV1"/>

<ExtendedObject linkedObject="4"/></column>

A good tip is to store a systemform (SalesOrder) as an xml-file (oForm.GetAsXML). Here you get an indication of what you can do.

Regards Leif

[Former Member · Accepted solution]

Hi Leif

I guess if you use dbdatasources the data is saved automatically when you click ok ?

Thanks alot for your help, you have been very helpful

I really appreciate your help

Regards Andy

[Former Member]

Hi,

Do all databinding and inserts of items, matrix/columns in the srf-file. That will improve your app a great deal. Try binding most of your columns to dbdatasources, not userdatasources. There should be a lot of post with examples on this matter. If you have created the timesheet-application "correctly" you should almost only use dbdatasources if you store data in UDT's.

Regards Leif

[FOA]

Hi Paul, as a reference you can check the delivered SDK sample in C:\Program Files\SAP Mangae\SAP Business One SDK\Samples\COM UI\VB.NET\06.MatrixAndDataSources

Normally is a matter of using the DBDatasources or the Userdatasource. If you still have doubts about it after check up the sample, can you post part of the code pls?

Thanks,

Felipe

[AlexGrebennikov]

Paul, a snippet of your project (or basic foundations) will help us to give you tips. Could you publish it here?

<i>Message was edited by: Alexey Grebennikov

ups.. i'm latecomer a little</i>

[Former Member]

Hi Felipe

I am already using userdatasources. Please find enclosed a portion of the code which loads the timesheet, I hope it makes sense.

Regards Andy

Set oitem = oform.Items.Add("Matrix1", it_MATRIX)

oitem.Left = 5

oitem.Width = 900

oitem.Top = 80

oitem.Height = 450

Set omatrix = oitem.Specific

Set oColumns = omatrix.Columns

'//***********************************

'// Adding Column items to the matrix

'//***********************************

Set oColumn = oColumns.Add("#", it_EDIT)

oColumn.TitleObject.Caption = " "

oColumn.Width = 120

oColumn.Editable = False

oform.DataSources.UserDataSources.Add "nc", dt_SHORT_TEXT, 30

oColumn.DataBind.SetBound True, "", "nc"

Call calc_no_days

tdate = Startdate

For lc = 1 To nodays

txtday = "day " & Str(lc)

Set oColumn = oColumns.Add(txtday, it_EDIT)

oform.DataSources.UserDataSources.Add txtday, dt_SHORT_TEXT, 8

oColumn.DataBind.SetBound True, "", txtday

oColumn.TitleObject.Caption = WeekdayName(Weekday(tdate, vbMonday), True, vbMonday) & " " & lc

oColumn.Width = 60

oColumn.Editable = True

If confirmed = True Then oColumn.Editable = False

tdate = tdate + 1

Next

' add total column

Set oColumn = oColumns.Add("total", it_EDIT)

oform.DataSources.UserDataSources.Add "total", dt_PRICE, 8

oColumn.DataBind.SetBound True, "", "total"

oColumn.TitleObject.Caption = "Total"

oColumn.Width = 60

oColumn.Editable = True

If confirmed = True Then oColumn.Editable = False

' need to store nl code

Set oColumn = oColumns.Add("glcode", it_EDIT)

oform.DataSources.UserDataSources.Add "glcode", dt_SHORT_TEXT, 30

oColumn.DataBind.SetBound True, "", "glcode"

oColumn.TitleObject.Caption = "GL Code"

oColumn.Width = 60

oColumn.Editable = False

' gltype

Set oColumn = oColumns.Add("gltype", it_EDIT)

oform.DataSources.UserDataSources.Add "gltype", dt_SHORT_TEXT, 2

oColumn.DataBind.SetBound True, "", "gltype"

oColumn.TitleObject.Caption = "GL Type"

oColumn.Width = 20

oColumn.Editable = False

' see if any gl codes have been added

Set orecset2 = oCompany2.GetBusinessObject(BoRecordset)

Set orecset = oCompany.GetBusinessObject(BoRecordset)

orecset2.DoQuery ("select acctname,acctcode from oact where fathernum = '100000000000000' and finanse = 'N' and locmantran = 'N'")

If orecset2.RecordCount > 0 Then

While Not orecset2.EOF

glcode = orecset2.Fields(1).Value

gldesc = orecset2.Fields(0).Value

orecset.DoQuery ("select * from [@tsr] where u_ts = '" & ts & "' and u_contrno = '" & glcode & "'")

If orecset.RecordCount = 0 Then

' add record

' calc new rec id

orecset.DoQuery ("select max(convert(int,code)) from [@tsr]")

recid = orecset(0).Value

recid = recid + 1

orecset.DoQuery ("insert into [@tsr] (code,name,u_ts,u_contrno,u_contrde,u_d1,u_d2,u_d3,u_d4,u_d5,u_d6,u_d7,u_d8,u_d9,u_d10,u_d11,u_d12,u_d13,u_d14,u_d15,u_d16,u_d17,u_d18,u_d19,u_d20,u_D21,u_d22,u_d23,u_d24,u_d25,u_d26,u_d27,u_d28,u_d29,u_d30,u_d31,u_gltype) values (" & recid & "," & recid & ",'" & ts & "','" & glcode & "','" & gldesc & "',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'1')")

End If

orecset2.MoveNext

Wend

End If

orecset.DoQuery ("select * from [@tsr] where u_ts = '" & ts & "'")

If orecset.RecordCount > 0 Then

While Not orecset.EOF

oform.DataSources.UserDataSources(0).Value = Left(orecset.Fields("u_contrde").Value, 30)

tot = 0

For lc = 1 To nodays

'oform.DataSources.UserDataSources(lc).Value = Left(orecset(lc + 3).Value, 30)

tfield = "u_d" & Trim(Str(lc))

If Left(orecset(tfield).Value, 30) <> "0" Then

oform.DataSources.UserDataSources(lc).Value = Left(orecset(tfield).Value, 30)

Else

oform.DataSources.UserDataSources(lc).Value = ""

End If

' tried to speed up this area of the code, not sure how fast it was before

' ab mod ref speed If oform.DataSources.UserDataSources(lc).Value = "0" Then oform.DataSources.UserDataSources(lc).Value = ""

tot = tot + Left(orecset(lc + 3).Value, 30)

ctot(lc) = ctot(lc) + orecset(lc + 3).Value

Next lc

' set total column

oform.DataSources.UserDataSources("Total").Value = tot

' set nom code

oform.DataSources.UserDataSources("glcode").Value = orecset.Fields("u_contrno").Value

oform.DataSources.UserDataSources("gltype").Value = orecset.Fields("u_gltype").Value

omatrix.AddRow

orecset.MoveNext

Wend

End If

[AlexGrebennikov]

Paul, could you please put your snippet within CODE-tag.

It's not possible to parse it well for our eyes.

[Former Member]

Hi Alexey

Sorry about that

Hope this is better

Regards Andy

Set oitem = oform.Items.Add("Matrix1", it_MATRIX)
oitem.Left = 5
oitem.Width = 900
oitem.Top = 80
oitem.Height = 450

Set omatrix = oitem.Specific
Set oColumns = omatrix.Columns

'//***********************************
'// Adding Column items to the matrix
'//***********************************

Set oColumn = oColumns.Add("#", it_EDIT)
oColumn.TitleObject.Caption = " "
oColumn.Width = 120
oColumn.Editable = False
oform.DataSources.UserDataSources.Add "nc", dt_SHORT_TEXT, 30
oColumn.DataBind.SetBound True, "", "nc"


Call calc_no_days
tdate = Startdate

    For lc = 1 To nodays
    
        txtday = "day " & Str(lc)
        Set oColumn = oColumns.Add(txtday, it_EDIT)
        oform.DataSources.UserDataSources.Add txtday, dt_SHORT_TEXT, 8
        oColumn.DataBind.SetBound True, "", txtday
        oColumn.TitleObject.Caption = WeekdayName(Weekday(tdate, vbMonday), True, vbMonday) & " " & lc
        oColumn.Width = 60
        oColumn.Editable = True
        If confirmed = True Then oColumn.Editable = False
        tdate = tdate + 1
         
    Next
    
    ' add total column
    Set oColumn = oColumns.Add("total", it_EDIT)
    oform.DataSources.UserDataSources.Add "total", dt_PRICE, 8
    oColumn.DataBind.SetBound True, "", "total"
    oColumn.TitleObject.Caption = "Total"
    oColumn.Width = 60
    oColumn.Editable = True
    If confirmed = True Then oColumn.Editable = False
    
    ' need to store nl code
    Set oColumn = oColumns.Add("glcode", it_EDIT)
    oform.DataSources.UserDataSources.Add "glcode", dt_SHORT_TEXT, 30
    oColumn.DataBind.SetBound True, "", "glcode"
    oColumn.TitleObject.Caption = "GL Code"
    oColumn.Width = 60
    oColumn.Editable = False
    
    ' gltype
    Set oColumn = oColumns.Add("gltype", it_EDIT)
    oform.DataSources.UserDataSources.Add "gltype", dt_SHORT_TEXT, 2
    oColumn.DataBind.SetBound True, "", "gltype"
    oColumn.TitleObject.Caption = "GL Type"
    oColumn.Width = 20
    oColumn.Editable = False
    
       
    ' see if any gl codes have been added
    Set orecset2 = oCompany2.GetBusinessObject(BoRecordset)
    Set orecset = oCompany.GetBusinessObject(BoRecordset)
    
    orecset2.DoQuery ("select acctname,acctcode from oact where fathernum = '100000000000000' and finanse = 'N' and locmantran = 'N'")
    If orecset2.RecordCount > 0 Then
    
      While Not orecset2.EOF
        glcode = orecset2.Fields(1).Value
        gldesc = orecset2.Fields(0).Value
        orecset.DoQuery ("select * from [@tsr] where u_ts = '" & ts & "' and u_contrno = '" & glcode & "'")
        If orecset.RecordCount = 0 Then
          ' add record
          ' calc new rec id
          orecset.DoQuery ("select max(convert(int,code)) from [@tsr]")
          recid = orecset(0).Value
          recid = recid + 1
          orecset.DoQuery ("insert into [@tsr] (code,name,u_ts,u_contrno,u_contrde,u_d1,u_d2,u_d3,u_d4,u_d5,u_d6,u_d7,u_d8,u_d9,u_d10,u_d11,u_d12,u_d13,u_d14,u_d15,u_d16,u_d17,u_d18,u_d19,u_d20,u_D21,u_d22,u_d23,u_d24,u_d25,u_d26,u_d27,u_d28,u_d29,u_d30,u_d31,u_gltype) values (" & recid & "," & recid & ",'" & ts & "','" & glcode & "','" & gldesc & "',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'1')")
        End If
        orecset2.MoveNext
      Wend

    End If


    orecset.DoQuery ("select * from [@tsr] where u_ts = '" & ts & "'")
    If orecset.RecordCount > 0 Then
      While Not orecset.EOF
        oform.DataSources.UserDataSources(0).Value = Left(orecset.Fields("u_contrde").Value, 30)
        tot = 0
        For lc = 1 To nodays
          'oform.DataSources.UserDataSources(lc).Value = Left(orecset(lc + 3).Value, 30)
          tfield = "u_d" & Trim(Str(lc))
          If Left(orecset(tfield).Value, 30) <> "0" Then
            oform.DataSources.UserDataSources(lc).Value = Left(orecset(tfield).Value, 30)
          Else
            oform.DataSources.UserDataSources(lc).Value = ""
          End If
          ' tried to speed up this area of the code, not sure how fast it was before
          ' ab mod ref speed If oform.DataSources.UserDataSources(lc).Value = "0" Then oform.DataSources.UserDataSources(lc).Value = ""
          tot = tot + Left(orecset(lc + 3).Value, 30)
          ctot(lc) = ctot(lc) + orecset(lc + 3).Value
        Next lc
        ' set total column
        oform.DataSources.UserDataSources("Total").Value = tot
        ' set nom code
        oform.DataSources.UserDataSources("glcode").Value = orecset.Fields("u_contrno").Value
        oform.DataSources.UserDataSources("gltype").Value = orecset.Fields("u_gltype").Value
        omatrix.AddRow
        orecset.MoveNext
      Wend
    End If