Log in to start a topic

About performance

Posted by: belle on 8 September 2017, 11:01 am EST

  • belle

    • Post Options:
    • Link
      Copy

    Posted 8 September 2017, 11:01 am EST

    Hello,

    I have a question about loading data. The table is with 30 columns and 757 rows and it takes more than 1 minutes to done.

    Here is the code:

    Dim ds As DataSet = New DataSet

    ds = cls.GetDataSet(strSQL)

    FpSpread1.Sheets(0).Rows.Count += TableDetail.Tables(0).Rows.Count

    For intRowCount = intPreCount To intPreCount + ds.Tables(0).Rows.Count - 1

          For intColCount = 0 To ds.Tables(0).Columns.Count - 1

                FpSpread1.Sheets(0).Cells(intRowCount, intColCount).Value = ds.Tables(0).Rows(intRowCount - intPreCount).Item(intColCount)

          Next 

    Next

    Is any suggesions to speed up? Thanks.

  • ankit-nigam

    Posted 8 September 2017, 11:01 am EST

    Hello,



    You are getting this delay may because you are assigning values to each cell individually. I suggest you to use the following code snippet to assign value to the spread:

    Dim ds As Data.DataSet = New Data.DataSet()

    Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)

    da.Fill(ds)

    FpSpread1.ActiveSheetView.DataSource = ds



    Regards,

    Ankit Nigam



  • belle

    Posted 8 September 2017, 11:01 am EST

    It works. Thanks a lot.

  • belle

    Posted 8 September 2017, 11:01 am EST

    Hello,

    I have some questions.



    First:

    FpSpread1.Columns(0).CellType = New CheckBoxCellType()

    FpSpread1.ActiveSheetView.DataSource = ds



    Second:

    FpSpread1.ActiveSheetView.DataSource = ds

    FpSpread1.Columns(0).CellType = New CheckBoxCellType()



    The First CellType doesn't work, but the second works.



    1. What's the difference between first and second?

    2. To set Format like FrozenColumnCount, Width, Label, Visible, BackColor, CellType and Formula, which way is better(before or after datasource)?

    3. FpSpread1.ActiveSheetView.Columns(2).Locked = False --> It doesn't work.

        FpSpread1.ActiveSheetView.Rows(2).Locked = False     --> It works.

        How can I set some columns to Locked=false?

    Thanks.

  • ankit-nigam

    Posted 8 September 2017, 11:01 am EST

    Hello,



    I will answer your question in the order they were asked:

    1) In the first one, default celltype of spread overlaps the CheckBoxCellType(). If you set up the celltype after binding the Spread, then it should work by default, but not if you set the celltype before. You need to set the DataAutoCellTypes property to False to be able to set the CellType property whenever you like.

    2) I suggest you to use all these property after binding.

    3) How you are using the locked property?



    Regards,

    Ankit Nigam



  • belle

    Posted 8 September 2017, 11:01 am EST

    Hello,



    After binding, it seem locked all cells. I am using locked property below.



    FpSpread1.ActiveSheetView.DataSource = ds

    FpSpread1.Columns(0).CellType = New CheckBoxCellType()

    FpSpread1.ActiveSheetView.Columns(0).Locked = False



    And I still have some questions.



    Here is my original code:

    Dim ds As DataSet = New DataSet

    ds = cls.GetDataSet(strSQL)

    FpSpread1.Sheets(0).Rows.Count = ds.Tables(0).Rows.Count

    For intRowCount = 0 To ds.Tables(0).Rows.Count - 1      

          For intColCount = 0 To ds.Tables(0).Columns.Count - 1

                FpSpread1.Sheets(0).Cells(intRowCount, intColCount).Value = xNull(ds.Tables(0).Rows(intRowCount).Item(intColCount))

          Next

          FpSpread1.Sheets(0).Cells(intRowCount, 0).Locked = False

          If xNull(ds.Tables(0).Rows(intRowCount).Item(0)) = "Y" Then

             FpSpread1.Sheets(0).Cells(intRowCount, 0).Value = "1"

          Else

             FpSpread1.Sheets(0).Cells(intRowCount, 0).Value = "0"

          End If

    Next



    To improve performance, modify the code as follow:

    Dim ds As DataSet = New DataSet

    ds = cls.GetDataSet(strSQL)

    FpSpread1.ActiveSheetView.DataSource = ds

    FpSpread1.ActiveSheetView.Columns(0).Locked = False   --->This isn't working.

    ................



    How can I check and change value of cells after binding without for-next? Any suggestions?



    Thanks for answering.

  • ankit-nigam

    Posted 8 September 2017, 11:01 am EST

    Hello,

    Could you please provide me more information about your requirements?  Do you want to lock all the columns in the spread except the first columns?

    Also you can edit the values in the spread by double clicking the cell.

     Regards,

    Ankit Nigam

  • belle

    Posted 8 September 2017, 11:01 am EST

    Hello,

    My requirements is to improve performance. I am assigning and checking values to each cell individually. You suggest me code snippet to assign value to the spread. And I find out some questions to use that. After binding, it seem to lock all the columns in the spread and I can't edit the values in the spread by double clicking the cell. How can I unlock columns after binding?



    Another question is how can I assign the checkbox's value dynamically after binding?

    Thanks for answering.

  • ankit-nigam

    Posted 8 September 2017, 11:01 am EST

    Hello,



    I am not sure why you are getting the locked columns. Could you please open the source(browser’s view source) of the render output and please check if the read-only property set for any column?



    I also suggest you check this forum to know how to set the checkbox’s value dynamically.

    http://www.clubfarpoint.com/Forums/forums/thread/46184.aspx



    Regards,

    Ankit Nigam

  • belle

    Posted 8 September 2017, 11:01 am EST

    Hello,

    I know why the columns locked. It's my mistake.

    If the value is Y/N, how can I bind to the checkbox value?

    (I know the value 0/1 can set the checkbox's value dynamically.)

    Thanks for answering.

  • belle

    Posted 8 September 2017, 11:01 am EST

    Hello,

    I provide the code snippet to help you more understand my question. Now it takes 50 second to done. Is there anything I can do to imporve performance?

    Dim ds As DataSet = New DataSet

    ds = cls.GetDataSet(strSQL)

    FpSpread1.ActiveSheetView.DataSource = ds

    For intRowCount = 0 To ds.Tables(0).Rows.Count - 1

        If xNull(ds.Tables(0).Rows(intRowCount).Item(0)) = "Y" Then

           FpSpread1.Sheets(0).Cells(intRowCount, 0).Value = "1"    'set the checkbox status to True

        Else

           FpSpread1.Sheets(0).Cells(intRowCount, 0).Value = "0"    'set the checkbox status fo False

        End If

    Next

    Thanks.

  • ankit-nigam

    Posted 8 September 2017, 11:01 am EST

    Hello,

    In this scenario I didn't find any way of improving the performance because the loop will take time depending on the number of rows you have.  However I suggest you to take a look on this forum post in which different methods of performance improvements had been discussed.

    Regards,

    Ankit Nigam

  • scotts-fp

    Posted 8 September 2017, 11:01 am EST

    Hello,

    Ankit is correct that there is not much you can do about peformance if the size of your DataSource is very large. If you are using paging, you could break up this code to only set the rows that are being shown on the page being rendered instead of the entire DataSet, Other than that, you can write your loop more effeciently using For Each instead of a For Loop.

      Dim intRowCount As Integer = 0

      For Each dr As DataRow In ds.Tables(0).Rows

       If Not dr.IsNull(0) Then

        If dr(0).ToString = "Y" Then

         FpSpread1.Sheets(0).Cells(intRowCount, 0).Value = 1   'set the checkbox status to True

        Else

         FpSpread1.Sheets(0).Cells(intRowCount, 0).Value = 0   'set the checkbox status fo False

        End If

       End If

       intRowCount += 1

      Next

  • belle

    Posted 8 September 2017, 11:01 am EST

    Ankit,

    I have seen that issue before I post this question. Thanks a lot.

     

    Scott,

    Thanks for answering. I try to compression viewstate, but it's just a little help. I will try to convince the user using paging.

    Thanks.



     

  • klgrube

    Posted 21 November 2017, 6:08 am EST

    The link in the above comment doesn’t seem to be valid, ankit-nigam. Might you be able to provide the correct link please.

    http://www.clubfarpoint.com/Forums/forums/thread/88706.aspx

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

  • © 2023 MESCIUS inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.